ValueError usupported format character 'd' with psycopg2

Question

I have code like this:

print "company_id = %d" % company_id
...
db.cursor.execute("insert into person (company_id, first, last, type) values (%d, '%s', '%s', %d);", (company_id, name[0], name[1], type))

I get the following output:

company_id = 1
Traceback (most recent call last):
...
  File "./GetPeople.py", line 125, in insertPerson
    db.cursor.execute("insert into person (company_id, first, last, type) values (%d, '%s', '%s', %d);",     

Why can it print out the first line but it gives an error for the db.cursor.execute() call?

Answer 1

The single quotes around the %s placeholders are incorrect and the %d isn't used as per the docs. Change

db.cursor.execute("insert into person (company_id, first, last, type) values (%d, '%s', '%s', %d);", (company_id, name[0], name[1], type))

to

db.cursor.execute("insert into person (company_id, first, last, type) values (%s, %s, %s, %s);", (company_id, name[0], name[1], type))

Answer 2

From the Psycopg FAQ:

Q: I can’t pass an integer or a float parameter to my query: it says a number is required, but it is a number!

A: In your query string, you always have to use %s placeholders, even when passing a number. All Python objects are converted by Psycopg in their SQL representation, so they get passed to the query as strings. See Passing parameters to SQL queries.

Replace the %d with %s.