How to create enterprise geodatabase python scripts that accept connection parameters similar to the sde command line.

[ForrestJones]

With ArcGIS, to administer an enterprise geodatabase, you need to utilize the catalog window in ArcGIS or the many geoprocessing tools. They operate through the use of a database connection file (.sde file). The connection file can also be shared and used for scripting. This works great and is easy to manage if you do all your administration through ArcCatalog/ArcMap or the geoprocessing window.

However, what if you have many databases to manage, want to use Linux, want to script tasks, or just don't want to have to deal with creating connection files all the time? With the old sde command line this was very easy, no .sde files to manage, just pass in the connection parameters and away you go.

Well, here's some good news! You can easily mimic the same behavior using some simple python concepts along with the powerful arcpy geoprocessing scripting environment. The sample code is available here if you want dig in immediately!

The example below works on Linux and Windows and uses the concepts detailed in the ArcGIS geoprocessing tools help to create a database connection file and then loads data from a specified directory to the enterprise geodatabase. This can easily be modified to run other tools or to do other tasks. I'll highlight the key pieces of the script.

First, the usage.

You pass in the connection properties to a database and a location to where the data is you would like to load:

Examples:
   
(Linux)  
/tmp>loaddata_sde_params.py --DBMS ORACLE -i myserver/orcl -u user1 -p user1 --dataloc /net/sharedata/location/data  
  
(Windows)  
c:\tmp>loaddata_sde_params.py --DBMS ORACLE -i myserver/orcl -u user1 -p user1 --dataloc \\sharedata\location\data  
    
>loaddata_sde_params.py --help  
  
Usage: loaddata_sde_params.py [Options]  
  
Options:  
  --version             show program's version number and exit  
  -h, --help            show this help message and exit  
  --DBMS=DATABASE_TYPE  Type of enterprise DBMS: SQLSERVER, ORACLE, or  
                        POSTGRESQL.  
  -i INSTANCE           DBMS instance name  
  --auth=ACCOUNT_AUTHENTICATION  
                        Authentication type options (case-sensitive):  
                        DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  
                        Default=DATABASE_AUTH  
  -u USER               user name  
  -p PASSWORD           password  
  --dataloc=DATALOCATION  
                        Path to the data (either a geodatabase or a directory  
                        with shapefiles  
  -D DATABASE           Database name (Not required for Oracle)  

Let's break it down a bit more...

The core component is the ability to accept parameters similar to the old command line, parse them correctly, and then automate the creation of the database connection file (It uses some code from the Upgrade Geodatabase (Data Management) help documentation).

The parser used is in the python module "optparse". This first defines the program, and then all the options:

# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 2.0; valid for 10.1+ only")

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', 'DB2','INFORMIX','DB2ZOS',''], default="", help="Type of enterprise DBMS: SQLSERVER, ORACLE, or POSTGRESQL.")
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("--auth", dest="account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-u", dest="User", type="string", default="", help="user name")
parser.add_option ("-p", dest="Password", type="string", default="", help="password")
parser.add_option ("--dataloc", dest="DataLocation", type="string", default="", help="Path to the data (either a geodatabase or a directory with shapefiles")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name (Not required for Oracle)")

Then we need to assign values to local variables from the options that get passed in:

#Usage parameters for spatial database connection.
account_authentication = options.account_authentication.upper()
username = options.User.lower()
password = options.Password
dataloc = options.DataLocation
database = options.Database.lower()
database_type = options.Database_type.upper()
instance = options.Instance

Next, we need to do some basic checks and find a location to put the .sde connection file. On Windows, the path to files uses backslashes, whereas on Linux, forward slashes are needed.

# Local variables
instance_temp = instance.replace("\\","_")
instance_temp = instance_temp.replace("/","_")
instance_temp = instance_temp.replace(":","_")
Conn_File_NameT = instance_temp + "_" + database + "_" + username

if os.environ.get("TEMP") == None:
    temp = "c:\\temp"
else:
    temp = os.environ.get("TEMP")
if os.environ.get("TMP") == None:
    temp = "/usr/tmp"
else:
    temp = os.environ.get("TMP")

Connection_File_Name = Conn_File_NameT + ".sde"
Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"

Create the connection file:

print "\nCreating Database Connection File...\n"
# Process: Create Database Connection File...
# Usage:  out_file_location, out_file_name, DBMS_TYPE, instance, database, account_authentication, username, password,  save_username_password(must be true)
outFile = arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=username, password=password, save_user_pass="TRUE")

Then the connection file can be used to run any other task or set of tasks. Here it loads data using the FeatureClassToGeodatabase tool:

# Set environment settings
# Set the workspace to the specified data location
env.workspace = dataloc

# Get all the feature classes in the environment
# list of fc's should be similar to this: ["accident.shp", "veg.shp"]
inFeatures = arcpy.ListFeatureClasses()

# Process: Load Data (Use the "Feature Class To Geodatabase" Tool...)
try:
    # Execute FeatureClassToGeodatabase
    print "Loading data...\n"
    arcpy.FeatureClassToGeodatabase_conversion(inFeatures, outLocation)

except:
    for i in range(arcpy.GetMessageCount()):
        arcpy.AddReturnMessage(i)

Full code is available here.

Enjoy!

[George_Thompson]

Managing Data​ Enterprise GIS​ Python​ Geoprocessing​

[BlakeTerhune]

I was just looking around for something like this! I was starting to develop my own similar solution but I was looking at using the tempfile module to automatically create, name, and delete the connection file. Any suggestions there?

[ForrestJones]

Hi Blake, I've never used the tempfile module, but from a brief look it seems like it might work as long as you can use the temp file in the arcpy functions. That would definitely make the management of the file even more optimized and easier.

[BlakeTerhune]

I simplified your code with the parser option parameters and such just for this example, but this is what I came up with for creating temporary connection files that will get cleaned up when finished. I've tested this a bit and it seems to work. On Win7, the temp folder gets created in C:\Users\Username\AppData\Local\Temp

import arcpy

from contextlib import contextmanager

import os

import shutil

import tempfile

def main():

    try:

        with makeTempDir() as temp_dir:

            tempConn = arcpy.CreateDatabaseConnection_management(

                temp_dir,  ## out_folder_path

                "tempConn.sde",  ## out_name

                "ORACLE",  ## database_platform

                "GISTEST.WORLD",  ## instance

                "DATABASE_AUTH",  ## account_authentication

                "myuser",  ## username

                "myuserpass",  ## password

            )

            print arcpy.GetMessages()

            tempConn = str(tempConn)  ## Format result object as string for path to connection file

            # Print workspace properties

            desc = arcpy.Describe(tempConn)

            cp = desc.connectionProperties

            print("User: ", cp.user)

            print("Workspace Type:", desc.workspaceType)

            print("WorkspaceFactoryProgID:", desc.workspaceFactoryProgID)

    except Exception as err:

        print err

    finally:

        # Cleanup

        arcpy.ClearWorkspaceCache_management()

@contextmanager

def makeTempDir():

    """Creates a temporary folder and returns the full path name.

    Use in with statement to delete the folder and all contents on exit.

    Requires contextlib contextmanager, shutil, and tempfile modules.

    """

    temp_dir = tempfile.mkdtemp()

    try:

        yield temp_dir

    finally:

        shutil.rmtree(temp_dir)

if __name__ == '__main__':

    main()

[ForrestJones]

That is great that it works for Windows. It should probably also work for Linux. If I get a chance I'll see if it could be used to optimize the script a bit.

[BlakeTerhune]

I just fixed a little bug with my code. The tempConn variable I'm assigning to arcpy.CreateDatabaseConnection_management() is actually a result object (even though it prints the full path to the connection file). Although it works with describe workspace, it fails when trying to use it in os.path.join() with RuntimeError: ResultObject: Error in getting output. You just need to format it as a string with tempConn = str(tempConn)

I'll update the code in my original post.

I also managed to use tempfile.NamedTemporaryFile() to generate a unique file name to use:

with tempfile.NamedTemporaryFile(dir=tempDir, delete=True)as temporaryFile:

    tempFileDir, tempFileName = os.path.split(temporaryFile.name)

Then just concatenate the tempFileName with the ".sde" extension when you create the connection. Alternatively, you could also specify the suffix parameter when you create the file name, but I decided to keep it separate.