[GIS] Having trouble nesting updatecursors

arcpyupdate

I'm trying unsuccessfully to perform updates on multiple linked tables using updatecursors.

The following code works as I would expect:

trackRows = arcpy.SearchCursor("tracks","Key_='b2879561-d751-e111-9e92-0050568e18a0'")
for trackRow in trackRows:
    arcpy.management.MakeFeatureLayer("tracks","currentTrack","Key_='" + str(trackRow.Key_) + "'")
    grXingRows = arcpy.SearchCursor(GradeCrossings,"LinkKey='" + str(trackRow.Key_) + "'");
    for grxrow in grXingRows:
        inspectionRows = arcpy.SearchCursor(Inspections,"LinkKey='" + str(grxrow.getValue("Key_")) + "'")
        for insprow in inspectionRows:
            grxDefRows = arcpy.UpdateCursor(GradeCrossingDefects,"LinkKey='" + str(insprow.getValue("Key")) + "'")
            for grxdefrow in grxDefRows:
                grxdefrow.LocationVal = 7
                grxDefRows.updateRow(grxdefrow)
            del(grxDefRows)
        del(insprow)
    del(grXingRows)
    arcpy.management.Delete("currentTrack")

However, when I change the 4th line to use arcpy.UpdateCursor, the script still runs, but the data rows that are updated in the inner loop don't get updated in the DB.

Any ideas?

EDIT: Having tested out the suggestions about deleting cursors and rows more thoroughly, I now have the following:

trackRows = arcpy.SearchCursor("tracks","Key_='b2879561-d751-e111-9e92-0050568e18a0'")
for trackRow in trackRows:
    arcpy.management.MakeFeatureLayer("tracks","currentTrack","Key_='" + str(trackRow.Key_) + "'")
    grXingRows = arcpy.UpdateCursor(GradeCrossings,"LinkKey='" + str(trackRow.Key_) + "'");
    for grxrow in grXingRows:
        inspectionRows = arcpy.SearchCursor(Inspections,"LinkKey='" + str(grxrow.getValue("Key_")) + "'")
        for insprow in inspectionRows:
            grxDefRows = arcpy.UpdateCursor(GradeCrossingDefects,"LinkKey='" + str(insprow.getValue("Key")) + "'")
            for grxdefrow in grxDefRows:
                grxdefrow.LocationVal = 7
                grxDefRows.updateRow(grxdefrow)
                del(grxdefrow)
            del(grxDefRows)            
            del(insprow)
        del(inspectionRows)
        del(grxrow)
    del(grXingRows)
    arcpy.management.Delete("currentTrack")
    del(trackRow)
del(trackRows)

Still no luck! I tried deleting the rows within the loop, and tried deleting the rows outside of the loop. Neither solved my problem.

EDIT: Now I feel a little silly. I had a map service running which was maintaining a lock on BOTH of the feature classes I was trying to update. I used arcpy.TestSchemaLock to test, and found that it returned false for both feature classes regardless of whether the first was using a searchcursor or an updatecursor. Stopping the service solved my issue. However there is still some funny behavior here that I'd like to understand:

According to the documentation, if the updatecursor can't get an exclusive lock, then it should error out…but my script ran just fine, no errors…why?
Even given that not erroring were a valid behavior, I still should not have been successful with the inner update EVER when the service was running, but it worked fine as long as it was the only updatecursor I was using. Why?

EDIT: After sleeping on it and trying again, it seems the success from 2 days ago was a fluke. Both yesterday and today I have been unable to get the script to update the database correctly. The script is unchanged, and I have checked to ensure that there are no schemalocks on these feature classes (TestSchemaLock returns true). I'm not sure what stars had aligned which are now misaligned, but I still have no solution to the problem.

Best Answer

Your issue has to do with the Locking action that happens when an UpdateCursor is called. From the help document:

Summary

The UpdateCursor function creates a cursor that lets you update or delete rows on the specified feature class, shapefile, or table. The cursor places a lock on the data that will remain until either the script completes or the update cursor object is deleted.

What that means to me is that if you call an UpdateCursor in the 4th line, then it puts a lock on the data layers that persists and blocks the UpdateCursor you have inside your loop from working. Since you can update many different parts of the feature with this Cursor, including feature geometry, or attribute information, it stands to reason that having a Linked Table in the Cursor will cause a lock to be placed on both tables involved in the join.

The other factor coming into play is the locking of datasets during editing. If the two featureclasses you are trying to update are located in the same Feature Dataset, then even if they are not linked in the Cursor, they would both be locked. Here is some additional information on Schema Locking.

Related Solutions

[GIS] Using arcpy.UpdateCursor to delete rows does not delete based on counter

Following up with some of the comments to your post, I've cleaned up your script a bit. There are some unnecessary variables and the logic is a bit cumbersome. I obviously don't have the shapefile that you are working with and haven't tested this with anything locally so it may not work for you right off the bat, but it should at least give us a good basis to get you going.

import arcpy

shp = r'C:\Trash\Nov2011\ResFinalC6.shp'
sql = " \"PolyGUID\" <> ''"

rows = arcpy.SearchCursor(shp, sql)
delList = []
for row in rows:
    delList.append(row.PolyGUID)
print len(delList)
del rows

rows = arcpy.UpdateCursor(shp, "#", "#", "#", "FMEAS2 A")
for row in rows:
    if row.PolyGUID in delList:
        print "Deleting: %s %s" % (row.PolyGUID,row.FMEAS2)
        rows.deleteRow(row)
del rows

EDIT:

Okay, I think I have a better handle on your process. This is not the most efficient way to do it as it will create a cursor for every PolyGUID whether or not it has duplicate entries, but it's a start... NOTE: Changed second cursor to an update cursor (13:06 EST)

import arcpy

shp = r'C:\Trash\Nov2011\ResFinalC6.shp'
sql = " \"PolyGUID\" <> ''"

#--First, lets get a complete list of all PolyGUIDs
rows = arcpy.SearchCursor(shp, sql)
polyguids = set()
for row in rows:
    polyguids.add(row.PolyGUID)
del rows

print len(polyGUIDs)

#--Now, lets iterate over each PolyGUID...
for guid in polyguids:

    #-we create a new cursor for each PolyGUID that will only return rows for that GUID
    sql = " \"PolyGUID\" = '%s'" % guid
    rows = arcpy.UpdateCursor(shp, sql, "#", "#", "FMEAS2 A")
    x = 0
    for row in rows:
        if x == 0:
            #-ignore the first record (since sorted by 'FMEAS2', first record is the one we want)
            x += 1
        else:
            #-if multiple records with same PolyGUID, delete them
            print "Deleting: %s %s" % (row.PolyGUID,row.FMEAS2)
            rows.deleteRow(row)
    del rows

ArcPy – Why Is Data Access Cursor Performance Enhanced in Recent Versions?

One of the developers of arcpy.da here. We got the performance where it is because performance was our primary concern: the main gripe with the old cursors were that they were slow, not that they lacked any particular functionality. The code uses the same underlying ArcObjects available in ArcGIS since 8.x (the CPython implementation of the search cursor, for example, looks a lot like code samples like this in its implementation except, you know, in C++ instead of C#).

The main two things we did to get the speedup are thus:

Eliminate layers of abstraction: the initial implementation of the Python cursor was based on the old Dispatch/COM based GPDispatch object, which enabled one to use the same API in any language that could consume COM Dispatch objects. This means that you had an API that was not particularly well-optimized for any single environment, but it also meant that there were a lot of layers of abstraction for the COM objects to advertise and resolve methods at runtime, for example. If you remember before ArcGIS 9.3, it was possible to write geoprocessing scripts using that same clunky interface many languages, even Perl and Ruby. The extra paperwork an object needs to do to handle the IDispatch stuff adds a lot of complexity and slowdown to function calls.
Make a tightly integrated, Python specific C++ library using Pythonic idioms and data structures: the idea of a Row object and the really strange while cursor.Next(): dance were just plain inefficient in Python. Fetching an item from a list is a very fast operation, and simplifies down to just a couple of CPython function calls (basically a __getitem__ call, heavily optimized on lists). Doing row.getValue("column") by comparison is more heavyweight: it does a __getattr__ to fetch the method (on which it needs to create a new bound method object), then call that method with the given arguments (__call__). Each part of the arcpy.da implementation is very closely integrated with the CPython API with a lot of hand-tuned C++ to make it fast, using native Python data structures (and numpy integration, too, for even more speed and memory efficiency).

You'll also notice that in nearly any benchmark (see these slides for example), arcobjects in .Net and C++ are still over twice as fast as arcpy.da in most tasks. Python code using arcpy.da is faster, but still not faster than a compiled, lower-level language.

TL;DR: da is faster because da is implemented in straight-up, unadulterated Arcobjects/C++/CPython which was specifically designed to result in fast Python code.

Best Answer

Related Solutions

[GIS] Using arcpy.UpdateCursor to delete rows does not delete based on counter

ArcPy – Why Is Data Access Cursor Performance Enhanced in Recent Versions?

Related Question