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!
