How to join 2 tables from different databases in SQLAlchemy?

Question

I am using python/SQLAlchemy to extract data from MySQL databases. I have 2 different hosts, each one with a database, and I need to join 2 tables (one in each host/database). How can I do it?

I'm reading this documentation but I couldn't get any straightforward help. Connecting to one database is as simple as:

engine = create_engine('mysql+pymysql://user:pass@host/database')

But I'm not sure how I can work with two engines.

Anybody? Thanks in advance.

Answer 1

You could use Pandas as the glue between the two databases. For example,

import config
import pandas as pd
import sqlalchemy as SA

engine_postgresql = SA.create_engine('postgresql+psycopg2://{u}:{p}@{h}/{d}'.format(
    u=PGUSER, p=PGPASS, h=PGHOST, d='pgtest'))
engine_mysql = SA.create_engine('mysql+mysqldb://{u}:{p}@{h}/{d}'.format(
    u=MYUSER, p=MYPASS, h=MYHOST, d='mytest'))

sql = 'SELECT col1, col2, col3 FROM tableA'
df1 = pd.read_sql(sql, con=engine_postgresql)
sql = 'SELECT col1, col2, col4 FROM tableB'
df2 = pd.read_sql(sql2, con=engine_mysql)

result = pd.merge(df1, df2, how='left', on=['col1', 'col2'])

pd.read_sql passes an SQL query to the database and returns a DataFrame. pd.merge joins the two DataFrames and returns a DataFrame.

DataFrames can also be inserted into databases as tables using the to_sql method. For example,

result.to_sql('tablename', engine_postgresql, if_exists='append')