Integrating SQLAlchemy into a CherryPy application

Quite often, people come on the CherryPy IRC channel asking about the way to use SQLAlchemy with CherryPy. There are a couple of good recipes on the tools wiki but I find them a little complex to begin with. Not to the recipes’ fault, many people don’t necessarily know about CherryPy tools and plugins at that stage.

The following recipe will try to make the example complete whilst as simple as possible to allow folks to start up with SQLAlchemy and CherryPy.

# -*- coding: utf-8 -*-
import os, os.path
 
import cherrypy
from cherrypy.process import wspbus, plugins
 
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy.types import String, Integer
 
# Helper to map and register a Python class a db table
Base = declarative_base()
 
class Message(Base):
    __tablename__ = 'message'
    id = Column(Integer, primary_key=True)
    value =  Column(String)
 
    def __init__(self, message):
        Base.__init__(self)
        self.value = message
 
    def __str__(self):
        return self.value.encode('utf-8')
 
    def __unicode__(self):
        return self.value
 
    @staticmethod
    def list(session):
        return session.query(Message).all()
 
 
class SAEnginePlugin(plugins.SimplePlugin):
    def __init__(self, bus):
        """
        The plugin is registered to the CherryPy engine and therefore
        is part of the bus (the engine *is* a bus) registery.
 
        We use this plugin to create the SA engine. At the same time,
        when the plugin starts we create the tables into the database
        using the mapped class of the global metadata.
 
        Finally we create a new 'bind' channel that the SA tool
        will use to map a session to the SA engine at request time.
        """
        plugins.SimplePlugin.__init__(self, bus)
        self.sa_engine = None
        self.bus.subscribe("bind", self.bind)
 
    def start(self):
        db_path = os.path.abspath(os.path.join(os.curdir, 'my.db'))
        self.sa_engine = create_engine('sqlite:///%s' % db_path, echo=True)
        Base.metadata.create_all(self.sa_engine)
 
    def stop(self):
        if self.sa_engine:
            self.sa_engine.dispose()
            self.sa_engine = None
 
    def bind(self, session):
        session.configure(bind=self.sa_engine)
 
class SATool(cherrypy.Tool):
    def __init__(self):
        """
        The SA tool is responsible for associating a SA session
        to the SA engine and attaching it to the current request.
        Since we are running in a multithreaded application,
        we use the scoped_session that will create a session
        on a per thread basis so that you don't worry about
        concurrency on the session object itself.
 
        This tools binds a session to the engine each time
        a requests starts and commits/rollbacks whenever
        the request terminates.
        """
        cherrypy.Tool.__init__(self, 'on_start_resource',
                               self.bind_session,
                               priority=20)
 
        self.session = scoped_session(sessionmaker(autoflush=True,
                                                  autocommit=False))
 
    def _setup(self):
        cherrypy.Tool._setup(self)
        cherrypy.request.hooks.attach('on_end_resource',
                                      self.commit_transaction,
                                      priority=80)
 
    def bind_session(self):
        cherrypy.engine.publish('bind', self.session)
        cherrypy.request.db = self.session
 
    def commit_transaction(self):
        cherrypy.request.db = None
        try:
            self.session.commit()
        except:
            self.session.rollback()  
            raise
        finally:
            self.session.remove()
 
 
 
 
class Root(object):
    @cherrypy.expose
    def index(self):
        # print all the recorded messages so far
        msgs = [str(msg) for msg in Message.list(cherrypy.request.db)]
        cherrypy.response.headers['content-type'] = 'text/plain'
        return "Here are your list of messages: %s" % '\n'.join(msgs)
 
    @cherrypy.expose
    def record(self, msg):
        # go to /record?msg=hello world to record a "hello world" message
        m = Message(msg)
        cherrypy.request.db.add(m)
        cherrypy.response.headers['content-type'] = 'text/plain'
        return "Recorded: %s" % m
 
if __name__ == '__main__':
    SAEnginePlugin(cherrypy.engine).subscribe()
    cherrypy.tools.db = SATool()
    cherrypy.tree.mount(Root(), '/', {'/': {'tools.db.on': True}})
    cherrypy.engine.start()
    cherrypy.engine.block()

The general idea is to use the plugin mechanism to register functions on an engine basis and enable a tool that will provide an access to the SQLAlchemy session at request time.

11 thoughts on “Integrating SQLAlchemy into a CherryPy application

  1. mike bayer

    A few comments, as I am frequently hearing about SQLAlchemy “complexity”, and I think that may be due to examples that feature a lot more API interaction than is needed:

    There’s no need to call flush() then commit(). commit() implies flush() and SQLAlchemy also flushes on an as needed basis. Under normal usage with autoflush there’s almost never any need to call flush() explicitly.

    There’s also no need to call expunge_all() in the try/except/finally block. The remove() inside the finally removes the Session entirely and any objects still strongly referenced elsewhere (which in itself is unlikely here) are deassociated with it.

    The call to clear_mappers() is strange here. Application classes that are designed to function as mapped classes are useless if their mappings have been removed. That the declaration and mapping of classes doesn’t occur in start() implies that nothing related to un-mapping classes should be happening in stop(). clear_mappers() is very specific to test suites that are testing SQLAlchemy functions and there’s really no reason I can think of for most applications to be using it.

    The “threadlocal” strategy in create_engine() is absolutely not necessary here, that is an ancient feature from version 0.2, which has a very specific use case today (which is described in http://www.sqlalchemy.org/docs/dbengine.html#using-the-threadlocal-execution-strategy), and it absolutely does not apply in the general case and especially not with the ORM.

    The convert_unicode flag is not necessary with SQLite as pysqlite handles Python unicode strings natively, and its generally better to declare Unicode columns explicitly.

  2. Sylvain Hellegouarch Post author

    Thank you very much for all those clarifications Mike. I have reflected them into the code. I guess the notion of complexity comes from the documentation which has all the bits and pieces but, in my opinion, not in a natural flow. There always seems to have more than way of doing things but the documentation never takes an authoritative stance on which is the standard one. One other aspect of the documentation that often confuses me is the fact examples aren’t complete (most don’t have their related import statements), making them out of context and hard to read.

    Once you’re comfortable with SA, it’s not much more complex than any other piece of software that size. But the learning curve is steep in my book.

  3. mike bayer

    Sylvain -

    I really think you should check out the modern SQLAlchemy documentation – they are nothing like the docs of 0.3 or 0.4, which are several years out of date at this point, and even since 0.5 they continue to get much better. I can only assume your criticisms above are based largely on the old docs of 0.3 or 0.4, back when someone might have been led to believe they needed the “threadlocal” strategy.

    The current ORM tutorial works really well for those who go through it and it certainly does lay out exactly what one needs to do things, it methodically tells the whole story of how to go from a URI to a full database interaction, including all the basic things (and not so basic things) one might want to do. When you read the mapping and session documentation later on, all of the examples in that section assume a similar context as the one explicitly laid out in the tutorial. We do try to name imports when discussing a concept that hasn’t been introduced before (this is a work in progress) though many examples in the mapper docs assume an in-progress application, and the imports would mislead the user to believe the example is “complete”.

    There are also lots of examples in the examples/ folder, which are all fully working applications. We keep them up to date with the latest APIs and make sure they all work (this was not always the case). We also include a description of all of these in the docs now at http://www.sqlalchemy.org/docs/examples.html . There are also many more examples on the Wiki which is actively maintained.

    SQLAlchemy has taken a very long time to create and I’m sorry that the first several iterations of documentation (as well as the whole usage paradigm of SQLAlchemy) were still finding their way (the current docs have a long way to go as well for sure, but we don’t have nearly the confusion we used to about things for new users). As an open source project we are very publicly laid out for all to see including all the bumps along our history, and I appreciate your sticking around with the project.

    1. Sylvain Hellegouarch Post author

      Problem is that you named your demo module cherrypy. When Python tries to import CherryPy itself, it’ll look into that demo module of yours rather than in its sys.path. Change the name of your module and it’ll work ;)

  4. Grant

    Thank you so much for posting this. I successfully ran the example as is. The recipe works great with persistent states, but I really really needed was transient states too. For example, I modified you model and added a transient attribute (self.testTrans). Its data kept in memory, follows the instance around, but never gets committed to a database. When I try to access the attribute in Root.index, python gives AttributeError.

    Can you recommend what I need to read to enable transient state? Or perhaps I am approaching this all wrong. Thanks!

    class Message(Base):
    __tablename__ = ‘message’
    id = Column(Integer, primary_key=True)
    value = Column(String)

    def __init__(self, message):
    Base.__init__(self)
    self.value = message
    self.testTransient = True
    def __str__(self):
    return self.value.encode(‘utf-8′) + ‘, ‘ + self.testTransient.encode(‘utf-8′)

    1. Grant

      nm i figured it out. Enabling normal cherrypy.session and sending the sqla object through it automagically worked! Thanks again for the post! Could not have made it without your recipe.

  5. Balage

    Thank You for the post! It was life saving for me… I’ve modified it to make a new engine.connection(), not an orm session, and its working fine.

    Thank again!

Comments are closed.