Community

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

4458
6
07-16-2015 05:16 PM
Labels (1)
Labels
ForrestJones
by Esri Contributor
Esri Contributor
4 6 4,458
‎07-16-2015 05:16 PM

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!

Labels
6 Comments
Labels