declarative versus classes mapped to multiple engines
Chris Withers
I'm wondering if the use case I have is one that is supported...
So, the situation is that I have a bunch of classes that I need to map
to a bunch of tables, and I'd prefer to do that declaratively. The
spicey bit is that I need to connect to several environments a lot of
the time and not all of these tables are available in all environments.
So, my plan is to have one engine per database I connect to.
But what to do about tables?
Am I right in thinking that I should have one MetaData object per
engine, and that MetaData object should only have the tables in it that
are actually present in that database?
If so, what's the recommended pattern for doing that?
Now, what about declarative? Declarative seems to like each class to
have a MetaData object, but how do I tie that in with multiple engines
and some tables not being present in some engines?
What about the declarative registry? I guess having only one of those is
fine since it just maps a string name to a class object, right?
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
Michael Bayer
On Feb 9, 2012, at 1:42 PM, Chris Withers wrote:
> Hi Again,
>
> I'm wondering if the use case I have is one that is supported...
>
> So, the situation is that I have a bunch of classes that I need to map to a bunch of tables, and I'd prefer to do that declaratively. The spicey bit is that I need to connect to several environments a lot of the time and not all of these tables are available in all environments.
>
> So, my plan is to have one engine per database I connect to.
> But what to do about tables?
So what does it mean for your application to import a module, that has a class "MyClass", which should be mapped to a table, but when the app is running, that table essentially doesn't exist ? Does that render "MyClass" useless and if so why import it ? Otherwise, if it is still useful, and I'm guessing you're using the declared reflection recipe, you'd need to enhance the usage of prepare() such that the class is not actually mapped, since there is no table. Or you do something else. This is all doable.
>
> Am I right in thinking that I should have one MetaData object per engine, and that MetaData object should only have the tables in it that are actually present in that database?
>
> If so, what's the recommended pattern for doing that?
Check this post, read the section "Model Setup":
http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/
>
> Now, what about declarative? Declarative seems to like each class to have a MetaData object, but how do I tie that in with multiple engines and some tables not being present in some engines?
>
> What about the declarative registry? I guess having only one of those is fine since it just maps a string name to a class object, right?
its all there, yup
Chris Withers
>
>> So, my plan is to have one engine per database I connect to.
>> But what to do about tables?
>
> So what does it mean for your application to import a module, that has a class "MyClass", which should be mapped to a table, but when the app is running, that table essentially doesn't exist ? Does that render "MyClass" useless
Yes.
> and if so why import it ?
Thing's like Pyramid config's scan (not the case here) and nose (one of
the issues here) mean that it should be importable but not cause
anything (including prepare) to blow up.
> Otherwise, if it is still useful, and I'm guessing you're using the declared reflection recipe,
What on earth would give you that idea? ;-) (yes, yes I am...)
> you'd need to enhance the usage of prepare() such that the class is not actually mapped, since there is no table.
Yeah, I had this working with the Table call wrapped in a try/except.
> Check this post, read the section "Model Setup":
>
> http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/
Almost, but...
We can't have per-connection models, that would be semantically weird.
Using a different metadata at session creation time, keyed off the dsn
of the database connected to, and with irrelevant classes blowing up if
used but ignored otherwise is semantically what I'm after. Clues as to
how to implement gratefully received...
Michael Bayer
On Feb 9, 2012, at 3:02 PM, Chris Withers wrote:
> Almost, but...
>
> We can't have per-connection models, that would be semantically weird.
> Using a different metadata at session creation time, keyed off the dsn of the database connected to, and with irrelevant classes blowing up if used but ignored otherwise is semantically what I'm after. Clues as to how to implement gratefully received...
do you have multiple, simultaneous engines when the app runs, where some engines might not be available, or just one engine, pointing to a database that has some subset of required tables ? this is confusing me.
what's not working with the try/except on the reflection ?
Chris Withers
>
> On Feb 9, 2012, at 3:02 PM, Chris Withers wrote:
>
>> Almost, but...
>>
>> We can't have per-connection models, that would be semantically weird.
>> Using a different metadata at session creation time, keyed off the dsn of the database connected to, and with irrelevant classes blowing up if used but ignored otherwise is semantically what I'm after. Clues as to how to implement gratefully received...
>
> do you have multiple, simultaneous engines when the app runs,
Yes. Some will have totally different schemas, where I wouldn't imagine
any mapped classes would interact with each other. For these, I'd
imagine a separate MetaData instance as in the "Model Setup" section
would work.
However, some will have very similar schemas^1 with just a few tables
deliberately missing. Here, it's "okay" (ie: programmer error) if
queries involving classes mapped to these blow up because the wrong
engine is used and so a table is missing. But, the reflection needs to
not blow up.
With one metadata object shared between these engines (which is what
declarative with reflection appears to support), if the first engine
used in an app (and these can be web apps and, just as annoyingly, unit
test runs, where the order is arbitrary) is one where a table is
missing, the class will be declaratively mapped, but the reflection will
fail, so it won't ever get mapped and will blow up if later used with an
engine that *does* have the table.
So, in my head I'm thinking of something like (excuse the hand waving):
data_per_dsn = dict()
def getSession(dsn):
if dsn not in data_per_dsn:
engine = make_engine(dsn) # ^2
metadata = MetaData()
reflect_tables_and_map_declarative_classes(metadata, engine)
data_per_dsn[dsn] = engine, metadata
engine, metadata = data_per_dsn[dsn]
return make_session(engine, metadata?)
...which would solve both the cases above.
The main problem I see is the case where the above would result in two
metadata objects for one declarative class.
Wow, I feel like I'm making a hash of describing this, I'm hope I'm
vaguely succeeding in getting the info across :-S
Chris
^1 Let's pretend that it's just missing tables, the fact that supposedly
identical tables in different environments have evolved over time to
have different primary keys^3 and numbers of columns is *not* something
SQLAlchemy should have to deal with ;-)
^2 Oh for a function in SQLAlchemy to turn a dsn into a SQLAlchemy URL,
and maybe back again...
^3 Let's also pretend that (seriously?!) some tables had not ended up
with unique indexes that postgres is happy enough with but SQLAlchemy
doesn't reflect correctly, because they're not *actually* primary keys -
*sigh* - again, SA should not have to deal with this ;-)