Simon, thanks for your response. Let me wrap my head around this and try it out.
Brian On 21 January 2015 at 04:59, Simon King <si...@simonking.org.uk> wrote: > 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. > -- 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.
