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 · Accepted Answer · 2017-06-19 09:57:21Z

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, 2017 at 9:57

answered Jun 19, 2017 at 9:45

Arpit Solanki

10k4 gold badges45 silver badges57 bronze badges

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

4 Comments

Ludvig Knutsmark Over a year ago

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 Over a year ago

See my response above

Arpit Solanki Over a year ago

@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.

jhutar Over a year ago

Hello. I think this approach would be prone to SQL injection attack.

Clodoaldo Neto · Accepted Answer · 2017-06-19 13:29:32Z

1

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

answered Jun 19, 2017 at 13:29

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Insert query with variables postgresql python

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related