topic How to correct SQL expression for Search Cursor in Arcpy in Python Questions

How to correct SQL expression for Search Cursor in Arcpy

fumaniwada1 — Sat, 15 Aug 2015 03:11:40 GMT

I need to create a search cursor to print out the values in an attribute table for a school shapefile. I need to print out all the values that correspond to HIGH SCHOOL in the field FACILITY for the School shapefile. For some reason the search cursor won't work when I try to apply the sql expression but works without it. I don't want to print out all the values in that field just specific ones. Is there something wrong with my sql statement?

#import arcpy module

import arcpy

from arcpy import env

arcpy.env.overwriteOutput = True

# I am setting the work path

env = 'S:\\376\\Summer15-2\\ahutche1\\lab07_data\\SectionB\\'

#I will set the variable for the schools shapefile

schools = 'Schools'

#create search cursor to loop thru schools. use where clause to return schools

#that are high schools. Use loops

#print out all the highschool names

#print total no. of high school records

sr = arcpy.SpatialReference(4326)

#I will create a search cursor for the schools shapefile

field = "FACILITY"

exp1 = '"FACILITY" = "HIGH SCHOOL"'

cursor = arcpy.SearchCursor(env+schools+'.shp',exp1)

for row in cursor:

print(row.getValue(field))

Re: How to correct SQL expression for Search Cursor in Arcpy

XanderBakker — Sun, 12 Dec 2021 07:36:17 GMT

There are a few issues:

First of all, please use the instructions found here: Posting Code blocks in the new GeoNet for insert code into a thread
The code does not work since the where clause is not valid. The delimiters of a field depend on the type of data used (shapefile, personal- file or enterprise geodatabase). Use the AddFieldDelimiters to structure the where clause.
If the would have worked, the statement "print(row.getValue(field))" would have printed the value HIGH SCHOOL for each high school and not the name
In the code below on line 12 a list of all the HIGH SCHOOL names is made and on line 17 the length of that list is printed.

def main():
    import arcpy
    arcpy.env.overwriteOutput = True
    fc = r"S:\376\Summer15-2\ahutche1\lab07_data\SectionB\Schools.shp"
    fld_fac = "FACILITY"
    fld_name = "FieldNameWithHighSchoolNames" # specify the name of the field

    # create a valid where clause
    sql = "{0} = 'HIGH SCHOOL'".format(arcpy.AddFieldDelimiters(fld))

    # print out all the highschool names
    lst = [r[1] for r in arcpy.da.SearchCursor(fc, (fld_fac, fld_name), sql)]
    for name in lst:
        print name

    # print total no. of high school records
    print "There are {0} High Schools".format(len(lst))

if __name__ == '__main__':
    main()

Code has not been tested and should be used with ArcGIS 10.1 SP1 or higher ...

Re: How to correct SQL expression for Search Cursor in Arcpy

WesMiller — Sat, 15 Aug 2015 23:07:53 GMT

When doing SQL expressions for python code i usually start by creating the query in select by attribute and then copy it to the code this usually ensures i have the correct syntax

Re: How to correct SQL expression for Search Cursor in Arcpy

BlakeTerhune — Mon, 17 Aug 2015 16:43:45 GMT

On line 9, you have an udefined variable fld in AddFieldDelimiters(). It also gives

TypeError: AddFieldDelimiters() takes exactly 2 arguments (1 given)

It needs a data source, then field name.

AddFieldDelimiters—Help | ArcGIS for Desktop

Re: How to correct SQL expression for Search Cursor in Arcpy

XanderBakker — Sun, 12 Dec 2021 07:36:20 GMT

Good catch, thanks!

Corrected code below (still not tested):

def main():
    import arcpy, os
    arcpy.env.overwriteOutput = True
    fc = r"S:\376\Summer15-2\ahutche1\lab07_data\SectionB\Schools.shp"
    fld_fac = "FACILITY"
    fld_name = "FieldNameWithHighSchoolNames" # specify the name of the field
    fc_ws, fc_name = os.path.split(fc)

    # create a valid where clause
    sql = "{0} = 'HIGH SCHOOL'".format(arcpy.AddFieldDelimiters(fc_ws, fld_fac))

    # print out all the highschool names
    lst = [r[1] for r in arcpy.da.SearchCursor(fc, (fld_fac, fld_name), sql)]
    for name in lst:
        print name

    # print total no. of high school records
    print "There are {0} High Schools".format(len(lst))

if __name__ == '__main__':
    main()

...edit: Blake T detected another error in my code: the fld variable on line 10 is not set and should be replaced by fld_fac which holds the name of the FACILITY field...