Using a variable inside a where clause

Question

I am trying to loop through a list of states, and use each state as a variable within a where clause to select by attribute. Below is the code I am trying to run. Note: I will eventually be running this with every state in stateList. I only have Wisconsin in there for testing purposes.

1 import arcpy, sys, os
2
3 # Set current directory
4 root = os.getcwd()
5
6 # Hardcoded FGDB
7 inFGDB = "C:/test/file.gdb"
8
9 arcpy.env.workspace = inFGDB
10
11 stateList = ["Wisconsin"]
12
13 arcpy.MakeFeatureLayer_management(inFGDB+"/MapAdminArea","MapArea")
14 
15 arcpy.AddJoin_management("MapArea","ADMIN_PLACE_ID","Admin","ADMIN_PLACE_ID")
16
17 for st in stateList:
18 qry = '"Admin.ORDER1_NAME" = ' + "'" +st+ "'"
19 arcpy.SelectLayerByAttribute_management("MapArea","NEW_SELECTION",qry)
20 arcpy.RemoveJoin_management("MapArea")
21 arcpy.CopyFeatures_management("MapArea","MapArea_"+st)

The select statement on line 19 is giving me an Invalid expression error. If I replace line 19 this this Select statement: arcpy.SelectLayerByAttribute_management("MapArea","NEW_SELECTION","Admin.ORDER1_NAME ='Wisconsin'") it selects Wisconsin as expected. I am hoping I can figure out the correct formatting for qry to select and export each individual state from a North America dataset.

• Bera: Try: qry = '"Admin.ORDER1_NAME" = {0}'.format("'" +st+ "'")
• mtbnski: Thanks @BERA, but I'm still getting an Invalid expression error.
• Bera: Take a look at tool Add Field Delimiters
• PolyGeo: I think this question has been asked many times before so I hope to find time to look for a duplicate.
• mtbnski: Thanks @PolyGeo, yes it has. I've tried many of the answers and I just can't get this to work.

Answer 1

Remove the double-quote marks from around your joined field name.

for st in stateList:
 qry = """ Admin.ORDER1_NAME = '{0}' """.format(st)
 arcpy.SelectLayerByAttribute_management("MapArea","NEW_SELECTION",qry)
 arcpy.RemoveJoin_management("MapArea")
 arcpy.CopyFeatures_management("MapArea","MapArea_{0}".format(st))

For some reason when you have the join, the Select by Attributes requires no quotes around the joined field name. When there is no join you can have them or not it makes not difference.

Additionally, I always use triple quotes around strings that may have other quote marks in, that way you don't need to escape or do weird tricks to incorporate the quote marks. It doesn't matter if they're double """ string "goes" here """ or single ''' string 'goes' here ''', the triple quotes around the string will allow you to include them.

• Midavalo: @mtbnski glad to help. Not a genius, just been here before.