How to work with multiple databases with Python Pyramid

Question

I want to work with multiple databases with Python Pyramid Framework and SQL Alchemy.

I have 1 database with user information, and multiple databases (with the same structure) where the application information is stored. Each user at login time selects a database and is only shown information from that database (not others).

How should I structure my code?

I was thinking on saving in the session the dbname and on every request check user permission on the selected database and generate a new db session. So my view would look like (PSEUDO CODE):

@view_config(route_name='home', renderer='json')

def my_view_ajax(request):

    try:

        database = int(request.GET['database'])

        # check user permissions from user database

        engine = create_engine('postgresql://XXX:XXX@localhost/'+database)

        DBSession.configure(bind=engine)

        items = DBSession.query('table').all()

    except DBAPIError:

        return 'error'

    return items

Should I generate a new db session with the user information on each request? Or is there a better way?

Thanks

Creating a new connection on every single request might be quite costly, although it depends on the type of API you're writing - if it fires off a process which takes a few minutes to finish then it would be negligible I guess. I've asked a similar question a while ago: http://stackoverflow.com/questions/13372001/multi-tenancy-with-sqlalchemy - and ended up using a single database with multiple namespaces, one per user account. — Sergey, Dec 10 '15 at 01:20

Iain Duncan · Answer 1 · 2015-12-13T05:38:35.097

This is quite easy to do in Pyramid+SQLAlchemy, but you'll likely want to switch to a heavier boilerplate, more manual session management style, and you'll want to be up on the session management docs for SQLA 'cause you can easily trip up when working with multiple concurrent sessions. Also, things like connection management should stay out of views, and be in components that live in the server start up lifecycle and are shared across request threads. If you're doing it right in Pyramid, your views should be pretty small and you should have lots of components that work together through the ZCA (the registry).

In my apps, I have a db factory objects that get sessions when asked for them, and I instantiate these objects in the server start up code (the stuff in __ init __.py) and save them on the registry. Then you can attach sessions for each db to your request object with the reify decorator, and also attach a house keeping end of request cleanup method to close them. This can be done either with custom request factories or with the methods for attaching to the request right from init, I personally wind up using the custom factories as I find it easier to read and I usually end up adding more there.

# our DBFactory component, from some model package
class DBFactory(object):

    def __init__(self, db_url, **kwargs):
        db_echo = kwargs.get('db_echo', False)
        self.engine = create_engine(db_url, echo=db_echo)

        self.DBSession = sessionmaker(autoflush=False)
        self.DBSession.configure(bind=self.engine)
        self.metadata = Base.metadata
        self.metadata.bind = self.engine

    def get_session(self):
        session = self.DBSession()
        return session


# we instantiate them in the __init__.py file, and save on registry
def main(global_config, **settings):
    """runs on server start, returns a Pyramid WSGI application  """

    config = Configurator(
        settings=settings,
        # ask for a custom request factory
        request_factory = MyRequest,
    )

    config.registry.db1_factory = DBFactory( db_url=settings['db_1_url'] )
    config.registry.db2_factory = DBFactory( db_url=settings['db_2_url'] )


# and our custom request class, probably in another file
class MyRequest(Request):
    "override the pyramid request object to add explicit db session handling"

    @reify
    def db1_session(self):
        "returns the db_session at start of request lifecycle"
        # register callback to close the session automatically after
        # everything else in request lifecycle is done
        self.add_finished_callback( self.close_dbs_1 )
        return self.registry.db1_factory.get_session()

    @reify
    def db2_session(self):
        self.add_finished_callback( self.close_dbs_2 )
        return self.registry.db2_factory.get_session()

    def close_dbs_1(self, request):
        request.db1_session.close()

    def close_dbs_2(self, request):
        request.db2_session.close()


# now view code can be very simple    
def my_view(request):
    # get from db 1
    stuff = request.db1_session.query(Stuff).all()
    other_stuff = request.db2_session.query(OtherStuff).all()
    # the above sessions will be closed at end of request when 
    # pyramid calls your close methods on the Request Factory

    return Response("all done, no need to manually close sessions here!")

How to work with multiple databases with Python Pyramid

1 Answers1