Context manager for Python's MySQLdb
Question: I am used to (spoiled by?) python's
[SQLite](http://docs.python.org/library/sqlite3.html) 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](http://stackoverflow.com/questions/1107297/looking-
for-a-more-pythonic-way-to-access-the-database), but it doesn't offer a
complete solution.


Answer: Note that MySQLdb connections are now context managers already. See
[user2966041's answer](http://stackoverflow.com/a/22840557/190597).

* * *

You could use something like this:

    
    
    import config
    import MySQLdb
    import MySQLdb.cursors as mc
    import _mysql_exceptions
    DictCursor = mc.DictCursor
    SSCursor = mc.SSCursor
    SSDictCursor = mc.SSDictCursor
    Cursor = mc.Cursor
    
    
    class Cursor(object):
        def __init__(self,
                     cursorclass=Cursor,
                     host=config.HOST, user=config.USER,
                     passwd=config.PASS, dbname=config.MYDB,
                     driver=MySQLdb,
                     ):
            self.cursorclass = cursorclass
            self.host = host
            self.user = user
            self.passwd = passwd
            self.dbname = dbname
            self.driver = driver
            self.connection = self.driver.connect(
                host=host, user=user, passwd=passwd, db=dbname,
                cursorclass=cursorclass)
            self.cursor = self.connection.cursor()
    
        def __iter__(self):
            for item in self.cursor:
                yield item
    
        def __enter__(self):
            return self.cursor
    
        def __exit__(self, ext_type, exc_value, traceback):
            self.cursor.close()
            if isinstance(exc_value, Exception):
                self.connection.rollback()
            else:
                self.connection.commit()
            self.connection.close()
    
    with Cursor() as cursor:
        print(cursor)
        connection = (cursor.connection)
        print(connection)
    

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

Note also that [oursql](http://packages.python.org/oursql/) is an alternative
driver for MySQL that comes with the `with` construct built in:

    
    
    with some_connection as cursor:
        do_something_with(cursor)
    

You might want to consider using oursql instead.

