127

What is the correct method to have the list (countryList) be available via %s in the SQL statement?

# using psycopg2
countryList=['UK','France']

sql='SELECT * from countries WHERE country IN (%s)'
data=[countryList]
cur.execute(sql,data)

As it is now, it errors out after trying to run "WHERE country in (ARRAY[...])". Is there a way to do this other than through string manipulation?

Thanks

Matt
  • 7,022
  • 16
  • 53
  • 66

5 Answers5

197

For the IN operator, you want a tuple instead of list, and remove parentheses from the SQL string.

# using psycopg2
data=('UK','France')

sql='SELECT * from countries WHERE country IN %s'
cur.execute(sql,(data,))

During debugging you can check that the SQL is built correctly with

cur.mogrify(sql, (data,))
Bryan
  • 17,112
  • 7
  • 57
  • 80
  • 7
    If you're having trouble even after reading this answer re-read it again very slowly. It's a tuple of tuples and you must remove the parans around the %s if you have them there. This tripped me up because a simpler test of mine used only a single value and everything worked. Just follow this exactly as Bryan wrote it out. – zachaysan Feb 20 '20 at 01:59
  • 1
    cur.mogrify is a great tip. I keep forgetting about that one... – João Portela Feb 15 '21 at 17:04
  • 2
    I got error 'psycopg.errors.SyntaxError: syntax error at or near "$1" LINE 1: SELECT * from countries WHERE country IN $1' – dzav Mar 18 '22 at 14:56
  • 2
    Use `ANY` instead. See that doc: https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#you-cannot-use-in-s-with-a-tuple – dzav Mar 20 '22 at 19:38
  • 1
    Please edit this answer as it is not valid for `psycopg >= 3.0` - see dzav's comments or mjuopperi's answer – jryan14ify Jan 09 '23 at 19:56
59

To expland on the answer a little and to address named parameters, and converting lists to tuples:

countryList = ['UK', 'France']

sql = 'SELECT * from countries WHERE country IN %(countryList)s'

cur.execute(sql, { # You can pass a dict for named parameters rather than a tuple. Makes debugging hella easier.
    'countryList': tuple(countryList), # Converts the list to a tuple.
})
Joshua Burns
  • 8,268
  • 4
  • 48
  • 61
  • 1
    Thank you for the tip on passing in a dict. That is much much better. – Jack Jan 08 '19 at 16:27
  • Can this be implemented with multiple WHERE x IN clauses and multiple lists in the dictionary? – Odisseo Oct 04 '19 at 01:41
  • 1
    Correction: @Odisseo Yes, with the almighty OR. Eg: `cur.execute("SELECT * FROM table WHERE col IN %(list1)s OR col IN %(list2)s", {'list1': tuple(1,2,3), 'list2' = tuple(4,5,6)})` – Joshua Burns Dec 27 '19 at 19:33
  • Warning, this will throw a postgres syntax error if the list / tuple is empty. Alternatively, you may use a WHERE ANY statement as shown in other answers. – Gman Feb 22 '23 at 16:09
20

You could use a python list directly as below. It acts like the IN operator in SQL and also handles a blank list without throwing any error.

data=['UK','France']
sql='SELECT * from countries WHERE country = ANY (%s)'
cur.execute(sql,(data,))

source: http://initd.org/psycopg/docs/usage.html#lists-adaptation

Praveenrajan27
  • 611
  • 6
  • 9
7

Since the psycopg3 question was marked as a duplicate, I'll add the answer to that here too.

In psycopg3, you can not use in %s with a tuple, like you could in psycopg2. Instead you have to use ANY() and wrap your list inside another list:

conn.execute("SELECT * FROM foo WHERE id = ANY(%s)", [[10,20,30]])

Docs: https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#you-cannot-use-in-s-with-a-tuple

mjuopperi
  • 773
  • 7
  • 25
-1

I'm using 'row_factory': dict_row and the following works for me:

SQL: ... where col_name = ANY(%(col_name)s);

Python: connection.execute(sql, {'col_name': ['xx',...,'yy']})

Frankie Drake
  • 1,338
  • 9
  • 24
  • 40
  • This solution is already provided by existing answers (using a dict for params is a trivial difference) – snakecharmerb Aug 17 '23 at 12:32
  • @snakecharmerb No, my answer differs – Frankie Drake Aug 17 '23 at 15:24
  • How does it differ? [This](https://stackoverflow.com/a/54379662/5320906) and [this](https://stackoverflow.com/a/75040183/5320906) both seem identical save for the style of parameter substitution. – snakecharmerb Aug 17 '23 at 16:48
  • @snakecharmerb Both solution uses `(%s)` placeholder and raw array with values, while my solution uses named placeholder `column_name` and dict with a column name key and array values – Frankie Drake Aug 18 '23 at 08:07