How to specify postgres table columns as variables in python?

Question

So I have a table that is being updated with new columns every time a new instance of a class is being instantiated. Here's my code doing that.

query = "alter table test add column %s integer"
rows = (str(Driver.count), str(Driver.count+1))
for c in rows:
    cur.execute(query, (AsIs(c),))
conn.commit()

Driver.count is the counter that is being incremented every new instance of the class. Now when I want to update the table, I use a command similar to this:

cur.execute("INSERT INTO test (str(Driver.count), str(Driver.count+1)) \
VALUES (%d, %d)" % (currentRow,currentCol));

Where the two columns I am specifying are variables. I know the above command won't work, since the column variables somehow need to be outside of the quotations, like when I specify the values to be inserted. How can I do this?

John's answer will work for you, but you should know that it is a bad idea to use numbers as column names. If possible, you should prepend some text to number, like dc1, dc2, ... — Nick
– Nick, Commented Aug 17, 2016 at 15:31

John Zwinck · Accepted Answer · 2016-08-17 15:02:05Z

2

cur.execute('INSERT INTO test ("%d", "%d") VALUES (%d, %d)'
    % (Driver.count, Driver.count+1, currentRow, currentCol))

Or using newer syntax:

cur.execute('INSERT INTO test ("{}", "{}") VALUES ({}, {})'.format(
    Driver.count, Driver.count+1, currentRow, currentCol))

edited Aug 17, 2016 at 15:02

answered Aug 16, 2016 at 21:08

John Zwinck

252k44 gold badges346 silver badges459 bronze badges

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

2 Comments

Nick Over a year ago

If those column names are numbers, this may still error out (in PostgreSQL). You will need to make sure the column names have double quotes around them. So something like: ... 'INSERT INTO test ("{}", "{}") VALUES ({}, {})'.format(...

Nick Over a year ago

That is still not correct. You have to use double-quotes, not single-quotes.

Collectives™ on Stack Overflow

How to specify postgres table columns as variables in python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related