topic Re: Ceating a SELECT query in arcpy.da.SearchCursor in Python Questions

Ceating a SELECT query in arcpy.da.SearchCursor

CliveSwan — Fri, 05 Mar 2021 11:41:03 GMT

If I SELECT ALL the query, without the select works.

I only want to loop through 5 - 10 records, not 30,000. So trying to get a select statement to limit the number of records returned.

This code works:

for filename in filenames: try: for row in (arcpy.da.SearchCursor(filename, ['Shape@'])): print(filename)

The sql_clause throws an error??

for filename in filenames: try: for row in arcpy.da.SearchCursor(filename, ['OBJECTID@', 'Shape@'], sql_clause=('select * from ' + ['OBJECTID@', 'Shape@'] + 'limit 5', None)): print(filename)

Appreciate any pointers to fixing the sql_clause.

Thanks,

Clive

Re: Ceating a SELECT query in arcpy.da.SearchCursor

DanPatterson — Fri, 05 Mar 2021 12:30:38 GMT

have you tried the where clause to limit the object ids queried?

whr = "OBJECTID >= 3 AND OBJECTID <= 9" # -- limit to records 3 through 9 c = arcpy.da.SearchCursor(in_fc, ('OID@', 'SHAPE@'), whr, SR) for i in c: print(c) (3, <Polygon object at 0x228a22acd08[0x228a22d75a0]>) (4, <Polygon object at 0x228a22ac208[0x228a22d7db0]>) (5, <Polygon object at 0x228a22acd08[0x228a22d75a0]>) (6, <Polygon object at 0x228a22ac208[0x228a22d7db0]>) (7, <Polygon object at 0x228a22acd08[0x228a22d75a0]>) (8, <Polygon object at 0x228a22ac208[0x228a22d7db0]>)

Re: Ceating a SELECT query in arcpy.da.SearchCursor

Anonymous User — Fri, 05 Mar 2021 13:02:51 GMT

The documentation for the 'sql_clause' parameter says that it takes an optional pair of prefix and postfix clauses organized in a list or tuple.

"An SQL prefix clause is positioned in the first position and will be inserted between the SELECT keyword and the SELECT COLUMN LIST. The SQL prefix clause is most commonly used for clauses such as DISTINCT or ALL."

"An SQL postfix clause is positioned in the second position and will be appended to the SELECT statement, following the where clause. The SQL postfix clause is most commonly used for clauses such as ORDER BY."

Giving it a full sql query will make it fail. Dan's post would be how to get a number of records.

Re: Ceating a SELECT query in arcpy.da.SearchCursor

HenryLindemann — Fri, 05 Mar 2021 13:13:43 GMT

Hi @CliveSwan,

You can also use the TOP clause

import arcpy fc = 'c:/data/base.mdb/well' fields = ['WELL_ID', 'WELL_TYPE'] # Use SQL TOP to sort field values for row in arcpy.da.SearchCursor(fc, fields, sql_clause=('TOP 3', None)): print('{0}, {1}'.format(row[0], row[1]))

Ref SearchCursor—Help | ArcGIS for Desktop

Re: Ceating a SELECT query in arcpy.da.SearchCursor

CliveSwan — Fri, 05 Mar 2021 15:22:01 GMT

Hi Henry,

I wish ESRI would provide context about the code.

That SearchCursor code example was my first stab. Unfortunately the TOP option is only for MS Access and SQL Server.

Thanks Dan, your pointer with the Where statement helped me solve the issue.

Much appreciated.

Re: Ceating a SELECT query in arcpy.da.SearchCursor

DanPatterson — Fri, 05 Mar 2021 16:10:11 GMT

No problem.