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:
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">remove_items</SPAN><SPAN class="punctuation token">(</SPAN>from_table<SPAN class="punctuation token">,</SPAN> ids<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
log<SPAN class="punctuation token">.</SPAN>add<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Start to clean orphan objects from: {0}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>from_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
total_to_delete <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
fields <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFields<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{0}.{1}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>user_do<SPAN class="punctuation token">,</SPAN> from_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">if</SPAN> <SPAN class="string token">"INSPECTION_ID"</SPAN> <SPAN class="keyword token">in</SPAN> <SPAN class="punctuation token">[</SPAN>f<SPAN class="punctuation token">.</SPAN>baseName <SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> fields<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{0}.{1}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>user_do<SPAN class="punctuation token">,</SPAN> from_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"INSPECTION_ID"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> count_cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> count_cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> ids<SPAN class="punctuation token">:</SPAN>
total_to_delete <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>
row_counter <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{0}.{1}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>user_do<SPAN class="punctuation token">,</SPAN> from_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"INSPECTION_ID"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> table_cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> table_cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> ids<SPAN class="punctuation token">:</SPAN>
table_cursor<SPAN class="punctuation token">.</SPAN>deleteRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
row_counter <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>
log<SPAN class="punctuation token">.</SPAN>add<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"In table {0} deleted rows {1} / {2}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>from_table<SPAN class="punctuation token">,</SPAN> row_counter<SPAN class="punctuation token">,</SPAN> total_to_delete<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
log<SPAN class="punctuation token">.</SPAN>add<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Total rows deleted from {0} : {1}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>from_table<SPAN class="punctuation token">,</SPAN> row_counter<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>Editor<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> edit<SPAN class="punctuation token">:</SPAN>
log<SPAN class="punctuation token">.</SPAN>add<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"This script may take a while to finish."</SPAN><SPAN class="punctuation token">)</SPAN>
remove_items<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Table1"</SPAN><SPAN class="punctuation token">,</SPAN> orphan_ids<SPAN class="punctuation token">)</SPAN>
remove_items<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Table2"</SPAN><SPAN class="punctuation token">,</SPAN> orphan_ids<SPAN class="punctuation token">)</SPAN>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:
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>Editor<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> edit<SPAN class="punctuation token">:</SPAN>
log<SPAN class="punctuation token">.</SPAN>add<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"This script may take a while to finish."</SPAN><SPAN class="punctuation token">)</SPAN>
remove_items<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Table1"</SPAN><SPAN class="punctuation token">,</SPAN> orphan_ids<SPAN class="punctuation token">)</SPAN>
edit <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>Editor<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace<SPAN class="punctuation token">)</SPAN>
edit<SPAN class="punctuation token">.</SPAN>startEditing<SPAN class="punctuation token">(</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>
edit<SPAN class="punctuation token">.</SPAN>startOperation<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
remove_items<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Table2"</SPAN><SPAN class="punctuation token">,</SPAN> orphan_ids<SPAN class="punctuation token">)</SPAN>
edit<SPAN class="punctuation token">.</SPAN>stopOperation<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
edit<SPAN class="punctuation token">.</SPAN>stopEditing<SPAN class="punctuation token">(</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>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?
