Is __enter__ and __exit__ behaviour for connection objects specified in the Python database API?

Question

Background

I recently discovered the Python with keyword and started seeing its potential usefulness for more prettily handling some scenarios where I'd previously have used try: ... finally: ... constructs. I immediately decided to try it out on the MySQLdb connection object in some code I was writing.

I didn't bother reading up on how __enter__ and __exit__ behave in implementors of the Python database API, and naively expected the behaviour to be like that of file objects - all I was expecting was for exit to call connection.close().

Imagine my confusion, then, at this behaviour:

>>> with util.get_db_connection() as conn:
...     print conn
... 
<MySQLdb.cursors.Cursor object at 0xb6ca8b4c>

get_db_connection() returns a MySQLdb connection object, but the __enter__ method of that connection object returns a cursor object, not the connection object itself like I was expecting given how __enter__ and __exit__ work for file objects. I guess I ought to be doing with util.get_db_connection() as cursor:, or else not using with at all.

Questions

Immediately this discovery makes me wonder a few things:

1. What else do the __enter__ and __exit__ methods of MySQLdb connection objects do? Is __exit__ going to magically commit or rollback changes for me without me explicitly asking for that to happen? Is there anything else non-obvious that I should know?
2. Is this behaviour the same in other implementers of the Python database API (like sqlite3, django, or psycopg2)?
3. Is this behaviour formally specced anywhere? ctrl-fing the latest spec (PEP 249 -- Python Database API Specification v2.0) for 'enter', 'exit' and 'context manager' doesn't throw up anything.

Answer 1

The Python DBAPI was written well before context managers were added to the Python language.

As such, different database libraries made their own decisions on how to implement context manager support (if they implemented it at all).

Usually using the database as a context manager ties you to a transaction. The transaction is started on __enter__, and committed or aborted on __exit__, depending on wether or not there was an exception. As such, you are supposed to use the MySQL connection as a context manager after connecting seperately:

connection = util.get_db_connection()

with connection as cursor:
    cursor.execute(...)

# connection commit is issued if no exceptions were raised.

The sqlite3 context manager implementation is subtly different; it also manages transactions, but does not return a cursor from the __enter__ method:

con = sqlite3.connect(":memory:")
with con:
    cursor = con.cursor()
    # or use the connection directly
    con.execute(...)

Technically, it just returns self on __enter__.