Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Graceful Primary Key Error handling in Python/psycopg2

Tags:

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

like image 224
jdennison Avatar asked Dec 13 '11 23:12

jdennison


People also ask

How do you handle psycopg2 errors?

In the psycopg2 adapter library you can return the code by accessing the exception's pgcode attribute. It should be an alpha-numeric string, five characters in length, that corresponds to an exception in the PostgreSQL Error Codes table.

Is psycopg2 connection thread safe?

Thread and process safetyThe Psycopg module and the connection objects are thread-safe: many threads can access the same database either using separate sessions and creating a connection per thread or using the same connection and creating separate cursors. In DB API 2.0 parlance, Psycopg is level 2 thread safe.

Is psycopg2 asynchronous?

Psycopg allows asynchronous interaction with other database sessions using the facilities offered by PostgreSQL commands LISTEN and NOTIFY.

Is psycopg2 an ORM?

psycopg2 is driver to run query for PostgreSQL from python. SQLAlchemy is the ORM which is not same as database driver.


1 Answers

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] 
like image 103
lig Avatar answered Sep 21 '22 16:09

lig