使用psycopg2将列名作为参数传递给PostgreSQL [英] Pass column name as parameter to PostgreSQL using psycopg2

问题描述

我正在尝试使用psycopg2

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

row1 below is a list of column names to be added to the table. I can do it manually but when I try to do it programatically I get an error.

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

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

My guess is that it has something to do with the single quotes ''

推荐答案

从Psycopg 2.7开始，安全的

As of Psycopg 2.7 there is the safe 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及更早版本:

With 2.6 and earlier:

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

这篇关于使用psycopg2将列名作为参数传递给PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！