Add Context Manager Methods to ArcPy Cursor Classes

[GIS_Spellblade]

TLDR: ArcPy Cursors do not universally respect Context Managers; Add Appropriate Methods to the Cursor Class to enable graceful exits (discard, disconnect, close) as part of the __exit__() call.

Recently an Esri blog was published detailing a relatively common "gotcha" pertaining to cursors applied against a file geodatabase: https://www.esri.com/arcgis-blog/products/arcgis-pro/data-management/locked-by-another-application-u...

The given solution was to embed a del call within a context manager (a with statement). This idea runs contrary to the syntactic sugar abstraction that is a context manager, or put otherwise, we use a context manager to not have to worry about some of the nitty-gritty of programming details like closing files and cursors. The suggested solution is problematic as it requires a user to have in-depth knowledge of what is likely an oversight due to the long-lived nature of the ArcPy library; further compounded by the lack of documentation outside of the blog's reference. https://realpython.com/python-with-statement/

This idea is to add in certain methods within the ArcPy Cursor Class to have it conform to the Python DBA Specification (https://peps.python.org/pep-0249/); while this sounds like a lot, in reality the additions would be fairly minimal and would be similar to the solution discussed in this blog post: https://dev.to/c_v_ya/sql-cursor-via-context-manager-2gc7

Having an implementation would make the ArcPy library more robust and allow the ArcPy Cursor to behave the same way regardless of the underlying data source-- --which would be the expectation for any other library, a function to behave the same way for all the inputs it is programmed to accept. This would benefit the users as they would not need to have an arcane understanding of a library that is possibly older than some of its users; they would be able to trust that ArcPy cursors comply with context managers which is a common usage pattern since Python 2.5.

[philnagel]

I believe we run into this issue quite a bit as well. I honestly had no idea the context manager for arcpy.da cursors was not a true context manager. This is a very frustrating issue and it seems crazy to me this is even a thing, considering that even ESRI staff are surprised by this as documented by the blog post you linked. ESRI, please fix this!

[HannesZiegler]

We're working on this!  This status does not guarantee that the functionality will be in the next release, but development work has begun. Release cycles vary by product so make sure to check the product life cycle information to get an idea of when to expect the next release.  

[HannesZiegler]

Congratulations! The idea made its way into the software (ArcGIS Pro 3.7) - arcpy.da Cursors now properly dispose of resources upon exiting the context scope. Manually deleting the cursor object to release locks is no longer necessary. You can see the difference if you run the sample code from the blog post referenced by the idea: Locked by another application using ArcPy and a File geodatabase.

Some workflows may unintentionally have leveraged this bug. For example, consider this code:

with arcpy.da.UpdateCursor(...) as cursor:
    for row in cursor:
        cursor.updateRow([...])

cursor.reset()            # Now fails with "I/O operation on closed file" (but did not pre 3.7)
for row in cursor: ...    # Now fails with "I/O operation on closed file" (but did not pre 3.7)

(Note the use of the context manager after it should have been disposed)

Going forward, context manager methods will no longer function outside of the scope of the context manager (as expected), such that cases like the above will no longer work. Keep everything within the context of the existing context manager or start a new context manager.

with arcpy.da.UpdateCursor(...) as cursor:
    for row in cursor:
        cursor.updateRow([...])

    cursor.reset()            # OK
    for row in cursor: ...    # OK

[MarcoBoeringa]

Hi @HannesZiegler ,

While this does sound nice, I still see one major issue. I have literally wasted days trying to debug a locking issue in a File Geodatabase that is created as temporary data store inside a larger ArcPy / Python geoprocessing workflow I created, and that I need to automatically delete after it is no longer relevant.

This was quite a nightmare to debug. I finally realized, the locking issue was caused by creating an ArcPy Feature Layer using the Make Feature Layer geoprocessing tool of Pro using a WHERE clause, and subsequently using it as input for a Search Cursor.

Even though the code creating the Feature Layer explicitly deleted the feature layer using the arcpy.management.Delete tool, and the fact the Feature Layer variable was created inside a separate function and should thus go out of scope and be irrelevant once the function returns and the code continues in the calling script, it would still cause an un-removable lock. Note that the deletion of the File Geodatabase would take place in the calling script, not in the function creating the Feature Layer.

Nothing helped. I could not use e.g. arcpy.management.ClearWorkspaceCache to remove the lock, nor using Compact. It was driving me crazy.

I finally realized the issue was with the Feature Layer creation and using it as input for the SearchCursor. As I realized that inside this function, the Feature Layer was actually irrelevant with the current state of the code, I simply replaced it by setting the WHERE clause directly during SearchCursor creation.

This finally fixed the locking issues, and I could now successfully use the Delete geoprocessing tool to delete the file geodatabase. I wished I had realized this issue before, but I had been chasing ghosts.

However, it seems ESRI may still have work to do in clearing out unnecessary locks related to Feature Layer objects in ArcPy, as this issue is still present in Pro 3.7.

Again, please note that:

- The creation of the Feature Layer took place inside a dedicated function, and the feature layer was also deleted in it (and variable removed using del)

- The Feature Layer was not added to e.g. an ArcGIS Pro "Map", it only existed temporarily in the context of the script / function.

- The locking issue occurred once the Feature Layer became input for the cursor creation (well, actually I am not sure, it may simply be a by-product of the Feature Layer creation, and that object not fully releasing locks upon destruction).

- In both cases, the cursor was correctly used with the with context manager, as you can see in the code examples below.

Code causing locking issues:

featureLayer = arcpy.MakeFeatureLayer_management(featureClassName,"lyr_INSERT_FROM_FGDB_{}_{}_{}".format(threadID,os.getpid(),retryID),whereClause).getOutput(0)

with arcpy.da.SearchCursor(featureLayer,cursorFields) as fgdbCursor:

Equivalent code not having locking issues:

with arcpy.da.SearchCursor(featureClassName,cursorFields,whereClause) as fgdbCursor:

[MarcoBoeringa]

Hi @HannesZiegler ,

I have attempted to dig a little deeper on this issue. Extending what I wrote in my last post, I now have indications that the issue with un-removable locks, appears to be caused, or may be caused, by using the "Layer.setSelectionSet" option on Feature Layers.

This method can be very handy in cases, but it appears that as soon as you set such a selection, ArcGIS Pro sets a lock in the data source's file geodatabase that will only be removed upon closure of the entire application.

It can not be removed by:

- Clearing the selection using 'Layer.setSelectionSet([],"NEW"), which I would have expected to also release the lock in the file geodatabase and as set on the feature class involved.

- Running arcpy.env.ClearWorkspaceCache()

- Running the Compact geoprocessing tool.

- Deleting the layer using the Delete geoprocessing tool and/or setting the un-referencing the variable of the Feature Layer with Python del

It seems this may be an omission in especially the underlying implementation of 'Layer.setSelectionSet([],"NEW") in ArcGIS Pro, which is the Help recommended way to clear selections.

[MarcoBoeringa]

Hi @HannesZiegler ,

Unfortunately, I think I need to report yet another major issue, likely a pretty severe regression bug in Pro 3.7+.

What I am seeing is that the Search Cursor does not honor any Definition Query set on a Feature Layer object. It returns all rows of the underlying table instead. Please note that this issue only appears to occur if the Search Cursor is based on a Feature Layer created in the script(!), but not added to a map in Pro.  I do not see the same issue if the Feature Layer is accessed from a layer added to a map in ArcGIS Pro, then the Search Cursor *will* correctly return the selection after setting the Definition Query property via ArcPy Python script code.

This is with the data source being a file geodatabase. And note that the script is run from a toolbox in ArcGIS Pro's interface, not command line!

The Help is very clear about this:

https://doc.esri.com/en/arcgis-pro/latest/arcpy/get-started/data-access-using-cursors.html

Cursors honor layer and table view definition queries and selections. The cursor object only contains the rows that would be used by any geoprocessing tool during an operation.Cursors honor layer and table view definition queries and selections. The cursor object only contains the rows that would be used by any geoprocessing tool during an operation.

Yet:

eliminateLayer = arcpy.MakeFeatureLayer_management(in_features=<FEATURE_CLASS_IN_FILE_GEODATABASE>,out_layer="eliminateLayer")[0]

eliminateLayer.definitionQuery = "objectid IN (1,2)"

arcpy.AddMessage("DEBUG: Layer definition query: '{}'".format(eliminateLayer.definitionQuery))

with arcpy.da.SearchCursor(eliminateLayer,"OID@") as objectIDCursor:
    for row in objectIDCursor:
        objectID = row[0]
        arcpy.AddMessage("DEBUG: ObjectID: '{}'".format(objectID))

returns *all* records of the underlying feature class, instead of the ones I attempted to select using the Definition Query, even though the definition query is clearly set.

Changing the above code to (NOTE: change is in the "with" code line creating the cursor!):

eliminateLayer = arcpy.MakeFeatureLayer_management(in_features=<FEATURE_CLASS_IN_FILE_GEODATABASE>,out_layer="eliminateLayer")[0]

eliminateLayer.definitionQuery = "objectid IN (1,2)"

arcpy.AddMessage("DEBUG: Layer definition query: '{}'".format(eliminateLayer.definitionQuery))

with arcpy.da.SearchCursor(eliminateLayer,"OID@",eliminateLayer.definitionQuery) as objectIDCursor:
    for row in objectIDCursor:
        objectID = row[0]
        arcpy.AddMessage("DEBUG: ObjectID: '{}'".format(objectID))

*does* successfully force the Search Cursor to abide by the selection as set in the Definition Query of the Feature Layer.

Also:

eliminateLayer = arcpy.MakeFeatureLayer_management(in_features=<FEATURE_CLASS_IN_FILE_GEODATABASE>,out_layer="eliminateLayer")[0]

eliminateLayer.definitionQuery = "objectid IN (1,2)"

arcpy.AddMessage("DEBUG: Layer definition query: '{}'".format(eliminateLayer.definitionQuery))

arcpy.management.DeleteFeatures(eliminateLayer)

*will* correctly delete the rows associated with the Definition Query, not all records. It just seems the Search Cursor implementation now fails in Pro 3.7 or 3.7.1??? (I haven't tested any of the other Cursors)

[MarcoBoeringa]

Hi @HannesZiegler ,

I have now submitted a bug report with reproducible case for the last issue concerning Search Cursors to the Dutch branch of ESRI. I hope they will be able to reproduce and log it. This is a pretty severe regression bug if confirmed, as it breaks a major contract between Cursors on the one hand, and Definition Queries and Selection Sets on the other hand, where Cursors are supposed to always respect selections.

This issue could break hundreds of automated workflows written with ArcPy and Python.