Basic Usage

You can get basic application template to use with SQLAlchemy by using alchemy scaffold. Check the narrative docs for more information.

Alternatively, you can try to follow wiki tutorial or blogr tutorial.

Using a Non-Global Session

It's sometimes advantageous to not use SQLAlchemy's thread-scoped sessions (such as when you need to use Pyramid in an asynchronous system). Thankfully, doing so is easy. You can store a session factory in the application's registry, and have the session factory called as a side effect of asking the request object for an attribute. The session object will then have a lifetime matching that of the request.

We are going to use Configurator.add_request_method to add SQLAlchemy session to request object and Request.add_finished_callback to close said session.


Configurator.add_request_method has been available since Pyramid 1.4. You can use Configurator.set_request_property for Pyramid 1.3.

We'll assume you have an .ini file with sqlalchemy. settings that specify your database properly:

 1# __init__.py
 3from pyramid.config import Configurator
 4from sqlalchemy import engine_from_config
 5from sqlalchemy.orm import sessionmaker
 7def db(request):
 8    maker = request.registry.dbmaker
 9    session = maker()
11    def cleanup(request):
12        if request.exception is not None:
13            session.rollback()
14        else:
15            session.commit()
16        session.close()
17    request.add_finished_callback(cleanup)
19    return session
22def main(global_config, **settings):
23    config = Configurator(settings=settings)
24    engine = engine_from_config(settings, prefix='sqlalchemy.')
25    config.registry.dbmaker = sessionmaker(bind=engine)
26    config.add_request_method(db, reify=True)
28    # .. rest of configuration ...

The SQLAlchemy session is now available in view code as request.db or config.registry.dbmaker().

Importing all SQLAlchemy Models

If you've created a Pyramid project using a paster template, your SQLAlchemy models will, by default, reside in a single file. This is just by convention. If you'd rather have a directory for SQLAlchemy models rather than a file, you can of course create a Python package full of model modules, replacing the models.py file with a models directory which is a Python package (a directory with an __init__.py in it), as per Modifying Package Structure. However, due to the behavior of SQLAlchemy's "declarative" configuration mode, all modules which hold active SQLAlchemy models need to be imported before those models can successfully be used. So, if you use model classes with a declarative base, you need to figure out a way to get all your model modules imported to be able to use them in your application.

You might first create a models directory, replacing the models.py file, and within it a file named models/__init__.py. At that point, you can add a submodule named models/mymodel.py that holds a single MyModel model class. The models/__init__.py will define the declarative base class and the global DBSession object, which each model submodule (like models/mymodel.py) will need to import. Then all you need is to add imports of each submodule within models/__init__.py.

However, when you add models package submodule import statements to models/__init__.py, this will lead to a circular import dependency. The models/__init__.py module imports mymodel and models/mymodel.py imports the models package. When you next try to start your application, it will fail with an import error due to this circular dependency.

Pylons 1 solves this by creating a models/meta.py module, in which the DBSession and declarative base objects are created. The models/__init__.py file and each submodule of models imports DBSession and declarative_base from it. Whenever you create a .py file in the models package, you're expected to add an import for it to models/__init__.py. The main program imports the models package, which has the side effect of ensuring that all model classes have been imported. You can do this too, it works fine.

However, you can alternately use config.scan() for its side effects. Using config.scan() allows you to avoid a circdep between models/__init__.py and models/themodel.py without creating a special models/meta.py.

For example, if you do this in myapp/models/__init__.py:

 1from sqlalchemy.ext.declarative import declarative_base
 2from sqlalchemy.orm import scoped_session, sessionmaker
 4DBSession = scoped_session(sessionmaker())
 5Base = declarative_base()
 7def initialize_sql(engine):
 8    DBSession.configure(bind=engine)
 9    Base.metadata.bind = engine
10    Base.metadata.create_all(engine)

And this in myapp/models/mymodel.py:

 1from myapp.models import Base
 2from sqlalchemy import Column
 3from sqlalchemy import Unicode
 4from sqlalchemy import Integer
 6class MyModel(Base):
 7    __tablename__ = 'models'
 8    id = Column(Integer, primary_key=True)
 9    name = Column(Unicode(255), unique=True)
10    value = Column(Integer)
12    def __init__(self, name, value):
13        self.name = name
14        self.value = value

And this in myapp/__init__.py:

 1from sqlalchemy import engine_from_config
 3from myapp.models import initialize_sql
 5def main(global_config, **settings):
 6    """ This function returns a Pyramid WSGI application.
 7    """
 8    config = Configurator(settings=settings)
 9    config.scan('myapp.models') # the "important" line
10    engine = engine_from_config(settings, 'sqlalchemy.')
11    initialize_sql(engine)
12    # other statements here
13    config.add_handler('main', '/{action}',
14                     'myapp.handlers:MyHandler')
15    return config.make_wsgi_app()

The important line above is config.scan('myapp.models'). config.scan has a side effect of performing a recursive import of the package name it is given. This side effect ensures that each file in myapp.models is imported without requiring that you import each "by hand" within models/__init__.py. It won't import any models that live outside the myapp.models package, however.

Writing Tests For Pyramid + SQLAlchemy