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
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.
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.
Psycopg allows asynchronous interaction with other database sessions using the facilities offered by PostgreSQL commands LISTEN and NOTIFY.
psycopg2 is driver to run query for PostgreSQL from python. SQLAlchemy is the ORM which is not same as database driver.
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With