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

Kamil Sindi
– Kamil Sindi

2016-02-21 21:55:41 +00:00
Commented Feb 21, 2016 at 21:55 — Kamil Sindi
– Kamil Sindi, Commented Feb 21, 2016 at 21:55

Gerrat · Accepted Answer · 2013-12-26 13:48:30Z

2

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, 2013 at 13:48

answered Aug 22, 2012 at 20:53

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike Over a year ago

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 Over a year ago

This is really a comment, not an answer to the question. Please use "add comment" to leave feedback for the author.

Gerrat Over a year ago

@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 Over a year ago

have problem when dicList have one item it will print (xxx,) error will appear

Arun Shyam · Accepted Answer · 2012-08-22 21:47:01Z

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, 2012 at 21:47

Arun Shyam

5792 gold badges8 silver badges20 bronze badges

1 Comment

Jorge Leitao Over a year ago

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.

Mike · Accepted Answer · 2012-08-22 21:39:47Z

0

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

answered Aug 22, 2012 at 21:39

Mike

4,43517 gold badges65 silver badges88 bronze badges

Collectives™ on Stack Overflow

Passing Python List into SQL Query

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related