Using Multiple Databases in TurboGears 2

Last week, I embarked on an adventure into the world of web application programming. Since my work place uses Python as much as possible and my boss likes TurboGears, I chose it for this endeavor. I have worked through various TurboGears tutorials and thought it looked pretty cool. However, it doesn't take long to discover that there is a lot of undocumented functionality. In this case, I needed a web application that could access multiple databases. I knew SqlAlchemy could do it and since TG2 uses SqlAlchemy, I figured it would too. In this article you will get to travel down the rabbit hole with me as I explain how I figured it out.

When you go looking for help, the first article Google is likely to return is Mark Ramm's blog post about how easy it is to do. However, Ramm does not actually explain how to do it. Another fellow on the TurboGears Google Group posted a link to the Pylons way of setting up multiple databases. If you go there, you'll find out that the first step is set up multiple SqlAlchemy URLs in your config file, which in the case of TurboGears is your development.ini file. All you need to do is add one or more SqlAlchemy URLs to the [app:main] section.

So, instead of this:


sqlalchemy.url = sqlite:///%(here)s/devdata.db


You would do something like this:


sqlalchemy.first.url=mssql://user:password@ntsql.servername.com/database
sqlalchemy.second.url=sqlite:///%(here)s/devdata.db


Notice that you add to the dot notation to make the URLs unique. I think the "sqlalchemy" and "url" parts are required at the beginning and the end, but the rest can be whatever you want. If that was all you needed to do, this would indeed be an easy setup. However, we have a couple more files to modify. The next part was pretty tricky. I discovered that there's some comments on multiple databases in the model folder's __init__.py file. It claims that you need to create a new MetaData object, which is true, but the commented out example is misleading. In the example, the metadata is not bound to an engine object. Without that info, the metadata object will be basically useless. I then noticed the init_model method which is there for reflection purposes. Upon further digging, I found that you needed to modify it to pass in the engines needed for your various databases, This is where I ended up putting my new metadata object. Technically, you could create it before the method and then just make it global, but I didn't need that in my test case. Change yours as needed. Following are the changes I needed to make:

# Global session manager: DBSession() returns the Thread-local
# session object appropriate for the current web request.
maker = sessionmaker(autoflush=True, autocommit=False,
                     extension=ZopeTransactionExtension())
DBSession = scoped_session(maker)

maker2 = sessionmaker(autoflush=True, autocommit=False,
                      extension=ZopeTransactionExtension())
secondSession = scoped_session(maker2)

def init_model(engineOne, engineTwo):
    """Call me before using any of the tables or classes in the
    model."""

    DBSession.configure(bind=engineOne)

    secondSession.configure(bind=engineTwo)

    # you only need this metadata
    # if you want to autoload a table
    second_metadata = MetaData(engineTwo)

The last file that should be edited is the "app_cfg.py" located in the config folder. Here you need to subclass the "AppConfig" object to override the "setup_sqlalchemy" method. This is required only if you have modified your init_model method (above) to accept multiple engines. If you do not do this, you'll receive a lovely traceback about your method needing additional parameters. Here's my code:

from pylons import config as pylons_config
from tg.configuration import config 

class MyAppConfig(AppConfig):
    def setup_sqlalchemy(self):
        """Setup SQLAlchemy database engine."""
        from sqlalchemy import engine_from_config
        engineOne = engine_from_config(pylons_config, 'sqlalchemy.first.')
        engineTwo = engine_from_config(pylons_config, 'sqlalchemy.second.')
        config['pylons.app_globals'].engineOne = engineOne
        config['pylons.app_globals'].sa_engine = engineTwo
        # Pass the engine to initmodel, to be able to introspect tables
        init_model(engineOne, engineTwo)

base_config = MyAppConfig() 

It should be noted here that the default websetup.py file is hardwired to use a variable called "sa_engine" when you're using authentication. Thus, I assign one of the engines to that variable above. You could also go into websetup.py and just edit it as needed to accept whatever customizations are required. I do not know if there are additional files that need to be modified as this I ended up just changing the variable name above rather than mess with additional issues like this one.

Once all that is done, you should be good to go. On the TurboGears IRC, one of the members there mentioned a way to use multiple databases by "calling setup twice". He didn't explain how this worked, so I don't know if it is simpler, but if you want to investigate this method and report back, that's fine by me. I went ahead and created some dummy files with my changes so you can see then in context. They are downloadable below:

Sample files as zip or tar