SQL join between two tables from two databases

[Brian Glogower]

Hi all,

I am trying to do a join between two tables, each residing on a separate databases.

Here is the table info for both. I have removed extraneous columns from each table.

Table A from DB 1:

class Host(Base):
    __tablename__ = 'hosts'
    __table_args__ = {'mysql_engine': 'InnoDB'}

    id = Column(u'HostID', INTEGER(), primary_key=True)
    hostname = Column(String(length=30))

Table B from DB 2:

class ssh_host_keys(Base):
    __tablename__ = 'ssh_host_keys'
    __table_args__ = {'mysql_engine': 'InnoDB'}

    hostname = Column(VARCHAR(30), primary_key=True)
    sha256 = Column(CHAR(64))

I would like to use the sqlalchemy orm to do something like the following query:

SELECT hostname, sha256 FROM hosts LEFT JOIN ssh_host_keys ON ssh_host_keys.hostname == hosts.hostname

I did some searching and did find https://www.mail-archive.com/sqlal...@googlegroups.com/msg14445.html, but there wasn't enough details for me to get it working (I am a sqlalchemy novice).

Is what I want to do even possible with sqlalchemy?

Thanks,
Brian

[Thierry Florac]

What database server do you use?

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

http://www.imagesdusport.com -- http://www.ztfy.org

[Jonathan Vanasco]

I haven't seen anyone bring this up before.  If you get stuck and no better answer shows up here... I'd try just having a single session with tables from both DBs in it, and using raw SQL to populate the ORM objects -- using the MySQL native cross-database query format.  It's not elegant, but I think that should work.  

[Brian Glogower]

I am using MySQL

[Brian Glogower]

On 19 January 2015 at 08:01, Jonathan Vanasco <jona...@findmeon.com> wrote:

I haven't seen anyone bring this up before.  If you get stuck and no better answer shows up here... I'd try just having a single session with tables from both DBs in it, and using raw SQL to populate the ORM objects -- using the MySQL native cross-database query format.  It's not elegant, but I think that should work.  

Thanks for the idea. Do you have an example?

I was also thinking of having two sessions and then implementing a pseudo join in code.

[Jonathan Vanasco]

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)

   )

I was also thinking of having two sessions and then implementing a pseudo join in code.

 I would try to stay away from that, because that will require two database connections.

[Michael Bayer]

Jonathan Vanasco <jona...@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().

[Jonathan Vanasco]

On Tuesday, January 20, 2015 at 7:12:18 PM UTC-5, Michael Bayer wrote:

 

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().

It's not, I just didn't think of it.  But I also saw some weird syntax in the raw sql for more advanced cross db joins, so I just defaulted to thinking of that.

[Brian Glogower]

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

[Simon King]

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

[Brian Glogower]

Simon, thanks for your response. Let me wrap my head around this and try it out.

Brian

[SF Markus Elfring]

> I am trying to do a join between two tables,
> each residing on a separate databases.

Would you like to consider another software
design option?

* Do you know if any special connectors or data
source adaptors are available for your database
software implementations?

* Can one of them be configured as a data source
for the other database so that you would only
need to deal with a single connection for
the desired query?

Regards,
Markus

[Brian Glogower]

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.