1

Is there a way to pass a python list as a parameter of prepared statement in pymysql?

list_id = [1,2,3]
sql = "select * from table where id in (%s)"
cursor.execute(sql,(list_id,))
szu
  • 932
  • 1
  • 9
  • 22
  • Nope. There's no way to do that... to pass a set (list) of values into a single bind placeholder. Prepared statement bind values are *scalar* values. It's not possible to pass in syntax that will be interpreted as SQL. To execute this is a prepared statement with bind placeholders, the SQL text would need to be "`SELECT * FROM table WHERE id IN (?,?,?)`", a separate question mark for each value. We could write code to get a count of the number of values in the list, and then dynamically generate a SQL statement with the required number of placeholders. – spencer7593 May 30 '18 at 19:00
  • @spencer7593 I think that it's the exact technique explained in the answer of the linked question – Jean-François Fabre May 30 '18 at 19:04

3 Answers3

3

I would do it this way.

list_id = [1,2,3]
sql = "select * from table where id in ({})".format(",".join([str(i) for i in list_id]))
print(sql)

Out[]: "select * from table where id in (1,2,3)"

Should you need an alternative way of running execution, here is another reference to doing many cursor execution through pyodbc library. Hope it helps.

cursor.executemany

executemany(sql, *params)

Executes the same SQL statement for each set of parameters, returning None. The single params parameter must be a sequence of sequences, or a generator of sequences.

params = [ ('A', 1), ('B', 2) ] executemany("insert into t(name, id) values (?, ?)", params) This will execute the SQL statement twice, once with ('A', 1) and once with ('B', 2).

Toto Lele
  • 394
  • 2
  • 13
  • This isn't what the OP was asking for. They wanted a single query that can use `IN` and then supply the list. – roganjosh May 30 '18 at 19:01
  • Yup, on the second thought I realized I was out of topic ;p so I modified the answer. Thanks for reminding – Toto Lele May 30 '18 at 19:04
  • 1
    Your edit is confusing (what relevance ids `executemany`?) and subject to a major risk of SQL injection. There is no reason to use string formatting here at all. It would be useful for you to read the duplicate because you're honestly opening yourself up to major issues if you ever do something like this in production code. – roganjosh May 30 '18 at 19:05
  • @roganjosh, I'm aware of the SQL injection risks. Thanks for pointing out the flaws in my code because I certainly did not have direct database design experience so I may be lacking the experience. I'm just trying to help the OP since the OP was trying to pass a set of list into a single placeholder which I think could be solved by casting the `int` element into `str` and join it with a comma to comply with SQL syntaxes. – Toto Lele May 30 '18 at 19:18
0

if your list will have only integers, then you will have to combine them with a ',' and pass as string.

Bharath Bharath
  • 45
  • 1
  • 1
  • 10
0
list_id = [1,2,3]
# not working:
list_str = ', '.join(list_id)
# most likely:
list_str = ', '.join(map(str, list_id))

But much better way, preventing construction crazy statements is with https://www.python.org/dev/peps/pep-0249/#paramstyle or as mentioned in a duplicate answer above. )

Evgeny
  • 4,173
  • 2
  • 19
  • 39
  • 1
    it doesn't work – Jean-François Fabre May 30 '18 at 18:56
  • pymysql doesn't accept comma-delimited string – szu May 30 '18 at 19:00
  • 1
    @Jean-FrançoisFabre it's also bad practice since it's open to SQL injection. – roganjosh May 30 '18 at 19:00
  • what is SQL statement you would like to construct though? – Evgeny May 30 '18 at 19:02
  • @EvgenyPogrebnyak almost certainly `SELECT * FROM somewhere WHERE some_val IN (1, 2, 3)`. I actually think your query could work, but it still is bad practice. EDIT no, it won't work, because you're mixing string interpolation syntax with SQL parameterized queries. – roganjosh May 30 '18 at 19:03
  • @Anthony has it – Evgeny May 30 '18 at 19:05
  • @EvgenyPogrebnyak no, he doesn't. These answers are only serving as **bad** examples of how to do this. – roganjosh May 30 '18 at 19:06
  • @roganjosh - there is only _that much_ harm you can do by constructing SQL queries manually ) what is a proper way though? – Evgeny May 30 '18 at 19:11
  • @EvgenyPogrebnyak like a malicious user irreversibly deleting every single bit of data in your whole database? Have you researched [SQL injection](https://www.alertlogic.com/blog/tried-and-true-sql-injection-still-a-leading-method-of-cyber-attack/)? `placeholders = ', '.join(['?' for item in list_id])`; `"select * from table where id in ({})".format(placeholders)` – roganjosh May 30 '18 at 19:14
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172110/discussion-between-evgeny-pogrebnyak-and-roganjosh). – Evgeny May 30 '18 at 19:19