Using list of numbers to Select By Attribute in ArcPy?

Question

I am trying to use Python to select all records whose attribute (which is a number) in a certain field is within a list. I cannot get the syntax correct. my list is:

upstream = [9841978, 9842008, 9841966]

I have tried several expressions without success, including:

expression = '"COMID" in ' + str(tuple(upstream))

I am receiving the following error:

Runtime error Traceback (most recent call last): File "<string>", line 2, in <module> 
File "c:\program files (x86)\arcgis\desktop10.4\arcpy\arcpy\management.py", line 7182, in SelectLayerByAttribute 
raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute). 
arcpy.SelectLayerByAttribute_management(catchments, "NEW_SELECTION", expression)

• Midavalo: What is your data stored in? Shapefile, File geodatabase etc.? What is the field type of the COMID field?
• mmoore: The data are stored are in a feature layer that was created from a feature class that is stored within a file geodatabase. The COMID field is Long.
• Vince: You should always print your SQL expressions to the console. If you had, it would be more obvious why it's not a valid SQL expression (the in operator requires a parenthesis-enclosed list).

Answer 1

Try removing the quote marks around your field name COMID

expression = 'COMID IN {0}'.format(tuple(upstream))
arcpy.SelectLayerByAttribute_management(catchments, "NEW_SELECTION", expression)

• mmoore: I am receiving the same error (see edits in original post) with this expression.
• Midavalo: @mmoore can you confirm you removed the extra quote marks? That is the exact error I get if I include the quote marks around the field name, but the expression works if I remove the quote marks
• mmoore: I have removed them... this is the expression I used, copy and pasted straight from the code that I tried: expression = 'COMID IN {0}'.format(tuple(upstreams))
• Midavalo: @mmoore Odd - I just used the same setup: feature layer from FGDB feature class, selecting on a LONG field type. Can you include more of your code in your question?
• Dan Jurgella: @mmoore In the code you pasted in the comment your list is 'upstreams', but in the question, and in the answer Midavalo gave, the list is 'upstream'.