How to setup multiple databases in TG2

[Mike Driscoll]

Hi,

I am having a little trouble wrapping my head around how to configure
my model's __init__.py file for multiple databases. I am working on an
application that accesses 3 databases and does reflection on select
tables from all three.

I assume I need to comment out this line:

metadata = DeclarativeBase.metadata

and instead create three metadata instances based on MetaData(),
correct?

Finally, as I understand it, I can use the "init_model" function to do
the mapping for some of them, but I am not understanding where to
create the engine object to pass to it. Also, once I have my tables
mapped in the function, what do I put in my real model file if
anything?

Hopefully that stuff makes sense. I am using Python 2.5 and TG2 in a
virtualenv on Windows XP.

Thanks!

Mike

[Mike Driscoll]

Hi,

According to Mark Ramm's blog, supporting multiple databases is
supposed to be easy (see
http://compoundthinking.com/blog/index.php/2008/07/31/10-reasons-why-the-new-turbogears-2-alpha-is-worth-a-look/).
So where is the documentation that says how to do it??

I "think" I need to put multiple sqlalchemy.url's in my
development.ini file. But where oh where do I set up the SA engines
at? The model's __init__ claims to support multiple databases too
using MetaData, but it doesn't show where to bind the engine unless
you're supposed to use the undocumented init_model function somehow.

I'm pretty sure the init_model came from Perkins. Maybe he can tell me
where to put my call to the function and how to set it up for multiple
databases?

That would be great!

Thanks,

Mike

[percious]

It's not too bad, but you have to do your own custom app setup, and
modify your init_model stuff to create multiple transaction manager.

app_cfg.py:

from webapp.model import init_model
from sqlalchemy import engine_from_config
from tg.configuration import AppConfig, Bunch
from pylons import config as pylons_config

class MyAppConfig(AppConfig):
def setup_sqlalchemy(self):
"""Setup SQLAlchemy database engine"""
users_engine = engine_from_config(pylons_config,
'sqlalchemy_users.')
samples_engine = engine_from_config(pylons_config,
'sqlalchemy_samples.')
config['pylons.app_globals'].sa_engine = users_engine
config['pylons.app_globals'].sa_users_engine = users_engine
config['pylons.app_globals'].sa_samples_engine =
samples_engine

# Pass the engine to initmodel, to be able to introspect
tables
init_model(users_engine, samples_engine)

base_config = MyAppConfig()
...

model/__init__.py:

import model.users
import model.samples

from model.users.mappers import *
from model.samples.mappers import *

from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy.orm import scoped_session, sessionmaker

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

DBSession = UsersDBSession = scoped_session(maker)
maker3 = sessionmaker(autoflush=True, autocommit=False,
extension=ZopeTransactionExtension())

SamplesDBSession = scoped_session(maker3)
from model.users.metadata import metadata as users_metadata
from model.samples.metadata import metadata as samples_metadata

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

global UsersDBSession, SamplesDBSession, users_metadata,
samples_metadata

UsersDBSession.configure(bind=users_engine)
SamplesDBSession.configure(bind=samples_engine)

users_metadata.bind = users_engine
samples_metadata.bind = samples_engine


This is sort of an off-the cuff answer, we will provide a better one
in the next documentation release.

Come find me on IRC if you need some more help.

cheers.
-chris

On Jun 4, 10:31 am, Mike Driscoll <kyoso...@gmail.com> wrote:
> Hi,
>

> According to Mark Ramm's blog, supporting multiple databases is

> supposed to be easy (seehttp://compoundthinking.com/blog/index.php/2008/07/31/10-reasons-why-...).

[Mark Ramm]

Sorry, yea, it's supported. And it should be documented, but it's not.

There are two places where you should look. The AppConfig object has
a setup_sqlalchemy method that does the actuall setup, the __init__ of
your model has the module level wiring, and the init_model function is
guaranteed to run after the model is setup, and the engines are bound.

It would be nice for us to present this to people in a little bit
easier package, but baring that we should definitely post a recipe for
it.

--
Mark Ramm-Christensen
email: mark at compoundthinking dot com
blog: www.compoundthinking.com/blog

[Mike Driscoll]

On Jun 5, 2:06 pm, percious <ch...@percious.com> wrote:
> It's not too bad, but you have to do your own custom app setup, and
> modify your init_model stuff to create multiple transaction manager.
>
> app_cfg.py:
>
> from webapp.model import init_model
> from sqlalchemy import engine_from_config
> from tg.configuration import AppConfig, Bunch
> from pylons import config as pylons_config
>
> class MyAppConfig(AppConfig):
>     def setup_sqlalchemy(self):
>         """Setup SQLAlchemy database engine"""
>         users_engine = engine_from_config(pylons_config,
> 'sqlalchemy_users.')
>         samples_engine = engine_from_config(pylons_config,
> 'sqlalchemy_samples.')
>         config['pylons.app_globals'].sa_engine = users_engine
>         config['pylons.app_globals'].sa_users_engine = users_engine
>         config['pylons.app_globals'].sa_samples_engine =
> samples_engine
>
>         # Pass the engine to initmodel, to be able to introspect
> tables
>         init_model(users_engine, samples_engine)
>
> base_config = MyAppConfig()
> ...

I had actually gotten this part figured out ...

>
> model/__init__.py:
>
> import model.users
> import model.samples
>
> from model.users.mappers import *
> from model.samples.mappers import *

I'm not sure what you're doing here. I haven't seen any examples where
there is a mappers variable in the model either in the sample template
or in the online docs.

Just in case there was some weird "magic" going on, I tried it but got
an "ImportError: No module named mappers"

>
> from zope.sqlalchemy import ZopeTransactionExtension
> from sqlalchemy.orm import scoped_session, sessionmaker
>
> # Global session manager.  DBSession() returns the session object
> # appropriate for the current web request.
> maker = sessionmaker(autoflush=True, autocommit=False,
>                      extension=ZopeTransactionExtension())
>
> DBSession = UsersDBSession = scoped_session(maker)
> maker3 = sessionmaker(autoflush=True, autocommit=False,
>                      extension=ZopeTransactionExtension())
>
> SamplesDBSession = scoped_session(maker3)
> from model.users.metadata import metadata as users_metadata
> from model.samples.metadata import metadata as samples_metadata

This has the same problem as the mappers variable above. Are you not
using the declarative format?

>
> def init_model(users_engine, samples_engine):
>     """Call me before using any of the tables or classes in the
> model."""
>
>     global UsersDBSession, SamplesDBSession, users_metadata,
> samples_metadata
>
>     UsersDBSession.configure(bind=users_engine)
>     SamplesDBSession.configure(bind=samples_engine)
>
>     users_metadata.bind = users_engine
>     samples_metadata.bind = samples_engine
>
> This is sort of an off-the cuff answer, we will provide a better one
> in the next documentation release.
>
> Come find me on IRC if you need some more help.
>
> cheers.
> -chris

At the moment, I don't have IRC working here. I think one of our
proxies blocks it or just drops the connection all the time. I'll see
if I can figure it out though.

Thanks for the help.

- Mike

[Mike Driscoll]

On Jun 5, 2:06 pm, percious <ch...@percious.com> wrote:

> It's not too bad, but you have to do your own custom app setup, and
> modify your init_model stuff to create multiple transaction manager.
>
> app_cfg.py:
>
> from webapp.model import init_model
> from sqlalchemy import engine_from_config
> from tg.configuration import AppConfig, Bunch
> from pylons import config as pylons_config
>
> class MyAppConfig(AppConfig):
>     def setup_sqlalchemy(self):
>         """Setup SQLAlchemy database engine"""
>         users_engine = engine_from_config(pylons_config,
> 'sqlalchemy_users.')
>         samples_engine = engine_from_config(pylons_config,
> 'sqlalchemy_samples.')
>         config['pylons.app_globals'].sa_engine = users_engine
>         config['pylons.app_globals'].sa_users_engine = users_engine
>         config['pylons.app_globals'].sa_samples_engine =
> samples_engine
>
>         # Pass the engine to initmodel, to be able to introspect
> tables
>         init_model(users_engine, samples_engine)
>
> base_config = MyAppConfig()
> ...

I had actually gotten this part figured out ...

>
> model/__init__.py:
>
> import model.users
> import model.samples
>
> from model.users.mappers import *
> from model.samples.mappers import *

I'm not sure what you're doing here. I haven't seen any examples where
there is a mappers variable in the model either in the sample template
or in the online docs.

Just in case there was some weird "magic" going on, I tried it but got
an "ImportError: No module named mappers"

>

> from zope.sqlalchemy import ZopeTransactionExtension
> from sqlalchemy.orm import scoped_session, sessionmaker
>
> # Global session manager.  DBSession() returns the session object
> # appropriate for the current web request.
> maker = sessionmaker(autoflush=True, autocommit=False,
>                      extension=ZopeTransactionExtension())
>
> DBSession = UsersDBSession = scoped_session(maker)
> maker3 = sessionmaker(autoflush=True, autocommit=False,
>                      extension=ZopeTransactionExtension())
>
> SamplesDBSession = scoped_session(maker3)
> from model.users.metadata import metadata as users_metadata
> from model.samples.metadata import metadata as samples_metadata

This has the same problem as the mappers variable above. Are you not
using the declarative format?


>

> def init_model(users_engine, samples_engine):
>     """Call me before using any of the tables or classes in the
> model."""
>
>     global UsersDBSession, SamplesDBSession, users_metadata,
> samples_metadata
>
>     UsersDBSession.configure(bind=users_engine)
>     SamplesDBSession.configure(bind=samples_engine)
>
>     users_metadata.bind = users_engine
>     samples_metadata.bind = samples_engine
>
> This is sort of an off-the cuff answer, we will provide a better one
> in the next documentation release.
>
> Come find me on IRC if you need some more help.
>
> cheers.
> -chris

At the moment, I don't have IRC working here. I think one of our
proxies blocks it or just drops the connection all the time. I'll see
if I can figure it out though.

Thanks for the help.

- Mike




>

[Mike Driscoll]

For the record, this was resolved. I didn't know how to set up the
second session object.

Thanks!

Mike

On Jun 5, 2:06 pm, percious <ch...@percious.com> wrote:

[Mike Driscoll]

By popular request, I'm sticking what I did on here:

I used the pylons docs to figure out part of this:
http://www.pylonshq.com/docs/en/0.9.7/models/#multiple-engines

Here are the files to change:

#--------------
# development.ini

# create 2 or more sqlalchemy urls

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

# -------------------------------------------


#--------------
# app_cfg.py (which is in the config folder)

# need to subclass AppConfig and
# override setup_sqlalchemy

from sqlalchemy import engine_from_config
from pyretention.model import init_model

class MyAppConfig(AppConfig):
def setup_sqlalchemy(self):
"""Setup SQLAlchemy database engine."""
engineOne = engine_from_config(pylons_config,
'sqlalchemy.main.')
engineTwo = engine_from_config(pylons_config,
'sqlalchemy.other.')
config['pylons.app_globals'].engineOne = engineOne
config['pylons.app_globals'].engineTwo = engineTwo

# Pass the engine to initmodel, to be able to introspect
tables

init_model(engineOne, engineTwo)

base_config = MyAppConfig()

# -------------------------------------------


#--------------
# model\__init__.py

# 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=engineTwo)

secondSession.configure(bind=engineOne)

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



Note that in websetup, there is a call to

model.metadata.create_all(bind=config['pylons.app_globals'].sa_engine)

So when you override AppConfig in app_cfg.py, you'll want to change
one of these lines or hack websetup.py and any other references to
sa_engine:

config['pylons.app_globals'].engineOne = engineOne
config['pylons.app_globals'].engineTwo = engineTwo

to

config['pylons.app_globals'].sa_engine = engineOne


Hope that helps others.

- Mike

[Mike Driscoll]

I got a question about my connecting the secondSession to the first
engine. I was just showing that you can connect the session to
whichever engine it needs to connect to.

Change that stuff as needed. If you're second engine should be hooked
into the second Session, then do that. As long as each session is
bound to a different engine, it should work.

- Mike

[Mike Driscoll]

Also note that the pylons_config module is imported like this:

from pylons import config as pylons_config

[Mike Driscoll]

Drat it all! I forgot this import too:

from tg.configuration import config

[Mike Driscoll]

For those who care, I wrote a more complete tutorial of how to do this
on my blog:

http://www.blog.pythonlibrary.org/?p=210

- Mike