Use ArcPy to Connect to SDE and Versions

This post will discuss how to use ArcPy to Connect to SDE, create a new version, and switch to that version all inside a Python script. At first glance this should be pretty straight forward - provide some connection details and a version name. Well, ArcPy isn’t really designed for that approach. There currently isn’t an ArcPy function to connect to SDE within code, or to deal with new/changing versions. The current documented approach is to use Toolbox tools or create the version in advance, create a connection file pointing to a version, and use this as a reference in a script.

There are many reasons why the documented approach might not meet our needs: We want everything inside one script without any related files required; We don’t know the new version name in advance; We want to run on Linux (ArcGIS server); Or we simply just want the option to use a single script for our scripty stuff.

Creating a SDE connection file in ArcPy

At this point, it might be easier to open ArcCatalog, create the SDE connection file, and point your script to it. But if you want the “everything we need is in this script” approach, how far can we go? Well, not too far unless we are willing to use some Toolbox tools along the way.

Creating the SDE connection file isn’t a problem since there is a toolbox tool to create one. Inside our Python script we can call the CreateDatabaseConnection_management tool directly and save the connection file for later use.

CreateDatabaseConnection_management(out_folder_path, out_name, 
    database_platform, instance, {account_authentication},
    {username}, {password}, {save_user_pass}, {database},
    {schema}, {version_type}, {version}, {date})

We are going to use Windows Authentication so we don’t need to expose any user credentials for now. When creating the connection, we need to supply a few parameters that impact our script:

‘version’: The name we specify here must already exist. In this example, we will use the Default version.
An output folder path (directory) for the file that is created. If on a server, there is a chance we aren’t sure where to save, or the script directory is read-only, and saving to c:\temp makes some admins a little nervous. Hmmmm, where to put the file?

What if we used the ArcPy “inMemory” space for the output? This would meet our needs, but inMemory doesn’t currently support this technique. Thankfully there is a great module called “tempfile” that comes included with core Python to handle this much the same as inMemory. The tempfile module generates temporary files and directories. It also works on all Python supported platforms.

tempfile.mkdtemp([suffix=''[, prefix='tmp'[, dir=None]]])

Within the tempfile module, there are commands for both directories and files that we can use much like the inMemory feature. The directory will be secure and accessible by the account running the script, just make sure to cleanup the temp directory when you are done.

sdeTempPath = tempfile.mkdtemp()
arcpy.CreateDatabaseConnection_management(sdeTempPath,
  'ConnName.sde','SQL_SERVER','db\\instance',
  'OPERATING_SYSTEM_AUTH','#', '#', '#','Database')

Creates a temporary directory in the most secure manner possible. There are no race conditions in the directory’s creation. The directory is readable, writable, and searchable only by the creating user ID. The user of mkdtemp() is responsible for deleting the temporary directory and its contents when done with it. ~Python Software Foundation, Overview of Tempfile

Create a new version in ArcPy

Toolbox is again required to create a version, using the CreateVersion_management tool.

CreateVersion_management (in_workspace, parent_version,
  version_name, {access_permission})

When creating a version in a DB like SQL Server, it will prefix the version name with the name of the user - even though we didn’t supply ours directly, it was determined when we created the connection file with reference to the current Windows Account. So before we can use the version, we will need to find it. Side note: We are assuming the version doesn’t already exist - you might want to check before creating.

arcpy.CreateVersion_management(sdeTempPath + os.sep +
  'ConnName.sde', 'sde.DEFAULT', 
  sdeVersionName, 'PUBLIC')

Using the new version

To continue complicating things, we can’t just switch our newly created version in the connection we already created. The only way to reference a version in ArcPy is to use a connection file that already points to that version. A little redundant, but good news is that we just learned how to create a SDE connection file, so we just need to find the full version name (user.version) and create a connection one more time with the new version name included.

The Code

Here is a script that will use ArcPy to Connect to SDE, create a new version, and switch to that version all inside a Python (ArcPy) script. This is just a sample and it is recommended to add more error handling, trap for correct licenses, check if the version exists, etc.