With a little side of applesauce...

Wednesday, September 15, 2010

Python - UPDATE query with psycopg2

I am using psycopg2 to work with my Postgresql database, and was not getting my UPDATE queries to modify rows in the database. Aha! You have to run a conn.commit() after executing the query to commit the changes to the db.
            cursor = conn.cursor()
updateRecordStatus = "UPDATE queue SET status = " + str(status) + " WHERE qid = " + str(qid) + ";"
cursor.execute(updateRecordStatus)
conn.commit()
cursor.close ()


The tricky part of troubleshooting this was that cursor.rowcount and cursor.statusmessage were returning '1' and 'UPDATE 1' respectively, yet the row was not changing in the table.

Thanks to this example for helping me out:
http://www.daniweb.com/forums/post962021.html#post962021

3 comments:

Zica pura said...

Thanks man! that was driving me nuts!!!

gis said...

The conn.commit() part is really important!

Anonymous said...

The example you 've red gives bad examples, don't concat strings, read the doc of cursor.execute()