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?

ThiefMaster · Accepted Answer · 2010-11-06 16:14:38Z

14

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.

answered Nov 6, 2010 at 16:14

ThiefMaster

320k85 gold badges608 silver badges648 bronze badges

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

2 Comments

Jim Jeffries Over a year ago

Thanks, that gets rid of that that error but now I get exceptions.TypeError: int argument required. The only one of the fields that should be an int is catname and it is an int.

ThiefMaster Over a year ago

You have to use %s for all values, even the int one. I've had it incorrect at the beginning and then edited my answer. You might have copied the incorrect code.

Collectives™ on Stack Overflow

python psycogp2 inserting into postgresql help

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related