CODEWITHSUNDEEP
postgresqlpython-3.xtransactionspsycopg2
x-yuri
x-yuri

Reputation: 18943

How do I recover from error in a transaction with psycopg2?

I have a script that imports data from old database. Somewhere along the way I run into unique constraint violation. I want to amend the query and execute it again, but it says, "psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block":

try:
    pcur.execute(sql, values)
except psycopg2.IntegrityError:
    value = ...
    pcur.execute(sql, values)

How do I do that without switching to autocommit mode?

Upvotes: 3

Views: 4229

Answers (1)

x-yuri
x-yuri

Reputation: 18943

Inspired by this answer:

pcur.execute('SAVEPOINT sp1')
try:
    pcur.execute(sql, values)
except psycopg2.IntegrityError:
    pcur.execute('ROLLBACK TO SAVEPOINT sp1')
    value = ...
    pcur.execute(sql, values)
else:
    pcur.execute('RELEASE SAVEPOINT sp1')

Upvotes: 5

Related Questions