Passing list of parameters to SQL in psycopg2

Question

I have a list of ids of rows to fetch from database. I'm using python and psycopg2, and my problem is how to effectively pass those ids to SQL? I mean that if I know the length of that list, it is pretty easy because I can always manually or automatically add as many "%s" expressions into query string as needed, but here I don't know how much of them I need. It is important that I need to select that rows using sql "id IN (id1, id2, ...)" statement. I know that it is possible to check the length of the list and concatenate suitable number of "%s" into query string, but I'm afraid that it would be very slow and ugly. Does anyone have an idea on how to solve it? And please don't ask why I need to do it with "IN" statement - it is a benchmark which is a part of my class assignment. Thanks in advance!

Answer 1

Python tuples are converted to sql lists in psycopg2:

cur.mogrify("SELECT * FROM table WHERE column IN %s;", ((1,2,3),)) 

would output

'SELECT * FROM table WHERE column IN (1,2,3);' 

For Python newcomers: It is unfortunately important to use a tuple, not a list here. Here's a second example:

cur.mogrify("SELECT * FROM table WHERE column IN %s;",      tuple([row[0] for row in rows])) 

Answer 2

this question is old and maybe there is a newer one out there, but the answer my colleagues are going with right now is this:

sql = "SELECT * FROM table WHERE column = ANY(%(parameter_array)s)" cur.execute(sql,{"parameter_array": [1, 2, 3]})