SQLAlchemy best practices: when / how to configure a scoped_session?

Question

I am trying to figure out the right approach to use SQLAlchemy scoped sessions the "right way" while keeping the logic of defining a session separate from configuration and separate from using the session. I have been told a number of times a good aproeach would be to have some global scoped_session factory where I can use everywhere:

"""myapp/db.py
"""

from sqlalchemy.orm import sessionmaker, scoped_session

Session = scoped_session(sessionmaker())

Then when I want to use it:

"""myapp/service/dosomething.py
"""

from myapp.db import Session

def do_something(data): 
    """Do something with data
    """
    session = Session()
    bars = session.query(Bar).all()
    for bar in bars:
        bar.data = data
    session.commit()

This seems right, but my problem is that in all examples I have seen, sessionmaker would also set some parameters of the session, namely and most importantly bind an engine. This makes no sense to me, as the actual DB engine will be created from configuration not known at the global scope during the import of the myapp.db module.

What I have looked at doing is to set everything up in my app's "main" (or in a thread's main function), and then just assume that the session is configured in other places (such as when used by do_something() above):

"""myapp/main.py
"""

from sqlalchemy import create_engine
from myapp.db import Session
from myapp.service.dosomething import do_something

def main(): 
    config = load_config_from_file()
    engine = create_engine(**config['db'])
    Session.configure(bind=engine)

    do_something(['foo', 'bar'])

Does this seem like a correct approach? I have not found any good examples of such a flow yet most other examples I find seem either over-simplified or framework specific.

Answer 1

This is old and I've never accepted any of the answers below, but flowing @univerio's comment and 3+ years of continued usage in SQLAlchemy in various projects, my selected approach now is to keep doing exactly what I suggested in the OP:

1. Create a myapp.db module which defines Session = ScopedSession(sessionmaker())
2. Import from myapp.db import Session everywhere it is needed
3. In my app's main or in the relevant initialization code, do:

def main(): 
     config = load_config_from_file()
     engine = create_engine(**config['db'])
     Session.configure(bind=engine)

     do_something(['foo', 'bar'])

I've used this pattern successfully in Web apps, command line tools and long-running backend processes, and never had to change it so far. Ot is simple, reusable and works great, and I'd recommend it to anyone stumbling here because they've asked themselves the same question I did 3 years ago.

Answer 2

What you can do is to separate the config out into a separate module:

"""myapp/cfg.py
"""
config = load_config_from_file()

Then you can import this file wherever you need, including in the db module, so you can construct the engine as well as the session:

"""myapp/db.py
"""
from .cfg import config
engine = create_engine(**config['db'])
Session = scoped_session(sessionmaker(bind=engine))