[sqlalchemy] Re: ORM Many to Many Across Two Databases
My apologies. I must have misunderstanding the behavior of
the schema option and thus its intent. How do I configure a
relation across two logical databases using schema?
I have tried the following:
engine = create_engine(DATABASEURI, echo=True)
meta = MetaData()
meta.bind = engine
left_schema = "LeftDatabaseName"
right_schema = "RightDatabaseName"
left_table = Table('LeftTable', meta,
Column('id', Integer, primary_key=True),
Column('description', String(128)),
schema=left_schema)
right_table = Table('RightTable', meta,
Column('id', Integer, primary_key=True),
Column('description', String(128)),
schema=right_schema)
assoc_table = Table('LeftAssoc', meta,
Column('left_id', Integer),
Column('right_id', Integer),
#quote_schema=False,
schema=left_schema)
MySession = sessionmaker(bind=engine)
class MyBase(object):
def __init__(self, description):
self.description = description
def __str__(self):
return str(self.description)
class Left(MyBase): pass
class Right(MyBase): 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=[assoc_table.c.left_id, assoc_table.c.right_id],
backref="rights"),
})
if __name__ == '__main__':
meta.drop_all()
meta.create_all()
session = MySession()
left1 = Left('Left 1')
left2 = Left('Left 2')
right1 = Right('Right 1')
right2 = Right('Right 2')
left1.rights.extend([right1, right2])
right1.lefts.extend([left1, left2])
session.add_all([left1, left2, right1, right2])
session.commit()
left1 = session.query(Left).filter_by(description="Left 1").one()
print left1
for right in left1.rights:
print " "*4, right
for left in right.lefts:
print " "*8, left
The table name in the generated SQL is DBNAME.TABLENAME,
which doesn't work. It needs to be
DBNAME.SCHEMANAME.TABLENAME
or
DBNAME..TABLENAME (uses default schema)
I tried using quote_schema=False and adding a "." to the end
of the schema value (schema="DBNAME.") but this results in
the broken SQL mentioned earlier:
SELECT [LeftTable_1].description AS [DBNAME._LeftTab_1]
Using schema="DBNAME.SCHEMANAME" didn't work either.
Please let me know how to do this correctly.
I am using:
SQL Server 8.0
Hardy Heron
Python 2.5
SQLAlchemy 0.5.6
pymssql 1.0.2
Thanks, again!
- Luke
On Wed, Oct 14, 2009 at 6:46 PM, Michael Bayer wrote:
>
> Luke Arno wrote:
> > So, is there any chance that relations across multiple _logical_
> > databases on a _single_ physical server will be supported by
> > SQLAlchemy in the future?
>
> that is supported now, via the "schema" argument to Table, assuming you
> are connecting with an account that has access to all logical databases.
>
> relation() supports spanning physical databases as well, if you either
> ensure the "secondary" table is on the same server as the target, or
> alternatively map the association table explicitly as I said in my initial
> response.
>
>
>
>
>
>
> >
> > Thanks.
> >
> > - Luke
> >
> > On Wed, Oct 14, 2009 at 3:01 PM, Luke Arno wrote:
> >
> >> Thanks, Simon. Good suggestion, but these are databases and not
> >> schema within a database. If I use the schema="MyDatabase." (
> >> notice the ".") and quote_schema=False, the table names come out
> >> like I want, but look at what happens to the label here:
> >>
> >> SELECT [LeftTable_1].description AS [MyDatabase._LeftTab_1]
> >> FROM MyDatabase..[LeftTable] AS [LeftTable_1]
> >> WHERE [LeftTable_1].id = %(param_1)s
> >>
> >> That is really abusing the schema feature, so this is not a bug... I
> >> just wish there were a databasename="Foo" option for Table() so I
> >> could use these ugly databases the way they are. :(
> >>
> >>
> >> On Wed, Oct 14, 2009 at 11:51 AM, King Simon-NFHD78 <
> >> simon.k...@motorola.com> wrote:
> >>
> >>>
> >>> > -Original Message-
> >>> > From: sqlalchemy@googlegroups.com
> >>> > [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno
> >>> > Sent: 14 October 2009 16:41
> >>> > To: sqlalchemy@googlegroups.com
> >>> > Subject: [sqlalchemy] Re: ORM
[sqlalchemy] Re: ORM Many to Many Across Two Databases
So, is there any chance that relations across multiple _logical_ databases on a _single_ physical server will be supported by SQLAlchemy in the future? As I mentioned before, this could work (at least for MSSQL) if we only had a databasename="foo" on Table(). I am not really sure how much work it would be, but it _sounds_ easy. :) Thanks. - Luke On Wed, Oct 14, 2009 at 3:01 PM, Luke Arno wrote: > Thanks, Simon. Good suggestion, but these are databases and not > schema within a database. If I use the schema="MyDatabase." ( > notice the ".") and quote_schema=False, the table names come out > like I want, but look at what happens to the label here: > > SELECT [LeftTable_1].description AS [MyDatabase._LeftTab_1] > FROM MyDatabase..[LeftTable] AS [LeftTable_1] > WHERE [LeftTable_1].id = %(param_1)s > > That is really abusing the schema feature, so this is not a bug... I > just wish there were a databasename="Foo" option for Table() so I > could use these ugly databases the way they are. :( > > > On Wed, Oct 14, 2009 at 11:51 AM, King Simon-NFHD78 < > simon.k...@motorola.com> wrote: > >> >> > -Original Message- >> > From: sqlalchemy@googlegroups.com >> > [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno >> > Sent: 14 October 2009 16:41 >> > To: sqlalchemy@googlegroups.com >> > Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases >> > >> > 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? >> > >> >> You can do this by using a single engine and metadata, and passing a >> 'schema' parameter when defining your tables: >> >> http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na >> me >> >> Hope that helps, >> >> Simon >> >> >> >> > --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Many to Many Across Two Databases
Thanks, Simon. Good suggestion, but these are databases and not schema within a database. If I use the schema="MyDatabase." ( notice the ".") and quote_schema=False, the table names come out like I want, but look at what happens to the label here: SELECT [LeftTable_1].description AS [MyDatabase._LeftTab_1] FROM MyDatabase..[LeftTable] AS [LeftTable_1] WHERE [LeftTable_1].id = %(param_1)s That is really abusing the schema feature, so this is not a bug... I just wish there were a databasename="Foo" option for Table() so I could use these ugly databases the way they are. :( On Wed, Oct 14, 2009 at 11:51 AM, King Simon-NFHD78 wrote: > > > -Original Message- > > From: sqlalchemy@googlegroups.com > > [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno > > Sent: 14 October 2009 16:41 > > To: sqlalchemy@googlegroups.com > > Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases > > > > 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? > > > > You can do this by using a single engine and metadata, and passing a > 'schema' parameter when defining your tables: > > http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na > me > > Hope that helps, > > Simon > > > > --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Many to Many Across Two Databases
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 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
-~--~~~~--~~--~--~---
[sqlalchemy] ORM Many to Many Across Two Databases
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"),
})
- 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
-~--~~~~--~~--~--~---
