Python/psycopg2 WHERE IN 语句 [英] Python/psycopg2 WHERE IN statement

问题描述

通过 SQL 语句中的 %s 获得列表 (countryList) 的正确方法是什么?

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)

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

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?

谢谢

推荐答案

对于 IN 操作符，您需要一个 tuple 而不是 list，并删除括号来自 SQL 字符串.

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

这篇关于Python/psycopg2 WHERE IN 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！