AnsweredAssumed Answered

How to choose proper edit session in arcpy?

Question asked by xtian79 on Aug 12, 2020

There are 2 databases A and B; Each one has two tables: Table1 and Table2, both were created with the same script, defining for both: fields, versioning and archiving.

It is required to delete some rows in both tables and, of course in both databases.

A script was prepared:

def remove_items(from_table, ids):
    log.add("Start to clean orphan objects from: {0}".format(from_table))
    total_to_delete = 0
    fields = arcpy.ListFields("{0}.{1}".format(user_do, from_table))
    if "INSPECTION_ID" in [f.baseName for f in fields]:
        with arcpy.da.SearchCursor("{0}.{1}".format(user_do, from_table), ["INSPECTION_ID"]) as count_cursor:
            for row in count_cursor:
                if row[0] not in ids:
                    total_to_delete += 1

        row_counter = 0
        with arcpy.da.UpdateCursor("{0}.{1}".format(user_do, from_table), ["INSPECTION_ID"]) as table_cursor:
            for row in table_cursor:
                if row[0] not in ids:
                    table_cursor.deleteRow()
                    row_counter += 1
                    log.add("In table {0} deleted rows {1} / {2}".format(from_table, row_counter, total_to_delete))

            log.add("Total rows deleted from {0} : {1}".format(from_table, row_counter))


with arcpy.da.Editor(arcpy.env.workspace) as edit:
    log.add("This script may take a while to finish.")
    remove_items("Table1", orphan_ids)
    remove_items("Table2", orphan_ids)

The script is run against Database A, and it runs with out any issue. The script is run against Database B, and it generates the following error:

['RuntimeError: Insufficient permissions [Database_B.Table_2][STATE_ID = 2880]\n', '\nThe above exception was the direct cause of the following exception:\n\n', 'Traceback (most recent call last):\n', '  File "C:/T/Repos/RemoveOrphanInspections.py", line 136, in <module>\n    main(argv[1:])\n', '  File "C:/T/Repos/RemoveOrphanInspections.py",'SystemError: <built-in method __exit__ of Workspace Editor object at 0x0000019EDF973180> returned a result with an error set\n']

Permissions were checked, and the data owner user (the one used to run the script), has the permissions of Select, Insert, Update and Delete.

I make a change to the script:

with arcpy.da.Editor(arcpy.env.workspace) as edit:
    log.add("This script may take a while to finish.")
    remove_items("Table1", orphan_ids)

edit = arcpy.da.Editor(arcpy.env.workspace)
edit.startEditing(False, True)
edit.startOperation()
remove_items("Table2", orphan_ids)
edit.stopOperation()
edit.stopEditing(True)

Now the script runs with out issue.

With this modification I am using two edit sessions, therefore there is two transaction scopes (That was not the original plan).

Versioning, archiving, permissions and fields are the same in both Databases.

How could check the differences between the Database_A.Table2 and Database_B.Table2? Which property of the table should be checked to choose a proper Edit session management?

Outcomes