arcpy SelectLayerByAttribute_management runs in the Python window in ArcGIS Pro, but fails to run from Spyder or PyCharm

Question

I have this script for querying a feature layer and then copying to a geodatabase. It works when I run it in the ArcGIS Pro Python window, but not when I run it from Spyder or PyCharm. When I run in Spyder the script fails at SelectLayerByAttribute_Management. I have tried multiple variations on the structure of the definition query. Hopefully someone can advise :)

The field the where_clause is applied to is a text field.

The error I am getting from Spyder is:

runfile('C:/Users/LENOVO-580/REMI/Join_Data.py', wdir='C:/Users/LENOVO-580/REMI') Made feature layer Traceback (most recent call last):

File "", line 1, in runfile('C:/Users/LENOVO-580/REMI/Join_Data.py', wdir='C:/Users/LENOVO-580/REMI')

File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 704, in runfile execfile(filename, namespace)

File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 108, in execfile exec(compile(f.read(), filename, 'exec'), namespace)

File "C:/Users/LENOVO-580/REMI/Join_Data.py", line 61, in Selection = arcpy.SelectLayerByAttribute_management(out_layer, "NEW_SELECTION", where_clause)

File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 7238, in SelectLayerByAttribute raise e

File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 7235, in SelectLayerByAttribute retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))

File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing_base.py", line 496, in return lambda *args: val(*gp_fixargs(args, True))

ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

import arcpy
monthYear = 'October 2018'
#feature from path
in_features = r'Z:\Data_Path{0}\Map 
Data\GDB_{0}.gdb\Assessed_Areas'.format(monthYear)
# Set the workspace
arcpy.env.workspace = r'Z:\Data_Path{0}\Map Data\GDB_{0}.gdb
out_layer = "out_layer"
where_clause = "Relevant_Column = '0'"
output = r'Z:\Data_Path{0}\Map Data\GDB_{0}.gdb\Output'.format(monthYear)
arcpy.MakeFeatureLayer_management(in_features, out_layer)
print("Made feature layer")
Selection = arcpy.SelectLayerByAttribute_management(out_layer, 
"NEW_SELECTION", where_clause)
arcpy.CopyFeatures_management(Selection, output, None, None, None, None)

Answer 1

You need to add field delimiters, like "quotes", 'single quotes' or [brackets]. Use AddFieldDelimiters for this:

Adds field delimiters to a field name to allow for use in SQL expressions.

where_clause = """{0} = '0'""".format(arcpy.AddFieldDelimiters(in_features,'somecolumnname'))

The field needs to be a text field or change '0' to 0.

There is no need to select by attributes since make feature layer will accept a where clause, just copy features afterwards. (You can also use Select tool and save some typing)

• KirkyLady: Thanks I was able to move the where clause into arcpy.MakeFeatureLayer_Management. I still couldn't get the definition query to work with a string value. I updated the field to numeric and was able to get it working that way.