postgresql 在 psycopg2 中将参数列表传递给 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8671702/
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
Passing list of parameters to SQL in psycopg2
提问by k_wisniewski
I have a list of ids of rows to fetch from database. I'm using python and psycopg2, and my problem is how to effectively pass those ids to SQL? I mean that if I know the length of that list, it is pretty easy because I can always manually or automatically add as many "%s" expressions into query string as needed, but here I don't know how much of them I need. It is important that I need to select that rows using sql "id IN (id1, id2, ...)" statement. I know that it is possible to check the length of the list and concatenate suitable number of "%s" into query string, but I'm afraid that it would be very slow and ugly. Does anyone have an idea on how to solve it? And please don't ask why I need to do it with "IN" statement - it is a benchmark which is a part of my class assignment. Thanks in advance!
我有一个要从数据库中获取的行 ID 列表。我正在使用 python 和 psycopg2，我的问题是如何有效地将这些 id 传递给 SQL？我的意思是，如果我知道该列表的长度，这很容易，因为我总是可以根据需要手动或自动将尽可能多的“%s”表达式添加到查询字符串中，但在这里我不知道我需要多少. 重要的是我需要使用 sql "id IN (id1, id2, ...)" 语句来选择这些行。我知道可以检查列表的长度并将合适数量的“%s”连接到查询字符串中，但我担心它会非常缓慢和难看。有没有人知道如何解决它？并且请不要问我为什么需要用“IN”语句来做——这是一个基准，是我课堂作业的一部分。
回答by philofinfinitejest
Python tuples are converted to sql lists in psycopg2:
Python 元组在 psycopg2 中转换为 sql 列表：
cur.mogrify("SELECT * FROM table WHERE column IN %s;", ((1,2,3),))
would output
会输出
'SELECT * FROM table WHERE column IN (1,2,3);'
For Python newcomers: It is unfortunately important to use a tuple, not a list here. Here's a second example:
对于 Python 新手：不幸的是，这里使用元组而不是列表很重要。这是第二个例子：
cur.mogrify("SELECT * FROM table WHERE column IN %s;",
tuple([row[0] for row in rows]))
回答by Brandon Henry
this question is old and maybe there is a newer one out there, but the answer my colleagues are going with right now is this:
这个问题很老了，也许还有一个更新的问题，但我的同事们现在的答案是这样的：
sql = "SELECT * FROM table WHERE column = ANY(%(parameter_array)s)"
cur.execute(sql,{"parameter_array": [1, 2, 3]})
回答by Alexei Martianov
Now sql module of psycopg2 (https://www.psycopg.org/docs/sql.html) can be used to safeguard against errors and injections, like e.g.:
现在 psycopg2 ( https://www.psycopg.org/docs/sql.html) 的sql 模块可用于防止错误和注入，例如：
import psycopg2
from psycopg2 import sql
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
ids = ['a','b','c']
sql_query = sql.SQL('SELECT * FROM {} WHERE id IN ({});').format(
sql.Identifier('table_name'),
sql.SQL(',').join(map(sql.Identifier, ids))
)
print (sql_query.as_string(cur)) # for debug
cur.execute(sql_query)
from configparser import ConfigParser
def config(filename='database.ini', section='postgresql'):
# create a parser
parser = ConfigParser()
# read config file
parser.read(filename)
# get section, default to postgresql
db = {}
if parser.has_section(section):
params = parser.items(section)
for param in params:
db[param[0]] = param[1]
else:
raise Exception('Section {0} not found in the {1} file'.format(section, filename))
return db
Note: sql.Identifierwill add quotes if needed so it will work if you use quoted identifiers in PostgreSQL also (they have to be used to allow e.g. case sensitive naming).
注意：sql.Identifier如果需要，将添加引号，因此如果您在 PostgreSQL 中也使用带引号的标识符，它将起作用（它们必须用于允许例如区分大小写的命名）。
Example and structure of database.ini:
database.ini 的示例和结构：
[postgresql]
host=localhost
port=5432
database=postgres
user=user
password=mypass
