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

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

EddX — Wed, 14 Feb 2024 04:00:25 GMT

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?

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

AlfredBaldenweck — Wed, 14 Feb 2024 14:02:28 GMT

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.

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

JakeSkinner — Wed, 14 Feb 2024 14:07:49 GMT

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

del oRows

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

EddX — Wed, 14 Feb 2024 22:37:26 GMT

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

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

EddX — Wed, 14 Feb 2024 22:33:17 GMT

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?

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

AlfredBaldenweck — Thu, 15 Feb 2024 00:14:06 GMT

Hey so out of curiosity:

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

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

EddX — Thu, 15 Feb 2024 03:44:26 GMT

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.

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

AlfredBaldenweck — Thu, 15 Feb 2024 14:06:38 GMT

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?

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

JakeSkinner — Thu, 15 Feb 2024 14:11:02 GMT

@EddX having the table open will cause a lock.

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

matkowm_aw — Sat, 17 Feb 2024 03:17:57 GMT

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