问题描述
我在 python (2.7.10) 中使用 psycopg2
连接到 postgresql 数据库.文档对动态 SQL 语句的组成非常清楚:
I use psycopg2
in python (2.7.10) to connect to a postgresql DB. The docs are pretty clear about composition of dynamic SQL statements:
Never, never, NEVER 使用 Python 字符串连接 (+) 或字符串参数插值 (%) 将变量传递给 SQL 查询字符串.甚至在枪口下也不行.
在 psycopg2
2.7 版中,有新的 sql
模块可以安全地防止 SQL 注入来执行此字符串组合.尽管如此,我还是不明白如何正确构建如下语句:
In psycopg2
version 2.7 there's the new sql
module to do this string composition in a way that's safe against SQL injection. I nevertheless don't understand how to properly construct a statement like:
import psycopg2 as ps
C = psycopg.connect(host='my_host', port=Port, database='My_DB')
cur = C.cursor()
schema = 'some_schema'
table = 'some_table'
SQL = cur.execute("SELECT * FROM "+schema+"."+table+";") # This is horribly wrong
SQL = cur.execute("SELECT * FROM some_schema.some_table;") # That's what the result should be
推荐答案
您可以使用 psycopg2.sql.Identifier
将标识符插入到查询中,例如:
You can use psycopg2.sql.Identifier
to interpolate an identifier to a query, e.g.:
from psycopg2 import sql
query = sql.SQL("SELECT * FROM {}.{}").format(*map(sql.Identifier, (schema, table)))
print query.as_string(conn)
cur.execute(query)
根据链接的文档页面,在 psycopg2
v2.8+ 中,您还可以将多个字符串传递给 Identifier
以表示限定名称,即点分隔的序列标识符:
As per the linked documentation page, in psycopg2
v2.8+ you can also pass multiple strings to Identifier
to represent a qualified name, i.e. a dot-separated sequence of identifiers:
query = sql.SQL("SELECT * FROM {}").format(sql.Identifier(schema, table))
这篇关于使用 psycopg2 编写动态 SQL 字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!