Python Psycopg error and connection handling (v MySQLdb)

Question

Another crack at this: I have a frequent problem with python's psycopg2, perhaps in 2 parts. First, I consistently screw up string assignment, if anyone can lobotomize line 5 below for me, much appreciated (The first answer does work with 1 value.) Second, when I try to add a long sequence to a table, something like string-number pair in a dictionary, I have trouble dealing with exceptions. I don't care about losing the occasional weird word, but does anyone know how 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 (and other postgres python modules?, am not wedded to psycopg2):

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

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
choked on agrippæ
choked on ague

Answer 1

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:

l = "a very long ... text".split()
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.

Answer 2

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.

try:
    cur.execute("SELECT this is an error")
except:
    conn.rollback()

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