python psycogp2 inserting into postgresql help

Question

I have the following code to insert do an insert into my postgresql database

conn = psycopg2.connect("my connection setting are in here")
cur = conn.cursor()
cur.execute('INSERT INTO src_event (location_id, catname, title, name) VALUES (%i, \"%s\", \"%s\", \"%s\")' % (1441, "concert", item['title'], item['artists'] )) 

However when I run this I get the following error:

psycopg2.ProgrammingError: column "concert" does not exist
LINE 1: ...(location_id, catname, title, name) VALUES (1441, concert, "...

But "concert" is not a column it is a value so I dont understand why I am getting this error.

EDIT - I have tried putting \" round the value concert and tried without

How can I get my data inserted with out getting this error?

Answer 1

You really, really shouldn't use python string formatting to build queries - they are prone to SQL injection. And your actual problem is that you use " for quoting while you have to use ' for quoting (" quotes table/column names etc, ' quotes strings).

Use the following code instead:

cur.execute('INSERT INTO src_event (location_id, catname, title, name) VALUES (%s, %s, %s, %s)', (1441, 'concert', item['title'], item['artists']))

Note that you have to use %s no matter what type you actually have.

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