Parameters ? not working with python postgres

Question

I am using python with postgres and i am trying this simple query but its not working and i am not able to find why

con = psycopg2.connect(**config)


self.cursor.execute("INSERT INTO mytable (id, age, year) VALUES (nextval('my_id_seq'), ?, ?)", ('77', '44'))

I am getting this error

psycopg2.ProgrammingError: syntax error at or near "," LINE 1: ...year) VALUES (nextval('my_id_seq'), ?, ?)

EDIT

This is the error

INSERT INTO mytable (id, age, year) VALUES (nextval('my_id_seq'), %s, %s)
'6.65', '4955/1'

  File "off.py", line 80, in writeRows
    self.cursor.execute(query, values)
TypeError: not all arguments converted during string formatting

CODE:

data = [('age', '6.65'), ('year', '4974/1'), . . ]
cols = ",".join(data.keys())
qmarks = ','.join(['%s' for s in data.keys()])
query = "INSERT INTO mytable (id, %s) VALUES (nextval('my_id_seq'), %s)" % (cols,qmarks)
self.cursor.execute(query, values)

falsetru · Accepted Answer · 2014-01-02 04:17:16Z

4

psycopg2 uses pyformat param style:

>>> import psycopg2
>>> psycopg2.paramstyle
'pyformat'

Replace the parameter marker ? with %s.

See PEP 249 -- Python Database API Specification v2.0 - paramstyle.

answered Jan 2, 2014 at 4:17

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

7 Comments

user3113427 Over a year ago

I am getting syntax error like this

self.cursor.execute("INSERT INTO mytable (id, age) VALUES (nextval('my_id_seq'), %s", '44') TypeError: not all arguments converted during string formatting

can you please write my code in %s style so that i know what else to chnage

falsetru Over a year ago

@user3113427, The number of parameter markers and the length of parameters should match (parameters should be list or tuple): self.cursor.execute("INSERT INTO mytable (id, age, year) VALUES (nextval('my_id_seq'), %s, %s)", ('77', '44'))

user3113427 Over a year ago

Thanks for that. if i have values = ('77', '44') and query = INSERT INTO mytable (id, age, year) VALUES (nextval('my_id_seq'), %s, %s) can i use self.cursor.execute(query, values) or thats wrong

falsetru Over a year ago

@user3113427, It seems ok. BTW, Did you quote the query string? (query = "INSERT INTO mytable (id, age, year) VALUES (nextval('my_id_seq'), %s, %s)") If you got any error, show me the error.

falsetru Over a year ago

@user3113427, You can use parameter markers only for parameters not for column name. In addition to that, if you want to insert multiple rows, use executemany.

|

Collectives™ on Stack Overflow

Parameters ? not working with python postgres

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related