[DB-SIG] Exeception Handling.

[M.-A. Lemburg]

On 2008-07-04 12:34, Heston James - Cold Beans wrote:
> Good afternoon Chaps,
> 
>  
> 
> I'm looking for your advice on the best way to handle exceptions on a query
> and commit. Within my application I have a universal database connection
> which gets injected into objects which require database access, I then use
> this connection to create cursors, run queries and commit the changes to the
> database. Does that sounds like a good approach?

Yes.

> Take a look at this query below:
> 
>  
> 
>         # Create the Cursor for the database.
> 
>         cursor = self.datasource.cursor()
> 
>         
> 
>         # Run the insert query to create the entry.
> 
>         cursor.execute("""
> 
>                         INSERT INTO message (
> 
>                                             message_id,
> 
>                                             name,
> 
>                                             checksum,
> 
>                                             created, 
> 
>                                             modified
> 
>                                             ) 
> 
>                         VALUES (
> 
>                                 %s,
> 
>                                 %s,
> 
>                                 %s,
> 
>                                 now(), 
> 
>                                 now()
> 
>                                 )""", (self.message_id, self.name,
> self.checksum))
> 
>  
> 
>         # Commit the change to the database.
> 
>         self.datasource.commit()
> 
>         
> 
>         # Close the cursor for the database.
> 
>         cursor.close()
>
> Now to add some exception handling to that query, do I simply wrap the whole
> code block up in a try/except, and handle any exceptions? or should I be
> doing a nested try/except which only runs the commit method is the execute
> runs without exception, and likewise only closes the cursor if it opens
> successfully?

I'd put the whole transaction code into a try-except:

try:
     # Start of transaction
     ... do stuff ...
except Exception:
     self.datasource.rollback()
     raise
else:
     self.datasource.commit()

This assures that a successful execution of your code results in
the transaction to be committed. In case something goes wrong,
the transaction is rolled back and the error reraised so that you
can process it at some higher level.

> I'd really appreciate your advice on this, I'm relatively new to the world
> of db implementation using the dbapi and want to make this as water tight as
> possible.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 04 2008)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2008-07-07: EuroPython 2008, Vilnius, Lithuania             2 days to go

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


    eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
     D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
            Registered at Amtsgericht Duesseldorf: HRB 46611