How to correct SQL expression for Search Cursor in Arcpy

[fumaniwada1]

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

[XanderBakker]

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 ...

[BlakeTerhune]

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

[XanderBakker]

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...

[WesMiller]

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