Personal tools

Contact Us 24/7 > 1 866.SIX FEET

Skip to content. | Skip to navigation

Home > Blog > Pyramid Request Methods & DBSession


Pyramid Request Methods & DBSession

written by david on Thursday August 29, 2013
Comments | Filed under: , ,

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?

Posted by Sascha Gottfried on May 07, 2015 02:54 AM
Have you discussed this observation in the pyramid/sqlalchemy/python community? Any links are welcome!
Add comment

You can add a comment by filling out the form below. Plain text formatting.


Next Steps

Select a type of support:

Contact our sales team

First name:
Last name:
Phone Number:
Fight spam:
What is + ?
Call Us 1 866.SIX FEET