Switching databases in TG2 during runtime

Question

I am doing an application which will use multiple sqlite3 databases, prepopuldated with data from an external application. Each database will have the exact same tables, but with different data.

I want to be able to switch between these databases according to user input. What is the most elegant way to do that in TurboGears 2?

Answer 1

If ALL databases have the same schema then you should be able to create several Sessions using the same model to the different DBs.

Answer 2

Dzhelil,

I wrote a blog post a while back about using multiple databases in TG2. You could combine this method with Jorge's suggestion of multiple DBSessions and I think you could do this easily.

How to use multiple databases in TurboGears 2.0

Hope this helps, Seth

Answer 3

I am using two databases for a read-only application. The second database is a cache in case the primary database is down. I use two objects to hold the connection, metadata and compatible Table instances. The top of the view function assigns db = primary or db = secondary and the rest is just queries against db.tableA.join(db.tableB). I am not using the ORM.

The schemata are not strictly identical. The primary database needs a schema. prefix (Table(...schema='schema')) and the cache database does not. To get around this, I create my table objects in a function that takes the schema name as an argument. By calling the function once for each database, I wind up with compatible prefixed and non-prefixed Table objects.

At least in Pylons, the SQLAlchemy meta.Session is a ScopedSession. The application's BaseController in appname/lib/base.py calls Session.remove() after each request. It's probably better to have a single Session that talks to both databases, but if you don't you may need to modify your BaseController to call .remove() on each Session.