Graceful Primary Key Error Handling In Python/psycopg2
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. Occas
Solution 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]
Solution 2:
First of all: CURRENT_DATE
is a reserved word in every SQL standard as well as in PostgreSQL. You cannot use it as identifier without double-quoting it. I would strongly advice not to use it at all. I renamed the column to curdate
in my example
Next, I am no expert in python syntax, but you seem to have reversed the order of your insert-columns:
(%(create_date)s, %(encounter_id)s )
Should be:
( %(encounter_id)s, %(create_date)s)
To your main question: you can avoid the problem altogether by checking if the key is already in the table before using it in the insert command:
INSERTINTO encounter_id_table (encounter_id, curdate)
SELECT1234, now()::dateWHERENOTEXISTS (SELECT*FROM encounter_id_table t
WHERE t.encounter_id =1234);
In Python syntax, that should be:
cur.execute("""INSERT INTO encounter_id_table (encounter_id, curdate)
SELECT %(encounter_id)s, %(create_date)s,
WHERE NOT EXISTS (
SELECT * FROM encounter_id_table t
WHERE t.encounter_id = %(encounter_id)s);""",
{'encounter_id':i.split('~')[1],
'create_date': datetime.date.today()})
Post a Comment for "Graceful Primary Key Error Handling In Python/psycopg2"