Join tables in two databases using SQLAlchemy

Question

I am working with two MySQL Databases. I want to join a table from DB 1 with a table from DB2 in SQLAlchemy.

I am using automap_base while creating data access layer in sqlalchemy as follows...

class DBHandleBase(object):

    def __init__(self, connection_string='mysql+pymysql://root:xxxxxxx@localhost/services', pool_recycle=3600):
            self.Base_ = automap_base()
            self.engine_ = create_engine(connection_string,
                                         pool_recycle = pool_recycle)
            self.Base_.prepare(self.engine_, reflect=True)
            self.session_ = Session(self.engine_)

And my tables Class is like

class T1D1_Repo():


    def __init__(self, dbHandle):
        # create a cursor
        self.Table_ = dbHandle.Base_.classes.t1
        self.session_ = dbHandle.session_

I am making the join like this,

db1_handle = DB1_Handle()
db2_handle = DB2_Handle()
t1d1_repo = T1D1_Repo(handle)
t1d2_repo = T1D2_Repo(person_handle)

result = t1d1_repo.session_.query(
            t1d1_repo.Table_,
            t1d2_repo.Table_).join(t1d2_repo.Table_, (
                t1d1_repo.Table_.person_id
                == t1d2_repo.Table_.uuid))

I am getting the error like this:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'db1.t1d2' doesn't exist") [SQL: 'SELECT 

we have created table t1 in database db1 and table t2 in database db2.

Do join is possible across two databases table in sqlalchemy ORM? How to achieve that?

Answer 1

In MySQL databases are synonymous with schemas. Where for example in Postgresql you can query between multiple schemas in a database, but not between databases (directly), you can query between multiple databases in MySQL as there's no distinction between the two.

In this light a possible solution to your multi-database query in MySQL could be to use a single engine, session, and Base handling both your schemas and passing the schema keyword argument to your tables, or reflecting both schemas so that they're fully qualified.

Since I don't have your data, I made 2 schemas (MySQL databases) on a test server called sopython and sopython2:

mysql> create database sopython;
Query OK, 1 row affected (0,00 sec)

mysql> create database sopython2;
Query OK, 1 row affected (0,00 sec)

and added a table in each:

mysql> use sopython
Database changed
mysql> create table foo (foo_id integer not null auto_increment primary key, name text);
Query OK, 0 rows affected (0,05 sec)

mysql> insert into foo (name) values ('heh');
Query OK, 1 row affected (0,01 sec)

mysql> use sopython2
Database changed
mysql> create table bar (bar_id integer not null auto_increment primary key, foo_id integer, foreign key (foo_id) references `sopython`.`foo` (foo_id)) engine=InnoDB;
Query OK, 0 rows affected (0,07 sec)

mysql> insert into bar (foo_id) values (1);
Query OK, 1 row affected (0,01 sec)

In Python:

In [1]: from sqlalchemy import create_engine

In [2]: from sqlalchemy.orm import sessionmaker

In [3]: from sqlalchemy.ext.automap import automap_base

In [4]: Session = sessionmaker()

In [5]: Base = automap_base()

Create the engine without specifying which schema (database) you use by default:

In [6]: engine = create_engine('mysql+pymysql://user:pass@:6603/')

In [7]: Base.prepare(engine, reflect=True, schema='sopython')

In [8]: Base.prepare(engine, reflect=True, schema='sopython2')
/home/user/SO/lib/python3.5/site-packages/sqlalchemy/ext/declarative/clsregistry.py:120: SAWarning: This declarative base already contains a class with the same class name and module name as sqlalchemy.ext.automap.foo, and will be replaced in the string-lookup table.
  item.__name__

The warning is something I don't fully understand, and is probably a result of the foreign key reference between the 2 tables causing re-reflecting of foo, but it does not seem to cause trouble.

---

The warning is the result of the second call to prepare() recreating and replacing the classes for the tables reflected in the first call. The way to avoid all that is to first reflect the tables from both schemas using the metadata, and then prepare:

Base.metadata.reflect(engine, schema='sopython')
Base.metadata.reflect(engine, schema='sopython2')
Base.prepare()

---

After all this you can query joining foo and bar:

In [9]: Base.metadata.bind = engine

In [10]: session = Session()

In [11]: query = session.query(Base.classes.bar).\
    ...:     join(Base.classes.foo).\
    ...:     filter(Base.classes.foo.name == 'heh')

In [12]: print(query)
SELECT sopython2.bar.bar_id AS sopython2_bar_bar_id, sopython2.bar.foo_id AS sopython2_bar_foo_id 
FROM sopython2.bar INNER JOIN sopython.foo ON sopython.foo.foo_id = sopython2.bar.foo_id 
WHERE sopython.foo.name = %(name_1)s

In [13]: query.all()
Out[13]: [<sqlalchemy.ext.automap.bar at 0x7ff1ed7eee10>]

In [14]: _[0]
Out[14]: <sqlalchemy.ext.automap.bar at 0x7ff1ed7eee10>

In [15]: _.foo
Out[15]: <sqlalchemy.ext.automap.foo at 0x7ff1ed7f09b0>