Writing where_clause to Select Layer By Attribute for Python Add-in?

Question

I have an add-in script for a toolbox in ArcMap that lets a user select a data frame, a layer within it, a field from the layer, an operator (= ,> , <, etc.), and a value from the field. I'm struggling to write a where_clause for the button class that is to execute the selection. I have global variables for each of the combo boxes, but I'm not unsure of how to translate the global variable for the field value to something usable in the Select By Attribute where_clause.

class OK(object):
"""Implementation for ok.button (Button)"""
def __init__(self):
 self.enabled = True
 self.checked = False
def onClick(self):
 mxd = arcpy.mapping.MapDocument("CURRENT")
 df = mxd.activeDataFrame
 arcpy.MakeFeatureLayer_management(layer, "layerSel") 
 where_clause = "\"field\"= + '"+ valueS + "'"
 arcpy.SelectLayerByAttribute_management("layerSel","NEW_SELECTION",where_clause)
 df.zoomToSelectedFeatures()
 arcpy.RefreshActiveView() 

layer, field, and valueS are the global variables for the user-chosen layer, field, and value, respectively.

This error appears when I run the script:

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

This I know has to do with the where_clause, I'm just now sure how to re-write it. I've read the Select Layer by Attribute article, as well as a few on SQL expressions, and I'm still very lost.

• Vince: Python is the only language I know where single and double quotes are equivalent. This is certainly not the case with SQL, where gratuitous double-quotes force case-matching on field names (sometimes, other times it's a syntax error) and single quotes are required to delimit strings (and only strings). Using string math is wasteful for this sort of formatting (slow and clumsy); instead use the String.format() function. If in doubt, print the string generated by your format, then paste it into a SQL client and check for a syntax error.
• Vince: Please also Edit the question to specify the format of the data source, and if it's in an RDBMS, which one. The documentation for arcpy.AddFieldDelimiters might also help.
• andm: Vince, thanks for the response. As to your first point, what would I put in the curly braces of the .format() in this case? Do I need to reference the user-chosen field that has the chosen value? As to your second point, I'm still fairly new to coding. Where would I look for the data source format?
• Vince: Literally, in what format is the source data? Shapefile? File geodatabase? Personal geodatabase? Enterprise geodatabase? Each of these have different formatting rules. The format string permits several different models, but since I've been using 'C' for 35 years, I use C-style "{:s} = '{:s}'".format(fieldName,strValue)
• andm: Oh, ok. I'm working with shapefiles, not in a geodatabase. So in the where_clause, I need to include both the user-chosen value, as well as the field to which it belongs?

Answer 1

Try a where clause like this:

 where_clause = """"{}" {} '{}'""".format(field, operator, value)

(where operator could be =, <, >, etc)

The triple-double quotes is one of Python's string literal delimiters. This particular string delimiter has a few differences from just a double quote or single quote delimiter in Python. One of the differences is that you can use both double quotes and single quotes within the string, both of which are required in this case.

As you are using shapefiles, you need to have double-quotes around your field names in queries and single quotes around string values in queries. (Which makes for a quadruple-double quote at the start of the string - the first three are the python triple-double quote string delimiter, the fourth is the shapefile query field delimiter.)

UPDATE:

As @Vince mentioned in comments, the above is a simplistic case (for the sake of readability) and would only work for string values. Numeric values should not include the single quotes around the value in the expression.

You test if the value is a string or not and handle it something like this:

if isinstance(value, basestring):
 where_clause = """"{}" {} '{}'""".format(field, operator, value)
else:
 where_clause = """"{}" {} {}""".format(field, operator, value)

(Note that no single quotes are used around the numeric values in else clause.)

You could also include variations for other operators such as LIKE, IN, IS NULL, IS NOT NULL, etc, which require different syntax.

• andm: Something I stupidly forgot to mention in the op is that the user must also choose from a list of operators (= ,>, <, etc.). I've been trying something like where_clause = "{0} + {1} + '{2}'".format(field, opSelect, valueS), where opSelect is the global variable referring to the user-chosen operator. No luck. I'll give your suggestion a try now. Any tips on how to incorporate the operator?
• andm: That did it. Thank you so much!
• Vince: Note this function only works with string fields (because of the final single-quote pair). Numeric values should not be quoted. And strings support a "LIKE" operator, which requires wildcards in the search string. And then there's the "IN" operator, which takes a paren-surrounded, comma-delimited list, which this doesn't cover either.