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 <[email protected]>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 <[email protected]> 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 <
> >> [email protected]> wrote:
> >>
> >>>
> >>> > -----Original Message-----
> >>> > From: [email protected]
> >>> > [mailto:[email protected]] On Behalf Of Luke Arno
> >>> > Sent: 14 October 2009 16:41
> >>> > To: [email protected]
> >>> > 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
