using "in" inside sql raw query in python with parameter

Question

i have to pass more than one id's to my sql raw query and these id's will be passed as parameter. i am doing this in following way :

itlist=[6009,[5989,5796,5793],5788,5750]
for it in itlist:
    cursor.execute("select branch_tag from builds where id in (%d);"%[it])
    data1=cursor.fetchall()
        for record in data1:
            print record[0]

this gives me an error, i dont know how to pass that list to the sql query. any help will be appreciated...thanks in advance

What is the error? Can you edit your question to include a partial stack trace? — huu, May 29 '14 at 05:15
i am doing it in django and on browser it shows me an error : unboundLocalError.. — d-ashu, May 29 '14 at 05:20
may help http://stackoverflow.com/questions/589284/imploding-a-list-for-use-in-a-python-mysqldb-in-clause — Haim Evgi, May 29 '14 at 05:21
Part of your trouble is the nested lists you're passing. There's no way that's going to result in a valid query. — g.d.d.c, May 29 '14 at 05:23
and using a list with the `%d` formatter will give you a `TypeError` ... — mgilson, May 29 '14 at 05:25
I think OP is attempting multiple queries, and when the item in the list is not itself a list, OP tries to put it into a list before passing it into the query. Of course, this breaks when the item is already a list. — huu, May 29 '14 at 05:27
so, how to do this?, and my list data is of int type not str... — d-ashu, May 29 '14 at 05:32
stackoverflow.com/questions/589284/… – Haim Evg it takes the input as string but my numbers are of integer type — d-ashu, May 29 '14 at 05:47
i can do this in following way ...simply using python basics : cursor.execute("select branch_tag from builds where id=(select max(id) from builds where id in ("+ str(it)[1:-1] +"));") — d-ashu, May 29 '14 at 07:52

score 0 · Answer 1 · answered May 29 '14 at 05:52

flatten the list into a newlist and pass this to your query:

def flatten(foo):
    newl = []
    for x in foo:
        if hasattr(x, '__iter__'):
            for y in flatten(x):
                 newl.append(y)
        else:
            newl.append(x)
    return newl

itlist=[6009,[5989,5796,5793],5788,5750]
newitlist = flatten(itlist)

for it in newitlist:
    cursor.execute("select branch_tag from builds where id in (%d);"%[it])
    data1=cursor.fetchall()
        for record in data1:
            print record[0]

i dont want to use every id as individual, i have to pass a list as it as if a list found in itlist, and in that case raw query has 3 values inside the "in" clause, otherwise single, as given in the example... — d-ashu, May 29 '14 at 06:07

score 0 · Answer 2 · edited May 23 '17 at 12:05

Adapted from the answer Haim referenced, but for your particular case where you have nested lists:

itlist=[6009,[5989,5796,5793],5788,5750]
for it in itlist:
    # This ensures that 'it' is a list even if it is just a single element
    if type(it) != list:
        it = [it]
    format_strings = ','.join(['%s'] * len(it))
    cursor.execute("select branch_tag from builds where id in (%s)" % format_strings,
            tuple(it))
    data1=cursor.fetchall()
        for record in data1:
            print record[0]

The reason why there's a %s in the query string and not a %d is because you're actually going to replace it with a bunch of %s's (so %s or %s,%s,%s) in order to accommodate the variable number of id's you're trying to pass in.

So in your case, the first query string that would be built is:

"select branch_tag from builds where id in (6009)"

And the next one would be:

"select branch_tag from builds where id in (5989,5796,5793)"

This is all, of course, assuming that itlist is meant to be nested as in your question. If it isn't, then most of this answer still applies as:

itlist=[6009,5989,5796,5793,5788,5750]
format_strings = ','.join(['%s'] * len(itlist))
cursor.execute("select branch_tag from builds where id in (%s)" % format_strings,
        tuple(itlist))
data1=cursor.fetchall()
    for record in data1:
        print record[0]

score -1 · Answer 3 · answered May 30 '14 at 03:57

convert the every element to a list and then just use the python basics in following way :

itlist=[[6009],[5989,5796,5793],[5750]]
for it in itlist:
    cursor.execute("select branch_tag from builds where id=(select max(id) from builds        where id in ("+ str(it)[1:-1] +"));")
data1=cursor.fetchall()
for record in data1:
    print record[0]

Thanx all for your feedbacks and your valuable time.

using "in" inside sql raw query in python with parameter

3 Answers3