Order of da.UpdateCursor

[SilvanStöckli]

Hello

I'd like to manipulate the order the update cursor loops through my attribute table.

At the moment, my code looks as follows:

with arcpy.da.UpdateCursor(lyr, column) as cursor:

        for row in cursor:

            row[0] = i

            i = i+1

            cursor.updateRow(row)

where "lyr" is my layer and "column" is my column where the cursor needs to update the data. at the moment loops through and adds 1 as it needs but it does it in the order of the Object ID. I have an other column named "Nummer" where I have Numbers (Doulbe). The coursor should update the rows following those numbers (from 1 to x). How can I do it?

I tried "with arcpy.da.UpdateCursor(lyrkartenausschn, seitenzkartenausschn,"","","", sql_clause=(None, 'ORDER_BY Nummer A') as cursor: "

And also without all of those "", "", and also with "ORDER BY"  and with/without the "A". But it always gives me an error.

Can anyone help me fixing this part of the code?

thanks! 😄

[JoshuaBixby]

It is "ORDER BY", not "ORDER_BY" with the underscore between the two.  What is the exact message when you try:

with arcpy.da.UpdateCursor(lyrkartenausschn, seitenzkartenausschn, sql_clause=(None, "ORDER BY Nummer")) as cursor:

[SilvanStöckli]

Thanks Joshua for helping me!

I copy pasted your Code and it says:

Runtime error Traceback (most recent call last):   File "<string>", line 59, in <module> RuntimeError: An invalid SQL statement was used. [SELECT Seitenzahl, SHAPE_Area, SHAPE_Length FROM Kartenausschnitte_Vertrag ORDER BY Nummer]

Line 59 is the first line of the for loop

It seems to me as it would want to go trough other columns aswell (because Seitenzahl, SHAPE_Area are other columns in that table)

[RichardFairhurst · Accepted solution]

Silvan:

This is where an adaptation of my Turbo Charging Data Manipulation With Python Cursors and Dictionaries can help overcome the limitation of the DA cursor to control record order (which is a slow database operation even if it worked) and instead take advantage of the speed of a dictionary.  The dictionary keys can be sorted and will also let you quickly access them randomly as you write the data back to the cursor.

To do this run a search cursor first through the entire record set and create a dictionary of sortable keys with initial values of 0.  Process the sorted dictionary keys and increase their values using a counter.  Finally run the update cursor and write to it using the dictionary.  I would use both your Nummer field and the ObjectID field values to be sure all keys are unique, since otherwise a repeated value in Nummer would mess up the counter.  (You are not limited to using the Nummer/ObjectID combination and this is how you can also set up relationships based on multi-field unique key values to overcome the limitations of single field joins).  The speed of this code will be virtually as fast as just running the update cursor.

So here is the code (replace 'COUNTER' with your actual counter name field):

import arcpy
lyr = YourLayer # replace this with your layer name
fields = ['Nummer', 'OID@']
# set up a dictionary and counter
dict = {}
counter = 0
# Use list comprehension to build a dictionary of Nummer/ObjectID tuple keys
dict = {(row[0:]):0 for row in arcpy.da.SearchCursor(lyr, fields)}
# Sort the dictionary keys and increase the counter values based on the key order
for value in sorted(dict.keys()):
    counter += 1
    dict[value] = counter
# Write back to the layer with an update cursor using the dictionary. Change 'COUNTER' to your counter field
fields = ['Nummer', 'OID@', 'COUNTER']
with arcpy.da.UpdateCursor(lyr, fields) as cursor:
    for row in cursor:
        row[2] = dict[(row[0],row[1])]
        cursor.updateRow(row)

[SilvanStöckli]

Richard! I don't know what to say but: you made my day! 🙂

It works perfectly and does exactly what I wanted.

Thank you so much for providing the code the way I only have to replace two words.

I think I would have been able to write this code by myself because the list is something I don't understand yet.

Thank you again! 😄

[RichardFairhurst]

Silvan:

Glad I could help.  Dictionaries and lists provide powerful methods for working with data as you can see.  You should definitely invest some time in learning how to use them.  They can come in very handy for problems like this.  Have a great day.

Richard

[BlakeTerhune]

I have two questions for you, Richard:

1. Does using OID@ for the field name let the cursor dynamically find the ObjectID field even if it isn't called ObjectID?
2. Could you explain the first colon in the list comprehension on line 8?

(row[0:])

[RichardFairhurst]

Blake:

1. Yes, using "OID@" will work for any field name that is identified as an OID type field.  So the names FID, OID, OBJECTID, etc. will all work as long as the field is typed as an OID field.

2.  (row[0:]) breaks down as follows:

A.  (...) creates a tuple (the outside parenthesis define this as a tuple).

B.  row[0:] reads the row pulled by the for loop within the list comprehension and iterates the listed fields in the field list parameter of the cursor.  row[0:] is the same as saying read all field values starting with the first field (0 based) and continuing to the last field for the current row.  The remainder of the list comprehension syntax understands to insert a comma between each field value since it is being comprehended within the tuple container.  So the full line is building a dictionary like:

dict = {(1,1):0,(2, 12):0,(3, 13):0,(4, 15):0,(4, 16):0,...};

The duplicate value of 4 in Nummer field is uniquely counted because it is being paired with the ObjectID value in each dictionary key.

[BlakeTerhune]

Oh, it's slicing! Very cool.

[RichardFairhurst]

Yes, it is slicing.  I remembered that term last night, but it did not come to mind when I was writing the response.