Arcpy Select by attributes IN operator with Tuple

Question

I have two layers 1. Buffer zones with a string ID 2. the same buffer zones with the String ID plus an extra field call it zone B

*this process is taking place within a loop already, just to give some scope

Objective - extract all of the first layers buffer ID's and use a select by attributes QRY on the second layer to select the first layer zones that are only labeled zone B

What I have done so far:

-extract ID's from first layer store it in a list then tuple

Zonelist = []
for x in gp.SearchCursor(layer1):
 ID = x.ID
 Zonelist.append(ID)
ZoneTuple = tuple(Zonelist)

now I want to construct a QRY for layer2 that selects all the ID's in the tuple and equal zone b

*I am storing the ID's into a tuple to emulate the parenthesis SQL syntax when using the IN operator within Arcmap

layer2QRY = "ID IN {} AND Labelr = 'ZoneB'".format(ZoneTuple)
layer2 = arcpy.MakeFeatureLayer_management(Allzones, "layer2select", layer2QRY)

Update: the QRY works when there are multiple ID's in the tuple. However when there is only one ID in the tuple ex. ('4321',) a comma appears after the one object and it throws an execute error

• recurvata: For a start, first, I'd move ZoneTuple = tuple(ZoneList) out of the for loop. All you're doing as is is rebuilding it each time. Second, you need a space in your query format after IN.
• ziggy: okay made your changes now I am getting an execute error

Answer 1

As it's not working, take it back a step and print yourlayer2QRY to see what is being passed to your arcpy.MakeFeatureLayer_management() - you'll see it's currently trying to query literally on

"ID IN " + ZoneTuple + " AND " + "Labelr="+"'ZoneB'"

whereas you want it to query on

ID IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AND Labelr = 'ZoneB' 

Once you get your query to print like the above, then you can add it back into your MakeFeatureLayer.

Zonelist = []
for x in arcpy.SearchCursor(layer1):
 ID = x.ObjectID
 Zonelist.append(ID)
ZoneTuple = tuple(Zonelist)
layer2QRY = """ ID IN {} AND Labelr = 'ZoneB' """.format(ZoneTuple)
print layer2QRY # Added to test layer2QRY
layer2 = arcpy.MakeFeatureLayer_management(Allzones, "layer2select", layer2QRY)

---

Update for if there is only one feature - this checks number of IDs in the list, and constructs a fake tuple without the comma based on the only ID in the list.

Zonelist = []
for x in arcpy.SearchCursor(layer1):
 ID = x.ObjectID
 Zonelist.append(ID)
if len(Zonelist) == 1:
 ZoneTuple = "({})".format(Zonelist[0])
else:
 ZoneTuple = tuple(Zonelist)

• ziggy: you've answered a bunch of my questions Midavalo, thanks!
• ziggy: also why does a comma get added to a tuple containing one item?
• Midavalo: see Why does adding a trailing comma after a variable name make it a tuple?

Answer 2

You've got some weird quoting going on.
I find it helpful to print out the query being used in cases like this.

In your case, the value of layer2QRY is literally:

' "ID IN " + ZoneTuple + " AND " + "Labelr="+"\'ZoneB\'" '

You've used triple quotes, which means everything between them is a string literal.

Your first attempt may have been closer to correct, but instead of using string arithmetic to build strings, it's usually better to use the format function. This is probably what you were trying to do:

layer2QRY = "ID IN {0} AND Labelr='ZoneB'".format(str(ZoneTuple))

• ziggy: figured out the formatting right before you answered this. check my update for the question now!