RE: [sqlalchemy] cross-database joins with MySQL
> -Original Message-
> From: sqlalchemy@googlegroups.com
> [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers
> Sent: 29 June 2010 10:28
> To: sqlalchemy@googlegroups.com
> Subject: Re: [sqlalchemy] cross-database joins with MySQL
>
> Michael Bayer wrote:
> >> We have engines set up like:
> >>
> >> engine1 = create_engine('mysql://username:passw...@server/db1')
> >> engine2 = create_engine('mysql://username:passw...@server/db2')
> >>
> >> ..and then have them bound to separate sessions, with
> separate model classes mapped to them.
> >>
> >> Now, mysql supports cross database joins, eg:
> >>
> >> select t1.colwhatever
> >> from db1.table1 as t1,db2.table2 as t2
> >> where t1.something=t2.something
> >>
> >> Is it possible to express that in SQLAlchemy, particularly
> at the ORM layer with the multiple session/engine/model setup
> described above?
> >> (I suppose the case to test would be, if ModelA is bound
> to engine1 and ModelB is bound to engine2, how would we do:
> >>
> >> session.query(ModelA,ModelB,ModelA.something==ModelB.something)
> >>
> >> ...or something similar, if the above isn't possible?
> >
> > its not possible across two distinct database connections,
> no. Only the database can do joins, and that requires a
> single connection session to do so.
>
> Right, but how can I create an engine such that it can be
> used to access
> two databases? Is it as simple as setting the __tablename__ as
> 'db.tablename' rather than just 'tablename'?
>
> Chris
>
You want the 'schema' parameter to the Table:
http://www.sqlalchemy.org/docs/metadata.html#specifying-the-schema-name
(Probably need to use __table_args__ if you are using declarative)
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] cross-database joins with MySQL
Michael Bayer wrote:
We have engines set up like:
engine1 = create_engine('mysql://username:passw...@server/db1')
engine2 = create_engine('mysql://username:passw...@server/db2')
..and then have them bound to separate sessions, with separate model classes
mapped to them.
Now, mysql supports cross database joins, eg:
select t1.colwhatever
from db1.table1 as t1,db2.table2 as t2
where t1.something=t2.something
Is it possible to express that in SQLAlchemy, particularly at the ORM layer
with the multiple session/engine/model setup described above?
(I suppose the case to test would be, if ModelA is bound to engine1 and ModelB
is bound to engine2, how would we do:
session.query(ModelA,ModelB,ModelA.something==ModelB.something)
...or something similar, if the above isn't possible?
its not possible across two distinct database connections, no. Only the
database can do joins, and that requires a single connection session to do so.
Right, but how can I create an engine such that it can be used to access
two databases? Is it as simple as setting the __tablename__ as
'db.tablename' rather than just 'tablename'?
Chris
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] cross-database joins with MySQL
On Jun 11, 2010, at 12:51 PM, Chris Withers wrote:
> Hi All,
>
> We have engines set up like:
>
> engine1 = create_engine('mysql://username:passw...@server/db1')
> engine2 = create_engine('mysql://username:passw...@server/db2')
>
> ..and then have them bound to separate sessions, with separate model classes
> mapped to them.
>
> Now, mysql supports cross database joins, eg:
>
> select t1.colwhatever
> from db1.table1 as t1,db2.table2 as t2
> where t1.something=t2.something
>
> Is it possible to express that in SQLAlchemy, particularly at the ORM layer
> with the multiple session/engine/model setup described above?
> (I suppose the case to test would be, if ModelA is bound to engine1 and
> ModelB is bound to engine2, how would we do:
>
> session.query(ModelA,ModelB,ModelA.something==ModelB.something)
>
> ...or something similar, if the above isn't possible?
its not possible across two distinct database connections, no. Only the
database can do joins, and that requires a single connection session to do so.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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] cross-database joins with MySQL
Hi All,
We have engines set up like:
engine1 = create_engine('mysql://username:passw...@server/db1')
engine2 = create_engine('mysql://username:passw...@server/db2')
..and then have them bound to separate sessions, with separate model
classes mapped to them.
Now, mysql supports cross database joins, eg:
select t1.colwhatever
from db1.table1 as t1,db2.table2 as t2
where t1.something=t2.something
Is it possible to express that in SQLAlchemy, particularly at the ORM
layer with the multiple session/engine/model setup described above?
(I suppose the case to test would be, if ModelA is bound to engine1 and
ModelB is bound to engine2, how would we do:
session.query(ModelA,ModelB,ModelA.something==ModelB.something)
...or something similar, if the above isn't possible?
cheers,
Chris
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
