Passing Python List into SQL Query

Question

I am trying to cursor search through items in a field in an access table. I then append them to a list called 'distList'. I want to pass the list into an SQL Query builder 'WHERE' clause in the equation:

"Field1" in distList

or in long hand, it would look like this:

"Field1" in ('ds(c1)', 'ds(b1)', 'ds(c2)', 'ds(g1)')

The list looks like this:

['ds(c1)', 'ds(b1)', 'ds(c2)', 'ds(g1)']

The problem I am having is, the SQL equation above doesn't like to look 'in' a python list. It doesn't recognize the square brackets. It prefers round brackets, so I thought about using a tuple. The problem I am facing is I don't know how to build a tuple after cursor searching the items from the access table. here's my code example:

distList = []
for lyr in arcpy.mapping.ListLayers(mxd, "", df):
 if lyr.name == "Disturbance":
 for row in arcpy.SearchCursor(lyr):
 if "ds" in row.Field1:
 distList.append(row.Field1)
 lyr.definitionQuery = '"Field1"' + "in " + distList

Can anyone suggest a way to get my list into a tuple or maybe just a better way to get my items into a format where they have round brackets instead of square ones?

As a work around, I tried converting the list to string str(distList) and then replacing the brackets. That works fine, but it seems cumbersome and I'm sure there's a better way.

Thanks, Mike

• Kamil Sindi: Possible duplicate of python list in sql query as parameter

Answer 1

Did you try:

str(tuple(distList))

Edit:

The above will only work for lists with multiple items.

Something similar to Arun's answer is more general, in that it'll work with one or more items:

distlist = '({})'.format(','.join(elem for elem in distlist))

Depending on the query though, and the RDBMS, it may be more efficient to check beforehand for a single item and just use an equality operator in that case.

• Mike: Thanks gerrat. Unfortunately, this didn't seem to work. I think the query box I'm trying to pass this into, doesn't like tuples either.
• Avi Kumar: This is really a comment, not an answer to the question. Please use "add comment" to leave feedback for the author.
• Gerrat: @Avi: Well, it was actually an answer, but I stated it like a comment. The "did you try" was kind of rhetorical, since when I used this on his data the resulting string looked correct.
• giaosudau: have problem when dicList have one item it will print (xxx,) error will appear

Answer 2

Preprocess the list:

distList = ['ds(c1)', 'ds(b1)', 'ds(c2)', 'ds(g1)']
refinedList = ','.join(a for a in distList)

pass refinedList in SQL in WHERE clause, something like this:

SQL_QUERY = """
select *
from abc
where someCol = ({0})
"""
SQL = SQL_QUERY.format(refinedList)

the {0} here is the parameter (refinedList). Look pythonDocs for better understanding of String format() function. Execute the query, will run.

• Jorge Leitao: bear in mind that the way to pass an argument to an SQL query is using cursor.execute(query, params) or equivalent to avoid SQL ejection.

Answer 3

I received an answer from another site, so I thought I'd pass it along. The following script changes the query to string and is accepted into my definitionQuery. For added value, I 'set' the list to eliminate duplicates:

if "ds" in row.Field1:
 distList.append('"{}"'.format(row.Field1))
 lyr.definitionQuery = '"Field1" in ({})'.format(", ".join(set(distList)))