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.