Compare and Add - Python

DavidBuehler — Wed, 28 Jul 2021 14:24:05 GMT

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:

https://community.esri.com/t5/data-management-questions/comparing-two-feature-classes-and-updating-one-of-them/m-p/247407#M14075

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#:~:text=The%20data%20cannot%20be%20versioned,before%20enabling%20attachments%20on%20either.), 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"

topic Compare and Add - Python in Python Questions

Compare and Add - Python