What is the recommended way to configure sqlalchemy connection to view calls?

Question

I use the sqlalchemy expression language for its notation and connection pool to create Tao objects to communicate with the persistence level. I would like to get some opinions on how I should approach the metadata and engine settings so that they are accessible to calling applications. According to the sqlalchemy documentation http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html , they are usually connected and declared global, however I don’t have this, nor a singleton approach - good ideas. Any thoughts would be appreciated ...

This is what my __init__.py file looks in my project 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 combine SQLAlchemy transactions and session management with Pyramid, pyramid_tm and zope.sqlalchemy . They take care of your sessions together:

 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 scoped_session there, using the transaction extension to integrate with Pyramid.

Then in the views, all you have to do is use the DBSession factory session 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() 

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

Answer 2

I think the sqlalchemy dc examples declare them global for conciseness and do not indicate that they recommend it.

I think the only thing you really want to pass to other parts of your application is the Session object. A simpler option is to use a scoped session (which, it seems to me, resembles the O'Reilly sqlalchemy book, actually recommends simpler web applications; your code offers this web application). I think that there are very few applications for working with the engine or metadata anywhere, except when you create a database connection.

A session with an area will also be created when the engine and metadata are created when the application starts (in the case of the pyramid in the main function here ). You will then pass it as a parameter to the various parts of the application that need access to the database.