Bottle.py PyMySQL Plugin

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!

Changing template tags in Bottle.py SimpleTemplate

A web coding tip for a change: After a long coding hiatus, I decided to try my hand at recoding my web-based budget software with AngularJS on the client side and Bottle.py handling the backend. Superb and compact combination, by the way!

Bottle.py comes with a great minimalist templating engine called SimpleTemplate, which uses {{ var }} syntax for inline variables. This does not mix well with client side AngularJS which uses the exactly same delimiters. There is an easy way to change Angular’s syntax with $interpolateProvider, but guess what? Many AngularJS additions, like the datagrid component ui-grid (previously ng-grid) don’t respect this setting, and just plain break with custom delimiters. Not nice

Changing Bottle.py template tags

So, what if you want to change SimpleTemplate syntax? Turns out there is very little documentation for it, even if it’s a single line change. Just locate this line in bottle.py:

default_syntax = '<% %> % {{ }}'

…and change it to:

default_syntax = '<% %> % [[ ]]'

And that’s it! Actually, not quite, because bottle default error page template is hardcoded for curly braces. So locate ERROR_PAGE_TEMPLATE in the same file, and change every {{var}} to [[var]].

There is elegant code in bottle.py that seems to enable replacing the default syntax, but unfortunately the only tip for using it seemed somewhat complicated. So I opted for this simple hack. I’ll sure regret it when I next time update my bottle.py, but, well, that’s not in this month, right?