[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 mike...@zzzcomputing.comwrote:
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 luke.a...@gmail.com 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
[sqlalchemy] Re: ORM Many to Many Across Two Databases
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] Re: ORM Many to Many Across Two Databases
-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 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
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 luke.a...@gmail.com 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
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 luke.a...@gmail.com 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 -~--~~~~--~~--~--~---
