Re: psycopg2: proper positioning of .commit() within try: except: blocks

Am Sun, Sep 08, 2024 at 12:48:50PM +1200 schrieb Greg Ewing via Python-list:

On 8/09/24 9:20 am, Karsten Hilbert wrote:

try:
do something
except:
log something
finally:
.commit()
>
cadence is fairly Pythonic and elegant in that it ensures the
the .commit() will always be reached regardless of exceptions
being thrown or not and them being handled or not.

>
That seems wrong to me. I would have thought the commit should only
be attempted if everything went right.
>
What if there's a problem in your code that causes a non-SQL-related
exception when some but not all of the SQL statements in the
transaction bave been issued? The database doesn't know something
has gone wrong, so it will happily commit a partially-completed
transaction and possibly corrupt your data.

A-ha !

try:
run_some_SQL_that_succeeds()
print(no_such_name) # tongue-in-cheek
1 / 0 # for good measure
except SOME_DB_ERROR:
print('some DB error, can be ignored for now')
finally:
commit()

which is wrong, given that the failing *Python* statements
may very well belong into the *business level* "transaction"
which a/the database transaction is part of.

See, that's why I was asking in the first place :-)

I was overlooking implications.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B