Using OR in a SQL Query with Select By Attributes in ArcPy

Question

The code in question is not that complicated at all. However, I can't seem to get my query to execute with using OR to test for more features in a field using Select By Attributes. See code snippet below, which has been edited to include the function in its current state. Note that this is not the final state of the function and it will include more functionality, I am just getting things working step by step:

def adds_field(feature_list_object, shapefile, queryString):
 feature_layer = arcpy.MakeFeatureLayer_management(shapefile, "feature_layer")
 arcpy.SelectLayerByAttribute_management(feature_layer, "NEW_SELECTION", queryString)
 arcpy.Append_management(feature_layer, feature_list_object, "NO_TEST")
for feature in features:
 if feature == 'SHELL_CL':
 query = ''' "Layer = 'ConnectTO'" OR "Layer = 'Connector'" '''
 adds_field(feature, cad_to_shapefile, query)

features is a list of file geodatabase feature classes.

The script fails for an invalid expression, and I've tried many different ways of formatting the query. Spent a day or two back and forth between how to format these and can't seem to find anything which has led me to getting it to execute.

Again, I know the syntax of the query is simple and it should run, I just can't seem to get it formatted correctly.

• MulfordnSons: I have edited the post with the information you have requested. I appreciate your response! I should add that the code above works perfectly fine if I just have a single argument in the SQL expression: "Layer = 'ConnectTO'" works perfectly fine.
• Vince: If you just print the content of query, you'll see that it's invalid SQL and shouldn't run. "Layer = 'ConnectTO'" OR "Layer = 'Connector'" is not valid -- the double-quotes must be around the field name, like this: "Layer" = 'ConnectTO' OR "Layer" = 'Connector' though use of IN is better in this use case: "Layer" IN ('ConnectTO','Connector')
• MulfordnSons: that query structure was an option I explored. However, how do I reconcile the fact that the 'OR' or 'IN' operators aren't enclosed in the string and therefore my IDE interprets them as a variable name that is unresolved?
• Vince: Put triple quotes around them, the way you did above. Or, since the double-quotes are only occasionally necessary (some data sources), I'd just use double-quotes (query = "Layer IN ('ConnectTO','Connector')"), or if the name list is a variable, in a str.format expression: query = "Layer IN ('{:s}')".format("','".join(list_of_layers))
• MulfordnSons: You're a gentleman and a scholar, and I appreciate you very much.

Answer 1

Python is staggeringly flexible in the way that it permits quoting with both double-quotes (") and single-quotes ('), both of which can be escaped, or used in triplets to obviate the need for escaping of singletons. But in the end, the resulting query string must be valid SQL.

The SQL language is not as flexible (in fact, it only supports single-quotes (aka ') for string identification, which can be doubled to escape an embedded apostrophe -- 'It''s okay')). Use of double-quotes (aka ") is an extension in most SQL implementations, which can allow for non-conforming column names to used (mixed case or leading numerics, mostly). Esri does NOT support case-sensitivity, so it effectively ignores double-quotes in file geodatabase, but if they are present, they must be around the field name.

In this case, the result of the expression is:

 "Layer = 'ConnectTO'" OR "Layer = 'Connector'"

which the parser reads as the Boolean expression:

 ValueOfField("Layer = 'ConnectTO'") OR ValueOfField("Layer = 'ConnectTO'")

but which fails, because no field with quotes or equals is permitted.

What you wanted was quoted field names, as in the string:

 "Layer" = 'ConnectTO' OR "Layer" = 'Connector'
 ^ ^ ^ ^

This could be constructed via triple-quoting in Python:

 query = ''' "Layer" = 'ConnectTO' OR "Layer" = 'Connector' '''

but could be alternately formatted as:

 query = ''' "Layer" IN ('ConnectTO','Connector') '''

or, because it's file geodatabase, which doesn't require quotes:

 query = ''' Layer = 'ConnectTO' OR Layer = 'Connector' '''

or

 query = "Layer = 'ConnectTO' OR Layer = 'Connector'"

or

 query = "Layer IN ('ConnectTO','Connector')"

In fact, if the list of acceptable Layer values is in a Python list, you could also do this:

 query = "Layer IN ('{:s}')".format("','".join(list_of_layers))

In the future, if you get a SQL error, print out the contents of the query and paste it into an expression evaluator (RDBMS UI or one of the ArcGIS query builders), which might be able to help you tease out what's wrong.

Also, to make your code as portable as possible, you can utilize arcpy.AddFieldDelimiters to wrap the field name with quotes or brackets, depending on the data source, which would look like this:

 query = "{:s} IN ('ConnectTO','Connector')".format(
 arcpy.AddFieldDelimiters(feature,"Layer"))

or

 query = "{:s} IN ('{:s}')".format(
 arcpy.AddFieldDelimiters(feature,"Layer"),
 "','".join(list_of_layers))