Q:

Context manager for Python's MySQLdb

I am used to (spoiled by?) python's SQLite interface to deal with SQL databases.  One nice feature in python's SQLite's API  the "context manager," i.e., python's with statement.  I usually execute queries in the following way:
import as sqlite

with sqlite.connect(db_filename) as conn:
    query = "INSERT OR IGNORE INTO shapes VALUES (?,?);"
    results = conn.execute(query, ("ID1","triangle"))

With the code above, if my query modifies the database and I forget to run conn.commit(),the context manager runs it for me automatically upon exiting the with statement. It also handles exceptions nicely: if an exception occurs before I commit anything, then the database is rolled back.
I am now using the MySQLdb interface, which doesn't seem to support a similar context manager out of the box.  How do I create my own?  There is a related question here, but it doesn't offer a complete solution.

A:

Previously, MySQLdb connections were context managers.
As of this commit on 2018-12-04, however, MySQLdb connections are no longer context managers,
and users must explicitly call conn.commit() or conn.rollback(), or write their own context manager, such as the one below.

You could use something like this:
import config
import MySQLdb
import MySQLdb.cursors as mc
import _mysql_exceptions
import contextlib
DictCursor = mc.DictCursor
SSCursor = mc.SSCursor
SSDictCursor = mc.SSDictCursor
Cursor = mc.Cursor

@contextlib.contextmanager
def connection(cursorclass=Cursor,
               host=config.HOST, user=config.USER,
               passwd=config.PASS, dbname=config.MYDB,
               driver=MySQLdb):
    connection = driver.connect(
            host=host, user=user, passwd=passwd, db=dbname,
            cursorclass=cursorclass)
    try:
        yield connection
    except Exception:
        connection.rollback()
        raise
    else:
        connection.commit()
    finally:
        connection.close()

@contextlib.contextmanager
def cursor(cursorclass=Cursor, host=config.HOST, user=config.USER,
           passwd=config.PASS, dbname=config.MYDB):
    with connection(cursorclass, host, user, passwd, dbname) as conn:
        cursor = conn.cursor()
        try:
            yield cursor
        finally:
            cursor.close()

with cursor(SSDictCursor) as cur:
    print(cur)
    connection = cur.connection
    print(connection)
    sql = 'select * from table'
    cur.execute(sql)
    for row in cur:
        print(row)

To use it you would place config.py in your PYTHONPATH and define the HOST, USER, PASS, MYDB variables there.

A:

Think things have changed since this question was originally asked. Somewhat confusingly (from my point of view at least), for recent versions of MySQLdb, if you use a connection in a context you get a cursor (as per the oursql example), not something that closes automatically (as you would if you opened a file for instance).
Here's what I do:
from contextlib import closing
with closing(getConnection()) as conn: #ensure that the connection is closed
    with conn as cursor:               #cursor will now auto-commit
        cursor.execute('SELECT * FROM tablename')

