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 ge

Solution 1:

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

Solution 2:

You cannot use SQL parameters for SQL object names. SQL parameters quote values explicitly so that they cannot be interpreted as such; that is one of the major reasons to use SQL parameters otherwise.

You'll have to use string interpolation here. Be extremely careful that you are not using user input to produce c here:

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

Psycopg2 does give you a method to mark parameters as 'already escaped' with psycopg2.extensions.AsIs(), but the intention is for this to be used on already escaped data instead.

A much better idea is to use the psycopg2.sql extension to manage correct identifier escaping:

from psycopg2 import sql

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