Table of Contents
The goal of this tutorial is to configure TurboGears to use multiple databases. In this tutorial we will simply set up two different databases engines that will use db session handlers of DBSession and DBSession2, db metadata names of metadata and metadata2, and DeclarativeBase objects of DeclarativeBase and DeclarativeBase2.
The first thing you will need to do is edit your .ini file to specify multiple url options for the sqlalchemy configuration.
In myapp/development.ini (or production.ini, or whatever.ini you are using), comment out the original sqlalchemy.url assignment and add the multiple config options:
#sqlalchemy.url = sqlite:///%(here)s/devdata.db sqlalchemy.first.url = sqlite:///%(here)s/database_1.db sqlalchemy.second.url = sqlite:///%(here)s/database_2.db
Now we need to instruct the app to load the multiple databases correctly. This requires telling base_config (in app_cfg.py) to load our own custom AppConfig with the proper multi-db assignments and a call to the model’s init_model method (more on that in the next step).
# make sure these imports are added to the top from tg.configuration import AppConfig, config from pylons import config as pylons_config from myapp.model import init_model # add this before base_config = class MultiDBAppConfig(AppConfig): def setup_sqlalchemy(self): """Setup SQLAlchemy database engine(s)""" from sqlalchemy import engine_from_config engine1 = engine_from_config(pylons_config, 'sqlalchemy.first.') engine2 = engine_from_config(pylons_config, 'sqlalchemy.second.') # engine1 should be assigned to sa_engine as well as your first engine's name config['pylons.app_globals'].sa_engine = engine1 config['pylons.app_globals'].sa_engine_first = engine1 config['pylons.app_globals'].sa_engine_second = engine2 # Pass the engines to init_model, to be able to introspect tables init_model(engine1, engine2) #base_config = AppConfig() base_config = MultiDBAppConfig()
Switching the model’s init from a single-db config to a multi-db simply means we have to duplicate our DBSession and metata assignments, and then update the init_model method to assign/configure each engine correctly.
# after the first maker/DBSession assignment, add a 2nd one maker2 = sessionmaker(autoflush=True, autocommit=False, extension=ZopeTransactionExtension()) DBSession2 = scoped_session(maker2) # after the first DeclarativeBase assignment, add a 2nd one DeclarativeBase2 = declarative_base() # uncomment the metadata2 line and assign it to DeclarativeBase2.metadata metadata2 = DeclarativeBase2.metadata # finally, modify the init_model method to allow both engines to be passed (see previous step) # and assign the sessions and metadata to each engine def init_model(engine1, engine2): """Call me before using any of the tables or classes in the model.""" # DBSession.configure(bind=engine) DBSession.configure(bind=engine1) DBSession2.configure(bind=engine2) metadata.bind = engine1 metadata2.bind = engine2
Now that the configuration has all been taken care of, you can instruct your models to inherit from either the first or second DeclarativeBase depending on which DB engine you want it to use.
For example, in myapp/model/spam.py (uses engine1):
from sqlalchemy import Table, ForeignKey, Column from sqlalchemy.types import Integer, Unicode, Boolean from myapp.model import DeclarativeBase class Spam(DeclarativeBase): __tablename__ = 'spam' self.id = id self.variety = variety id = Column(Integer, autoincrement=True, primary_key=True) variety = Column(Unicode(50), nullable=False)
And then in myapp/model/eggs.py (uses engine2):
from sqlalchemy import Table, ForeignKey, Column from sqlalchemy.types import Integer, Unicode, Boolean from myapp.model import DeclarativeBase2 class Eggs(DeclarativeBase2): __tablename__ = 'eggs' def __init__(self, id, pkg_qty): self.id = id self.pkg_qty = pkg_qty id = Column(Integer, autoincrement=True, primary_key=True) pkg_qty = Column(Integer, default=12)
If you needed to use the DBSession here (or in your controllers), you would use DBSession for the 1st engine and DBSession2 for the 2nd (see the previous and next sections).
If you want your setup_app method to populate each database with data, simply use the appropriate metadata/DBSession objects as you would in a single-db setup.
def setup_app(command, conf, vars): """Place any commands to setup myapp here""" load_environment(conf.global_conf, conf.local_conf) # Load the models from myapp import model print "Creating tables for engine1" model.metadata.create_all() print "Creating tables for engine2" model.metadata2.create_all() # populate spam table spam = [model.Spam(1, u'Classic'), model.Spam(2, u'Golden Honey Grail')] # DBSession is bound to the spam table model.DBSession.add_all(spam) # populate eggs table eggs = [model.Eggs(1, 12), model.Eggs(2, 6)] # DBSession2 is bound to the eggs table model.DBSession2.add_all(eggs) model.DBSession.flush() model.DBSession2.flush() transaction.commit() print "Successfully setup"
Difficulty: Hard. At some point, we should also find a way to document how to handle Horizontal and Vertical Partitioning properly, and document that in here, too.