Insert row into psql db using psycopg2

Question

Could you please help me figure out what I am doing wrong? I am trying to insert a new player into the players table. this is the python code:

def registerPlayer(name):

code for connecting to db and cursor 

c.execute("INSERT INTO players(player_name) VALUES({name});".format(name=name))

code for committing to db and closing the connection

here is my table schema:

CREATE TABLE players(
  player_id serial PRIMARY KEY,
  player_name  varchar(50) NOT NULL

);

below is error:

psycopg2.ProgrammingError: syntax error at or near "Nalaar" LINE 1:

INSERT INTO players(player_name) VALUES(Chandra Nalaar);

Taku · Accepted Answer · 2017-06-23 01:48:04Z

2

You should never use string formatting for placing values into sql query. Instead, you should use %s and pass the name in the vars parameter. The reason behind doing it this way is because it helps you convert the parameters into the appropriate data types.

Btw, having a ; at the end in the sql string is redundant when called by cursor.execute, since it does it for you automatically.

c.execute("INSERT INTO players(player_name) VALUES(%(name)s)", {"name":name})

See this page for further details:

http://initd.org/psycopg/docs/usage.html#query-parameters

edited Jun 23, 2017 at 1:48

answered Jun 22, 2017 at 2:59

Taku

34.1k12 gold badges79 silver badges88 bronze badges

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

3 Comments

rstreet Over a year ago

thanks a lot! it didn't show any error after I revised the code (though the last part should be {"name": name"} according to the documentation.

Taku Over a year ago

@rstreet you’re welcome, sorry about the typo, I typed it from my phone :p

Alexander Stohr Over a year ago

how about f-strings?

Collectives™ on Stack Overflow

Insert row into psql db using psycopg2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related