Where Clause in SelectLayerByAttribute function resulting in invalid expression

Question

All of the attempts below result in ERROR 000358: Invalid expression Any ideas on what I am doing wrong? I am working within a File GDB and with polygons.

Attempt 1:

def SelectLayer(val):
 print '"Att1 = ' + "'" + val + "'" + '"'
 arcpy.SelectLayerByAttribute_management('Source_lyr',
 'NEW_SELECTION',
 '"Att1 = ' + "'" + val + "'" + '"')
SelectLayer('Red')

Attempt 2:

def SelectLayer(val):
 where_clause = '"Att1 = ' + "'" + val + "'" + '"'
 print where_clause
 arcpy.SelectLayerByAttribute_management('Source_lyr',
 'NEW_SELECTION',
 where_clause)
SelectLayer('Red')

Attempt 3:

def SelectLayer(val):
 print "\"Att1 = \'{0}\'\"".format(val)
 arcpy.SelectLayerByAttribute_management('Source_lyr',
 'NEW_SELECTION',
 "\"Att1 = \'{0}\'\"".format(val)) 
SelectLayer('Red')

This line below was working without error before I put it into a function. This is what all of the attempts above, and the line below, produce: "Att1 = 'Red'"

arcpy.SelectLayerByAttribute_management('Source_lyr', 'NEW_SELECTION', "Att1 = 'Red'")

• Vince: Lose the double-quotes inside the expression. A valid SQL expression is in the form "column_name operator value". Character values need single-quotes around them. Please edit the question to specify the data source (personal geodatabase often needs bizarre formatting) and the datatype (field datatype of the column being evaluated).
• reevesii: Thanks @Vince, removed the double-quotes so that line read arcpy.SelectLayerByAttribute_management('Source_lyr', 'NEW_SELECTION', 'Att1 = ' + "'" + val + "'") and it worked.
• SMiller: Also search this site for posts on "AddFieldDelimiters". From my understanding this looks at the data source (shapefile, geodatabase, etc) and the field type, and adds things like quotes to the field name where appropriate.

Answer 1

You have one level of quote too much. If your variable is already a string, no need to add extra "" to tell that it is a string. Think of what you want to write if you run a selection in ArcMap. In this case:

Att = 'Red'

while your where_clause gives:

"Att = 'Red'"

In your case, you should use this where clause:

where_clause = "Att = '{}'".format(val)

or

where_clause = "Att = '" + val + "'"

Answer 2

Use AddFieldDelimiters:

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

The field delimiters used in an SQL expression differ depending on the format of the queried data. For instance, file geodatabases and shapefiles use double quotation marks (" "), personal geodatabases use square brackets ([ ]), and enterprise geodatabases don't use field delimiters. The function can take away the guess work in ensuring that the field delimiters used with your SQL expression are the correct ones.

sql = """{0}='{1}'""".format(arcpy.AddFieldDelimiters(datasource='Source_lyr', field='Att1'),val)

• reevesii: val = 'Red' sql = """{0}='{1}'""".format(arcpy.AddFieldDelimiters(datasource='Source_lyr', field='Att1'),val) print sql prints "Att1"='Red' Is this what you intended? And is there a preference of using field delimiters over the two where-clauses suggested by @radouxju?