WHERE IN Clause in python list

Question

I need to pass a batch of parameters to mysql in python. Here is my code:

sql = """ SELECT * from my_table WHERE name IN (%s) AND id=%(Id)s AND puid=%(Puid)s"""

params = {'Id':id,'Puid'  : pid}
in_p=', '.join(list(map(lambda x: '%s', names)))
sql = sql %in_p

cursor.execute(sql, names) #todo: add params to sql clause

The problem is I want to pass the name list to sql IN clause, meanwhile I also want to pass the id and puid as parameters to the sql query clause. How do I implement these in python?

score 6 · Accepted Answer · edited May 23 '17 at 12:24

6

Think about the arguments to cursor.execute that you want. You want to ultimately execute

cursor.execute("SELECT * FROM my_table WHERE name IN (%s, %s, %s) AND id = %s AND puid = %s;", ["name1", "name2", "name3", id, pid])

How do you get there? The tricky part is getting the variable number of %ss right in the IN clause. The solution, as you probably saw from this answer is to dynamically build it and %-format it into the string.

in_p = ', '.join(list(map(lambda x: '%s', names)))
sql = "SELECT * FROM my_table WHERE name IN (%s) AND id = %s AND puid = %s;" % in_p

But this doesn't work. You get:

TypeError: not enough arguments for format string

It looks like Python is confused about the second two %ss, which you don't want to replace. The solution is to tell Python to treat those %ss differently by escaping the %:

sql = "SELECT * FROM my_table WHERE name IN (%s) AND id = %%s AND puid = %%s;" % in_p

Finally, to build the arguments and execute the query:

args = names + [id, pid]
cursor.execute(sql, args)

edited May 23 '17 at 12:24

Community

1
1

answered Mar 30 '16 at 00:39

univerio

19,548
3
66
68

1

Awesome, it works! Thanks univerio. But one more question, what if my args is a map like this: args = names + {'tid':id, 'tpid':pid} Does it also works? – Jensz Mar 30 '16 at 17:39
1

@Jensz Why don't you try it? – univerio Mar 30 '16 at 17:45
1

It doesn't work. python raise error: TypeError: can only concatenate list (not "dict") to list – Jensz Mar 30 '16 at 17:53
1

@Jensz Right. You can't add a `list` and `dict` together. – univerio Mar 30 '16 at 17:54
If I could upvote this ten times I would. Thanks. This is the best explanation of what must be a common use case. – cstrutton Jul 30 '21 at 05:08

score -1 · Answer 2 · answered Mar 30 '16 at 00:33

-1

sql = """ SELECT * from my_table WHERE name IN (%s) AND id=%(Id)s AND puid=%(Puid)s""".replace("%s", "%(Clause)s")
print sql%{'Id':"x", 'Puid': "x", 'Clause': "x"}

This can help you.

answered Mar 30 '16 at 00:33

Leviathan

32
5

WHERE IN Clause in python list

2 Answers2