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

Greg Ewing greg.ewing at canterbury.ac.nz
Sat Sep 7 20:48:50 EDT 2024


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.

This is how I normally do things like this:

   try:
     do something
     .commit()
   except:
     log something
     .rollback()

Doing an explicit rollback ensures that the transaction is always
rolled back if it is interrupted for any reason.

-- 
Greg


More information about the Python-list mailing list