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?

• Nick: Your column names will be numbers, is that correct?
• Nick: 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, ...

Answer 1

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

• Nick: 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: That is still not correct. You have to use double-quotes, not single-quotes.