Blog

Pyramid's ability to add additional methods to the request object provides some interesting possibilities for maintaining state in your application. In the cookbook, there is an entry for attaching a user attribute to the request.

Using the SQLAlchemy DBSession Attribute

In a recent project, we took this a step further and added a db_session attribute. For this project, we only had a need for a single database connection. This idea, however, could be extended to a db_sessions mapping, so that you could have access to connections to read-only databases and the write master (similar to Flask's binds). We wanted this class to be overridable at run-time, so we also made use of Pyramid's utilities for resolving a path to a Python object:

from pyramid.path import DottedNameResolver

from sqlalchemy.orm import scoped_session, sessionmaker

from zope.sqlalchemy import ZopeTransactionExtension


DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

def get_db_session(request, settings=None):
    if settings is None:
        settings = request.registry.settings
    session_path = settings.get('bowab.db_session', DBSession)
    resolver = DottedNameResolver()
    db_session = resolver.maybe_resolve(session_path)
    return db_session

Now that the request has this db_session attribute, it can be used anywhere in your view code. And since you're not relying on global state, it is easy to mock out this single attribute in your test suite and provide a stripped down API.

Alchemy Scaffold Considersations

While working on this project, we realized that the alchemy scaffold generates boilerplate that is a bit of an anti-pattern. Specifically, each time you create a new SQLAlchemy-based Pyramid project, this scaffold will create new DBSession classes. This is problematic, as these independent DBSession classes will not be able to make use of SQLAlchemy's excellent "unit of work" philosophy. Even if you configure these different packages to use the same database connection information, SQLAlchemy still considers them completely distinct sessions. This is particularly noticeable if use PostgreSQL. You will be able to see a distinct process created for each of these DBSession classes when they are used to interact with the database.

Due to this, we highly recommend using a utility package that houses your base DBSession (and any other common database code). Then any future Pyramid project that will be using SQLAlchemy can just depend on this utility package and import its DBSession.

Want to learn more about Pyramid?

• Learn about the Python Web Framework Pyramid and why we prefer it.