Compare and Add - Python

[DavidBuehler]

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-o...

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"