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

Answer 1

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