Python/psycopg2 WHERE IN 语句

通过 SQL 语句中的% s 使列表(country List)可用的正确方法是什么？

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


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

与现在一样，它在尝试运行“ WHERE country in (ARRAY [ ... ])”之后出错。除了通过字符串操作之外，还有其他方法可以做到这一点吗？

谢谢

49258 次浏览

小开

最佳答案

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

小开

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

小开

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