Back in 2014, I had help figuring comparing two lists. It would add records from Table A that were not in Table B, and delete records from Table B that were not in Table A. See:
My new use for this is based on a Survey123 limitation. As I understand it based on (https://doc.arcgis.com/en/survey123/desktop/create-surveys/survey123withexistingfeatureservices.htm#....), when using a feature layer/table to base the survey on and push back to, the data cannot be versioned.
My goal is to have a related table on versioned data, which causes the related table to be versioned, that stores the records collected from the Survey123 form. To end around the versioned issue, what I think the best solution is, is to have a separate table that meets the Survey123 criteria to push to, then push the new records to versioned table so the relationship will work.
I think comparing globalids should be the field to compare on, and really all I need to do is push adds only.
I am not sure if I need an edit session or not, and I do get this error: Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server]Syntax error, permission violation, or other nonspecific error]
Any suggestions, help, insight will be greatly appreciated. Below is what I have:
import arcpy
import sys
import os
print "Looping through and adding and deleting"
#Looping through each feature class and building a list of comparsions. Then adds or deletes them to the SDE feature class.
def createMatchingFieldList(fc1, fc2):
lst1 = [fld.name for fld in arcpy.ListFields(fc1)]
lst2 = [fld.name for fld in arcpy.ListFields(fc2)]
return list(set(lst1) & set(lst2))
def createWhereClause(fc, fld_name, value):
if len(arcpy.ListFields(fc, fld_name)) == 1:
fld = arcpy.ListFields(fc, fld_name)[0]
if fld.type == "String":
where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(fc, fld_name), value)
else:
where = "{0} = {1}".format(arcpy.AddFieldDelimiters(fc, fld_name), value)
return where
def getPrimaryFieldValues(fc, field):
return [r[0] for r in arcpy.da.SearchCursor(fc, [field])]
def getSelectCursor(fc, flds, whereClause):
return arcpy.da.SearchCursor(fc, flds, whereClause)
def diff(a, b):
return list(set(a) - set(b))
#source = arcpy.GetParameterAsText(0)
#destination = arcpy.GetParameterAsText(1)
#fieldName = arcpy.GetParameterAsText(2)
source = 'Database Connections/GIS Editor to COMGIS3.sde/COMGIS3.GIS.SanCleanings_Test'
destination = 'Database Connections/GIS Editor to COMGIS3.sde/COMGIS3.GIS.SanCleanings_Test2'
fieldName = "globalid"
# Start an edit session. Must provide the workspace.
edit = arcpy.da.Editor(r'C:\Users\XXXXXXXXX\AppData\Roaming\ESRI\Desktop10.7\ArcCatalog\GIS Editor to COMGIS3.sde')
# Edit session is started without an undo/redo stack for versioned data
# (for second argument, use False for unversioned data)
edit.startEditing(False,False)
# Start an edit operation
edit.startOperation()
# create a list of field names which are in both featureclasses
flds = createMatchingFieldList(source, destination)
sourceValues = getPrimaryFieldValues(source, fieldName)
destinationValues = getPrimaryFieldValues(destination, fieldName)
additions = diff(sourceValues, destinationValues)
#deletions = diff(destinationValues, sourceValues)
with arcpy.da.InsertCursor(destination, flds) as insertCursor:
for a in additions:
where = createWhereClause(source, fieldName, a)
insertRows = getSelectCursor(source, flds, where)
for r in insertRows:
insertCursor.insertRow(r)
#for d in deletions:
# where = createWhereClause(destination, fieldName, d)
# with arcpy.da.UpdateCursor(destination, flds, where) as deleteCursor:
# for d in deleteCursor:
# deleteCursor.deleteRow()
# Stop the edit operation.
edit.stopOperation()
# Stop the edit session and save the changes
edit.stopEditing(True)
print "Success"