Arcpy seems to not carry out my SQL clause

Question

In the following code I meant to select rows from a table of cities named fc where the COUNTY='Socorro County' and ORDERED BY NAME:

fc="cities_copy"


where_selection=""""COUNTY"='Socorro County'"""
sql=(None,"ORDER BY NAME")

cursor=arcpy.da.SearchCursor(fc,["NAME","COUNTY"],where_selection,sql_clause=sql)

for row in cursor:
    print("City Name: {0},\t {1}".format(row[0],row[1]))

From this code I get a list of cities by Name and County of those cities only in Socorro County, the original table selected from has cites with many other counties. I wish to order the selected cites by the NAME field, which I specified in my SQL variable. Instead I get this which is not ordered by NAME:

City Name: Veguita,  Socorro County  
City Name: Sabinal,  Socorro County  
City Name: Abeytas,  Socorro County  
City Name: Las Nutrias,  Socorro County  
City Name: Bernardo,     Socorro County

Which is not ordered by NAME field. What am I doing wrong?

A minimal reproducible example is a great start when asking for SQL assistance. — jarlh
– jarlh, Commented Jan 5 at 21:38

P.Harrison · Accepted Answer · 2025-01-11 18:07:49Z

0

The recommended way to sort columns from Arcpy is this:

    import arcpy

fc = 'c:/data/base.gdb/well' <-- Make sure this is a geo database not folder.
fields = ['WELL_ID', 'WELL_TYPE']

# Use ORDER BY sql clause to sort field values
for row in arcpy.da.SearchCursor(
        fc, fields, sql_clause=(None, 'ORDER BY WELL_ID, WELL_TYPE')):
    print(u'{0}, {1}'.format(row[0], row[1]))

See 5B: https://desktop.arcgis.com/en/arcmap/latest/analyze/arcpy-data-access/searchcursor-class.htm#P_GUID-3CB1DFF4-983D-445F-9CB2-0FF1CD4B4880

After reading: https://community.esri.com/t5/python-questions/sql-clause-in-arcpy-da-searchcursor-is-not/td-p/51603

I found this reference in the documentation of ArcGIS: https://pro.arcgis.com/en/pro-app/latest/help/analysis/geoprocessing/basics/the-in-memory-workspace.htm

Limitations Memory-based workspaces have the following limitations:

Memory-based workspaces do not support geodatabase elements such as feature datasets, relationship classes, attribute rules, contingent values, field groups, spatial or attribute indexes, representations, topologies, geometric networks, or network datasets.

Specifically Attribute indexes means you cant sort. If you're not using an actual geo database.

edited Jan 11 at 18:07

answered Jan 6 at 20:00

P.Harrison

1891 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NBC Jan 8 at 2:08

Thanks! My original workspace was a folder, i made it a proper geodatabase and it worked!

P.Harrison Jan 8 at 20:12

Glad you got it working! To confirm and ill amend the answer. You changed fc to be a proper database?

NBC Jan 10 at 3:14

Yes I imported fc into a geodatabase in ArcGIS and the search cursor I initialized THEN worked completely and correctly. When fc was just in a folder, the search cursor only functioned partially.

Collectives™ on Stack Overflow

Arcpy seems to not carry out my SQL clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related