Nested searchcursor to update the nesting data

GeoffOlson

I have a script that uses one layer to select features of a second layer in the first loop of the script.  When the second layer's features are selected, I have a second loop that is supposed to update the fields on the first layer in the first loop.   Is there a specific way of using an updateCursor or setValue to affect the nesting loop?

for row1 in rows1:
    count = 1
    print "row %d" %parcelrow
    parcelrow = parcelrow +1
    selectcount = 0
    currentPIN = row1.getValue(selectField)
    arcpy.SelectLayerByAttribute_management(mapLyr1, "NEW_SELECTION", "\"%s\" = '%s'" %(selectField, currentPIN))
    print currentPIN
    arcpy.SelectLayerByAttribute_management(mapLyr2, "ADD_TO_SELECTION", "\"%s\" = '%s'" %(selectField, currentPIN))
    selectcount = int(arcpy.GetCount_management(mapLyr2).getOutput(0))
    print "%d points selected" %selectcount
    if selectcount == 0:
        arcpy.SelectLayerByAttribute_management(mapLyr1, "CLEAR_SELECTION")
        pass
    else:
        for row2 in rows2:
            hydrantfield = copytoField + "%d" %count
            print "filling field " + hydrantfield
            hydrID = row2.getValue(copyField)


#This setValue is supposed to update mayLyr1

            row1.setValue(hydrantfield, hydrID)
            rows1.updateRow(row1)
            count = count + 1
    arcpy.SelectLayerByAttribute_management(mapLyr1, "CLEAR_SELECTION")
    arcpy.SelectLayerByAttribute_management(mapLyr2, "CLEAR_SELECTION")

The second loop seems to get stuck.  Shouldn't the loop only run through the selected features then continue back to the first loop?

RichardFairhurst

The probably looks pretty ugly, but I found two examples that I got working to create a list and definition query for the features.  Here's the entire script.  I did not try del rows2 yet in the first loop.

import arcpy
#set map doc and the layer to be used
mxd = arcpy.mapping.MapDocument("Current")
mapLyr1 = arcpy.mapping.ListLayers(mxd, "Parcels_With_PageNum") [0]
mapLyr2 = arcpy.mapping.ListLayers(mxd, "H2O_Pts_Nearest_Address") [0]
ID_Silvis = int()

field = 'NEW_PIN'
fcName = 'H2O_Pts_Nearest_Address'
myList = set([row.getValue(field) for row in arcpy.SearchCursor(fcName)])

field = 'NEW_PIN'
dquery = []
for mapLyr2 in arcpy.mapping.ListLayers(mxd, fcName):
    for row in arcpy.SearchCursor(mapLyr2):
        dquery.append("'{}'".format(row.NEW_PIN))
    mapLyr1.definitionQuery = '"NEW_PIN" in ({})'.format(", ".join(dquery))


rows1 = arcpy.UpdateCursor(mapLyr1, "", "", "NEW_PIN")
for row in rows1:
    hydcount = 1
    newpin = row.NEW_PIN
    print newpin
    arcpy.SelectLayerByAttribute_management(mapLyr2, "NEW_SELECTION", "\"NEW_PIN\" = '%s'" %newpin)
    rows2 = arcpy.SearchCursor(mapLyr2)
    for row2 in rows2:
        hydfield = "Hydr_Num" + str(hydcount)
        ID_Silvis = row2.ID_Silvis
#        arcpy.CalculateField_management(mapLyr1, hydfield, hydID, "PYTHON")
        row.setValue(hydfield, ID_Silvis)
        hydcount = hydcount + 1
    rows1.updateRow(row)
    arcpy.SelectLayerByAttribute_management(mapLyr2, "CLEAR_SELECTION")
mapLyr1.definitionQuery = ''

del mxd, row, rows1, mapLyr1, mapLyr2, hydcount, hydfield, row2, rows2, dbquery, field, myList, fcName, field#, hydID

The code addition does not look all that bad and using a definition query is wise, since they are faster than regular queries.  Anyway, if the code now does everything you need that is great.

However, now that you are running through the entire record set of the mapLay2 anyway before entering your first loop why build a list and not a dictionary?  The dictionary would replace the entire inside loop and your performance would increase at least 10 fold minimum if done correctly.  Hitting the recordset on disk for each inner loop query really makes perfance go down the tubes if done over a big enough record set.

You would build the dictionary much like you have the list with the NEW_PIN values acting as your key.  A tuple object would hold all of the hydrant numbers as the dictionary value associated each key.  Iterating the dictionary keys to create your definition query is no more time consuming that iterating the list.  The tuples of hydrant numbers within each key would iterate at blazing speed compared to the code you have written.  As long as you are not talking incredibly large datasets, load your entire mapLyr2 table to memory before entering the first loop and this tool should perform like a dream.  You can eliminate the Search Layer by Attribute on the mapLyr2 (big speed gain) and the inner rows2 cursor with an iteration of the tuple value associated with the NEW_PIN key.  You are now 75% there in your code structure.  When you go to data access cursors ultimately after upgrading to 10.1 or higher you would get another big speed boost.