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
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]