本文介绍了使用 psycopg2 编写动态 SQL 字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在 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 字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 12:23
查看更多