nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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! 😄

Encontre mais publicações marcadas com

Accepted answers

RichardFairhurst

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)

All comments

SilvanStöckli

Hello Joshua

I'm using 10.3 aswell. So I think this isn't the problem.

RichardFairhurst

Please be clear. Original update cursors and data access (da) module update cursors are two different things and may operate differently when it comes to the ORDER BY option. da update cursors are fast and the only cursor type worth using. So are you using the ORDER BY with a da module update cursor and if so what is the syntax? The documentation does not cover this aspect of using the da cursor at all and it would be nice to know how to use it if it is an option.

Having said that, dictionaries can out perform the ORDER BY operation by a factor of at least 10 times the speed, since no disk access is involved, whereas Updating using the Order By operator has to retrieve and write to each record in order, which is very slow in my experience and severely impacted by the overall size of the table, whereas unsorted reading to a dictionary and sorting the dictionary is extremely fast.

JoshuaBixby

What version of ArcGIS are you using? I am running ArcGIS 10.3, and I don't have any issues with using ORDER BY in a SQL clause for update cursors. That said, I do seem to recall problems with it back in ArcGIS 10.1 and possibly 10.2.0. Anyhow, I am just interested in whether your error might be related to the version you are running.

blake.terhune

I have two questions for you, Richard:

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

(row[0:])

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:

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

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)

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:

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.