3

I would like to know if it's possible to use python to transfer features class from SDE into a GDB and then transfer it back to SDE without losing SDE parameters such topology, versionning, relationship.

The reason of this loop is that I would like to use FME to edit the features class, but I dont have the license to read/write into SDE directly so I would like to do the updates inside a GDB.

Can somebody tell if it's possible, and if yes, expose the arcpy functions that I shall use?

I have this so far

Before running the FME translation:

#import FeatureCLass from SDE into GDB used as input for FME

import arcpy

SOURCE = "C:/Users/DemMa18/AppData/Roaming/ESRI/Desktop10.3/ArcCatalog/GEOP011@demma18.sde/DEMMA18.TestFME"
DEST = "C:/temp/arcgis/FME/TestFME_IN.gdb"

#delete all FC in GDB
arcpy.env.workspace = DEST
fcDestList = arcpy.ListFeatureClasses()
for fc in fcDestList:
    arcpy.Delete_management(fc)

#copy SDE FC into GDB
arcpy.env.workspace = SOURCE
fcSourceList = arcpy.ListFeatureClasses()

for fc in fcSourceList:
    arcpy.Copy_management(fc, DEST+"/"+fc)

After FME translation:

#export FeatureCLass from GDB used as output by FME to SDE

import arcpy

DEST = "C:/Users/DemMa18/AppData/Roaming/ESRI/Desktop10.3/ArcCatalog/GEOP011@demma18.sde/DEMMA18.TestFME"
SOURCE = "C:/temp/arcgis/FME/TestFME_OUT.gdb"

#Delete features in SDE FC
arcpy.env.workspace = DEST
fcDestList = arcpy.ListFeatureClasses()
for fc in fcDestList:
    arcpy.TruncateTable_management(fc)

#Append GDB FC into SDE FC
arcpy.env.workspace = SOURCE
fcSourceList = arcpy.ListFeatureClasses()

for fc in fcSourceList:
    arcpy.Append_management(fc, DEST+"/"+fc)
  • as far as I'm aware, FME uses the same license and permissions as the user, so you'd likely run into the same issue when trying to write back to SDE. So does arcpy, if that's what you're using to write back to SDE – Midavalo Apr 14 '16 at 18:21
  • are you wanting to transfer just the features, or the feature class itself? – Midavalo Apr 14 '16 at 18:24
  • @Midavalo I think it will be safer to transfer only features, at leat when transferring back to SDE. So I presume I have to delete all features in the SDE before copying back. – Below the Radar Apr 14 '16 at 18:27
  • @Midavalo I can read/write into SDE with arcpy but not with FME – Below the Radar Apr 14 '16 at 18:36
  • 1
    You will need a way to identify the records to update, object ID isn't a good choice – Midavalo Apr 14 '16 at 19:07
3

I have slightly modified another script I had laying around which should do what you want, using Insert Cursors and Update Cursors. It does require you to specify the name of your unique ID field (can't be ObjectID). It also has no error checking (I didn't need it for what I was doing but as you're modifying SDE feature classes it may be wise to add some!)

Please back-up your data and test before using:

import arcpy

#####################################################
# Update these variables to match your requirements #
#####################################################

idField = 'IDField' # Set this to the name of your unique ID field

gdbFeatureClass = r"N:\GISSE\SE.gdb\Test1" # This is path to your file geodatabase feature class
sdeFeatureClass = r"Database Connections\GeoDatabase.sde\Test1" # This is path to your enterprise geodatabase feature class
arcpy.env.workspace = r"Database Connections\GeoDatabase.sde"

#####################################################
#####################################################

workspace = arcpy.env.workspace
idFieldName = idField.upper()

fields = [idFieldName]

# Get list of gdb features
gdbFeatureSet = set()
with arcpy.da.SearchCursor(gdbFeatureClass, fields) as cursor:
    for row in cursor:
        gdbFeatureSet.add(row[0])

# Get list of sde features
sdeFeatureSet = set()
with arcpy.da.SearchCursor(sdeFeatureClass, fields) as cursor:
    for row in cursor:
        sdeFeatureSet.add(row[0])

# Describe sdeFeatureClass to get fields (this assumes that the
# two feature classes have matching fields)
desc = arcpy.Describe(sdeFeatureClass)
sdeFields = desc.fields

# Create list of fields excluding ObjectID, Shape, Length, Area 
# (Shape is re-added as SHAPE@ to ensure geometry included, the others are auto-generated by the GDB)
cursorSdeFields = ['SHAPE@']
fieldnames = [field.name for field in sdeFields if field.name != desc.OIDFieldName and field.name != desc.ShapeFieldName and field.name != desc.LengthFieldName and field.name != desc.AreaFieldName]

for fn in fieldnames:
    cursorSdeFields.append(fn)

# Find position of ID field to query later
i = 0
for fn in cursorSdeFields:
    if fn.upper() == idFieldName:
        idFieldPos = i
    i += 1

## Start Editing (only needed if SDE is Versioned dataset)
#edit = arcpy.da.Editor(workspace)
#edit.startEditing(False, True)
#edit.startOperation()

# Create Insert Cursor and Insert any new features
iCursor = arcpy.da.InsertCursor(sdeFeatureClass,cursorSdeFields ) 
with arcpy.da.SearchCursor(gdbFeatureClass, cursorSdeFields) as cursor:
    for row in cursor:
        if row[idFieldPos] not in sdeFeatureSet:
            iCursor.insertRow(row) 
del iCursor # Remove Insert Cursor

# Update Cursor for updating existing records
# This is separate to InsertCursor as if they are done at the same time
# they need to be done within an edit session, which can slow the process.
# Search Cursor searches the gdb Feature class and adds to sde
# using the Update Cursor
with arcpy.da.SearchCursor(gdbFeatureClass, cursorSdeFields) as cursor:
    for row in cursor:
        if row[idFieldPos] in sdeFeatureSet:
            whereclause = "{} = {}".format(idFieldName, row[idFieldPos])
            with arcpy.da.UpdateCursor(sdeFeatureClass, cursorSdeFields, whereclause) as xCursor:
                for xrow in xCursor:
                    xrow = row
                    xCursor.updateRow(xrow)

# Update Cursor for deleting removed records
# Search cursor searches the sde feature class and removes
# any (using deleteRow) that don't exist in the gdb
with arcpy.da.SearchCursor(sdeFeatureClass, cursorSdeFields) as cursor:
    for row in cursor:
        if row[idFieldPos] not in gdbFeatureSet:
            whereclause = "{} = {}".format(idFieldName, row[idFieldPos])
            with arcpy.da.UpdateCursor(sdeFeatureClass, cursorSdeFields, whereclause) as xCursor:
                for xrow in xCursor:
                    xCursor.deleteRow()

## Stop Editing (Only needed if SDE is Versioned dataset
#edit.stopOperation()
#edit.stopEditing(True)
  • That looks really great! can you share the link to the post where you found the original script? – Below the Radar Apr 15 '16 at 13:23
  • About error checking... what kind of error might occur here? – Below the Radar Apr 15 '16 at 13:40
  • @BelowtheRadar I wrote the original for a similar process I had - In that process we had a static feature class (nobody edited it) that was based on a table in another (non-spatial) database. My script was used to update the static feature class to add/update/delete any changes in the table – Midavalo Apr 15 '16 at 19:06
  • @BelowtheRadar Knowing the type of error can be an art. Potentially if there is some kind of difference in the schema of the two feature classes (fields in one that don't exist in the other) this will break the script and potentially leave you with incomplete data. It probably needs to run another search cursor before all the edits, just to confirm everything fits, and bail out if it finds something that doesn't fit – Midavalo Apr 15 '16 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.