Use a list in prepared statement

Question

I want to use a Python list (or a set actually) in my execute, but I don't quite get it.

BRANDS = {
  'toyota',
  'ford',
  'dodge',
  'spyker'
}

cur = connection.cursor()
cur.execute("SELECT model FROM cars WHERE brand IN (%s)", (list(BRANDS),)

How can I use a set or list in an IN clause in psycopg2?

Answer 1

psycopg2 converts lists to arrays, and (%s) means a single value inside a tuple, so that's obviously not correct.

What you want to do is either:

• let postgres convert a tuple to a tuple

  cur.execute("SELECT model FROM cars WHERE brand IN %s", (tuple(BRANDS),))
  

• use array operators with an array

  cur.execute("SELECT model FROM cars WHERE brand = any(%s)", (list(BRANDS),))
  

The performances should be equivalent, I usually recommend =any() because the typing makes more sense and it works even if the parameter is empty, postgres does not like empty tuples so brand in () generates an error. brand = any('{}') however works fine.

Oh and psycogp2's execute is completely happy with a list of parameters, I find that much more readable and less error prone so I'd recommend it:

cur.execute(
    "SELECT model FROM cars WHERE brand = any(%s)", [
    list(BRANDS)
])