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')

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)

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.