topic Re: Order of da.UpdateCursor in Python Questions

Order of da.UpdateCursor

SilvanStöckli — Tue, 07 Apr 2015 19:15:05 GMT

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

Re: Order of da.UpdateCursor

JoshuaBixby — Tue, 07 Apr 2015 20:08:23 GMT

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:

Re: Order of da.UpdateCursor

SilvanStöckli — Wed, 08 Apr 2015 06:14:51 GMT

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]

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)

Re: Order of da.UpdateCursor

RichardFairhurst — Sat, 11 Dec 2021 10:16:55 GMT

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)

Re: Order of da.UpdateCursor

SilvanStöckli — Wed, 08 Apr 2015 16:34:47 GMT

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

Re: Order of da.UpdateCursor

RichardFairhurst — Wed, 08 Apr 2015 17:05:55 GMT

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

Re: Order of da.UpdateCursor

BlakeTerhune — Wed, 08 Apr 2015 20:28:32 GMT

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:])

Re: Order of da.UpdateCursor

JoshuaBixby — Wed, 08 Apr 2015 22:00:45 GMT

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.

Re: Order of da.UpdateCursor

RichardFairhurst — Thu, 09 Apr 2015 00:19:44 GMT

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.

Re: Order of da.UpdateCursor

RichardFairhurst — Thu, 09 Apr 2015 00:34:42 GMT

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.

Re: Order of da.UpdateCursor

SilvanStöckli — Thu, 09 Apr 2015 06:41:54 GMT

Hello Joshua

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

Re: Order of da.UpdateCursor

JoshuaBixby — Thu, 09 Apr 2015 15:59:12 GMT

Richard Fairhurst wrote:
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.

10 times? What backend storage are you using?

As a quick test, I created a table with one million records and two fields. One field held a randomly generated number and the other just an empty field to be updated in order of the other field. When using an in_memory table, the ORDER BY approach was 300% faster. When using a file geodatabases, the ORDER BY approach was 7% slower. When using a personal geodatabases, the ORDER BY approach was the same. Disregarding the in_memory example, file-based geodatabases don't take that much of a hit using ORDER BY with an update cursor (arcpy.da).

The sorting of a dictionary might not involve any disk access, but the overall approach of the dictionary method surely does involve disk access. Iterating over a search cursor to populate the dictionary hits the disk, and iterating over an update cursor to update records hits the disk again. Although there is some overhead in having the database engine order the records, there is also overhead in iterating over the records twice instead of once.

I don't have access to Oracle or SQL Server this morning, so maybe ORDER BY does perform significantly worse in this case. At least for file-based geodatabases, I don't see a significant difference in performance.

Re: Order of da.UpdateCursor

RichardFairhurst — Thu, 09 Apr 2015 16:52:48 GMT

I am basing my statements on ArcObjects cursors, not Python cursors, since I don't know how to make a da cursor sort. da cursors seem more optimized than ArcObjects cursors, so that may be the reason you see such fast performance. Several years ago I gave up on sorted cursors and never was willing to try them again, especially since there is virtually no documentation on how to set it up (figuring out ArcObjects was a pain and disappointment).

ORDER BY is not documented with da cursors. Document how to use it in this post. I am at a disadvantage, since I have explained my code so you can test my approach, but you have not shown or explained your da cursor syntax as far as I have seen so I cannot test your approach (if I missed it, please repeat it).

My technique is actually more of a way to avoid doing joins and field calculations than it is to avoid sorting within a single feature class, but it fits this need as well. In the case of joins and data transfers between feature classes/tables my approach is much faster than all other methods I have tried.

Re: Order of da.UpdateCursor

BlakeTerhune — Thu, 09 Apr 2015 17:00:43 GMT

Oh, it's slicing! Very cool.

Re: Order of da.UpdateCursor

RichardFairhurst — Thu, 09 Apr 2015 17:05:35 GMT

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

Re: Order of da.UpdateCursor

JoshuaBixby — Sat, 11 Dec 2021 10:16:58 GMT

Fair enough, now I understand the perspective you are coming from.

If you haven't seen it already, you might be interested in the How is the data access cursor performance so enhanced compared to previous versions? thread over at StackExchange. The first answer is from Jason Scheirer, one of the arcpy.da developers. In his response he explains how they optimized the performance of the new cursors. There is also a link to some benchmark results.

Although the sql_clause documentation has remained the same, the arcpy.da SearchCursor code samples have been expanded with ArcGIS 10.3 to include an SQL ORDER BY example. The SearchCursor code sample is transferrable to the UpdateCursor.

The sql_clause was buggy when the data access module was first released at ArcGIS 10.1. I believe it was ArcGIS 10.2.1 where a couple bugs related to the sql_clause were patched.

Below is a code snippet that creates a table with randomly generated values and then sorts against those values to update another field in order.

import arcpy
import numpy
import random

fc = #table location in file geodatabase

random.seed(3.14)
l = []
for i in range(1000000):
    l.append((random.randint(0,100000000), 0.0))
narr = numpy.array(l, dtype=([('rand','i4'),('numb', 'f8')]))
arcpy.da.NumPyArrayToTable(narr, fc)

i = 1000
with arcpy.da.UpdateCursor(fc, ["rand", "numb"],sql_clause=(None,"ORDER BY rand")) as cur:
    for row in cur:
        i += 1 
        row[1] = i
        cur.updateRow(row)

Re: Order of da.UpdateCursor

RichardFairhurst — Fri, 10 Apr 2015 23:26:50 GMT

I appreciate getting information that brings me up to date on what is happening with da cursors in the latest versions of ArcGIS. I don't have 10.3, and only recently got 10.2.1 installed. I tried the Order By option at 10.1 with the release of the da cursor, but I gave up on it. I have not tried it since then.

This has happened to me several times where I try something that should work in an early release and discover that it has serious issues that make it either fail or perform horribly. Once I give up on code that fails me it can be hard to give Esri another chance. Generally figuring out a workaround took so much effort that I resent the original method that should have worked for wasting so much of my time. If a workaround is very effective, then even if the workaround is complex, I forget to even consider the possibility that the original approach that is simpler might have been fixed in the latest release. Anyway, thanks for bringing me up to date.

Re: Order of da.UpdateCursor

JoshuaBixby — Sat, 11 Apr 2015 01:30:38 GMT

I understand, completely. The fact that some of the sql_clause bugs took until 10.2.1 to get addressed is, well, frustrating. Oddly enough, I just tried my code snippet on an ArcGIS 10.1 SP1 machine and it worked. I thought ORDER BY was also impacted by NIM094797, but that doesn't seem to be the case.

Overall, I have found the ArcPy documentation in the new 10.3 help to be either improved or expanded for quite a few objects, methods, functions, etc....