I’ve been playing around today with BottlePy, an excellent mini-framework for Python web development. However, getting Python MySQL support is always a hassle, I never know if MySQLdb has died or not, so I thought I’d try PyMySQL for a change.

In addition to being just generally awesome, Bottle has a nice plugin syntax, and even a great 60-line sample of a SQLite plugin. So I adapted it for PyMySQL, turns out this was quite easy:

import pymysql.cursors
import inspect

class PyMySQLPlugin(object):
    ''' This plugin passes a pymysql database handle to route callbacks
    that accept a `db` keyword argument. If a callback does not expect
    such a parameter, no connection is made. You can override the database
    settings on a per-route basis. '''

    name = 'pymysql'
    api = 2

    def __init__(self, db, user, password, host='localhost', charset='utf8mb4', keyword='db'):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset
        self.keyword = keyword

    def setup(self, app):
        ''' Make sure that other installed plugins do not affect the same
            keyword argument.'''
        for other in app.plugins:
            if not isinstance(other, PyMySQLPlugin): continue
            if other.keyword == self.keyword:
                raise PluginError("Found another %s plugin with "\
                "conflicting settings (non-unique keyword)." % self.name)

    def apply(self, callback, context):
        # Override global configuration with route-specific values.
        #conf = context.config.get('sqlite') or {}
        #dbfile = conf.get('dbfile', self.dbfile)

        # Test if the original callback accepts a 'db' keyword.
        # Ignore it if it does not need a database handle.
        args = inspect.getargspec(context.callback)[0]
        if self.keyword not in args:
            return callback

        def wrapper(*args, **kwargs):
            # Connect to the database
            db = pymysql.connect(host=self.host,
                    user=self.user,
                    password=self.password,
                    db=self.db,
                    charset=self.charset,
                    cursorclass=pymysql.cursors.DictCursor)

            # Add the connection handle as a keyword argument.
            kwargs[self.keyword] = db

            try:
                rv = callback(*args, **kwargs)
                #if autocommit: db.commit()
            #except sqlite3.IntegrityError, e:
                #db.rollback()
                #raise HTTPError(500, "Database Error", e)
            finally:
                db.close()
            return rv

        # Replace the route callback with the wrapped one.
        return wrapper

As you may see, there is no autocommit support and graceful recovery from database errors is something that is missing as well, but it’s a great start. There were a couple of GitHub projects with similar aims, but those seemed a bit behind the times (one was adding Python 3.4 support, and I’m already on 3.5, for example). A little NIH syndrome for me, maybe. Using the above library is quite easy:

from bottle import install, route
from somefile import PyMySQLPlugin

pymysql = PyMySQLPlugin(user='dbuser',
        password='dbpass', db='dbname')
install(pymysql)

@route('/demo')
def demo(db):
    # This method has a "db" parameter, and the plugin activates
    with db.cursor() as cursor:
        sql = "SELECT `id`, `password` FROM `users` WHERE `username`=%s"
        cursor.execute(sql, ('johndoe',))
        result = cursor.fetchone()
        return str(result)

Hope you found this useful!