James Henstridge wrote: > On Mon, Jul 18, 2011 at 3:52 PM, M.-A. Lemburg <m...@egenix.com> wrote: >> Vernon Cole wrote: >>> A cursor and/or a >>> connection should probably be context managers, so that they will work in a >>> "with" statement. >> >> This is was discussed before and it should probably go into >> the DB API in some form. >> >> I'm no particular fan of hiding transactions in context >> managers, though. In practice this often causes problems, since >> you usually want to apply error handling logic in case of >> problems, other than simply issuing a .rollback(). > > For most of the applications I work with, the transaction handling has > been delegated to function decorators, or have it hidden in the > framework (e.g. Django's TransactionMiddleware). If there are clean > up tasks that need to happen on transaction commit or roll back (e.g. > deleting a file on a failed transaction), then using a global > transaction manager like Zope's transaction module seems to be a good > fit. > > For transaction retry or error reporting, I haven't seen much benefit > in leaving the transaction in an "open by broken" state over cleaning > up with a rollback.
In more detail: When letting a context manager do a .rollback() on a connection in case of an error, the error still propagates up the call chain. However, since the transaction was rolled back, access to cursors on the connection and the connection itself will no longer work, so your error reporting and cleanup possibilities are left with just the error object. Likewise, when having the context manager do an implicit .commit() when leaving the block, you are giving away control and have to add extra code in case you want to prevent the changes from being committed, e.g. in a debug situation. Things can get really interesting in multi-threaded applications, if you're sharing connections between threads - but it's better to avoid that anyway, so I won't go into detail. Most of these issues can be resolved by coding explicit context managers that deal with your particular case, e.g. with TransactionalContext(connection): ... Overall, I think using a transaction manager that explicitly controls the .commit() and .rollback() calls is a lot cleaner and the direct "with connection:" construct causes more problems than it solves in more complex applications. On the plus side, in simple applications it can help the user to not forget the .commit() call (the .rollback() is implicit at garbage collection time anyway). So in conclusion, I think we should add it, but with a warning to the user that it's often better to write your own context manager. >> Another problem is that the connections used in a with >> statement will usually have already started a transaction >> before entering the context, so the .rollback() would >> remove more than just the things added in the with >> context. > > None of the databases I've worked with fit the Python DB API's > "implicit begin" behaviour for transactions, so their adapters have > all needed to run a simple state machine to determine when it is > necessary to start a transaction. So it would be pretty easy for them > to raise an error if an attempt was made to use the context manager > when a transaction was already in progress. The implicit start of transactions originates from the ODBC standard which is used by quite a few database as native API, so the above cannot easily be generalized. There's also a difference between starting a transaction and actually making changes on that transaction. An error should only be raised in case changes were made, but that would require a lot tedious internal state keeping by the database package, unless the backend provides an API to query whether the transaction actually contains any changes. I don't think there's much we can do about this particular aspect of using connections as context managers. > If all adapters that support transactions function like that, then I > think it would be sensible to require them to raise an error in that > case. If they don't raise an error, you know that someone somewhere > is going to write code like the following: > > with transaction: > # do stuff > with transaction: > # do more stuff > > ... and wonder why things break. Indeed. >> There's also the problem of intuitive use: some users will >> likely expect the connection to also be closed when leaving >> the context - much like what happens with files. > > If that is a concern, you could require a method call to get the > context manager rather than making the connection itself a context > manager. For example: > > with connection.begin_transaction(): > ... True, but as discussed above, what would that method do ? It could do an implicit rollback, but this would then likely cause other problems such as clearing out changes the user actually wants to commit later on. The safe way I know is opening a new connection: with db.connect(...) as connection: ... -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Jul 20 2011) >>> Python/Zope Consulting and Support ... http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/ ________________________________________________________________________ ::: Try our new mxODBC.Connect Python Database Interface 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 http://www.egenix.com/company/contact/ _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig