SQL Query Syntax gives ERROR 000358: Invalid expression?

Question

I'm writing a script that will cycle through a shapefile of building footprints for an entire state, create a folder for the individual counties in that state, and then intersect the building footprints file with a county polygon stored.

Because I'm working with multiple states and hundreds of counties, I've created two variables that will take the state and county names of the current iteration- I keep running into an issue when it comes to writing a query that uses those variables. My goal is to create the following query:

"ST_NAME = 'Colorado' AND "POLYGON_NM" = 'Weld'

I'm not familiar with writing sql queries in arcpy and the syntax is throwing me. Here's what I currently have as my query:

dictionary = set(zip(statenames, countynames))
for item in dictionary:
 try:
 stname = str(item[0])
 coname = str(item[1].replace(r' ', '_'))
 except:
 stname = str(item[0])
 coname = str(item[1])
 if stname == 'Colorado':
 coco = arcpy.CreateFolder_management((basefile + '/Colorado/Counties'), coname)
 query = '"ST_NAME" =' + "'" + stname + "'" + " AND" + '"POLYGON_NM" =' + "'" + coname + "'"
 fl = arcpy.MakeFeatureLayer_management(counties, 'in_memory/counties_lyr')
 coselect = arcpy.SelectLayerByAttribute_management(fl, '', query)
 fc = arcpy.FeatureClassToFeatureClass_conversion(str(coselect), 'in_memory', coname)
 arcpy.Intersect_analysis([Colorado, fc], (str(coco) + '/' + coname))

I keep getting the following error for the Make Feature Layer tool:

ERROR 000358: Invalid expression

In the past I used a different syntax, but it only produced empty shapefiles, if no errors:

query = "ST_NAME" = ('"' + stname + '"') AND "POLYGON_NM" = ('"' + coname + '"')

Answer 1

Use AddFieldDelimiter and three double quotes to get a valid expression:

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.

import arcpy
fc = r'C:\database.gdb\feature_class' #Or a feature layer, table etc.
query = """{0} = '{1}' AND {2} = '{3}'""".format(arcpy.AddFieldDelimiters(fc, 'ST_NAME'), stname, arcpy.AddFieldDelimiters(fc, 'POLYGON_NM'), coname)
arcpy.MakeFeatureLayer_management(...

Answer 2

You have a typo in

query = '"ST_NAME" =' + "'" + stname + "'" + " AND" + '"POLYGON_NM" =' + "'" + coname + "'"

you are missing a space after AND --> ... + " AND " + ..., so the expression is invalid.

In your other expression, you are using double quotes around stname and coname, which means they are field name. The expression would be looking for the field Colorado, which doesn't exist.

query = "ST_NAME" = ('"' + stname + '"') AND "POLYGON_NM" = ('"' + coname + '"')

Answer 3

Using string math makes these sort of errors common. If you instead use String.format() syntax, even datatype errors become clear:

>>> stname = "foo"
>>> coname = "bar"
>>> query = "\"ST_NAME\" = '{:s}' AND \"POLYGON_NM\" = '{:s}'".format(stname,coname)
>>> print query
"ST_NAME" = 'foo' AND "POLYGON_NM" = 'bar'