Re: [sqlalchemy] SQL join between two tables from two databases
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 -- 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.
Re: [sqlalchemy] SQL join between two tables from two databases
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
Re: [sqlalchemy] SQL join between two tables from two databases
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
Re: [sqlalchemy] SQL join between two tables from two databases
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.
Re: [sqlalchemy] SQL join between two tables from two databases
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.
Re: [sqlalchemy] SQL join between two tables from two databases
I am using MySQL
On 17 January 2015 at 04:06, Thierry Florac tflo...@gmail.com wrote:
What database server do you use?
2015-01-17 0:49 GMT+01:00 Brian Glogower bglogo...@ifwe.co:
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/sqlalchemy@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
--
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.
--
http://www.imagesdusport.com -- http://www.ztfy.org
--
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.
Re: [sqlalchemy] SQL join between two tables from two databases
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. -- 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.
Re: [sqlalchemy] SQL join between two tables from two databases
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.
Re: [sqlalchemy] SQL join between two tables from two databases
On 19 January 2015 at 08:01, Jonathan Vanasco jonat...@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. -- 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.
Re: [sqlalchemy] SQL join between two tables from two databases
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. -- 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.
Re: [sqlalchemy] SQL join between two tables from two databases
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. -- 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.
Re: [sqlalchemy] SQL join between two tables from two databases
What database server do you use?
2015-01-17 0:49 GMT+01:00 Brian Glogower bglogo...@ifwe.co:
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/sqlalchemy@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
--
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.
--
http://www.imagesdusport.com -- http://www.ztfy.org
--
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.
[sqlalchemy] SQL join between two tables from two databases
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/sqlalchemy@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
--
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.
