what is the recommended way of setting up an sqlalchemy connection for view callables?

Question

I am using the sqlalchemy expression language for its notation and connection pooling to create dao objects for communicating with the persistence layer. I wanted to get some opinions on how I should approach setting up the metadata and engine so that they are available to the applications view callables. According to sqlalchemy's documentation http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html, they are typically bound and declared global, however I've neither this or the singleton approach are good ideas. Any thoughts would be appreciated...

This is what my __init__.py file looks like inside my project's directory:

from pyramid.config import Configurator
from sqlalchemy import engine_from_config, MetaData, create_engine
from pyramid_beaker import session_factory_from_settings

db_url = 'postgresql://user:password@localhost/dbname'
engine = create_engine(db_url)
meta = MetaData()

def main(global_config, **settings):
    meta.bind = engine
    .
    .
    .
    [other configuration settings]

Answer 1

The Pyramid documentation includes a tutorial on integrating Pyramid with SQLAlchemy.

There are two special packages that integrate SQLAlchemy transactions and session management with Pyramid, pyramid_tm and zope.sqlalchemy. These together take care of your sessions:

from sqlalchemy import engine_from_config

from .models import DBSession

def main(global_config, **settings):
    """This function returns a Pyramid WSGI application."""

    engine = engine_from_config(settings, 'sqlalchemy.')
    DBSession.configure(bind=engine)
    # Configuration setup

Here we take the configuration settings from your .ini configuration file; and in models.py:

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    )

from zope.sqlalchemy import ZopeTransactionExtension

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

class YourModel(Base):
    # Define your model

Note the use of a scoped_session there, using the transaction extension to integrate with Pyramid.

Then in views, all you need to do is use the DBSession session factory to get your sessions:

from pyramid.view import view_config
from .models import (
    DBSession,
    YourModel,
    )

@view_config(...)
def aview(request):
    result = DBSession.query(YourModel).filter(...).first()

Committing and rolling back will be integrated with the request; commit on 2xx and 3xx, rollback on exceptions, for example.

Answer 2

I think the sqlalchemy doc examples declare them as global for succinctness and not to indicate that they recommend that.

I think the only thing you really want to pass around to the different parts of your application is a Session object. The simpler option there is to use a scoped session (which I seem to recall the O'Reilly sqlalchemy book in fact recommends for simpler web based applications; your code suggests it's a web app). I think there's very few applications for needing the engine or metadata in any location other than when you're instantiating the database connection.

The scoped session would also be created when the engine and metadata are created, upon app startup (in the case of pyramid, in the main function here). Then you'd pass it as a parameter to the various parts of your application that need database access.