Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I believe this will amount to largely a sql syntax question. Essentially, 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]) +")"

So, I refer to you SQL experts: got any suggestions? What am I missing here?

share|improve this question
add comment

3 Answers 3

up vote 5 down vote accepted

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

qry = '"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)
share|improve this answer
    
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
    
This seems to work. Thanks. –  jsnider Jul 17 '12 at 20:51
add comment

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
share|improve this answer
add comment

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)
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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