Passing param to DB .execute for WHERE IN... INT list

python postgresql pg8000
John Giotta picture John Giotta · Feb 12, 2010 · Viewed 9.1k times · Source

With Python's DB API spec you can pass an argument of parameters to the execute() method. Part of my statement is a WHERE IN clause and I've been using a tuple to populate the IN. For example:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

But when I run into a situation where the parameter tuple is only a tuple of 1 item, the execute fails.

ProgrammingError: ERROR: syntax error at or near ")"
LINE 13: WHERE id IN (3,)

How can I get the tuple to work with clause properly?

Answer

mechanical_meat picture mechanical_meat · Feb 12, 2010

Edit: If you think this answer circumvents the built-in protections against SQL-injection attack you're mistaken; look more closely.

Testing with pg8000 (a DB-API 2.0 compatible Pure-Python interface to the PostgreSQL database engine):

This is the recommended way to pass multiple parameters to an "IN" clause.

params = [3,2,1]
stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in params)
cursor.execute(stmt, params)

Full example:

>>> from pg8000 import DBAPI
>>> conn = DBAPI.connect(user="a", database="d", host="localhost", password="p")
>>> c = conn.cursor()
>>> prms = [1,2,3]
>>> stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in prms)
>>> c.execute(stmt,prms)
>>> c.fetchall()
((1, u'myitem1'), (2, u'myitem2'), (3, u'myitem3'))

Related questions

AttributeError: 'UUID' object has no attribute 'replace' when using backend-agnostic GUID type

I want to have a primary key id with type uuid in a Postgresql database using SQLAlchemy 1.1.5, connecting to the database with the pg8000 adapter. I used the Backend-agnostic GUID Type recipe from the SQLAlchemy documentation. When I want to …

Read More
Can't subtract offset-naive and offset-aware datetimes

I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age. The problem I'm having is that both datetime.datetime.…

Read More
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