topic Re: arcpy.da.Searchcursor sql_clause in Geoprocessing Questions

arcpy.da.Searchcursor sql_clause

MarcHoogerwerf — Sun, 12 Feb 2012 13:38:33 GMT

Hi,

Can someone shed a bit of light on the sql_clause parameter on arcpy.da.SearchCursor?

The help states that the pre-fix supports None, DISTINCT and TOP and the post-fix supports None, ORDER_BY and GROUP_BY, but I'm not sure how to formulate it corrcetly.

For example:

layer = "c:/temp/myGdb.gdb/myFeatureClass"
dsc = arcpy.Describe(layer)
OIDFieldName = dsc.OIDFieldName
delimitedFieldName = arcpy.AddFieldDelimiters(layer, OIDFieldName)
sWhereClause = delimitedFieldName + " < 100"
sql_clause = (None,'ORDER BY OBJECTID DESC')

rows = arcpy.da.SearchCursor(layer,("SHAPE@","OID@"),sWhereClause,None,False,sql_clause)
rows.reset() # Throws a 'not callable function' error

Re: arcpy.da.Searchcursor sql_clause

JasonScheirer — Sun, 12 Feb 2012 16:41:26 GMT

You don't need to call reset(), you should be able to just do

 for row in arcpy.da.SearchCursor(layer,("SHAPE@","OID@"),sWhereClause,None,False,sql_clause):

and avoid all that extra code.

Re: arcpy.da.Searchcursor sql_clause

GusMartinka — Wed, 15 Feb 2012 21:23:22 GMT

Can tokens such as "SHAPE@X" be used in the sql_clause or the where clause?

Whenever I try and use an sql clause with one of these it says it cannot find the field.

Re: arcpy.da.Searchcursor sql_clause

JasonScheirer — Mon, 20 Feb 2012 19:10:22 GMT

No, the special tokens can only be used in the list of columns you'd like to select.

Re: arcpy.da.Searchcursor sql_clause

MarcHoogerwerf — Sat, 11 Dec 2021 10:33:22 GMT

You don't need to call reset(), you should be able to just do
 for row in arcpy.da.SearchCursor(layer,("SHAPE@","OID@"),sWhereClause,None,False,sql_clause):
and avoid all that extra code.

I tried this one

>>> dsc = arcpy.Describe("Demo.DBO.U1H000Ruimte")
>>> OIDFieldName = dsc.OIDFieldName
>>> delimitedFieldName = arcpy.AddFieldDelimiters("Demo.DBO.U1H000Ruimte", OIDFieldName)
>>> print delimitedFieldName
"OBJECTID"
>>> sWhereClause = delimitedFieldName + " < 100"
>>> print sWhereClause
"OBJECTID" < 100
>>> sql_clause = (None,'ORDER BY OBJECTID DESC')
>>> print sql_clause
(None, 'ORDER BY OBJECTID DESC')

>>> for row in arcpy.da.SearchCursor("Demo.DBO.U1H000Ruimte",("SHAPE@","OID@"),sWhereClause,None,False,sql_clause):
...     print row[1]
...     
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
TypeError: expected callable function - logger(obj)

Then tried:

sql_clause = (None,'ORDER BY "OBJECTID" DESC')

But I keep getting the same results.

Everything is ok if I omit the sql_clause part, so I must doing something wrong with that part. Any ideas?

Update: Then tried this one, swapping the explode_to_points and sql_clause parameters

>>> for row in arcpy.da.SearchCursor("Demo.DBO.U1H000Ruimte",("SHAPE@","OID@"),sWhereClause,None,(None,'order by objectid desc'),None):
...     print row[1]

This will print the objectids but not in descending order. What I don't understand is that the tuple is accepted for the explode_to_points parameter (that is supposed to take values of True and False only)

Re: arcpy.da.Searchcursor sql_clause

markdenil — Tue, 22 May 2012 11:26:13 GMT

arcpy.da is a Defense Analyst module?

Is there a reason to use a Search Cursor in arcpy.da instad of the one in arcpy?

The standard arcpy Search Cursor sort fields syntax is "FIELD_1 A; FIELD_2 D" to sort the first assending and the second decending.
Of course, in a different module the syntax could very well be very different.... after all, let's remember who wrote these modules.

Re: arcpy.da.Searchcursor sql_clause

MarcHoogerwerf — Tue, 22 May 2012 11:40:32 GMT

arcpy.da is a Defense Analyst module?

Is there a reason to use a Search Cursor in arcpy.da instad of the one in arcpy?

The standard arcpy Search Cursor sort fields syntax is "FIELD_1 A; FIELD_2 D" to sort the first assending and the second decending.
Of course, in a different module the syntax could very well be very different.... after all, let's remember who wrote these modules.

Nope arcpy.da.SearchCursor is a 10.1 Data Access SearchCursor and it uses a slightly different syntax. Best reason to use the da.SearchCursor instead of the old SearchCursor is speed and fexibility. The sql_clause is documented in the online help system, so it should work, but I'm probably missing something.

Re: arcpy.da.Searchcursor sql_clause

ChrisSnyder — Sat, 11 Dec 2021 10:33:24 GMT

Hi Marc (Hoogerwerf) - Did you ever get the SQL sort function to work?

I'm having the same difficulty...

Trying to make this old code:

arcpy.UpdateCursor(oesfHydroDislvFC, "", "", "", "SL_WTRTY_CD A;RIP_COMBO_UID D")

Fly in v10.1...

Trying all sorts of derivations of:

fieldList = ["SL_WTRTY_CD","RIP_COMBO_UID","CHAN_WIDTH"]
updateRows = arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, None, None, False, (None, 'ORDER BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))

but no luck so far...

Common error is:

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

Any suggestions?

Re: arcpy.da.Searchcursor sql_clause

JasonScheirer — Mon, 13 Aug 2012 20:51:58 GMT

Better yet, use named parameters:

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

Re: arcpy.da.Searchcursor sql_clause

ChrisSnyder — Mon, 13 Aug 2012 21:07:23 GMT

Thanks Jason...

Working sytax: http://forums.arcgis.com/threads/64580-SQL-sort-parameter-syntax-for-da.cursors?p=223774&viewfull=1#post223774

Re: arcpy.da.Searchcursor sql_clause

oscarespitia — Thu, 26 Sep 2013 15:49:06 GMT

give a look at this

http://forums.arcgis.com/threads/93056-manual-selection-as-input-parameter-for-a-script?p=330656#post330656