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

[Michael Bayer]

On Nov 4, 2012, at 6:37 AM, Christoph Zwerschke wrote:

> Am 04.11.2012 01:11, schrieb Michael Bayer:
> > not to mention "There should be one-- and preferably
> > only one --obvious way to do it."
> 
> Which can easily be overtrumped by "simple is better than complex", "readability counts", "flat is better than nested", and "practicality beats purity" ;-)

IMHO the DBAPI is a low level API that should do just one thing, allow rudimental functionality to a relational database with as much consistency as possible.  When some DBAPIs decide to put an .execute() method on the Connection, others don't, and it then has hidden performance implications on some backends and not others, ambiguity and confusion is introduced into the API.   The usage of cursor() for databases that don't actually have the concept of a cursor, like SQLite, is serving a greater purpose in its consistency versus the potential disservice by providing for an extra step.  

There is a space for database APIs that are designed to follow the underlying behavior of the database, rather than a blanket specification - such an example is the APSW api for SQLite (http://code.google.com/p/apsw/), which explicitly disregards the DBAPI in favor of offering a variety of behavioral and performance enhancements specific to SQLite, which would not otherwise fit into the DBAPI.

The majority of database applications are actually using higher level abstraction systems on top of the DBAPI, such as the Django ORM, SQLAlchemy, Storm ORM, and others.   It's not the place for the DBAPI itself to present a rich set of programming choices, as these can, and should, be easily implemented as wrappers or abstraction systems around a compliant driver.

Basically I think the task of providing database programming patterns should be shipped separately from a DBAPI itself, so that DBAPI authors only need implement a minimal and clear set of behaviors that accomplish the task of basic database communication and nothing more.  Real world applications should not be using the DBAPI directly without some abstraction system, either home grown or separately shipped, in between.