Python/psycopg2 WHERE IN 语句
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/28117576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python/psycopg2 WHERE IN statement
提问by Matt
What is the correct method to have the list (countryList) be available via %s in the SQL statement?
通过 SQL 语句中的 %s 获得列表 (countryList) 的正确方法是什么?
# 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?
就像现在一样,它在尝试运行“WHERE country in (ARRAY[...])”后出错。除了通过字符串操作之外,还有其他方法可以做到这一点吗?
Thanks
谢谢
采纳答案by Bryan
For the IN
operator, you want a tupleinstead of list, and remove parentheses from the SQL string.
对于IN
运算符,您需要一个元组而不是list,并从 SQL 字符串中删除括号。
# 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
在调试期间,您可以检查 SQL 是否正确构建
cur.mogrify(sql, (data,))
回答by Joshua Burns
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.
})
回答by Praveen KR
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.
您可以直接使用 python 列表,如下所示。它的作用类似于 SQL 中的 IN 运算符,还可以处理空白列表而不会引发任何错误。
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
来源:http: //initd.org/psycopg/docs/usage.html#lists-adaptation