Insert query with variables postgresql python

Question

I'm trying to insert several variables in a insert query on postgres using python. I can't wrap my head around how to use the string formatting.

For example, this works fine:

cursor.execute('''CREATE TABLE %s 
 (id SERIAL PRIMARY KEY,
 sender varchar(255) not null,
 receiver varchar(255) not null,
 message varchar(255))''' %username)

as does this:

cursor.execute('''INSERT INTO test (sender, receiver, message)             
VALUES(%s,%s,%s)''', (sender, receiver,message))

My problem is that I want to have the table name as a variable too. I have tried:

cursor.execute('''INSERT INTO %s (sender, receiver, message)
VALUES(%s,%s,%s)''' %username, (sender, receiver, message))

I get the following error:

TypeError: not enough arguments for format string

I get that I have to change the parentheses somehow, but I don't know how.

Thanks in advance.

EDIT:

Choose a different approach from this psycopg2 which worked perfectly.

Arpit Solanki · Answer 1 · 2017-06-19T09:57:21.763

2

You are passing the arguments in a wrong way. The arguments passed are causing you the trouble. Use format function instead of % as it is more sophisticated and readable.

"INSERT INTO {} (sender, receiver, message) VALUES({},{},{})".format("some", "world", "world","hello")

The output of the above:

'INSERT INTO some (sender, receiver, message) VALUES(world,world,hello)'

edited Jun 19 '17 at 09:57

answered Jun 19 '17 at 09:45

Arpit Solanki

9,567
3
41
57

with: cursor.execute("INSERT INTO {} (sender, receiver, message) VALUES({},{},{}).format(username, "one", "two", "three")) I got: >psycopg2.ProgrammingError: column "one" does not exist LINE 1: ...SERT INTO test (sender, receiver, message) VALUES(one,two,th... – Ludvig Knutsmark Jun 19 '17 at 09:56
See my response above – Ludvig Knutsmark Jun 19 '17 at 10:05
@LudvigKnutsmark Your error is from a wrong query but string is formatted correctly. Check that table you are inserting in does exists and column names are correct. If you are having another error then ask a new question. – Arpit Solanki Jun 19 '17 at 10:09
Hello. I think this approach would be prone to SQL injection attack. – jhutar Mar 02 '23 at 05:58

score 1 · Answer 2 · answered Jun 19 '17 at 13:29

Use the high level sql module to avoid likely mistakes:

from psycopg2 import sql

query = sql.SQL('''
    insert into {} (sender, receiver, message)
    values (%s, %s, %s)
''').format(sql.Identifier(username))

cursor.execute (query, (sender, receiver, message))

Insert query with variables postgresql python

2 Answers2