1. Mike Bayer
  2. sqlalchemy

Wiki

Clone wiki

sqlalchemy / UsageRecipes / CaseBasedPolymorphicOn

CaseBasedPolymorphicOn

Use the CASE construct to control the loading of polymorphic classes beyond a simple one-to-one mapping.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event

Base= declarative_base()

class Person(Base):
    __tablename__ = "person"

    id = Column(Integer, primary_key=True)
    type = Column(String(20))

    discriminator_expr = case(
        [(type=='employee', "employee")],
        else_="person"
    )
    discriminator = column_property(discriminator_expr)

    __mapper_args__ = {
        # this can be just "discriminator" in 0.7.4
        "polymorphic_on":discriminator_expr,
        "polymorphic_identity":"person"
    }

@event.listens_for(Person, "init", propagate=True)
def set_identity(instance, *arg, **kw):
    """Set the value of the 'type' column to a reasonable default."""
    instance.type = instance.__mapper__.polymorphic_identity

class Employee(Person):
    __tablename__ = 'employee'
    id = Column(Integer, ForeignKey('person.id'), primary_key=True)
    __mapper_args__ = {
        "polymorphic_identity":"employee"
    }

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)

p1, p2, p3, p4 = Person(), Employee(), Person(), Person()

# set artificial types
p3.type = "engineer"
p4.type = "manager"

s.add_all([p1, p2, p3, p4])
s.commit()
s.close()

r = [type(x) for x in s.query(Person).order_by(Person.id)]
assert \
    r == \
    [Person, Employee, Person, Person]

Updated