[GIS] Using an ArcSDE Connection through Python

arcpyconnectionenterprise-geodatabase

I would like to run a script on a ArcSDE feature class. The script runs perfectly when I hard code a shapefile, but is unable to connect to an ArcSDE database. The purpose of the script is to assign odd valued UIDs, and generate a dbf as an output with selected fields.

import arcpy
from arcpy import management as dm
arcpy.env.overwriteOutput = True

# Set environment path to SDE connection
arcpy.env.workspace = r"C:\Users\client\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\OZ@DB_MAPS.sde"

# Define fc name, DBF output location, and DBF output name
fc = "DB.OZ.PlantCenter"
gisID = "GIS_2016_0"
dbfLocation = r"C:\Users\client\data"
dbfOutputName = "trees_GIS"

if arcpy.Exists(fc):
    arcpy.AddMessage("The feature class exists")
else:
    arcpy.AddError("The feature class does not exist")

with arcpy.da.UpdateCursor (fc, gisID) as cursor:
    for row in cursor:
        if row[0] == None:
            row[0] = 0
            cursor.updateRow(row)

# Query out odd values in "GIS_2016_0"
odd = []
even = []
oddCursor = arcpy.SearchCursor(fc)
for row in oddCursor:
    uid = row.getValue (gisID)
    if uid %2 == 1:
        odd.append (uid)
    else:
        even.append (uid)

field = arcpy.AddFieldDelimiters(fc,gisID)
odd_sql = ' OR '.join('%s = %s' %(field,i) for i in odd)
dm.MakeFeatureLayer(fc, "fc_temp")
dm.SelectLayerByAttribute("fc_temp", "NEW_SELECTION", odd_sql)

# Search for the latest maximum odd value
maxCursor = arcpy.da.SearchCursor("fc_temp", gisID)
FirstRecord = True
for row in maxCursor:
    if FirstRecord:
        FirstRecord = False
        maxValue = int(row[0])
    else:
        maxValue = max(int(row[0]),maxValue)

if (maxValue % 2 == 1): #odd
    # Define the function to assign incrementing odd values
    def autoIncrement (start=0, step=1):
        i = start
        while 1:
            yield i
            i+=step

    # Query for new attributes with no assigned unique ID, and populate with a number following the last largest odd value
    zero_qry = '"' + gisID + '" = ' + "0"
    dm.MakeFeatureLayer(fc, "fc_temp")
    dm.SelectLayerByAttribute("fc_temp", "NEW_SELECTION", zero_qry)
    incrementCursor = arcpy.UpdateCursor ("fc_temp")
    nextValue = maxValue + 2
    incrementer = autoIncrement (nextValue, 2)
    for row in incrementCursor:
        row.setValue(gisID, incrementer.next())
        incrementCursor.updateRow(row)
else: 
    arcpy.AddMessage("No odd unique IDs found.")

# Delete previous dbfs and export the feature class as a dbf with selected fields (overwrite)
dbfPath = dbfLocation + '\\' + dbfOutputName + '.dbf'
if arcpy.Exists(dbfPath):
    dm.Delete(dbfPath)
arcpy.TableToTable_conversion (fc, dbfLocation, dbfOutputName) 

# Get all fields in dbf and remove unwanted fields
fields = arcpy.ListFields("fc_temp") 
keepFields = ['GIS_2016_0', 'SourceAcce', 'AccessionC', 'SectionNam', 'Longitude', 'Latitude']
dropFields = [x.name for x in fields if x.name not in keepFields]
dm.DeleteField (dbfPath, dropFields)
dm.Delete("fc_temp")

When the client ran this script through task scheduler using a basic task, the script returned no errors, but no output was produced.

New edit 01/13/2017:

The client ran the script in Arc, and was provided this error message:

enter image description here

I believe this SDE layer is versioned, so it might be related to the issue here: https://geonet.esri.com/thread/88268

Any thoughts on how to resolve the code?

Best Answer

It appears you need to open an edit session, as you pointed out, this is on a versioned geodatabase. I am just looking at your stack trace provided. If this is a scheduled job, and your connection file is using a Windows NT connection, I believe you can set your scheduled task to run under your users credentials. Or better yet, if you can get a named database user, go with that. Also be aware when dropping fields of schema locks. If any are present, your script is going to blow up there as well.

Below is an example of how to open an edit session in ArcPy.

http://desktop.arcgis.com/en/arcmap/10.3/analyze/arcpy-data-access/editor.htm

Related Question