使用psycopg2将列名作为参数传递给PostgreSQL

Question

我正在尝试使用psycopg2向表中添加列。

row1是要添加到表中的列名列表。我可以手动添加，但当我尝试以编程方式添加时，会出现错误。

for c in row1:
    cur.execute("ALTER TABLE HHV2PUB ADD COLUMN %s text", (c,))

错误信息为：

    cur.execute("ALTER TABLE HHV2PUB ADD COLUMN %s text", (c,))
psycopg2.ProgrammingError: syntax error at or near "'HOUSEID'"
LINE 1: ALTER TABLE HHV2PUB ADD COLUMN 'HOUSEID' text

我猜这与单引号 '' 有关。

- nodebase

---

"那个单引号在 'HOUSEID'（%s）中可能是问题所在。" - Vivek S.

@varchar：是的，那些单引号在这里是因为OP使用了SQL参数。 - Martijn Pieters

Answer 1

从 Psycopg 2.7 开始，有一个安全的 sql 模块：

from psycopg2 import sql

query = sql.SQL("alter table t add column {} text")

row1 = ('col1', 'col2')
for c in row1:
    cursor.execute(query.format(sql.Identifier(c)))

在2.6及以前版本中：

使用psycopg2.extensions.AsIs

适配器符合ISQLQuote协议，适用于其字符串表示已经有效作为SQL表示的对象。

import psycopg2
from psycopg2.extensions import AsIs

conn = psycopg2.connect("host=localhost4 port=5432 dbname=cpn")
cursor = conn.cursor()

query = "alter table t add column %s text"

row1 = ('col1', 'col2')
for c in row1:
    cursor.execute(query, (AsIs(c),))
conn.commit()

- Clodoaldo Neto

---

请注意，这并不比直接插值对象名称更安全。 - Martijn Pieters

使用 sql.Identifier() 是安全的，是的：https://github.com/psycopg/psycopg2/issues/577 但是 AsIs() 不是。 - evan_b

Answer 2

你不能对 SQL对象名称使用SQL参数。 SQL参数显式引用值，以使其不能被解释为这样的值; 这是使用SQL参数的主要原因之一否则。

您需要在此处使用字符串插值。请非常小心，不要使用用户输入来生成c：

for c in row1:
    cur.execute("ALTER TABLE HHV2PUB ADD COLUMN %s text" % c)

Psycopg2提供了一种方法将参数标记为“已经转义”使用psycopg2.extensions.AsIs()，但是这仅适用于已经转义的数据。

一个更好的方法是使用psycopg2.sql扩展来管理正确的标识符转义：

from psycopg2 import sql

for c in row1:
    cur.execute(
        sql.SQL("ALTER TABLE HHV2PUB ADD COLUMN {} text").format(
            sql.Identifier(c)))

- Martijn Pieters