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

[Michael Bayer]

On Nov 1, 2012, at 10:01 AM, Christoph Zwerschke wrote:

> We're currently preparing a new release of PyGreSQL and want to make use of context managers. My obvious idea was to let connections and cursors act as context managers that just close themselves, and add an extra context manager in form of a "transaction" property on the connection object that can be used to wrap transactions.
> 
> However, I then noticed that PySqlite, cx_Oracle, mx_odbc and pyodbc use connection as context managers differently, they do not close the connection on exit, but execute a rollback or commit instead.
> 
> Though I felt a separate context manager for wrapping transactions would have been a better solution, I now think I should better follow the above examples and wrap transactions in the context manager of the connection, it looks like it already has become a quasi standard. Any opinions on that or reasons why it has been implemented this way?

IMO most real-world applications don't open and close raw database connections multiple times within their stream of execution, as the app will certainly need to talk to the database again, and the re-engagement of a new connection each time, including the messaging that occurs, is unnecessarily wasteful.   The connection is usually stored in some global way or in a connection pool.    The one exception to this would be an application that builds upon a transparent pooling service like PGBouncer.

Whereas the transactional state on the connection is really the "resource" that is transitory - the snapshots and locks held for that transaction - that need to be cleanly closed surrounding many distinct operations as the application proceeds.