You don't need to convert it to a Table object, but you probably do need to add 'schema': 'whatever' to the __table_args__ dictionary.
In answer to your second question, I very much doubt you can use query.join() with 2 DB connections. query.join() simply adds an SQL JOIN clause to the query that is eventually sent to the database - there's no way of making that work with 2 separate connections. As an alternative, I think it should be possible to put the tables that exist in a separate schema in a separate SQLAlchemy MetaData (they'd need to use a separate declarative Base class). The MetaData can hold the default schema for the tables, and I *think* you should be able to use tables from different MetaData in query.join(). (I haven't tested this though). http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/basic_use.html#accessing-the-metadata http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#metadata-describing Hope that helps, Simon On Wed, Jan 21, 2015 at 9:09 AM, Brian Glogower <bglogo...@ifwe.co> wrote: > Hi Michael, > > Do I need to redefined mapped class ssh_host_keys as a Table object? > > ssh_host_keys = Table('ssh_host_keys', metadata, > Column('hostname', VARCHAR(30), primary_key=True), > Column('pub', VARCHAR(1600)), > Column('sha256', CHAR(64)), > Column('priv', VARCHAR(2000)), > schema='keys', > mysql_engine='InnoDB' > ) > > Do I need to convert mapped class 'Host' to a Table object as well? I would > prefer not to touch this class, since it is part of a separate module, but > if needed, it is possible. > > class Host(Base): > __tablename__ = 'hosts' > __table_args__ = {'mysql_engine': 'InnoDB'} > > id = Column(u'HostID', INTEGER(), primary_key=True) > hostname = Column(String(length=30)) > > Can you please give an example how to use schema with a query.join(), for my > scenario (two sessions, one for each DB connection)? > > Thanks, > Brian > > On 20 January 2015 at 16:12, Michael Bayer <mike...@zzzcomputing.com> wrote: >> >> >> >> Jonathan Vanasco <jonat...@findmeon.com> wrote: >> >> > >> > >> > On Tuesday, January 20, 2015 at 5:34:27 PM UTC-5, Brian Glogower wrote: >> > >> > Thanks for the idea. Do you have an example? >> > >> > I don't have a personal example handle, but from the docs... >> > >> > >> > http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-literal-sql >> > >> > >>> session.query(User).from_statement( >> > ... text("SELECT * FROM users where name=:name")). >> > \ >> > >> > ... params(name='ed').all() >> > [<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>] >> > >> > >> > So you should be able to do something like: >> > >> > query = Session.query(Host)\ >> > .from_statement( >> > sqlaclhemy.text("SELECT hostname, sha256 FROM DATABASE1.hosts >> > LEFT JOIN DATABASE2.ssh_host_keys ssh_host_keys ON ssh_host_keys.hostname >> > == >> > hosts.hostname) >> > ) >> >> why is text() needed here? these could be the Table objects set up with >> “schema=‘schema name’” to start with, then you’d just do the join with >> query.join(). >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
