Pass column name as parameter to PostgreSQL using psycopg2

Question

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

that single quotes in 'HOUSEID' (%s) should be the problem — Vivek S.
– Vivek S., Commented Dec 4, 2014 at 9:06
@varchar: Yes, and those single quotes are there because the OP used SQL parameters. — Martijn Pieters
– Martijn Pieters, Commented Dec 4, 2014 at 9:07

Clodoaldo Neto · Accepted Answer · 2017-06-23 16:24:37Z

27

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

edited Jun 23, 2017 at 16:24

answered Dec 4, 2014 at 10:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martijn Pieters Over a year ago

Note that this is no safer than using straight-up interpolation of the object name.

evan_b Over a year ago

Using sql.Identifier() is safe, yes: github.com/psycopg/psycopg2/issues/577 But AsIs() is not.

Martijn Pieters · Accepted Answer · 2017-06-02 18:27:53Z

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

Collectives™ on Stack Overflow

Pass column name as parameter to PostgreSQL using psycopg2

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related