[DB-SIG] Exeception Handling.
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?
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 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.
Many thanks,
Heston James.
___
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
Re: [DB-SIG] Exeception Handling.
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
___
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
Re: [DB-SIG] Exeception Handling.
> 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.
Hi Marc-Andre,
Thank you for your advice, that's really very helpful. I'd like to extend
this question a little if you don't mind. Let's say for instance that the
persistence of an object isn't just in the database but also on the file
system. And for instance this is done like so:
# Open the file object in write binary mode.
message = open("/files/%s/%s" % (self.message_id, self.name),
"wb")
# Write the binary to the file.
message.write(base64.decodestring(self.binary_data))
# Close the file object.
message.close()
Now, within my object let's say that I have a method such as save() which I
need to save the object to the database (using the code we've already
discussed) and also run that file save code to write it to the file system.
How would you transaction ALL of this? So if either the file save or the
database persist fails then it rolls them both back? So I don't end up with
a file without a DB entry or a DB entry without a file?
I'd be interested to know A) how would you arrange a try/except block to
handle this, and B) would you put this method in the bean or abstract it out
into a service layer and have two methods in the bean, one for the file save
and one for the database save and wrap those in a transaction at a higher
level?
Many thanks
Heston
___
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
Re: [DB-SIG] Exeception Handling.
On 2008-07-04 14:03, Heston James - Cold Beans wrote:
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.
Hi Marc-Andre,
Thank you for your advice, that's really very helpful. I'd like to extend
this question a little if you don't mind. Let's say for instance that the
persistence of an object isn't just in the database but also on the file
system. And for instance this is done like so:
# Open the file object in write binary mode.
message = open("/files/%s/%s" % (self.message_id, self.name),
"wb")
# Write the binary to the file.
message.write(base64.decodestring(self.binary_data))
# Close the file object.
message.close()
Now, within my object let's say that I have a method such as save() which I
need to save the object to the database (using the code we've already
discussed) and also run that file save code to write it to the file system.
How would you transaction ALL of this? So if either the file save or the
database persist fails then it rolls them both back? So I don't end up with
a file without a DB entry or a DB entry without a file?
If you want to do this right, you need a transaction manager and
then have to use two-phase commits to commit the work on all
resources. Depending on the needs and requirements, this can be
anything from hard to undoable (not all resources support two-phase
commit).
I'd be interested to know A) how would you arrange a try/except block to
handle this, and B) would you put this method in the bean or abstract it out
into a service layer and have two methods in the bean, one for the file save
and one for the database save and wrap those in a transaction at a higher
level?
I usually write manager classes for such things which buffer the output
and only write to the file system if .commit() is called. A .rollback()
would just clear the buffers.
If done right, you can also add two-phase commit logic to such a manager
class.
BTW: The DB-API has recently received an update which defines a
two-phase extension for those backends which support this. Have
a look at PEP 249 near the end of the page.
--
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
___
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
Re: [DB-SIG] Exeception Handling.
On 2008-07-04 14:22, M.-A. Lemburg wrote:
On 2008-07-04 14:03, Heston James - Cold Beans wrote:
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.
Hi Marc-Andre,
Thank you for your advice, that's really very helpful. I'd like to extend
this question a little if you don't mind. Let's say for instance that the
persistence of an object isn't just in the database but also on the file
system. And for instance this is done like so:
# Open the file object in write binary mode.
message = open("/files/%s/%s" % (self.message_id, self.name),
"wb")
# Write the binary to the file.
message.write(base64.decodestring(self.binary_data))
# Close the file object.
message.close()
Now, within my object let's say that I have a method such as save()
which I
need to save the object to the database (using the code we've already
discussed) and also run that file save code to write it to the file
system.
How would you transaction ALL of this? So if either the file save or the
database persist fails then it rolls them both back? So I don't end up
with
a file without a DB entry or a DB entry without a file?
If you want to do this right, you need a transaction manager and
then have to use two-phase commits to commit the work on all
resources. Depending on the needs and requirements, this can be
anything from hard to undoable (not all resources support two-phase
commit).
I'd be interested to know A) how would you arrange a try/except block to
handle this, and B) would you put this method in the bean or abstract
it out
into a service layer and have two methods in the bean, one for the
file save
and one for the database save and wrap those in a transaction at a higher
level?
I usually write manager classes for such things which buffer the output
and only write to the file system if .commit() is called. A .rollback()
would just clear the buffers.
If done right, you can also add two-phase commit logic to such a manager
class.
BTW: The DB-API has recently received an update which defines a
two-phase extension for those backends which support this. Have
a look at PEP 249 near the end of the page.
FWIW: I'll be giving a talk on large-scale application design at
EuroPython 2008 next week which touches some of these design
principles.
Here's an older version:
http://www.egenix.com/library/presentations/#DevelopingLargeScaleApplicationsInPython
--
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
___
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
