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

Possible duplicate of [python list in sql query as parameter](http://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter) — Kamil Sindi, Feb 21 '16 at 21:55

Gerrat · Answer 1 · 2013-12-26T13:48:30.647

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.

edited Dec 26 '13 at 13:48

answered Aug 22 '12 at 20:53

Gerrat

28,863
9
73
101

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. – Mike Aug 22 '12 at 21:37
This is really a comment, not an answer to the question. Please use "add comment" to leave feedback for the author. – Avi Kumar Aug 23 '12 at 04:16
@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. – Gerrat Aug 23 '12 at 12:57
have problem when dicList have one item it will print (xxx,) error will appear – giaosudau Dec 26 '13 at 04:26

score 1 · Answer 2 · answered Aug 22 '12 at 21:47

1

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.

answered Aug 22 '12 at 21:47

Arun Shyam

559
2
8
20

1

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. – Jorge Leitao Feb 27 '15 at 14:38

score 0 · Answer 3 · answered Aug 22 '12 at 21:39

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

Passing Python List into SQL Query

3 Answers3