Sqlalchemy layout with WSGI application

Question

I'm working on writing a small WSGI application using Bottle and SqlAlchemy and am confused on how the "layout" of my application should be in terms of SqlAlchemy.

My confusion is with creating engines and sessions. My understanding is that I should only create one engine with the 'create_engine' method. Should I be creating an engine instance in the global namespace in some sort of singleton pattern and creating sessions based off of it? How have you done this in your projects?

Any insight would be appreciated. The examples in the documentation dont seem to make this entirely clear (unless I'm missing something obvious). Any thoughts?

Answer 1

What you need to achieve is well described in the pylons documentation: Defining Tables and ORM classes:

The model consists of two files: __init__.py and meta.py. __init__.py contains your table definitions and ORM classes, and an init_model() function which must be called at application startup. meta.py is merely a container for SQLAlchemy’s housekeeping objects (Session, metadata, and engine), which not all applications will use.

The example of the __init__.py is shown in the link, whereas the meta.py looks similar to this:

from sqlalchemy import MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
__all__ = ['Session', 'engine', 'metadata']
engine = None
Session = scoped_session(sessionmaker())
metadata = MetaData()

You can consider this module a singleton implementation if you like, since it will do the job (of loading and having one instance in more Pythonic) for you when you first load the module.

Answer 2

You don't have to create an engine manually. For web applications, it's best to use a scoped session which is effectively a threadlocal, used during a single request.

from sqlalchemy import MetaData
from sqlalchemy.orm import scoped_session, sessionmaker

session = scoped_session(sessionmaker())
metadata = MetaData('sqlite://') # or whatever: creates the engine for you

The engine will be available as metadata.bind. You don't need to bind a session to an engine - it's optional, see here.