1

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)
  • 1
    There's a indent error in your source. – Vince Feb 26 at 18:54
  • You need to add field delimiters surrounding field4. Use tool arcpy.addfielddelimiters. For example clause = "{0} > 3".format(arcpy.addfielddelimiters(field4) – BERA Feb 26 at 19:51
  • 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. – Winn Feb 26 at 22:21
  • 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. – Aaron Feb 27 at 15:25
2

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
| improve this answer | |
1

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/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.