Difficulty with select by attributes using list fields in for loop

Question

I am trying to add fields to a table and then populate the fields with text values (Yes/No). The text values are based on whether or not a species was found in a specific block. If the species was found in a block then the field> 0 and if no species was found in a block then field = 0.

I am having issues with my select by attribute where clause. Specifically, I am trying the use the field name text from my ListFields function in the sql statement (where_clause= field4 + " > 0").

I get the following error:

ExecuteError: ERROR 000358: Invalid expression

import arcpy
from arcpy import env
arcpy.env.overwriteOutput = True
# Purpose is to generate a table with presence/absence data for blocks falling outside of species ranges 
blocks = r"C:\Users\Range\Workspace\scrap\FB_Ground_MLR.shp"
# layer for make feature
blyr = r"C:\Users\Workspace\scrap\FB_Ground_MLR.lyr"
lyr = arcpy.MakeFeatureLayer_management(in_features = blocks, out_layer = blyr) 
fields = arcpy.ListFields(blocks)
x = 0
for field in fields:
 field4 = field.name
 f4 = field4[0:8] + "X"
 x = x + 1
 if x >= 4:
 arcpy.AddField_management(blocks, f4, "TEXT") 
 arcpy.SelectLayerByAttribute_management(lyr, selection_type="NEW_SELECTION", where_clause= field4 + " > 0")
 arcpy.CalculateField_management(blocks, f4, 'Yes')
 arcpy.SelectLayerByAttribute_management(lyr, selection_type="NEW_SELECTION", where_clause= field4 + " = 0")
 arcpy.CalculateField_management(blocks, f4, 'No')
else:
 print(f4)

• Vince: There's a indent error in your source.
• Bera: You need to add field delimiters surrounding field4. Use tool arcpy.addfielddelimiters. For example clause = "{0} > 3".format(arcpy.addfielddelimiters(field4)
• Winn: Unfortunately, I am still having issues with the where clause (where_clause= "{}" >= "0" .format(arcpy.AddFieldDelimiters(blyr, field4))). It still gives me the invalid expression error.
• Aaron: Are you working with 2 featureclasses: the first a polygon fc indicating blocks and the second fc some sort of species point data? If so, I would do a spatial join, add a new text field, and populate that new field with Y or N based on join_count > 0.

Answer 1

If you do lyr = arcpy.MakeFeatureLayer...:

import arcpy
fc = r'C:\somefolder\Default.gdb\an_riks_Intersect'
lyr = arcpy.MakeFeatureLayer_management(in_features=fc, out_layer='templyr')
print type(lyr)
<class 'arcpy.arcobjects.arcobjects.Result'>

Then lyr will be a Result object and cant be used in a sql Query. In my example output layer to use in query is 'templyr'.

And when constructing the sql Query, use AddFieldDelimiters and enclose the whole Query in quotes:

sql = "{0} > 0".format(arcpy.AddFieldDelimiters('templyr','somefieldname_of_number_type')

Example:

print arcpy.GetCount_management(in_rows='templyr')
sql = "{0} > 2000000000".format(arcpy.AddFieldDelimiters(datasource='templyr', field='Shape_Area'))
arcpy.SelectLayerByAttribute_management(in_layer_or_view='templyr', where_clause=sql)
print sql
print arcpy.GetCount_management(in_rows='templyr')

Outputs:

294
"Shape_Area" > 2000000000
67

Answer 2

Your where_clause needs to use SQL syntax. Using the format() function in Python is a good way to put together your where_clause if you're looping through variables. In your case, it would be:

arcpy.SelectLayerByAttribute_management(lyr, selection_type="NEW_SELECTION", where_clause= "{} >= 0".format(field4))

More info on the format function can be found here: https://www.geeksforgeeks.org/python-format-function/