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