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.

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.

13 thoughts on “Integrating SQLAlchemy into a CherryPy application”

  1. 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, 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. 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. 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 . 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. 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. 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):
    self.value = message
    self.testTransient = True
    def __str__(self):
    return self.value.encode(‘utf-8’) + ‘, ‘ + self.testTransient.encode(‘utf-8’)

    1. 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. 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.