[DB-SIG] Use of context managers with DB API 2
mal at egenix.com
Sun Nov 4 00:07:20 CET 2012
On 03.11.2012 23:50, Christoph Zwerschke wrote:
> Am 03.11.2012 20:56, schrieb M.-A. Lemburg:
>> That said, using connections as context managers in the described
>> way is popular and probably already a standard practice, so I
>> guess it's better to standardize on it and document it properly,
>> rather than leaving it open for interpretation - even if just to
>> settle on one interpretation.
> Yep. The fact that PySqlite is now part of the standard library has already created a de facto
> standard, and most other database modules seem to follow it.
>>> Or, when using the shortcut methods:
>>> with dbapi2.connect(...) as con:
>>> with con.transaction:
>>> con.execute("insert into debit(amount) values (-100)")
>>> con.execute("insert into credit(amount) values (100)")
>>> with con.transaction:
>>> con.execute("insert into debit(amount) values (-200)")
>>> con.execute("insert into credit(amount) values (200)")
>> The "shortcut" methods you describe were explicitly removed when
>> moving from DB-API 1.0 to 2.0, so it's not a good idea to reintroduce
>> them :-)
> But PySqlite has already reintrocuced them. They are now also part of the standard library:
> Is there anything bad about these shortcut methods?
I don't remember the details (the discussion is somewhere in the
archives). From today's perspective, here are some reasons for
* DB-API 1.0 had all cursors methods/attributes duplicated
on the connections to allow writing database modules
for backends which don't support cursors. Today, the cursor
concept is widely accepted, so non-support is a non-issue.
* Having those methods on connection objects blurs the distinction
between connections and cursors, making them harder to explain
* The short-cuts don't make the underlying cursors available for
* Creating new cursors for each .execute() is inefficient, since
setting up and tearing down cursors can be time consuming
(e.g. if cursors are mirrored on the server side).
* Optimizations such as reusing prepared statements is not
(easily) possible using such short-cuts, unless the database
module works with cursor pools.
* The shortcut methods only work for non result-set generating
SQL statements, since there's no way to fetch the result sets
without access to the cursors.
... and probably a few more :-)
Overall, it boils down to "explicit is better than implicit".
Professional Python Services directly from the Source (#1, Nov 03 2012)
>>> Python Projects, Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope/Plone.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
More information about the DB-SIG