Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
409 views
in Technique[技术] by (71.8m points)

python - Pass column name as parameter to PostgreSQL using psycopg2

I'm trying to add columns to a table using psycopg2

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

The error is:

    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 ''

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

With 2.6 and earlier:

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...