python - 使用 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

我的猜测是它与单引号 ''

有关

Answer 1

从 Psycopg 2.7 开始，有保险箱 sql module :

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

Adapter conform to the ISQLQuote protocol useful for objects whose string representation is already valid as SQL representation.

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()