Ceating a SELECT query in arcpy.da.SearchCursor

[CliveSwan]

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

[DanPatterson · Accepted solution]

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]>)

[Anonymous User]

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.

[HenryLindemann]

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

[CliveSwan]

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.

[DanPatterson]

No problem.