topic Re: da.SearchCursor: Nested cursor will not 'reset' in Python Questions

da.SearchCursor: Nested cursor will not 'reset'

ZacharyHart — Thu, 13 Jan 2022 19:54:39 GMT

To be fair, I don't know if this actually qualifies as a 'nested cursor' but it is a cursor calling up a function which has a cursor in it.

For starters, here is my function: it simply assigns a new identifier based on the points spatial location:

def SpatialRenumberPoints(InLayer, StartNumber): arcpy.management.AddXY(InLayer)##Could verify if exists first## arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first## fields = ['OID@','POINT_X','POINT_Y','NewID'] sqlOrder = "ORDER BY {0} DESC, {1} ASC".format(fields[2], fields[1]) with arcpy.da.UpdateCursor(InLayer, fields,sql_clause = (None, sqlOrder)) as cursor: for row in cursor: row[3]= StartNumber StartNumber = StartNumber + 1 cursor.updateRow(row) del cursor, row

When calling this function up in the Python Window, the next time it was run, the function would continue where it's last highest value left off. This was fixed by deleting out the cursor at the end of the function. Life is/was good.

Now, I realized I had a use case where I wanted to run the same tool against a dataset which has groups of points (identified by an attribute) and iterate through each group. No problem I thought, I'll just use use 'GROUP BY' for the SQL Clause in a search cursor. Like so:

with arcpy.da.SearchCursor('PlotCopy',['CLUSTER_ID'],sql_clause=(None, "GROUP BY CLUSTER_ID")) as cur: for row in cur: SpatialRenumberPoints('PlotCopy',1) print(row)

The only problem is that here again, the cursor isn't resetting or starting from '1' for each iteration.

Perhaps I'm using GROUP BY inappropriately and should use my search cursor to setup a selection instead?

Thanks to anyone who can help!!

Re: da.SearchCursor: Nested cursor will not 'reset'

DanPatterson — Thu, 13 Jan 2022 20:18:22 GMT

I like selections since you can use them then use the same code to set the selection to none, before carrying on with the next selection, I just don't skip the middle step of setting the selection to none after the initial selection and before the next (theoretically it shouldn't matter, but I like to make sure I am starting with a clean slate each time and not relying on underlying magic)

Re: da.SearchCursor: Nested cursor will not 'reset'

CMV_Erik — Fri, 14 Jan 2022 22:38:14 GMT

In this case, you could get away without a parameter; just set StartNumber = 1 as the first line of the function. If you want to retain the ability to change with a parameter, you could declare a new local variable inside the function and use it instead, like below. That way, every new function call should automatically start with 1 because the value is never shared outside the function.


def SpatialRenumberPoints(InLayer, paramStartNumber):
    StartNumber = paramStartNumber
    arcpy.management.AddXY(InLayer)##Could verify if exists first##
    arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first##
    fields = ['OID@','POINT_X','POINT_Y','NewID']
    sqlOrder = "ORDER BY {0} DESC, {1} ASC".format(fields[2], fields[1])
    with arcpy.da.UpdateCursor(InLayer, fields,sql_clause = (None, sqlOrder)) as cursor:
        for row in cursor:
            row[3]= StartNumber
            StartNumber = StartNumber + 1
            cursor.updateRow(row)
    del cursor, row

Re: da.SearchCursor: Nested cursor will not 'reset'

JoshuaBixby — Mon, 17 Jan 2022 16:51:18 GMT

In your second code snippet, the overall search cursor and the update cursor in SpatialRenumberPoints are independent of each other. The reason the numbering isn't resetting is that the numbering is starting at 1 and going until the end for all records each time you call SpatialRenumberPoints.

What might work is if you pass the CLUSTER_ID value through to SpatialRenumberPoints as a new argument, and then add a SQL WHERE clause to the update cursor in SpatialRenumberPoints to filter records to only that CLUSTER_ID.

Re: da.SearchCursor: Nested cursor will not 'reset'

ZacharyHart — Thu, 03 Feb 2022 14:30:08 GMT

I clicked too soon. Declaring the variable within the function did not fix the issue. I think I need to consider something like what @JoshuaBixby suggested.

Re: da.SearchCursor: Nested cursor will not 'reset'

ZacharyHart — Thu, 03 Feb 2022 15:32:58 GMT

@JoshuaBixby @CMV_Erik Sorry for the lag in response here, we've been swamped with project work so dev efforts get shelved.

@JoshuaBixby wrote:
The reason the numbering isn't resetting is that the numbering is starting at 1 and going until the end for all records each time you call SpatialRenumberPoints.

That is the exact insight I needed. To be real honest, I'm pretty sure my lack of this comprehension has confounded other things in the past too. I tried Erik's solution but it didn't work for, I presume, the exact reason you point out.

My challenge is that I want to keep this function stand alone to be used in other scripts, so I'll try to make an ~~option argument like 'GroupField=None' or something.~~ Nope that's a dead end...doh!

UPDATE:

Here are Josh's suggestions integrated and the code is now working!

with arcpy.da.SearchCursor('PlotCopy',['CLUSTER_ID'],sql_clause=(None, "GROUP BY CLUSTER_ID")) as cur: for row in cur: SpatialRenumberPoints('PlotCopy',1,'CLUSTER_ID',row[0]) print("Updating {0}...".format(row[0]))

which calls up the function:

def SpatialRenumberPoints(InLayer, StartNumber, GroupField=None, GroupID=None): arcpy.management.AddXY(InLayer)##Could verify if exists first## arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first## fields = ['OID@','POINT_X','POINT_Y','NewID'] if GroupField is None: WhereClause = None else: WhereClause =""" "{0}" = '{1}' """.format(GroupField, GroupID) print(WhereClause) sqlOrder = "ORDER BY {0} DESC, {1} ASC".format(fields[2], fields[1]) with arcpy.da.UpdateCursor(InLayer, fields, where_clause = WhereClause, sql_clause = (None, sqlOrder)) as cursor: for row in cursor: row[3]= StartNumber StartNumber = StartNumber + 1 cursor.updateRow(row) del cursor, row

@JoshuaBixby I'm guessing this looks pretty crude to you, any suggestions for improvements? Also, is the Group By in the Search Cursor even needed now? Thanks for any additional insight!

Re: da.SearchCursor: Nested cursor will not 'reset'

CMV_Erik — Thu, 03 Feb 2022 15:41:51 GMT

I didn't look closely enough at the code the first time, but I agree. @JoshuaBixby nailed it.

Re: da.SearchCursor: Nested cursor will not 'reset'

CMV_Erik — Thu, 03 Feb 2022 19:41:20 GMT

To try to make up for my oversight, an alternate approach:

If there's only one dataset involved, this could be also done with a single cursor & no functions if you sort the update cursor by CLUSTERID. More work per record, but fewer geodatabase requests. Something like:

InLayer = 'PlotCopy'
arcpy.management.AddXY(InLayer)##Could verify if exists first##
arcpy.AddField_management(InLayer, "NewID", 'LONG')##Could verify if exists first##
fields = ['OID@','POINT_X','POINT_Y','NewID', 'CLUSTERID']
sqlOrder = "ORDER BY {0} ASC, {1} DESC, {2] ASC".format(fields[4], fields[2], fields[1])

last_cluster_id = 0
StartNumber = 1
with arcpy.da.UpdateCursor(InLayer, fields, sql_clause = (None, sqlOrder)) as cursor_sorted_by_cluster_id:
    for row in cursor_sorted_by_cluster_id:
        # test if the clusterid was the same as the last one
        if row[4] == last_cluster_id:   # still the same CLUSTERID group
            StartNumber += 1            # increment the counter
        else:                           # starting a new CLUSTERID group - reset
            last_cluster_id = row[4]    # change the value   
            StartNumber = 1             # reset the counter
        row[3] = StartNumber
        cursor_sorted_by_cluster_id.updateRow(row)