Passing list of parameters to SQL in psycopg2

python postgresql psycopg2
k_wisniewski picture k_wisniewski · Dec 29, 2011 · Viewed 22.7k times · Source

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

philofinfinitejest picture philofinfinitejest · Dec 29, 2011

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

Related questions

DatabaseError: current transaction is aborted, commands ignored until end of transaction block?

I got a lot of errors with the message : "DatabaseError: current transaction is aborted, commands ignored until end of transaction block" after changed from python-psycopg to python-psycopg2 as Django project's database engine. The code remains the same, just don't know …

Read More
How to set up a PostgreSQL database in Django

I'm new to Python and Django. I'm configuring a Django project using a PostgreSQL database engine backend, But I'm getting errors on each database operation. For example when I run manage.py syncdb, I'm getting: C:\xampp\htdocs\djangodir>…

Read More
Error: No module named psycopg2.extensions

I am trying to set up a PostgreSQL database for my django project, which I believe I have done now thanks to the replies to my last question Problems setting up a postgreSQL database for a django project. I am …

Read More