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; } } }

arcpy.da.Searchcursor sql_clause

MarcHoogerwerf_TAP

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

Encontre mais publicações marcadas com

Accepted answers

All comments

oscarespitia

give a look at this

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

ChrisSnyder

Thanks Jason...

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

JasonScheirer

Better yet, use named parameters:

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

ChrisSnyder

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?

MarcHoogerwerf_TAP

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.

markdenil

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.

MarcHoogerwerf_TAP

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)

JasonScheirer

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

GusMartinka

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.

JasonScheirer

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.

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.