CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
brillenheini
brillenheini

Reputation: 983

SQLAlchemy and foreign keys in reflected tables

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

Upvotes: 4

Views: 2766

Answers (3)

Corina Roca
Corina Roca

Reputation: 546

I was looking for a way to check that my Foreign keys columns has been created succesfully (they are reflected in the MEtaData) So What I did is:

from sqlalchemy import *
engine = sqlalchemy.create_engine('sqlite:///yourdb.db')#, echo=True)

metadata = MetaData()
metadata.reflect(engine)
tbl = Table('mytable', metadata)
for column in tbl.c:
    print (column.name)
    print(column.foreign_keys)

Upvotes: 0

c8999c 3f964f64
c8999c 3f964f64

Reputation: 1627

not sure if this is the source of your problem, since you say that your metadata loads correctly, but I load the metadata with a bind=engine argument right away

metadata = db.MetaData(bind=engine)

db is sqlalchemy object.

I also load tables without a class and just make a reference to it like this:

results_table = db.Table('result_table', metadata, autoload=True)

without the autoload_with=engine argument.

Upvotes: 0

Mohammad Aarif
Mohammad Aarif

Reputation: 1715

you can try this - 

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

Upvotes: 1

Related Questions