1

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?

D R
  • 21,936
  • 38
  • 112
  • 149
  • Almost any other design is better than this. Why can't you load a single database from multiple sources? – S.Lott Jul 07 '09 at 18:05
  • Or attach the external databases to your current one. Which causes them to behave like they are in the same connection, just in a different namespace. – Christopher Jul 07 '09 at 18:38
  • In order to load everything into one database, I would need to modify the schema, and I cannot do that as other applications depend on that schema. The ATTACH suggestion sounds interesting, but I can't think of a way to make it work in my case. I am relying on sqlalchemy to build my model declaratively, and each model class is associated with a tablename. To attach identical databases I will need to prepend each tablename with an identifier, which will break sqlalchemy. – D R Jul 11 '09 at 20:28

3 Answers3

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.

Jorge Vargas
  • 6,712
  • 7
  • 32
  • 29
1

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

Seth
  • 1,550
  • 3
  • 16
  • 20
1

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.

joeforker
  • 40,459
  • 37
  • 151
  • 246