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!
