It looks like if I put the relation on the obj mapped to the DB where
the association table is _not_, it works in one direction. (So, in the
example Right.lefts can work but Left.rights cannot.) When trying to
use Left.rights, it looks for the table in the wrong database.
It appears that it would be fine if I could just get the table names all
qualified with database name in the issued SQL. Is there a way to
make that happen, by any chance?
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=[assoc_table.c.left_id, assoc_table.c.right_id],
backref="rights"),
})
Accessing Left.rights gives me this:
Invalid object name 'LeftAssoc'.
DB-Lib error message 20018, severity 5:
General SQL Server error: Check messages from the SQL Server.
'SELECT [RightTable].id AS [RightTable_id], [RightTable].description AS
[RightTable_description] \nFROM [RightTable], [LeftAssoc] \nWHERE
[LeftAssoc].left_id = %(param_1)s AND [RightTable].id =
[LeftAssoc].right_id' {'param_1': 9}
I realize that this is not good DB design - not _my_ design ;) - but
I would guess it is not that rare an abuse of SQL Server. If it is not
supported now, is it worth adding such? I _sounds_ like it would be
simple to qualify the table names...
BTW, sorry for not putting [sqlalchemy] in my subject line. Thanks!
- Luke
On Wed, Oct 14, 2009 at 10:01 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
