[GIS] Transfering features class from ArcSDE to File Geodatabase using ArcPy

arcgis-desktoparcpyenterprise-geodatabasefile-geodatabase

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)

Best Answer

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)
Related Question