Ceating a SELECT query in arcpy.da.SearchCursor

348
5
Jump to solution
03-05-2021 03:41 AM
CliveSwan
Occasional Contributor II

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

0 Kudos
1 Solution

Accepted Solutions
DanPatterson
MVP Notable Contributor

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

... sort of retired...

View solution in original post

5 Replies
DanPatterson
MVP Notable Contributor

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

... sort of retired...

View solution in original post

JeffK
by
Occasional Contributor III

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
Esri Contributor

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
Occasional Contributor II

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.

0 Kudos
DanPatterson
MVP Notable Contributor

No problem. 


... sort of retired...
0 Kudos