In this tutorial, we will be using using SearchCursors with a where clause used as an optional parameter. We will explain the function of where clauses and their syntax when creating a SearchCursor object with arcpy.
What is a where clause?
When you look at the syntax for writing a SearchCursor in arcpy, you will notice that there are required and optional parameters to pass in a SearchCursor object. Required parameters are a feature class name and a variable that refers to one or multiple fields. A third, optional parameter is a where clause. The name ‘where clause’ might sound strange in the context of spatial data, but comes from SQL, which is a programming language for working with databases. A where clause in SQL is the part of a database query where a condition is defined, so that only records are returned from the database that meet this condition. In other words, a where clause limits the amount of database records to be returned and is a way to filter records.
The function of a SearchCursor is similar to that of an SQL query, as it returns records from a table or feature class containing tabular data. Without a where clause, a SearchCursor will not be able to select records based on a condition. We will now look at how a where clause is written in Python.
How to define a where clause inside a SearchCursor object
Let’s return to our Hospitals_England Feature Service Feature Class used in earlier tutorials. To follow along, open up a new ArcGIS Pro project file and search for the file in ArcGIS Online and add it to the map window.
Before writing the code let’s have a look at the attribute table of the Hospitals feature class. We’ll use two column names containing string values for a simple example defining a where clause: first, the ‘Sector’ column which contains the hospital type for a particular row (this can be either ‘NHS Sector’ or ‘Independent Sector’) and second, the City column stating in which city a particular hospital is located. We’ll define a where clause where we’ll only return ‘Sector’ field values for hospitals in London. Without this where clause, the SearchCursor would return the field values for all rows in the attribute table, which is what we want to avoid: we want to limit the amount of rows returned by the SearchCursor.
To do this, we need to pass in a field name and a condition to apply to this field name to our SearchCursor so that Python can evaluate this information logically and apply our search filter, returning only the rows meeting our condition. This is done using arcpy’s AddFieldDelimiters function, which takes in two parameters: a datasource and field name.
For our London Hospitals example, the datasource is the feature class used (the Hospitals feature class) and the field is the ‘City’ field inside the attribute table. The only thing that’s left to do is specifying the condition the field needs to meet, which is that the City field value needs to be ‘London’ in order to be returned by the SearchCursor. This is done inside the cursor definition, by joining the delimfield variable with a string value containing the condition (delimfield + “= ‘London'”). Here’s the entire code, followed by an image showing the code and the output. The f-string in the last line of code contains a width specification to have the output neatly lined up.
import arcpy
fc = “Hospitals_England”
fields = [‘Sector’, ‘City’]
delimfield = arcpy.AddFieldDelimiters(fc, fields[1])
cursor = arcpy.da.SearchCursor(fc, fields, delimfield + “= ‘London'”)
for row in cursor:
print(f” Hospital Type: {row[0]:20} City: {row[1]}”)
Learn more about using Python with ArcGIS Pro in our Introduction to Programming ArcGIS Pro with Python and Intermediate ArcGIS Pro with Python classes.
