Specifying a query in Python

Structured Query Language (SQL) is a powerful language used to define one or more criteria that can consist of attributes, operators, and calculations. For example, imagine you have a table of customer data, and you want to find those customers who spent more than $50,000 with you last year and whose business type is restaurant. You would select the customers with this expression: "Sales > 50000 AND Business_type = 'Restaurant'".

When a query is specified for an update or search cursor, only the records satisfying that query are returned. An SQL query represents a subset of the single table queries that can be made against a table in an SQL database using the SQL SELECT statement. The syntax used to specify the WHERE clause is the same as that of the underlying database holding the data.

The example below filters the rows of a search cursor to only roads of a specific road class:

import arcpy

fc = "D:/St_Johns/data.gdb/roads"

# Create a search cursor using an SQL expression
#
cursor = arcpy.da.SearchCursor(fc, ("roadclass", "name"), """"roadclass" = 2""")
for row in cursor:
    # Print the name of the residential road
    #
    print(row[1])

Tip:

In Python, building expressions surrounded with triple quotes is both easier to read and to understand.

Using AddFieldDelimiters with the SQL expression

The field delimiters used in an SQL expression differ depending on the format of the queried data. For instance, file geodatabases and shapefiles use double quotes (" "), personal geodatabases use square brackets ([ ]), and ArcSDE geodatabases don't use field delimiters. The AddFieldDelimiters function can take away some of the guess work in ensuring that the field delimiters used with your SQL expression are the correct ones. The following example expands on the above example to add the proper field delimiters for the SQL expression.

import arcpy

fc = "D:/St_Johns/data.gdb/roads"
fieldname = "roadclass"

# Create field name with the proper delimiters
#
whereclause = """{} = 2""".format(arcpy.AddFieldDelimiters(fc, fieldname))

# Create a search cursor using an SQL expression
#
cursor = arcpy.da.SearchCursor(fc, ("roadclass", "name"), whereclause)

for row in cursor:
    # Print the name of the residential road
    print(row[1])