Handling errors in psycopg2 - one error seems to create more?

Question

I'm using Python 2 with psycopg2 v2.6.2. I'm running a series of psycopg2 commands, and catching any errors:

for r in records:
  county = r[0]
  q = 'INSERT INTO allparcels(county, geom) '
  q += "SELECT %s, ST_Union(ST_Buffer(wkb_geometry, 0)) FROM parcel "
  q += "WHERE county=%s"
  print q % (county, county)
  try:
    cursor.execute(q, (county, county))
    conn.commit()
  except Exception, e:
    print e
    print e.pgerror
cursor.close()
conn.close()

This runs for the first couple of records, then I get ERROR: current transaction is aborted, commands ignored until end of transaction block in rapid succession for all the rest of the rows.

Oddly, if I take one of the later commands and run it directly in my database, it works fine. So I think the later errors are something to do with psycopg2 and my error handling, not the SQL command.

I think I must not be handling the error correctly. I'd like my script to print the error, and then continue smoothly to the next command.

How should I do this instead?

Answer 1

The issue here is the following:

try:
    # it is this specific line that causes an error
    cursor.execute(q, (county, county))

    # this never happens, so the transaction is still open
    conn.commit()
except Exception, e:
    ...
    # you never issued a rollback on the transaction ... its still open

As you can see, if cursor.execute fails then you neither attempt to commit the transaction or roll it back. The next iterations through the loop will be attempting to execute SQL on an already aborted but not rolled back transaction.

Instead you need to follow this kind of pattern

try:
    cursor.execute(...)
except Exception, e:
    conn.rollback()
else:
    conn.commit()