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

Karsten Hilbert Karsten.Hilbert at gmx.net
Sun Sep 8 07:06:19 EDT 2024


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


More information about the Python-list mailing list