13

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?

14

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')
  • I didn't realize the ArcGIS selection interface supported 'IN'. This is probably more efficient than my solution. – AHigh Jul 17 '12 at 18:50
  • 1
    Just be wary there is an upper limit that is supported by the IN query I think it's 2000 records – Tristan Forward Nov 23 '16 at 20:42
9

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
6

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)
  • 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 Jul 17 '12 at 16:32
  • this appears to be working, but will certainly some time to process each individual selection for longer lists. – jsnider Jul 17 '12 at 16:47
  • 2
    Updated the answer with a different approach. – AHigh Jul 17 '12 at 17:02
  • 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! – jsnider Jul 17 '12 at 17:52
  • I'll update my answer with your chosen approach so it's parsed and easier to read. Glad it worked. – AHigh Jul 17 '12 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.