Multiple databases
Nick
databases within my application, i'm wondering if anyone has advice on
the best way to go about it.
I have a main "accounts" database, this has a table containing
accounts, users, groups etc along with secondary database connection
details. People will be authenticated and authorised in this database
and then using the db connection details stored in this db i want to
be able to connect my elixir Session to these secondary databases -
these are simple db's all the same, but i need one per "account".
I've looked at the Multiple engines section of the "Sqlalchemy 0.4 for
people in a hurry" (pylons docs) which gives me an indication of what
i need to do, however it describes two methods, the first is to use a
static bind of database to tables. I don't think this will work for me
as far i can tell because it needs calling before sessionmaker (in
model/__init__.py) and my session needs to be modified to point to a
different engine after this point.
The other method says i can rebind my Session object on a per request
basis, but i will also need to update the metadata bind - do i need to
call bind on the metadata at the same time and i assume i will need to
call setup_all again as the classes and table mappings won't be known
until i do.
Has anyone done this already done this with Elixir and Pylons, and if
they haven't what do they think is the right way to do it? Should i
have multiple Sessions/metadata's somehow (is this possible?) or
should i "switch" do the secondary db?
Thanks
Nick
Gaetan de Menten
If the tables are the same in all db, you can (IMHO should) use the
same metada. There is no problem in using several metadata, or several
sessions, but I don't think that's going to help you for the secondary
databases. I think the best way in your case is to have two metadata:
one for the "accounts" and the other for the children database (one
for them all), then switch that metadata'sbind (and/or the session's
bind -- if you bind at the session level) to the correct engine on a
per request basis. Now, I don't know Pylons so I can't tell you where
to do that in a Pylons app.
To switch the metadata's bind in a request, just reassign a new engine
to the bind attribute.
Hope this helps,
--
Gaëtan de Menten
http://openhex.org
Nick
On 22 Nov, 16:19, "Gaetan de Menten" <gdemen...@gmail.com> wrote:
info. However, I'm not sure though that i understand the distinction
of binding at the session level. What i'm thinking at the moment is
importing my Session's and metadata's at the global level -
AccountSession = elixir.session
AccountMetadata = elixir.metadata
AppSession = elixir.session
AppMetadata = elixir.metadata
Then calling the following function to connect to the correct database
once i've retrieved it -
def database_connect_dynamic(session, metadata, dburl, **kwargs):
import sqlalchemy
import elixir
engine = sqlalchemy.create_engine(dburl, **kwargs)
session.bind = engine
metadata.bind = engine
from myapp.application import *
elixir.setup_all()
What i'm not sure about is if the elixir.setup_all() will work
correctly, i thought that calling this will update global mappings/
metadata (i think!?). So i suppose i'm wondering how to "connect" my
accounts model with one session+metadata pair and my secondary model
with another session+metadata pair, If thats a correct way to do it?
Thanks again
Gaetan de Menten
You've probably figured it out by yourself by now but well... let's
answer this anyway...
> AccountSession = elixir.session
> AccountMetadata = elixir.metadata
> AppSession = elixir.session
> AppMetadata = elixir.metadata
This won't work, as you'd expect: it'll use the same session and
metadata for both the the account stuff and the "app" stuff..
You should rather create a new session & metadata:
from sqlalchemy.orm import sessionmaker
AccountSession = elixir.session
AccountMetadata = elixir.metadata
AppSession = sessionmaker(bind=engine, autoflush=True,
transactional=True) # or whatever options you want.
AppMetadata = ThreadLocalMetaData() # I think you'd be better of with
this kind of metadata
# see http://www.sqlalchemy.org/docs/04/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_ThreadLocalMetaData
> Then calling the following function to connect to the correct database
> once i've retrieved it -
>
> def database_connect_dynamic(session, metadata, dburl, **kwargs):
> import sqlalchemy
> import elixir
> engine = sqlalchemy.create_engine(dburl, **kwargs)
> session.bind = engine
> metadata.bind = engine
> from myapp.application import *
> elixir.setup_all()
>
> What i'm not sure about is if the elixir.setup_all() will work
> correctly,
No. It should only be called once. It does indeed populate the
metadata, and it also create mappers, but you can reuse the same
metadata for each of your databases. Except from that, it seems ok.
> i thought that calling this will update global mappings/
> metadata (i think!?). So i suppose i'm wondering how to "connect" my
> accounts model with one session+metadata pair and my secondary model
> with another session+metadata pair, If thats a correct way to do it?
--
Nick
still not having any luck with getting the two binding's done
correctly. It seems that elixir is using a global binding whatever i
do because after running create_all i get all my tables created in one
database - do i need to do something with __metadata__ and
__session__ - it wasn't quite clear from the docs if i should be
using these to re-assign my metadata and session.
I'm pretty beaten on this task, i don't have a strong enough grasp of
the division between elixir and sa (or elixir itself) to determine if
what i'm doing is correct and i've tried many combinations of options
now, all of which resulted in complete failure or the above problem of
tables all being associated with only one db. So if i may i'm going to
use an example and ask very very nicely if you can show me how to do
it (i think others will almost certainly be interested in this -
please please!!).
For a really simple attempt, i've cut out everything, including
pylons, so say you had an "AClass" class/entity destined for a_engine
and a "Bclass" destined for storage in b_engine, why doesn't this work
-
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
class Aclass(Entity):
name = Unicode(30)
using_options(metadata=a_metadata, tablename="a")
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()
class Bclass(Entity):
name = Unicode(30)
using_options(metadata=b_metadata, tablename="b")
setup_all()
create_all()
--
Traceback (most recent call last):
File "test.py", line 23, in <module>
create_all()
File "/usr/lib/python2.5/site-packages/PIL/__init__.py", line 97, in
create_all
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.0-py2.5.egg/
sqlalchemy/schema.py", line 1231, in create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
AttributeError: 'NoneType' object has no attribute 'create'
--
Thanks again for your help
Gaetan de Menten
The problem here is that neither of your metadata's are bound to their
engine. Your sessions are, but not the metadata. And the create_all
call doesn't go through the session (which is pretty normal).
A little tip: sessions are only for ORM stuff. For creating the tables
you don't need the ORM part.
Nick
advice worked wonders!
On 6 Dec, 17:54, "Gaetan de Menten" <gdemen...@gmail.com> wrote:
Nick
usage and the later for use with pylons. I haven't tried with elixir
0.5 and sa 4.2 yet, though i can't see why it wouldn't work.
--- Standalone (all in one file) ---
#
# inform elixir of externally created session and metadata using
entity's "using_options" in this scenario
# otherwise elixir won't know which entity should be matched to which
metadata and session
#
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
b_engine = create_engine('sqlite:////tmp/b.db', echo=True)
b_session = application_session =
scoped_session(sessionmaker(autoflush=True, transactional=True,
bind=b_engine))
engine'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()
--- Standalone (in multiple files) ---
#
# File a.py
# inform elixir of externally created session and metadata using
__metadata__ and __session__ at the module level; no need for binding
at entity level
# with using_options anymore
#
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
a_engine = create_engine('sqlite:////tmp/a.db', echo=True)
a_session = scoped_session(sessionmaker(autoflush=True,
a_metadata = metadata
__metadata__ = a_metadata
__session__ = a_session
class A(Entity):
name = Field(Unicode(30))
using_options(tablename="a")
======
#
# File b.py
#
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.schema import ThreadLocalMetaData
from elixir import *
transactional=True))
b_metadata = ThreadLocalMetaData()
__metadata__ = b_metadata
__session__ = b_session
class B(Entity):
name = Field(Unicode(30))
using_options(tablename="b")
======
#
# File c.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()
===
# run with
touch /tmp/{a,b}.py
python c.py
--- Pylons integration ---
Above model files a.py and b.py would be similar - remove the engine/
create_engine lines, c.py above is essentially same as model/
__init__.py and it would become
# /project/model/__init__.py
from a import *
from b import *
setup_all()
# /development.ini
...
sqlalchemy.a.url = sqlite:///%(here)s/databases/a.db
sqlalchemy.b.url = sqlite:///%(here)s/databases/b.db
...
# /project/config/environment.py
# end of the file add
...
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
# finished call method with:
finally:
model.a_session.remove()
model.b_session.remove()
---
Hope that helps someone, my implementation does things slightly
differently now in that when a user logs in i have a method that binds
the b_session to a specific engine dynamically.
Nick
Gaetan de Menten
>
> For clarity, this is what i ended up with. The former for standalone
> usage and the later for use with pylons. I haven't tried with elixir
> 0.5 and sa 4.2 yet, though i can't see why it wouldn't work.
Thanks for posting this. It would be nice if you could post this as
one (or two?) Recipe on the wiki:
http://elixir.ematia.de/trac/wiki/Recipes/