Take the 2-minute tour ×

Is there a way to make psycopg and postgres deal with errors without having to reestablish the connection, like MySQLdb? The commented version of the below works with MySQLdb, the comments make it work with Psycopg2:

results = {'felicitas': 3, 'volumes': 8, 'acillevs': 1, 'mosaics': 13, 'perat\xe9': 1, 'representative': 6....}
for item in sorted(results):
        cur.execute("""insert into resultstab values ('%s', %d)""" % (item, results[item]))
        print item, results[item]
#       conn.commit()
#       conn=psycopg2.connect(user='bvm', database='wdb', password='redacted')
#       cur=conn.cursor()
        print 'choked on', item

This must slow things down, could anyone give a suggestion for passing over formatting errors? Obviously the above chokes on apostrophes, but is there a way to make it pass over that without getting something like the following, or committing, reconnecting, etc?:

agreement 19
agreements 1
agrees 1
agrippa 9
choked on agrippa's
choked on agrippina
share|improve this question
can you add the code of addthings? becaue I guess that you have a quoting problem –  Mauli Sep 16 '08 at 9:49
add comment

2 Answers

up vote 2 down vote accepted

First of all you should let psycopg do the escaping for you by passing to the execute() method the parameters instead of doing the formatting yourself with '%'. That is:

cur.execute("insert into resultstab values (%s, %s)", (item, results[item]))

Note how we use "%s" as a marker even for non-string values and avoid quotes in the query. psycopg will do all the quoting for us.

Then, if you want to ignore some errors, just rollback and continue.

    cur.execute("SELECT this is an error")

That's all. psycopg will rollback and start a new transaction on your next statement.

share|improve this answer
add comment

I think your code looks like this at the moment:

l = "a very long ... text".split()
for e in l:
    cursor.execute("INSERT INTO yourtable (yourcol) VALUES ('" + e + "')")

So try to change it into something like this:

for e in l:
    cursor.execute("INSERT INTO yourtable (yourcol) VALUES (%s)", (e,))

so never forget to pass your parameters in the parameters list, then you don't have to care about your quotes and stuff, it is also more secure. You can read more about it at http://www.python.org/dev/peps/pep-0249/

also have a look there at the method .executemany() which is specially designed to execute the same statement multiple times.

share|improve this answer
Thanks, I do do the string assignment as you suggest. I think my bigger problem is that when I try to add something invalid, I lose the connection, and have to reestablish it, I am curious if there is a way to make it more like mysqldb, where errors can just be skipped w/ normal exception handling –  unmounted Sep 17 '08 at 7:50
add comment

Your Answer


