SQLAlchemy/Python: Using an engine per process, and injecting that engine into dependencies

[Zach]

SQLAlchemy recommends using one engine per process because the underlying DBAPI connections are not necessarily safe to use across processes.

I have global engine and session variables that gets lazily populated with a SQLAlchemy engine and a session factory, respectively. This makes it easy to write database helpers that depend can call get_session() at runtime to use the session variable after it has been assigned a value by init_db.

That works well for letting helpers that need access to engine or session be imported before engine or session are initialized. But this pattern locks me into using one engine and sessionglobally.

However, what I really need to do is:

engine, session = init_db(DB_URL)

... from the calling application/method, and then inject the session into these database helpers. Why? Well, I need to use one engine per process. I think I need an inversion of control pattern here, but am having trouble coming up with something workable (that doesn't involve passing the session around to the helpers on each call, which is hacky and a last resort).

I thought I could just set up the engine after os.fork(). Each process would start with engine=None, then instantiate it. But engine has the same memory address in both the parent and child process. This makes sense; it's how Python works.

from utils import init_db, engine, DB_URL
import os

def connect():
    print('before fork, engine is: {}'.format(id(engine)))
    newpid = os.fork()

    if newpid == 0:
        init_db(DB_URL)
        print('child engine is: {}'.format(id(engine))) # id(engine) is equal to below call
    else:
        init_db(DB_URL)
        print('parent engine is: {}'.format(id(engine))) # id(engine) is equal to above call

if __name__ == '__main__':
    connect()

Is my best option to create a pool of sorts based on the process ID? I'd like to use a clean inversion of control pattern if possible.

NullPool doesn't pool connections and would make this work, but I don't think that's a good thing - it seems expensive to constantly open and close connections?

Python version: 3.4.0

SQLAlchemy version: 0.9.7

(I deleted a previous post with the same title because the code example was improperly indented.)

[Michael Bayer]

On Aug 17, 2014, at 3:50 PM, Zach <za...@ledgerx.com> wrote:

... from the calling application/method, and then inject the session into these database helpers. Why? Well, I need to use one engine per process. I think I need an inversion of control pattern here, but am having trouble coming up with something workable (that doesn't involve passing the session around to the helpers on each call, which is hacky and a last resort).

I thought I could just set up the engine after os.fork(). Each process would start with engine=None, then instantiate it. But engine has the same memory address in both the parent and child process. This makes sense; it's how Python works.

in your child process when it starts up, take the engine from your parent process, call dispose() on it.  that will wipe out any connections it’s holding onto.   Or just reassign it to a new create_engine().  Doesn’t need to be any more than that.