SQLAlchemy and foreign keys in reflected tables

Question

I'm a little bit stuck with a problem related to SQLAlchemy and reflected tables, which are connected with a foreign key.

I have two tables:

result_table:
=============
| id | team_home | team_visitor | result |
| -- | --------- | ------------ | ------ |
|  1 |         1 |            2 |    3:2 |
|  2 |         1 |            3 |    2:0 |

team_table:
===========
| id | team_name |
| -- | --------- |
|  1 | Hawks     |
|  2 | Sparrows  |
|  3 | Eagles    |

Now, those tables already exist in the database. I created them using PGAdmin and filled them with sample data. team_homeand team_visitor are both foreign keys pointing to team_table.id.

With SQLAlchemy I use declaration to reflect them:

from sqlalchemy import MetaData, Table, create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base

database = {
            ...
           }

engine = create_engine(URL(**database))

Base = declarative_base()
meta = MetaData()

class Results(Base):
    __table__ = Table('result_table', meta, autoload=True, autoload_with=engine)

class Teams(Base):
    __table__ = Table('team_table', meta, autoload=True, autoload_with=engine)

Querying each table isn't a problem, but I'm not able to join them. A statement like session.query(Results, Teams).join(Teams).all() will result in a AmbiguousForeignKeysError.

The result should be:

| id | team_home | team_visitor | result |
| -- | --------- | ------------ | ------ |
|  1 | Hawks     | Sparrows     |    3:2 |
|  2 | Hawks     | Eagles       |    2:0 |

Viewing the metadata I cleary see that the foreign key is loaded. Now I'm stuck. I'm sure that I miss a column within my class declaring the relationship (like relationship("Address", foreign_keys=[billing_address_id])) but since the definition of that class is reflected, the names of the foreign keys are not avaible at calling time (or instancing time).

Anyway, any hints from outside on how to get foreign keys working within reflected tables? Or should it be better to override the table definition in its class?

Many thanks for your patience.

Regards, Thomas

UPDATE

It's a lot easier using just the core functionality of SQLAlchemy:

# database connection expected for this example

from sqlalchemy import MetaData
from sqlalchemy.sql import select, alias
import pandas as pd

metadata = MetaData()
metadata.reflect(bind=engine)

res_table = metadata.tables['result_table']
tt_home = metadata.tables['team_table'].alias('team_table_home')
tt_vis = metadata.tables['team_table'].alias('team_table_visitor')

s = select([
    res_table.c.id, 
    tt_home.c.team_name, 
    tt_vis.c.team_name,
    res_table.c.result
    ]).select_from(
        res_table.join(
            tt_home, res_table.c.team_home == tt_home.c.id
        ).join(
            tt_vis, res_table.c.team_visitor == tt_vis.c.id)
    ).order_by(res_table.c.id)

result_df = pd.read_sql_query(s, engine).set_index('id')

Hope this might help somebody else as well when struggeling joining two tables connected with two foreign keys.

Cheers, Thomas

Answer 1

you can try this -

session.query(Results, Teams).join(Teams, Teams.id==Results.team_home).all()