Graceful Primary Key Error handling in Python/psycopg2

Question

Using Python 2.7 and

In [150]: psycopg2.version Out[150]: '2.4.2 (dt dec pq3 ext)'

I have a simple python scripts that processing transactions and writes data to a database. Occasionally there is an insert that violates my primary key. This is fine, i just want it to ignore that record and continue on it merry way. The problem I am having is that psycopg2 primary key error is aborting the entire transaction block and all inserts after the error fail. Here is an example error

ERROR: duplicate key value violates unique constraint "encounter_id_pkey" DETAIL:  Key (encounter_id)=(9012235) already exists. 

This is on the next insert. not a violation.

Inserting: 0163168~9024065 ERROR: current transaction is aborted, commands ignored until end of transaction block 

The Second error repeats itself for every insert. Here is a simplified loop. I am looping through a pandas data frame, but it could be any loop.

conn = psycopg2.connect("dbname='XXXX' user='XXXXX' host='XXXX' password='XXXXX'")  cur = conn.cursor()  for i, val in df2.iteritems():     try:         cur = conn.cursor()         cur.execute("""insert into encounter_id_table (         encounter_id,current_date  )         values                (%(create_date)s, %(encounter_id)s ) ;""",         'encounter_id':i.split('~')[1],           'create_date': datetime.date.today() })                    cur.commit()         cur.close()      except Exception , e:         print 'ERROR:', e[0]         cur.close()  conn.close()    

Again the basic idea is to gracefully handle the Error. In the dictum of Admiral Nelson of the Royal Navy: "Damn the maneuvers go straight at them". Or in our case damn the Errors go straight at them." I thought by opening a cursor on every insert that I would be resetting the transaction block. I do not want to have to reset the connection just because of a primary key error. Is there something i am just missing?

Thanks before hand for your time.

John

Answer 1

You should rollback transaction on error.

I've added one more try..except..else construction in the code bellow to show the exact place where exception will occur.

try:     cur = conn.cursor()      try:         cur.execute("""insert into encounter_id_table (             encounter_id,current_date  )             values                    (%(create_date)s, %(encounter_id)s ) ;""",             'encounter_id':i.split('~')[1],               'create_date': datetime.date.today() })     except psycopg2.IntegrityError:         conn.rollback()     else:         conn.commit()      cur.close()  except Exception , e:     print 'ERROR:', e[0]