Selecting features by attribute if in Python list?

Question

I am trying to complete a select by attribute in Python but based on the query of whether an attribute is present in a list.

Such a query at its simplest should be something like this:

qry = " \"OBJECTID\" in oid_list"
arcpy.SelectLayersByAttribute_management(inft, "NEW_SELECTION", qry)

but that approach returns an invalid expression error.

In the past, I've had to use more complicated sytax for this type of query, such as:

sqlQuery2 = "nid in (" + ','.join(["'"+x+"'" for x in delta_list]) +")"

but an adaptation of this snippet doesn't seem to work for me either, ie.:

 "OBJECTID_1 in (" + ','.join(["'"+str(x)+"'" for x in oid_list]) +")"

What am I missing here?

Answer 1

Your original query could have been modified for a list of integers:

'"OBJECTID_1" IN ' + str(tuple(oid_list))

so if oid_list = [7, 9, 4, 8], then the result is:

"OBJECTID_1" IN (7, 9, 4, 8)

Be aware that this "trick" works if oid_list always has two or more items, since other valid tuples, such as () or (7,), will result with a SQL syntax error.

A more generic expression that would also handle zero or one oid_list items would be:

'"OBJECTID_1" IN ({0})'.format(', '.join(map(str, oid_list)) or 'NULL')

• AHigh: I didn't realize the ArcGIS selection interface supported 'IN'. This is probably more efficient than my solution.
• Tristan Forward: Just be wary there is an upper limit that is supported by the IN query I think it's 2000 records

Answer 2

Here is a slightly modified version of the function in this answer, to accept a Python list instead of a semicolon-delimited string:

def buildWhereClauseFromList(table, field, valueList):
 """Takes a list of values and constructs a SQL WHERE
 clause to select those values within a given field and table."""
 # Add DBMS-specific field delimiters
 fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(table).path, field)
 # Determine field type
 fieldType = arcpy.ListFields(table, field)[0].type
 # Add single-quotes for string field values
 if str(fieldType) == 'String':
 valueList = ["'%s'" % value for value in valueList]
 # Format WHERE clause in the form of an IN statement
 whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))
 return whereClause

Answer 3

I think the most straightforward approach to this is to iterate through the values in your list singularly and add them to the selection (So you can change your query with each value in the list). Something like this:

oidList = [1,2,3,4]
arcpy.management.MakeFeatureLayer(thisFC,thisLyr)
for values in oidList:
 query = "\"OBJECTID\"="+str(values)
 arcpy.management.SelectLayerByAttribute(thisLyr,"ADD_TO_SELECTION",query)

You can use the ADD_TO_SELECTION even if there are no features selected, it will create a new selection on the first iteration.

Edit:

If you think the cost of doing individual SelectLayerByAttribute will be too high you could use an approach like this where you create a quite large selection clause depending on your list length:

oidList = [1,2,3,4]
arcpy.management.MakeFeatureLayer(thisFC,thisLyr)
query=""
q=""
oidList.sort()
for x in oidList:
 query="\"OBJECTID\"="+str(x)+" OR "+q
 q=query
q=q[1:-4]
arcpy.management.SelectLayerByAttribute(thisLyr,"NEW_SELECTION",q)

• jsnider: Interesting idea to iterate through the values and perform a select by attribute for each iteration. I'll test this, but I'm fairly certain this should work. Thanks.
• jsnider: this appears to be working, but will certainly some time to process each individual selection for longer lists.
• AHigh: Updated the answer with a different approach.
• jsnider: Good idea with the updated answer. I have chosen to use this approach as it much faster for processing larger lists. Slightly modified: q = " " for x in oid_set: query = '"OBJECTID_1" = ' + str(x) + ' OR ' q = query q = q[1:-4] and then selectbyattribute. Seems to work!
• AHigh: I'll update my answer with your chosen approach so it's parsed and easier to read. Glad it worked.

Answer 4

I had this same problem, and anticpated many sizes of the list to be used. What I did was change my list to a string then removed brackets:

oid_list = str(oid_list)
oid_list = oid_list[1:-1]
qry = " \\"OBJECTID\\" in ({})".format(oid_list)
arcpy.SelectLayersByAttribute_management(inft, "NEW_SELECTION", qry)

Note: I use {} with .format() to designate variables in strings.