ArcPy - Select by Attribute for string doesn't work

Question

I am developing a Python script, in which I want to select features based on an attribute, which is a string. I tried for hours and used a hundred different ways, but I always get the Error 000358 that the query statement is not correct.

I work with a feature class, not shapefile. Assume that my field is called "Fruit" and the value I want to select is "Apple". What I already tried:

whereClause = """"{} = {}""".format("Fruit", "'Apple'")
whereClause = """"{} = '{}'""".format("Fruit", "Apple")
whereClause = """"{0} = '{1}'""".format("Fruit", "Apple")
fruit = "Fruit"
value = "Apple"
whereClause = """"{} = '{}'""".format(fruit, value)
arcpy.management.SelectLayerByAttribute(myFeatureClass, "NEW_SELECTION", whereClause)

I also did some other Select By Attribute queries, but always using Integer or other numeric fields and I never had problems. Seems that I cannot get it to work with strings.

The error message I get is:

Traceback (most recent call last):
 File "<string>", line 718, in execute
 File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 8754, in SelectLayerByAttribute
 raise e
 File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 8751, 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 511, in <lambda>
 return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression. Error with executing of SelectLayerByAttribute.

• Michael Stimson: whereClause ="{} = {}".format("Fruit", "'Apple'") should work. Are you getting any error messages or just no selection? Is your data in a file or enterprise geodatabase? Shapefiles and personal geodatabase SQL isn't case sensitive but file and enterprise geodatabase SQL is. Is there a chance your values for the Fruit field contains leading or trailing whitespace?
• GIS_USAr: The whereClause does not work. I always get an error message, I put it in the question. My data is in a file geodatabase. No, there are no whitespaces, I checked that.
• PolyGeo: Try this: whereClause = "{} = '{}'".format("Fruit", "Apple")
• GIS_USAr: I tried it but it also results in the same error. As I populate the "Fruit" field, at the beginnung all values are set to <Null>. But I just tried and changed that before the Select query to an empty field, so also NULL values should not be a problem.
• Michael Stimson: Are you trying to select from a feature class or layer? myFeatureClass isn't defined in the snippet. I suspect you need to make feature layer resources.arcgis.com/en/help/main/10.2/index.html#//… from your feature class first as layers support selection but feature classes do not.

Answer 1

Try using AddFieldDelimiters:

import arcpy
features = r'C:\folder\data.gdb\featureclass'
whereClause = """"{} = '{}'""".format(arcpy.AddFieldDelimiters(datasource=features, field="Fruit"), "Apple")
arcpy.MakeFeatureLayer_management(in_features=features, out_layer='templyr', whereClause=whereClause)

Now 'templyr' will be a feature layer created using your whereClause.

Or you can use MakeFeatureLayer without a whereClause and Select by attributes with the feature layer as input.