Define Workspace for SDE Connection in Python

Question

How do we define SDE Connection for workspace in Python Scripting?

• Erica: possible duplicate of How does one access a featurelayer in SDE via Python?

Answer 1

Examples 3 through 5 on this page are amazing for this problem: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//0017000000q7000000

Here is a simplified version I made that lets me do on the fly connections in python using Sql Server direct connect only.

"""
Name: sdeconn.py
Description: Utility functions for sde connections
"""
# Import system modules
import arcpy, os, sys
def connect(database, server="<default server>", username="<default user>", password="<default password>", version="SDE.DEFAULT"):
 # Check if value entered for option
 try:
 #Usage parameters for spatial database connection to upgrade
 service = "sde:sqlserver:" + server 
 account_authentication = 'DATABASE_AUTH'
 version = version.upper()
 database = database.lower()
 # Check if direct connection
 if service.find(":") <> -1: #This is direct connect
 ServiceConnFileName = service.replace(":", "")
 ServiceConnFileName = ServiceConnFileName.replace(";", "")
 ServiceConnFileName = ServiceConnFileName.replace("=", "")
 ServiceConnFileName = ServiceConnFileName.replace("/", "")
 ServiceConnFileName = ServiceConnFileName.replace("\\", "")
 else:
 arcpy.AddMessage("\n+++++++++")
 arcpy.AddMessage("Exiting!!")
 arcpy.AddMessage("+++++++++")
 sys.exit("\nSyntax for a direct connection in the Service parameter is required for geodatabase upgrade.")
 # Local variables
 Conn_File_NameT = server + "_" + ServiceConnFileName + "_" + 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 = temp + os.sep + Conn_File_NameT + ".sde"
 if os.path.isfile(Connection_File_Name):
 return Connection_File_Name
 # Check for the .sde file and delete it if present
 arcpy.env.overwriteOutput=True
 # Variables defined within the script; other variable options commented out at the end of the line
 saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME
 saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION
 print "\nCreating ArcSDE Connection File...\n"
 # Process: Create ArcSDE Connection File...
 # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password, version, save_version_info
 print temp
 print Conn_File_NameT
 print server
 print service
 print database
 print account_authentication
 print username
 print password
 print saveUserInfo
 print version
 print saveVersionInfo
 arcpy.CreateArcSDEConnectionFile_management(temp, Conn_File_NameT, server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo)
 for i in range(arcpy.GetMessageCount()):
 if "000565" in arcpy.GetMessage(i): #Check if database connection was successful
 arcpy.AddReturnMessage(i)
 arcpy.AddMessage("\n+++++++++")
 arcpy.AddMessage("Exiting!!")
 arcpy.AddMessage("+++++++++\n")
 sys.exit(3) 
 else:
 arcpy.AddReturnMessage(i)
 arcpy.AddMessage("+++++++++\n")
 return Connection_File_Name
 #Check if no value entered for option 
 except SystemExit as e:
 print e.code
 return

Using this script, I can make a connection file on the fly by simply calling:

import arcpy, sdeconn
myconnect1 = sdeconn.connect("database1", "server")
myconnect2 = sdeconn.connect("database2", "server")

This eliminates the problem of database connection files being inconsistent from machine to machine or user profile to user profile.

• Bryan: Great script, but I found a few minor issues with it during my development. - It saves the password, but not as part of the file name, so when I supplied a different password the code found a connection file for the database but did not know the password was different. I changed the name to md5.new( server + "_" + ServiceConnFileName + "_" + database + "-" + version + "_" + username + password).hexdigest() - The indentation on the posting for the return is incorrect, so I didn't know my connection was failing. - The code changes the version to upper case, my version was lower case
• blord-castillo: Yeah, if I made the script now it would have some more options for things like forcing file creation (for when you change the password).

Answer 2

D.E.Wright just beat me to it, he's right on, use a connection just like in ArcCatalog. But here's my take, done at the Python prompt in ArcMap, using the direct full path to a sde connection file:

>>> import arcpy
>>> arcpy.env.workspace = "C:\\Users\\chad\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\anrc_water (anrcuser).sde"
>>> fdlist = arcpy.ListDatasets()
>>> for fd in fdlist:
... print fd
... 
anrc_water.DBO.ChadTest
anrc_water.DBO.Temp_Data
anrc_water.DBO.Master_Datasets
ANRC_WATER.DBO.ENF_FILL_FACC
ANRC_WATER.DBO.ENF_FILL_FDIR
>>> 

To get the path to my sde connection file, I just right-clicked on my SDE database in the Catalog tree, went to properties, then on the General tab, copy the path from the Name field:

enter image description here

• Ramakrishna Billakanti: Thank you sir I got it now. i really appreciate your help. Thanks a lot.
• Timothy Michael: If you're using the Python window in ArcCatalog to create your scripts, you can drag & drop your connection into the Python window and it will format the path properly.
• ketar: @TimothyMichael You just saved my life. Thank you.

Answer 3

You need to define your SDE connection document as you would normally in ArcCatalog; Then you will create the path to the layer in Python like this:

DataConnections = "C:\\AGS_GCSS_Tools\\DatabaseConnections\\" 
TCA_Connection = "prod_sde.sde\\prod_SDE.GIS.PropertyTax" + CAPSYear + "\\prod_SDE.GIS.Tca"
TCA_Layer = DataConnections + TCA_Connection

This will set your path to where your .SDE file lives, but then you set the path inside that connection to the layer you are looking for. In my case I also set a Year variable.

• Ramakrishna Billakanti: Hi Wright,Thanks for your response i really dont understand what you are saying, I need to run geoprocessing from my local desktop accessing my sde connection on a different server. I have connection created for the sde service on arc catalog. what should i do if want to access data from the sde connection.
• Alex Tereshenkov: Nowadays Esri recommends using os.path.join for joining the .sde connection file (sdeworkspace) variable and the object name. So it will be indata = os.path.join(sdeworkspace,"FeatureClass").

Answer 4

you can also define the path of connection directly in your Query .

PathSdeConnection= "C:\\Users\\{Username Of windows}\\AppData\\Roaming\\ESRI\\Desktop10.2\\ArcCatalog\\{name of ConenctionString}.sde

and use it in Search and etc.

with arcpy.da.SearchCursor(PathSdeConnection,("OBJECTID","SHAPE@","SHAPE@JSON"),{WhereClause})as cursor:
 for row in cursor:
 .
 .
 .