Sqlalchemy schema with WSGI application

Question

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

My confusion is to create engines and sessions. I understand that I have to create only one engine using the create_engine method. Should I create an instance of the engine in the global namespace as a singleton template and create sessions based on it? How did you do this in your projects?

Any insight would be appreciated. The examples in the documentation do not seem to make this completely clear (unless I miss something obvious). Any thoughts?

Answer 1

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

The model consists of two files: __init__.py and meta.py. __init__.py contains table definitions and ORM classes, as well as the init_model () function, which must be called when the application starts. meta.py is just a container for SQLAlchemys household objects (session, metadata, and engine) that not all applications will use.

An example of __init__.py shown in the link, while meta.py looks something like 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 as a singleton implementation if you want, because it will do the job (load and have one instance in more Pythonic) for you when you first load the module.

Answer 2

You do not need to create an engine manually. For web applications, it is best to use a restricted session, which is effectively 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 do not need to bind the session to the engine - this is optional, see here .