CODEWITHSUNDEEP
postgresqlpython-3.xpsycopg2
forvas
forvas

Reputation: 10189

Problems with psycopg2 cursor in Python3

I am using psycopg2 with Python3 and I have just realized that if I make two queries in a row and the first one is wrong, the second one fails in spite of being right. Here is an example:

query_get_dbs_wrong = (
    'SELECT * '
    'FOM pg_database;'
)
query_get_dbs_right = (
    'SELECT * '
    'FROM pg_database;'
)

try:
    connecter.cursor.execute(query_get_dbs_wrong)
except:
    print('ERROR')

try:
    connecter.cursor.execute(query_get_dbs_right)
except:
    print('ERROR')

With this code I get two messages 'ERROR' in console, shouldn't I get only one? I need to manage the second one working in spite of the first one being wrong. I tried to close the cursor and reopen it, but the problem is still there.

Any idea, please? Thank you in advance.

THE SOLUTION

Add this line to any part of the exception code of each query:

connecter.rollback()

Upvotes: 2

Views: 1303

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325221

if I make two queries in a row and the first one is wrong, the second one fails in spite of being right [snip]. With this code I get two messages 'ERROR' in console, shouldn't I get only one?

Nothing to do with Python 3.

psycopg2 defaults to autocommit off. So it opens a transaction when you run the first command. This transaction enters the aborted state when the first ERROR is thrown. As you don't do any explicit ROLLBACK on the transaction, the second command fails with an error indicating that the transaction is already aborted.

You are incorrectly assuming that catching the Python exception means that the database transaction can also ignore the error and resume. This is not the case. You must explicitly roll the transaction back (see the transaction control methods on the connection object) and begin a new one. Or you can use SAVEPOINTs, and ROLLBACK TO SAVEPOINT on error - but be aware that this incurs a performance penalty in the database.

Upvotes: 2

Related Questions