topic Re: Nested searchcursor to update the nesting data in Python Questions

Nested searchcursor to update the nesting data

GeoffOlson — Thu, 20 Feb 2014 18:07:58 GMT

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?

Re: Nested searchcursor to update the nesting data

RichardFairhurst — Sat, 11 Dec 2021 15:51:56 GMT

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?

You have left out crucial code or your code is not correct. Nothing initializes rows2, so there are no rows to update. If rows2 is initialized outside of this loop it will only run one time and then for the next row1 feature it will never update the row. It also may have destroyed rows2 after finishing the first loop through that second cursor so that the second row1 feature would then trigger an error. I also think the Select layer won't affect or screws up a cursor that was already populated. You have to populate the cursor after the selection change.

However, don't do this code. This is bad coding from the get go and should be abandoned in favor of a dictionary or list replacing the second cursor. Only load the second cursor table once to a dictionary or list and never read that table again. Cursor disk reads kill scripts like this. 1,000 list reads will be 1,000 times faster than 1,000 separate cursor queries especially if only one row is returned per the row2 cursor query you intended to have used.

Caleb Mackey's code proposal in this post to replace an embedded cursor loop with a dictionary for the inner cursor is much closer to what you should do. Other forum posts dealing with dictionaries or lists should be what you look for rather than any embedded cursor routine code.

It also looks like you are using the old cursor syntax, which is terribly slow. If you have 10.1 or higher use data access style cursors only. They are 10 times as fast. If you can't use data access cursors that is even more of a reason to redesign this code to eliminate an embedded cursor at all costs.

Re: Nested searchcursor to update the nesting data

MichaelVolz — Thu, 20 Feb 2014 21:06:03 GMT

http://forums.arcgis.com/threads/102800-Copy-Row-fromr-spatial-Join-featues

Use this thread as a reference for a dictionary instead of 2 loops. Caleb Mackey provides excellent code samples.

Re: Nested searchcursor to update the nesting data

GeoffOlson — Sat, 11 Dec 2021 15:51:59 GMT

Thank you for that. I kind of restarted what I had yesterday, and I'll past the whole thing this time. I understand the posts I've quickly looked at in that link and will give that consideration when I rework this again. For now this is what I have. And just our of curiousity, I already know you don't recommend the old cursors (read that part) can a nested loop update or set a value to a layer outside of itself? In other words, can "row2" update a field in "row1?"

import arcpy, os, time

#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_Address2") [0]
ID_Silvis = int()

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.UpdateCursor(mapLyr2)
    for row2 in rows2:
        hydfield = "Hydr_Num" + str(hydcount)
        hydID = ID_Silvis
        row.setValue(hydfield, hydID)
        rows1.updateRow(row)
        hydcount = hydcount + 1
    arcpy.SelectLayerByAttribute_management(mapLyr2, "CLEAR_SELECTION")

del mxd, row, rows1, row2, rows2, mapLyr1, mapLyr2, hydcount, hydfield, hydID

I know this code is considered slow, but from what I've learned of Python so far, it's the best I know how to do. But I will read through the page you linked. Thank you.

Re: Nested searchcursor to update the nesting data

RichardFairhurst — Sat, 11 Dec 2021 15:52:02 GMT

Thank you for that. I kind of restarted what I had yesterday, and I'll past the whole thing this time. I understand the posts I've quickly looked at in that link and will give that consideration when I rework this again. For now this is what I have. And just our of curiousity, I already know you don't recommend the old cursors (read that part) can a nested loop update or set a value to a layer outside of itself? In other words, can "row2" update a field in "row1?"
import arcpy, os, time

#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_Address2") [0]
ID_Silvis = int()

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.UpdateCursor(mapLyr2)
    for row2 in rows2:
        hydfield = "Hydr_Num" + str(hydcount)
        hydID = ID_Silvis
        row.setValue(hydfield, hydID)
        rows1.updateRow(row)
        hydcount = hydcount + 1
    arcpy.SelectLayerByAttribute_management(mapLyr2, "CLEAR_SELECTION")

del mxd, row, rows1, row2, rows2, mapLyr1, mapLyr2, hydcount, hydfield, hydID
I know this code is considered slow, but from what I've learned of Python so far, it's the best I know how to do. But I will read through the page you linked. Thank you.

The code should work (slowly), since embedded cursors are possible. I am not sure what ID_Silvis is doing, since it is initialized to 0 and never changes. I would have thought you would have been doing something like this instead:

hydID = row2.GetValue("ID_Silvis") # or whatever field that holds the row2 ID

I also think you would delete row2 and rows2 inside the first level loop after clearing the selection on the mapLyr2 rather than outside all of the loops, since it recreates for the next first level loop row. Also rows1.updateRow(row) should happen outside the second loop, since you only want to save the updates after every field is updated to avoid slowing the code even more. So I would do the code something like this:

import arcpy, os, time

#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_Address2") [0]
ID_Silvis = int()

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.UpdateCursor(mapLyr2)
    for row2 in rows2:
        hydfield = "Hydr_Num" + str(hydcount)
        hydID = row2.GetValue("ID_Silvis") # or whatever field that holds the row2 ID
        row.setValue(hydfield, hydID)
        hydcount = hydcount + 1
    arcpy.SelectLayerByAttribute_management(mapLyr2, "CLEAR_SELECTION")
    rows1.updateRow(row)
    del row2, rows2

del mxd, row, rows1, mapLyr1, mapLyr2, hydcount, hydfield, hydID

This won't void out "Hydr_Num" + str(hydcount) for any row later deleted from mapLyr2. So if a previous run of the program assigned a value to one of these fields and then you deleted one of the hydrants from that run, the next run would not null out the now unused hHydr_Num# field.

You would need a second loop after clearing the selection to continue from the last hydcount value in the rows2 loop to the last number of the Hydr_Num# fields you have created to Null out the rest of the fields (or set them to some other value indicating nothing was found). Then you would process the rows1.updateRow(row) line.

You might also consider a fail safe break in the event you reach a Hydr_Num# count that exceeds the number of fields you have created and some type of print statement to alert you that you hit it.

Re: Nested searchcursor to update the nesting data

GeoffOlson — Sat, 11 Dec 2021 15:52:05 GMT

Thank you for your help Richard. You're right about ID_Silvis. Between revisions that got mixed up. ID_Silvis is the field I need copied. So I went and corrected that and on a single feature with 2 points it seems to be working. The next thing, would be to create a selection of features to run the script on. So right now I have about 340 points, but when I do a join, they only seem to be associated with 90 parcels. So to create a definition query that could select just those 90 parcels and run the script on those instead of 3,500 parcels. I will be reading into how to create a list and using that to create a selection. So far here's what I've got working.

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()

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.UpdateCursor(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")

del mxd, row, rows1, mapLyr1, mapLyr2, hydcount, hydfield, hydID, row2, rows2

Re: Nested searchcursor to update the nesting data

RichardFairhurst — Sat, 11 Dec 2021 15:52:07 GMT

Thank you for your help Richard. You're right about ID_Silvis. Between revisions that got mixed up. ID_Silvis is the field I need copied. So I went and corrected that and on a single feature with 2 points it seems to be working. The next thing, would be to create a selection of features to run the script on. So right now I have about 340 points, but when I do a join, they only seem to be associated with 90 parcels. So to create a definition query that could select just those 90 parcels and run the script on those instead of 3,500 parcels. I will be reading into how to create a list and using that to create a selection. So far here's what I've got working.

Sounds to me like all you need to do is include a Select By Location on mapLyr1 before running the loop. You should also change the rows2 cursor from an Update cursor to just a search cursor if you are not actually updating the row2 objects. You could add speed by limiting the attribute fields to just the ID_Silvis field for rows2, since you are not using all of the fields.

I would have thought your row update cursor would expand the field list to include all possible "Hydr_Num"# field names in the field list, since that is safer. You should include all field names that you read from or write to in the field list. I also think you should include the del row2, rows2 line I have added, since those cursor objects regenerate in the loop and need clean up there.

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()

# uncomment the next line if you want to be sure all points affect the parcels selected
#    arcpy.SelectLayerByAttribute_management(mapLyr2, "CLEAR_SELECTION")
arcpy.SelectLayerByLocation_management(mapLyr1, 'intersect', mapLyr2)
# add every Hydr_Num field name to list
rows1 = arcpy.UpdateCursor(mapLyr1, "", "", "NEW_PIN; Hydr_Num1; Hydr_Num2; Hydr_Num3; Hydr_Num4")
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, "", "", "ID_Silvis")
    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)
    del row2, rows2
#    arcpy.SelectLayerByAttribute_management(mapLyr2, "CLEAR_SELECTION")

del mxd, row, rows1, mapLyr1, mapLyr2, hydcount, hydfield, hydID

Re: Nested searchcursor to update the nesting data

GeoffOlson — Mon, 24 Feb 2014 17:28:14 GMT

Since there aren't too many points to parcels in the data, I was able to sort the attribute table of the points and see what the max number of hydrants per parcel there was and so I created 6 or 7 "hydr_x" fields. The counter starts at one and can fill up to that point. You're right about what if I run out of fields, though. Then the counter resets back to 1 at the beginning of the first loop. I tried adding the del row2, rows2 to the end of the first loop, but I get an error saying row2, rows2 isn't defined.

For a selection, I was thinking about creating a list of all unique values in the point layer and using that as a definition query. Not all the points are contained within a parcel. But I did use a spatial join of "nearest neighbor" to get a PIN for each point. So select by location wouldn't return all the parcels that need to be selected.

Re: Nested searchcursor to update the nesting data

RichardFairhurst — Mon, 24 Feb 2014 17:54:44 GMT

Since there aren't too many points to parcels in the data, I was able to sort the attribute table of the points and see what the max number of hydrants per parcel there was and so I created 6 or 7 "hydr_x" fields. The counter starts at one and can fill up to that point. You're right about what if I run out of fields, though. Then the counter resets back to 1 at the beginning of the first loop. I tried adding the del row2, rows2 to the end of the first loop, but I get an error saying row2, rows2 isn't defined.

For a selection, I was thinking about creating a list of all unique values in the point layer and using that as a definition query. Not all the points are contained within a parcel. But I did use a spatial join of "nearest neighbor" to get a PIN for each point. So select by location wouldn't return all the parcels that need to be selected.

Since you have defined layers you can use the Add Join tool. Make sure both feature classes have an Attribute Index on the NEW_PIN common fields and then create the join on those fields. Then do a select by attribute where the mapLyr2tablename.OBJECTID > -1. Then use the Remove Join tool. That will do the selection for you.

Just for my entertainment do just del rows2 instead of del row2 and rows2. But if you don't have a lot of records it could be unnecessary.

Re: Nested searchcursor to update the nesting data

GeoffOlson — Sat, 11 Dec 2021 15:52:10 GMT

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

Re: Nested searchcursor to update the nesting data

RichardFairhurst — Sat, 11 Dec 2021 15:52:13 GMT

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.