[DB-SIG] Use of context managers with DB API 2

[M.-A. Lemburg]

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:
> 
> http://docs.python.org/2/library/sqlite3.html#using-shortcut-methods
> 
> 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
the removal:

 * 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
   to newcomers.

 * The short-cuts don't make the underlying cursors available for
   error handling.

 * 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".

-- 
Marc-Andre Lemburg
eGenix.com

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
               http://www.egenix.com/company/contact/