Working with Multiple Databases

This is a set of simple recipe examples to show how to use Elixir with multiple databases. Tested on SQLAlchemy 0.4 and Elixir 0.4, should work without modification on 0.5.

Single file/program

This example uses entity level setup of binding's between session, metadata and entities with the using_options call to ensure each table is created in it's respective database. Database a.db contains table a and database b.db contains table b. Queries are executed within each using the global Entity name (not shown).

from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.schema import ThreadLocalMetaData
from elixir import *

a_engine = create_engine('sqlite:////tmp/a.db', echo=True)
a_session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=a_engine))
a_metadata = metadata
a_metadata.bind = a_engine

b_engine = create_engine('sqlite:////tmp/b.db', echo=True)
b_session = application_session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=b_engine))
b_metadata = ThreadLocalMetaData() # or just MetaData() for staticengine's (see above posts)
b_metadata.bind = b_engine

class A(Entity):
    name = Field(Unicode(30))
    using_options(metadata=a_metadata, session=a_session,tablename="a")

class B(Entity):
    name = Field(Unicode(30))
    using_options(metadata=b_metadata, session=b_session,tablename="b")

create_all()
setup_all()

adata = A(name="a table data")
bdata = B(name="b table data")

a_session.commit()
b_session.commit()

Multiple files (module)

This version is differentiated by the module level binding's of session, metadata and entity's by setting the __metadata__ and __session__ variables.

a.py

from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from elixir import *

a_engine = create_engine('sqlite:////tmp/a.db', echo=True)
a_session = scoped_session(sessionmaker(autoflush=True, transactional=True))
a_metadata = metadata

__metadata__ = a_metadata
__session__ = a_session

class A(Entity):
    name = Field(Unicode(30))
    using_options(tablename="a") 

b.py

from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.schema import ThreadLocalMetaData
from elixir import *

b_engine = create_engine('sqlite:////tmp/b.db', echo=True)
b_session = scoped_session(sessionmaker(autoflush=True, transactional=True))
b_metadata = ThreadLocalMetaData() 

__metadata__ = b_metadata
__session__ = b_session

class B(Entity):
    name = Field(Unicode(30))
    using_options(tablename="b") 

This version uses "ThreadLocalMetaData" instead of the well known standard "MetaData", with this type it is possible to rebind multiple times per session. Further explanation of this can be found in the SQLAlchemy Docs.

__init__.py

from a import *
from b import *

b_metadata.bind = b_engine
b_session.bind = b_engine
a_metadata.bind = a_engine
a_session.bind = a_engine

setup_all()
create_all()

adata = A(name="a table data")
bdata = B(name="b table data")

a_session.commit()
b_session.commit()

This can be imported as a module.

Pylons Integration

Use the "multiple files" method above as a starting point. Remove the "create_engine" lines in the model files a.py and b.py.

project/model/__init__.py

from a import *
from b import *
setup_all()

development.ini

Just add as many sqlalchemy urls as neccessary.

sqlalchemy.a.url = sqlite:///%(here)s/databases/a.db
sqlalchemy.b.url = sqlite:///%(here)s/databases/b.db 

project/config/environment.py

Edit the global environment settings and set the following to setup your session's and metadata's when the application is started.

from sqlalchemy import engine_from_config
config['pylons.g'].engine_a = engine_from_config(config,'sqlalchemy.a.')
config['pylons.g'].engine_b = engine_from_config(config,'sqlalchemy.b.')

import project.model as model
model.a_session.bind = config['pylons.g'].engine_a
model.a_metadata.bind = config['pylons.g'].engine_a
model.b_session.bind = config['pylons.g'].engine_b
model.b_metadata.bind = config['pylons.g'].engine_b

project/lib/base.py

Edit the end of call method to cleanup with

   finally:
       model.a_session.remove()
       model.b_session.remove()