Python in ArcPro - How To arcpy.da.UpdateCursor on a SDE which is open/schema locked

[EddX]

Hello,
I am updating a script which was used in ArcMap (python 2) to ArcPro (python 3). This script is ran through ArcPro with the layers loaded into the map. 

I have a script which references a sde file (a table):

sTabName = "SDE_Company.ADMIN.Cadaster"

which firstly does a search cursor to get some data

with arcpy.da.SearchCursor(sTabName, ('X','Y','Z')) as oRows

 this works,


But later on I need to update sTabName so I use:

with arcpy.da.UpdateCursor(sTabName, ("Z")) as oRows

 the script hangs and gets stuck at this point. I have not used "edit.startOperation()" as the with statement should handle the editing opening and closing. 

Does ArcPro have more strict schema locking than ArcMap, which is causing this issue? This script worked fine in ArcMap. 

I have ran:

if arcpy.TestSchemaLock(sTabName):

at the start of the script and sTabName has a lock on it, as it is open in the current map (it is also open by other user profiles in the business)

Is this causing the issues/hang? 

How can I work around this to update the sTabName table? 

[AlfredBaldenweck]

Unfortunately you do need an editor class to use an update cursor on an enterprise geodatabase.

You can get around this by using Field Calculator.

[EddX]

I can set up an editor class ( https://pro.arcgis.com/en/pro-app/3.1/arcpy/data-access/editor.htm this?) 
will startEditing allow me to override the schema lock that exists and be able to use UpdateCursor? Reading the documentation in the link above, to my understanding the edit session is automatically handled within a with statement when using cursors, is this correct?


And for field calculator, is there a way to do this via arcpy? is it this? https://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/calculate-field.htm 

[JakeSkinner]

@EddX after you run the SearchCursor portion of code, try executing the following:

del oRows

[EddX]

Hello, I've added this as requested. The script still hangs at the UpdateCursor, and the schema lock still exists. Do you think the schema lock is causing the issue? 

[AlfredBaldenweck]

Hey so out of curiosity:

1. How many records does your table have?
   and
2. Do you have the attribute table open when running this?

[EddX]

200,000+
Yes, the attribute table is open when I run this. 

Unfortunately part of the process of this script requires human involvement (checking) and hence the script is run via the python terminal within ArcPro with the table open/loaded. Somehow this worked previously within ArcMap. I read somewhere that ArcPro has much stricter schema locking that ArcMap. 

I can share the script if that helps? 

Thank you for your help so far, I appreciate it.

[AlfredBaldenweck]

So, not sure if this causing it or not, but the reason I asked is because of this.

Granted, it was happening in a Python toolbox and not in the python window, but the short of it was having running an update cursor on a table with 200+ records caused a lock error if the table was open while the tool was running.

Shot in the dark, but maybe try closing the table while running the script and then reopening it to check?

[JakeSkinner]

@EddX having the table open will cause a lock.

[matkowm_aw]

Both the InsertCursor and the UpdateCursor require an Editing Session to be created and started. You can only create a single InsertCursor or UpdateCursor in any single editing session. I typically run my updates to catch any errors during the update and drops all edits if an exception is thrown. 

Also, you do not need to check for a schema lock on the dataset. A schema lock will not prevent you from editing the table. 

import arcpy
from pathlib import Path

connection_path: str = r'path\to\geodatabase\connection\file.sde'
table_name: str = 'Cadaster'
table_path: str = [str(Path(dirpath) / filename) 
                   for dirpath, _, filenames in arcpy.da.Walk(connection_path)
                   for filename in filenames
                   if filename.split('.')[-1] == table_name].pop()

edit_session: arcpy.da.Editor = arcpy.da.Editor(connection_path)
edit_session.startEditing(True, True)
edit_session.startOperation()
save_edits: bool = True
total_update_count: int = 0

try:
    with arcpy.da.UpdateCursor(table_name, 'Z') as update_cursor:
        for current_row in update_cursor:
            updated_row = current_row
            # do what ever you need to alter the row
            update_cursor.updateRow(updated_row)
            total_update_count += 1

except Exception as e:
    save_edits = False
    raise e

finally:
    # Stop and save the editing session
    edit_session.stopOperation()
    edit_session.stopEditing(save_edits)
    del edit_session