arcpy.da.Searchcursor sql_clause

12885
10
02-12-2012 05:38 AM
MarcHoogerwerf
Occasional Contributor
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
0 Kudos
10 Replies
JasonScheirer
Regular Contributor II
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.
0 Kudos
GusMartinka
Occasional Contributor
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.
0 Kudos
JasonScheirer
Regular Contributor II
No, the special tokens can only be used in the list of columns you'd like to select.
0 Kudos
MarcHoogerwerf
Occasional Contributor
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)
0 Kudos
markdenil
Regular Contributor II
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.
0 Kudos
MarcHoogerwerf
Occasional Contributor
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.
0 Kudos
ChrisSnyder
Regular Contributor III
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?
0 Kudos
JasonScheirer
Regular Contributor II
Better yet, use named parameters:

updateRows = arcpy.da.UpdateCursor(oesfHydroDislvFC, fieldList, sql_clause=(None, 'ORDER BY SL_WTRTY_CD, RIP_COMBO_UID DESC'))
0 Kudos
ChrisSnyder
Regular Contributor III
0 Kudos