SQL sort parameter syntax for da.cursors?

4359
9
Jump to solution
08-13-2012 01:03 PM
ChrisSnyder
Regular Contributor III
Looking for examples of using the "ORDER_BY" SQL syntax that is now suported in the arcpy.da cursor model...

Help system is lacking in examples on this front...

Can any v10.1 pro rewrite this in arcpy.da format?

arcpy.UpdateCursor(oesfHydroDislvFC, "", "", "", "SL_WTRTY_CD A;RIP_COMBO_UID D")
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
ChrisFox3
Occasional Contributor III
Thanks Chris

I'm thinking the "fancy" SQL parameter is the 6th one down the line, so it probably should be something like this:

arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, None, None, False, (None, 'ORDER_BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))

But that is thowing an error:
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
TypeError: expected callable function - logger(obj)

Been trying a lot of things for a while - with nothing working out...


If you used a named parameter than you don't have to specify the value for every optional paramater to the function. This makes your code a lot easier to write and read and sometimes resolves errors that may have accidentally been added. So since you are just accepting the defaults for the 3rd 4th and 5th parameter you could change your code to:

arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, sql_clause=(None, 'ORDER_BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))


Also make sure the input is a Geodatasbase Feature Class, the sql_clause paramater does not work on shapefiles. I just tested this similar expression on my data and it worked as expected. If you remove the sql_clause paramater is the cursor created succesfully? Did you want to share a small subset of your data?

View solution in original post

0 Kudos
9 Replies
ChrisFox3
Occasional Contributor III
it would be something like:

sqlClause = (None, "ORDER BY Field1 ASC, Field2 DESC")
arcpy.da.UpdateCursor(inFC, fields, sql_clause=sqlClause)


Make sure any fields you use in the sql expression are also passed in the field_names parameter.
0 Kudos
ChrisSnyder
Regular Contributor III
Thanks Chris

I'm thinking the "fancy" SQL parameter is the 6th one down the line, so it probably should be something like this:

arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, None, None, False, (None, 'ORDER_BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))

But that is thowing an error:
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
TypeError: expected callable function - logger(obj)

Been trying a lot of things for a while - with nothing working out...
0 Kudos
ChrisSnyder
Regular Contributor III
0 Kudos
ChrisFox3
Occasional Contributor III
Thanks Chris

I'm thinking the "fancy" SQL parameter is the 6th one down the line, so it probably should be something like this:

arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, None, None, False, (None, 'ORDER_BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))

But that is thowing an error:
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
TypeError: expected callable function - logger(obj)

Been trying a lot of things for a while - with nothing working out...


If you used a named parameter than you don't have to specify the value for every optional paramater to the function. This makes your code a lot easier to write and read and sometimes resolves errors that may have accidentally been added. So since you are just accepting the defaults for the 3rd 4th and 5th parameter you could change your code to:

arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, sql_clause=(None, 'ORDER_BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))


Also make sure the input is a Geodatasbase Feature Class, the sql_clause paramater does not work on shapefiles. I just tested this similar expression on my data and it worked as expected. If you remove the sql_clause paramater is the cursor created succesfully? Did you want to share a small subset of your data?
0 Kudos
ChrisFox3
Occasional Contributor III
Additional info, I get the same error as you if I attempt to enter all the parameters in the same order as specified in the function's signature.

>>> rows = arcpy.da.UpdateCursor("U.S. Cities", fields, None, None, False, (None, 'Order By STATE_NAME, TYPE DESC'))
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
TypeError: expected callable function - logger(obj)


This seems to be a bug, I will look into this further. Using sql_clause= instead works.

>>> rows = arcpy.da.UpdateCursor("U.S. Cities", fields, None, None, False, sql_clause=(None, 'Order By STATE_NAME, TYPE DESC'))
0 Kudos
ChrisSnyder
Regular Contributor III
Okay - I get the 'named parameter' thing... .It's working now... Thanks Chris and Jason!

Correct sytax for a multiple field sort:

arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, sql_clause=(None, 'ORDER BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))

Note that the Help system indicates "ORDER_BY" but it is really "ORDER BY" (with no underscore).

VERIFIED:
arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, None, None, False, (None,'ORDER BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))

results in an error.... Not sure why...
0 Kudos
ChrisFox3
Occasional Contributor III
I logged a new bug for this issue, NIM083765.
0 Kudos
ChrisSnyder
Regular Contributor III
I logged a new bug for this issue, NIM083765.


Appreciate that. Thanks Chris.
0 Kudos
oscarespitia
New Contributor
0 Kudos