CODEWITHSUNDEEP
pythonpsycopg2where-in
Matt
Matt

Reputation: 7222

Python/psycopg WHERE IN statement

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

Upvotes: 140

Views: 70773

Answers (5)

Frankie Drake
Frankie Drake

Reputation: 1388

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

Upvotes: 0

mjuopperi
mjuopperi

Reputation: 971

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

Upvotes: 23

Praveenrajan27
Praveenrajan27

Reputation: 641

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

Upvotes: 23

Joshua Burns
Joshua Burns

Reputation: 8582

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.
})

Upvotes: 65

Bryan
Bryan

Reputation: 17703

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

Upvotes: 218

Related Questions