Simon,
I was able to get the select join working with the following:
select = """
SELECT hostname, sha256
FROM hosts
JOIN environments ON hosts.environment_id = environments
.environmentID
JOIN zones ON environments.zone_id = zones.ZoneID
JOIN %s.ssh_host_keys USING (hostname)
WHERE ZoneName = %s
""" % (self.config['db']['private']['database'], zone)
rp = self.session.execute(select)
It might not be the best, but it works. Luckily, I only need to read one
table from the other database.
On 21 January 2015 at 12:31, Brian Glogower <bglogo...@ifwe.co> wrote:
> 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.
