Join Table Inheritance with SQLAlchemy – Frank’s Goldfish Projects

Intro

Object Relationship Mapper is a typical pattern with building applications. “Join Table Inheritance” is a pattern let you share most of the common column together.

We are building and three level inheritance model with the last two model having one of them as virtual.


import sqlalchemy as sa
print("sqlalchemy version: %s"%sa.__version__)
print("="*100)

from sqlalchemy import ForeignKey, Integer, String, case, Boolean, Uuid
from sqlalchemy.orm import declarative_base, mapped_column
import uuid
from matplotlib import pyplot as plt
from matplotlib.image import imread


Base = declarative_base()

class Animal(Base):
    """Master table for all animal"""
    __tablename__ = 'animal'
    id = mapped_column(Uuid, primary_key=True, default = uuid.uuid4)
    name = mapped_column(String, nullable=False)
    family = mapped_column(String, nullable=False)
    image_path = mapped_column(String, nullable=True)
    __mapper_args__ = {
        'polymorphic_identity': 'animal',
        'polymorphic_on': family
    }
    def __repr__(self):
        return f"Animal(name={self.name}, family={self.family})"

    def _repr_html_(self):
        if self.image_path:
            return f"<img src='{self.image_path}' />"
        else:
            return None
    

class Felidae(Animal):
    """Group all cats together"""
    __tablename__ = 'felidae'
    id = mapped_column(Uuid, ForeignKey('animal.id'), primary_key=True, default = uuid.uuid4)
    species = mapped_column(String, nullable=False)
    evilness_score = mapped_column(Integer, nullable=False)
    __mapper_args__ = {
        'polymorphic_identity': 'felidae',
        'polymorphic_abstract': True,# if this is not set then 
        'inherit_condition': id == Animal.id,
        'polymorphic_on': case(
            (species == 'cat', 'cat'),
            (species == 'tigger', 'tigger'),
            else_=Animal.family
        ),
    }
class Bear(Animal):
    """We want to materialize bear"""
    __tablename__ = 'ursidae'
    id = mapped_column(Uuid, ForeignKey('animal.id'), primary_key=True, default = uuid.uuid4)
    honey_lover = mapped_column(Boolean, nullable=False)
    __mapper_args__ = {
        'polymorphic_identity': 'bear',
        'inherit_condition': id == Animal.id
    }
    def __repr__(self):
        return f"Bear(name={self.name}, honey_lover={self.honey_lover})"
class Tigger(Felidae):
    """We want materialize tigger"""
    __tablename__ = 'tigger' # tigger is actually materialized
    id = mapped_column(Uuid, ForeignKey('felidae.id'), primary_key=True, default = uuid.uuid4)
    __mapper_args__ = {
        'polymorphic_identity': 'tigger',
        'inherit_condition': id == Felidae.id
    }
    def __repr__(self):
        return f"Tigger(name={self.name}, species={self.species})"

class Cat(Felidae):
    """We don't really care about cat yet but here they are"""
    __mapper_args__ = {
        'polymorphic_identity': 'cat',
    }
    def __repr__(self):
        return f"Cat(name={self.name}, species={self.species})"

engine = sa.create_engine('sqlite:///:memory:')


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

# ================================
# Insert data
# ================================
Session = sa.orm.sessionmaker(bind=engine)


with Session() as session:

    session.add(Bear(name="Winnie the Pooh"
                    , honey_lover=True
                    ))
    session.add(Tigger(name='"T-I-double-guh-er"'
                    , family="felidae"
                    , image_path="img/Tigger-from-Winnie-the-Pooh.png"
                    , evilness_score=0
                    , species="tigger"))

    session.add(Cat(name="Hello Kitty"
                    , family="felidae"
                    , image_path="img/Hello-Kitty.png"
                    , evilness_score=1000
                    , species="cat"))
    session.add(Cat(name="Tom the Cat"
                    , family="felidae"
                    , evilness_score=30
                    , species="cat"))

    # ================================
    # Commit
    # ================================
    session.commit()

with Session() as session:
    # ================================
    # Select data
    # ================================
    print("Select all animals")
    print(session.query(Animal).all())

    print("Select all tiggers")
    print(session.query(Tigger).all())

    print("Select all felidae")
    print(session.query(Felidae).all())

sqlalchemy version: 2.0.45
====================================================================================================
Select all animals
[Bear(name=Winnie the Pooh, honey_lover=True), Animal(name="T-I-double-guh-er", family=felidae), Animal(name=Hello Kitty, family=felidae), Animal(name=Tom the Cat, family=felidae)]
Select all tiggers
[Tigger(name="T-I-double-guh-er", species=tigger)]
Select all felidae
[Tigger(name="T-I-double-guh-er", species=tigger), Cat(name=Hello Kitty, species=cat), Cat(name=Tom the Cat, species=cat)]

Intro to ORM inheritance

If you read those individual tables they do not have the shared attribute.

from sqlalchemy import MetaData
import pandas as pd
# ================================
# Display data as Table
# ================================
print("="*100)
print("Display data")

# Here use a database reflect pattern which means you don't have to map everything.
metadata = MetaData()
metadata.reflect(bind=engine)

with Session() as session:
    for tbl, _ in metadata.tables.items():
        display(pd.read_sql(f"select * from {tbl}", session.connection()).style.set_caption(tbl))

====================================================================================================
Display data

Table 1: animal

	id	name	family	image_path
0	bb285ab4d71f4d438aaf3e4982dd6e5a	Winnie the Pooh	bear	None
1	8da6cfc70b8e44c0a45470a5c946c84e	"T-I-double-guh-er"	felidae	img/Tigger-from-Winnie-the-Pooh.png
2	a5567af4b9e049978e06dd0ca7a048ef	Hello Kitty	felidae	img/Hello-Kitty.png
3	7c1c52a8b35746439deba58508e9ebf8	Tom the Cat	felidae	None

Table 2: felidae

	id	species	evilness_score
0	8da6cfc70b8e44c0a45470a5c946c84e	tigger	0
1	a5567af4b9e049978e06dd0ca7a048ef	cat	1000
2	7c1c52a8b35746439deba58508e9ebf8	cat	30

Table 3: tigger

	id
0	8da6cfc70b8e44c0a45470a5c946c84e

Table 4: ursidae

	id	honey_lover
0	bb285ab4d71f4d438aaf3e4982dd6e5a	1

However reading a “Felidae” as an “ORM” object you can get the full inherited column from animal without having to rejoin everything back up.

from sqlalchemy import select
with Session() as session:
    display(pd.read_sql(select(Felidae), session.connection()).style.set_caption("felidae"))

Table 5: felidae

	id	id_1	name	family	image_path	species	evilness_score	_sa_polymorphic_on
0	8da6cfc7-0b8e-44c0-a454-70a5c946c84e	8da6cfc7-0b8e-44c0-a454-70a5c946c84e	"T-I-double-guh-er"	felidae	img/Tigger-from-Winnie-the-Pooh.png	tigger	0	tigger
1	a5567af4-b9e0-4997-8e06-dd0ca7a048ef	a5567af4-b9e0-4997-8e06-dd0ca7a048ef	Hello Kitty	felidae	img/Hello-Kitty.png	cat	1000	cat
2	7c1c52a8-b357-4643-9deb-a58508e9ebf8	7c1c52a8-b357-4643-9deb-a58508e9ebf8	Tom the Cat	felidae	None	cat	30	cat

You might also realise there are no table called “cat” - this entity is virtualized. This is a single table inheritance

with Session() as session:
    display(pd.read_sql(select(Cat), session.connection()).style.set_caption("cat"))

Table 6: cat

	id	id_1	name	family	image_path	species	evilness_score	_sa_polymorphic_on
0	a5567af4-b9e0-4997-8e06-dd0ca7a048ef	a5567af4-b9e0-4997-8e06-dd0ca7a048ef	Hello Kitty	felidae	img/Hello-Kitty.png	cat	1000	cat
1	7c1c52a8-b357-4643-9deb-a58508e9ebf8	7c1c52a8-b357-4643-9deb-a58508e9ebf8	Tom the Cat	felidae	None	cat	30	cat

But why be so boring putting everything in a table?

session = Session()
x = session.query(Tigger).filter(Tigger.name == '"T-I-double-guh-er"').one()
x
# plt.imshow(imread(hello_kitty.image_path))