Pylons: Sharing SQLAlchemy MySQL connection with external library

Question

I am running Pylons using SQLAlchemy to connect to MySQL, so when I want to use a database connection in a controller, I can do this:

from myapp.model.meta import Session

class SomeController(BaseController):
    def index(self):
        conn = Session.connection()
        rows = conn.execute('SELECT whatever')
...

Say my controller needs to call up an external library, that also needs a database connection, and I want to provide the connection for it from the SQLAlchemy MySQL connection that is already established:

from myapp.model.meta import Session

import mymodule

class SomeController(BaseController):
    def index(self):
        conn = Session.connection()
        myobject = mymodule.someobject(DATABASE_OBJECT)
        ...
        conn.close()

What should DATABSE_OBJECT be? Possibilities:

1. Pass Session -- and then open and close Session.connection() in the module code
2. Pass conn, and then call conn.close() in the controller
3. Just pass the connection parameters, and have the module code set up its own connection

There is another wrinkle, which is that I need to instantiate some objects in app_globals.py, and these objects need a database connection as well. It seems that app_globals.py cannot use Session's SQLAlchemy connection yet -- it's not bound yet.

Is my architecture fundamentally unsounds? Should I not be trying to share connections between Pylons and external libraries this way? Thanks!

Answer 1

You should not manage connections yourself - it's all done by SQLAlchemy. Just use scoped session object everywhere, and you will be fine.

def init_model(engine):
    sm = orm.sessionmaker(autoflush=False, autocommit=False, expire_on_commit=False, bind=engine)
    meta.engine = engine
    meta.Session = orm.scoped_session(sm)

def index(self):
    rows = Session.execute('SELECT ...')

You can pass Session object to your external library and do queries there as you wish. There is no need to call .close() on it.

Regarding app_globals, I solved that by adding other method in globals class which is called after db initialization from environment.py

class Globals(...):
    def init_model(self, config):
        self.some_persistent_db_object = Session.execute('...')

def load_environment(...):
    ...
    config['pylons.app_globals'].init_model(config)
    return config

Answer 2

What should DATABSE_OBJECT be? Possibilities:

4. pass a "proxy" or "helper" object with higher level of abstraction interface

Unless the external library really needs direct access to SQLAlchemy session, you could provide it with object that has methods like "get_account(account_no)" instead of "execute(sql)". Doing so would keep SQLAlchemy-specific code more isolated, and the code would be also easier to test.

Sorry that this is not so much an answer to your original question, more a design suggestion.