In November of last year, CoServ Electric contracted SSP for an installation and configuration of Schneider Electric’s Responder™ software in a development environment. We were hoping to learn the ins and outs of the installation process, the gotcha’s that might occur and gain insight on how to properly administer the system.

Jeff Mertz was assigned to the task and was onsite for the better part of a week. At the end of the week, and after a successful development implementation, I had a chance to show Jeff a few of the Python geoprocessing scripts that I had created for various processes and automated functions. I explained to him that I was interested in being able to reuse some code in my scripts to save time and make the code more efficient.

Armed with a moderate dose of Object Oriented Programming knowledge already, I was interested in beefing up my procedural scripts by giving it some OOP power. Also, being an avid reader of the SSP newsletter, I was aware of Jeff’s prowess as a programmer too (see May 2012’s “Fixing Your Data Sources with Python” blog post). So I wanted to take advantage of the time I had and ask him for a few pointers.

Python online help from Esri is strictly procedural in nature and does not focus at all on enhancing your code beyond that, and I wanted to know how to create a simple Python module that accepts a parameter or two, and subsequently call that module from another script. Jeff took a few minutes and left me with a simple shell that I was able expand upon and use in many of my Python scripts.

Most of the geoprocessing tasks that we utilize first need to make an SDE connection in order to gain access to the data. This is done quite easily in Python by importing the ArcPy module and using the CreateSDEConnectionFile_management method. However, this method accepts eleven parameters of which four are required and because CoServ uses an Oracle database with database authentication in order to access the SDE, just about all of the “optional” parameters are required in order to use this method.

Below is an example of all of the instantiated variables and how the tool was called passing those variables.

# Set variables for .sde file
folderName = “C:\Users\abelschner\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog”
fileName = “Name of Connection File.sde”
serverName = “GIS_Server”
serviceName = “sde:oracle11g:/;LOCAL= GISDevelopment ”
databaseName = “”
authType = “”
username = “abelschner”
password = “123456789”
saveUserInfo = “”
versionName = “SDE.DEFAULT”
saveVersionInfo = “SAVE_VERSION”

# create new SDE connection to default
arcpy.CreateArcSDEConnectionFile_management(folderName, fileName, serverName, serviceName, databaseName, authType, username, password, saveUserInfo, versionName, saveVersionInfo)

This option worked and successfully created a connection to SDE.Default but it had to be added at the beginning of every script. Also the username for the roaming profile (used for SDE connection file locations) was hard-coded into the code, which is fine as long as that user is still in the user profile on the machine the script is running on.

But what if that user profile was not on the machine and someone other than the author of the script wants to use it? I wanted to address this issue as well. So I took the basic example Jeff had left me, and created an entire library of SDE connection modules for the most common username and databases that we connect to for geoprocessing and automation.

Below is one example of that:

import arcpy
import os
import datetime
import getpass

# Global Variables
today = datetime.date.today()
userName = getpass.getuser()

# Electric SDE Connection to GISDevelopment
def ElectricCnctGISDevelopment(fileName):
errorLogFileName = “ElectricConnectionGISDevelopment.txt”

if len(fileName) > 0:
# Set vaiables for .sde file
folderName = “C:\\Users\\” + username + “\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\”
serverName = “GIS_Server”
serviceName = “sde:oracle11g:/;LOCAL=GISDevelopment”
databaseName = “”
authType = “”
username = “Electric”
password = “123456789”
saveUserInfo = “”
versionName = “SDE.DEFAULT”
saveVersionInfo = “SAVE_VERSION”

# Create SDE connection to default
try:
arcpy.CreateArcSDEConnectionFile_management(folderName, fileName, serverName, serviceName, databaseName, authType, username, password,  saveUserInfo, versionName, saveVersionInfo)
except:
CantCreateSDEFile(errorLogFileName)

else:
SDEfileNameError(errorLogFileName)

Return

Now this can be done a number of different ways and I could have had as many or as few parameters as I liked for this method. I chose to only accept the name of the SDE connection file as an argument and to put the database username and password inside the method. I could have left that to be written and passed every time the method is called, which could be helpful if database passwords change often. But however you decide to create the method is fine.

This script also checks to make sure the connection string name that I pass is at least 1 character in length and will create an error log file if it is not. It will also create an error log if for some reason a connection to the database cannot be made.

Also, I imported the Python getpass module and used its getuser() method to get the current Windows AD user of the machine and will pass that string variable to the script for the roaming profile SDE connection file. This takes care of the roaming profile issue by placing the SDE connection file in the current user folder for Arc Catalog.

I repeated this format for a multitude of database schema users, naming each method something that I easily recognize as the user and the database I want to connect to. I now do not have to create 11 variables and pass each one of them every time I need to make a connection to the SDE.

I also do not have to remember the usernames and passwords for each connection either. For example, a method to connect as an Electric schema user to the development database would be named ElectricCnctGISDevelopment. A gas connection to a test database might be name GasCnctGISTest or something similar. The passwords and databases inside the method would be different but the rest of the method is basically the same.

I also wrote a method to delete the connection file and that can be used once the script is complete as to not leave garbage connection files cluttering up any roaming profiles.

So now I have a reusable method that I can call at the beginning of every geoprocessing script in which an SDE connection must be made. The beauty is when I create a new script all I have to do is import the file which I named SDE_Connection, and all of the methods are available to me.

I then select the user and database I want to connect to, pass the filename variable as an argument to the method, (SDE_Connections.ElectricCnctGISDevelopment(filename)) and the rest is Python magic.

If you are wanting even further validation of the success of the script, you can also open ArcCatalog to see your newly created SDE connection if you so desire.

This basic OOP principle has helped beef up the geoprocessing scripts that CoServ utilizes as part of its daily work flows and processes. The same basic principles could be used for any code that you find yourself using repeatedly. It will help save time and keep your script shorter too.

Andrew Belschner is a Senior GIS Analyst at CoServ Electric in Corinth, TX