SQLAlchemy And Werkzeug

Using SQLAlchemy with a Werkzeug application is pretty straight-forward. With SQLAlchemy 0.4 onwards there is a scoped session in the SQLAlchemy core which allows SQLAlchemy to look up the current active database session based on the current context. A context is loosely defined as the current thread which most of the time is the current request.

Werkzeug itself comes with a module called "werkzeug.local" which provides a thread local object which explicitly cleans up at the end of the request. Additionally it supports greenlets. With some more modifications you can sync up the current SQLAlchemy session with werkzeug locals.

The following method is not the easiest one but allows you to have multiple instances of the same application class in the same python interpreter.

Somewhere in your application code defined this, for example in an application utility file (app.util for example):

from sqlalchemy import MetaData
from sqlalchemy.orm import scoped_session, create_session
from werkzeug import Local, LocalManager

# create the local object
local = Local()
local_manager = LocalManager()

# now add a proxy for the aplication object we put on the local later
application = local('application')

# This piece of code now creates a new session class for us that
# does all the thread / greenlet / application engine lookup magic
session = scoped_session(lambda: create_session(application.database_engine,
                         autoflush=True, transactional=True),
                         local_manager.get_ident)

# the database metadata for all of our tables
metadata = MetaData()

The next thing we have to do is to create an engine in the application constructor and propagate the application to the local object every request and at the end of the request clean up properly. Additionally we provide a method to create all database tables.

from sqlalchemy import create_engine
from werkzeug import ClosingIterator

# here we import the local_manager and the metadata we defined
# in the piece of code from above.
from app.util import session, local, local_manager, metadata


class Application(object):

    def __init__(self, database_uri):
        self.database_engine = create_engine(database_uri, convert_unicode=True)

    def __call__(self, environ, start_response):
        # set the current application so that the session factory
        # callback can access the database engine.
        local.application = self

        # here comes the request dispatching code that returns
        # a response object.
        response = dispatch(...)

        # and at the end of the request we clean up properly.
        return ClosingIterator(response(environ, start_response),
                               [local_manager.cleanup, session.remove])

    def init_database(self):
        """The bonus: create all tables."""
        # the metadata create all function does not use any ORM
        # stuff so we can savely ignore local.application here.
        metadata.create_all(self.database_engine)

Finally we have to define some database models. Important is that your database models are all imported before the WSGI application is initialized. If not the metadata won't know about all models and forget to create all required tables or relations will point to nowhere.

How to define Tables and Models is explained in the SQLAlchemy ORM Tutorial. Just don't forget that you already have a metadata defined in your utils module and that the engine is discovered by default. If you want to play in the Python Interpreter with it, don't forget to assign an instance of the application to the local.application attribute.

Working With Models

As soon as you have created some models you should be able to query them with Model.query like mentioned in the SQLAlchemy documentation. The example code above creates a session that automatically saves models created in a transactional context. To send the changes to the database you have to call session.commit().