Luke Arno wrote:
> I have a MSSQL server with two logical databases. (I inherited this
> situation,
> of course.) There is a table in each database and an association table in
> one of them. What is the right way to configure this? Here is what I have
> and
> it complains about the values in foreign_keys. I've tried a lot of
> permutations
> and can't seem to hit on the right one. Thanks much!
>
> left_engine = create_engine(SERVER_A_DB_ONE)
> left_meta = MetaData()
> left_meta.bind = left_engine
>
> right_engine = create_engine(SERVER_A_DB_TWO)
> right_meta = MetaData()
> right_meta.bind = right_engine
>
> left_table = Table('LeftTable', left_meta,
> Column('id', Integer, primary_key=True),
> Column('description', String(128)))
>
> right_table = Table('RightTable', right_meta,
> Column('id', Integer, primary_key=True),
> Column('description', String(128)))
>
> assoc_table = Table('LeftAssoc', left_meta,
> Column('left_id', Integer),
> Column('right_id', Integer))
>
> MySession = sessionmaker(binds={
> left_table: left_engine,
> right_table: right_engine,
> assoc_table: left_engine
> })
>
> class Left(object): pass
>
> class Right(object): pass
>
> mapper(Left, left_table)
>
> mapper(Right, right_table, properties={
> 'lefts': relation(Left, secondary=assoc_table,
> primaryjoin=right_table.c.id==assoc_table.c.right_id,
> secondaryjoin=assoc_table.c.left_id==left_table.c.id,
> foreign_keys=[right_table.c.id, left_table.c.id],
> backref="rights"),
> })
>
the foreign keys here would be assoc_table.c.left_id and
assoc_table.c.right_id.
However I don't think relation() + secondary is going to work here. The
load of "child" objects will be against the target database only - there's
no behavior such that it will separately select rows from the association
table first, then the target table.
Your two options here are to map assoc_table explicitly, optionally using
association_proxy to have its usage be implicit, or alternately to build a
read-only accessor on your class which manually queries the association
table and then queries the target table.
Another possibility, if this were Oracle I'd suggest using "dblink" tables
- tables which are present in the local database but are "remote" to
another server. Since MS-SQL competes fiercely with Oracle I wouldn't be
surprised if MS-SQL supports such a concept as well.
> - Luke
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
