The SQL "IN" operator in psycopg2

I’ve been working as of late with psycopg2, the Python bindings for PostgreSQL. Documentation is a bit, erm, lacking. You can get the basic stuff working by reading Python’s DBAPI-2.0 specification, since psycopg2 follows it, but (as I’ve only recently realized) discovering the extra features that exist needs you to poke the source.

The other day I was wanting to pass to the execute method of a cursor a sequence to fill the value list in a SQL “IN” statement. The DBAPI spec does not mention this usage at all, and a cursory question in #python told me it wasn’t supported (required?) by the spec.

It is, in any case, supported in psycopg2. You just need to

  import psycopg2.extensions

and it just works: you can pass tuples, and they’ll be converted to a value list. If you want to be able to pass lists as well:

  from psycopg2.extensions import SQL_IN, register_adapter
  register_adapter(list, SQL_IN)

There is also a row factory in psycopg2.extras that offers a dictionary interface, instead of tuple-based.

Finally, I have no idea why, but when I started with psycopg2, I came to get the idea that only named parameter substitution was supported (INSERT ... VALUES (%(var1)s, %(var2)s)), but simple %s substitution is also supported: just pass (of course) a tuple as the second parameter to execute, instead of a dictionary.