MIME-Version: 1.0
Received: by 10.151.112.12 with SMTP id p12mr24096ybm.1.1244565346147; Tue, 09 
	Jun 2009 09:35:46 -0700 (PDT)
Date: Tue, 9 Jun 2009 09:35:46 -0700 (PDT)
In-Reply-To: <595cf3c6-279e-4400-a23b-f89c8c8cb77b@u10g2000vbd.googlegroups.com>
X-IP: 67.224.67.83
References: <baa9d441-6c6a-4442-a600-9ac2ec8b79d1@l32g2000vba.googlegroups.com> 
	<89201a2a-0269-4dc4-90dc-17308ea9f38b@r16g2000vbn.googlegroups.com> 
	<e439aa17-e2f1-4753-b5f1-f44c42dfd303@q14g2000vbn.googlegroups.com> 
	<78197b72-1eb9-41b7-bda4-3261e4fa24dc@x6g2000vbg.googlegroups.com> 
	<254e9267-f57a-421c-ae0b-b6629aeb1edf@d31g2000vbm.googlegroups.com> 
	<595cf3c6-279e-4400-a23b-f89c8c8cb77b@u10g2000vbd.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.10) 
	Gecko/2009042316 Firefox/3.0.10 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Message-ID: <2e45348a-df49-4457-a219-35293cd0d750@f10g2000vbf.googlegroups.com>
Subject: Re: How to setup multiple databases in TG2
From: Mike Driscoll <kyoso...@gmail.com>
To: TurboGears <turbogears@googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Drat it all! I forgot this import too:

from tg.configuration import config



On Jun 9, 11:30=A0am, Mike Driscoll <kyoso...@gmail.com> wrote:
> Also note that the pylons_config module is imported like this:
>
> from pylons import config as pylons_config
>
> On Jun 9, 10:52=A0am, Mike Driscoll <kyoso...@gmail.com> wrote:
>
> > 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.c=
om/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 =3D sqlite:///%(here)s/devdata.db
> > sqlalchemy.other.url =3D 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):
> > =A0 =A0 def setup_sqlalchemy(self):
> > =A0 =A0 =A0 =A0 """Setup SQLAlchemy database engine."""
> > =A0 =A0 =A0 =A0 engineOne =3D engine_from_config(pylons_config,
> > 'sqlalchemy.main.')
> > =A0 =A0 =A0 =A0 engineTwo =3D engine_from_config(pylons_config,
> > 'sqlalchemy.other.')
> > =A0 =A0 =A0 =A0 config['pylons.app_globals'].engineOne =A0=3D engineOne
> > =A0 =A0 =A0 =A0 config['pylons.app_globals'].engineTwo =A0=3D engineTwo
> > =A0 =A0 =A0 =A0 # Pass the engine to initmodel, to be able to introspec=
t
> > tables
> > =A0 =A0 =A0 =A0 init_model(engineOne, engineTwo)
>
> > base_config =3D MyAppConfig()
>
> > # -------------------------------------------
>
> > #--------------
> > # model\__init__.py
>
> > # Global session manager: DBSession() returns the Thread-local
> > # session object appropriate for the current web request.
> > maker =3D sessionmaker(autoflush=3DTrue, autocommit=3DFalse,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0extension=3DZopeTransactionE=
xtension())
> > DBSession =3D scoped_session(maker)
>
> > maker2 =3D sessionmaker(autoflush=3DTrue, autocommit=3DFalse,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 extension=3DZopeTransaction=
Extension())
> > secondSession =3D scoped_session(maker2)
>
> > def init_model(engineOne, engineTwo):
> > =A0 =A0 """Call me before using any of the tables or classes in the
> > model."""
>
> > =A0 =A0 DBSession.configure(bind=3DengineTwo)
>
> > =A0 =A0 secondSession.configure(bind=3DengineOne)
>
> > =A0 =A0 # you only need this metadata
> > =A0 =A0 #if you want to autoload a table
> > =A0 =A0 second_metadata =3D MetaData(engineOne)
> > # -------------------------------------------
>
> > Note that in websetup, there is a call to
>
> > model.metadata.create_all(bind=3Dconfig['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 =3D engineOne
> > config['pylons.app_globals'].engineTwo =3D engineTwo
>
> > to
>
> > config['pylons.app_globals'].sa_engine =3D engineOne
>
> > Hope that helps others.
>
> > - Mike
>
> > On Jun 9, 9:10=A0am, Mike Driscoll <kyoso...@gmail.com> wrote:
>
> > > 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=A0pm, 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):
> > > > =A0 =A0 def setup_sqlalchemy(self):
> > > > =A0 =A0 =A0 =A0 """Setup SQLAlchemy database engine"""
> > > > =A0 =A0 =A0 =A0 users_engine =3D engine_from_config(pylons_config,
> > > > 'sqlalchemy_users.')
> > > > =A0 =A0 =A0 =A0 samples_engine =3D engine_from_config(pylons_config=
,
> > > > 'sqlalchemy_samples.')
> > > > =A0 =A0 =A0 =A0 config['pylons.app_globals'].sa_engine =3D users_en=
gine
> > > > =A0 =A0 =A0 =A0 config['pylons.app_globals'].sa_users_engine =3D us=
ers_engine
> > > > =A0 =A0 =A0 =A0 config['pylons.app_globals'].sa_samples_engine =3D
> > > > samples_engine
>
> > > > =A0 =A0 =A0 =A0 # Pass the engine to initmodel, to be able to intro=
spect
> > > > tables
> > > > =A0 =A0 =A0 =A0 init_model(users_engine, samples_engine)
>
> > > > base_config =3D 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. =A0DBSession() returns the session object
> > > > # appropriate for the current web request.
> > > > maker =3D sessionmaker(autoflush=3DTrue, autocommit=3DFalse,
> > > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0extension=3DZopeTransact=
ionExtension())
>
> > > > DBSession =3D UsersDBSession =3D scoped_session(maker)
> > > > maker3 =3D sessionmaker(autoflush=3DTrue, autocommit=3DFalse,
> > > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0extension=3DZopeTransact=
ionExtension())
>
> > > > SamplesDBSession =3D 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):
> > > > =A0 =A0 """Call me before using any of the tables or classes in the
> > > > model."""
>
> > > > =A0 =A0 global UsersDBSession, SamplesDBSession, users_metadata,
> > > > samples_metadata
>
> > > > =A0 =A0 UsersDBSession.configure(bind=3Dusers_engine)
> > > > =A0 =A0 SamplesDBSession.configure(bind=3Dsamples_engine)
>
> > > > =A0 =A0 users_metadata.bind =3D users_engine
> > > > =A0 =A0 samples_metadata.bind =3D samples_engine
>
> > > > This is sort of an off-the cuff answer, we will provide a better on=
e
> > > > in the next documentation release.
>
> > > > Come find me on IRC if you need some more help.
>
> > > > cheers.
> > > > -chris
>
> > > > On Jun 4, 10:31=A0am, 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.ph=
p/2008/07/31/10-reasons-why-...).
> > > > > 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 engin=
es
> > > > > at? The model's __init__ claims to support multiple databases too
> > > > > using MetaData, but it doesn't show where to bind the engine unle=
ss
> > > > > you're supposed to use the undocumented init_model function someh=
ow.
>
> > > > > I'm pretty sure the init_model came from Perkins. Maybe he can te=
ll me
> > > > > where to put my call to the function and how to set it up for mul=
tiple
> > > > > databases?
>
> > > > > That would be great!
>
> > > > > Thanks,
>
> > > > > Mike
>
> > > > > On May 28, 12:47=A0pm, Mike Driscoll <kyoso...@gmail.com> wrote:
>
> > > > > > Hi,
>
> > > > > > I am having a little trouble wrapping my head around how to con=
figure
> > > > > > my model's __init__.py file for multiple databases. I am workin=
g on an
> > > > > > application that accesses 3 databases and does reflection on se=
lect
> > > > > > tables from all three.
>
> > > > > > I assume I need to comment out this line:
>
> > > > > > metadata =3D DeclarativeBase.metadata
>
> > > > > > and instead create three metadata instances based on MetaData()=
,
> > > > > > correct?
>
> > > > > > Finally, as I understand it, I can use the "init_model" functio=
n 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 ta=
bles
> > > > > > 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
>