CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Plasma
Plasma

Reputation: 1961

SQLAlchemy: Subclassed model db entry gets properties of other subclassed model

I'm having an issue with SQLAlchemy in a Flask app where I have two models Instructor and Student that subclass the same model User. When I'm creating a Student object, it's listed in the database under User with properties that should be unique for Instructor. Here are my (simplified) classes:

class User(db.Model):
    __tablename__ = "user"
    discriminator = db.Column("type", db.String(50))  # Student or Instructor
    __mapper_args__ = {"polymorphic_on": discriminator}
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50)) 

class Instructor(User):
    __mapper_args__ = {"polymorphic_identity": "instructor"}
    reputation = db.Column(db.Integer, default=1)
    approved_for_teaching = db.Column(db.Boolean, default=False)

class Student(User):
    __mapper_args__ = {"polymorphic_identity": "student"}
    lessons_taken = db.Column(db.Integer, default=0)

I'm creating a Student like this:

new_student = Student(name=user_name)
db.session.add(new_student)
db.session.commit()

But when inspecting the object in the database, the student gets attributes from the Instructor model, i.e. reputation (value is 1) and approved_for_teaching (value is False). Am I doing something wrong here, or is this expected behaviour? I could understand if the columns would have to be present, since they share the same table (User) in the db, but then I'd expect the values to be null or something. Thanks!

Upvotes: 0

Views: 146

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

It is expected behaviour, since you're using single table inheritance:

Single table inheritance represents all attributes of all subclasses within a single table. A particular subclass that has attributes unique to that class will persist them within columns in the table that are otherwise NULL if the row refers to a different kind of object.

Though the documentation mentions leaving the columns belonging to other classes NULL, the client side defaults you've given the columns kick in during inserts to user table, which underlies all the classes inheriting from User, even when they're not a part of the particular subclass' columns. A context sensitive default function could perhaps be used to avoid that, for example:

def polymorphic_default(discriminator, identity, value):
    def default(context):
        # This should be replaced with context.get_current_parameters() in 1.2 and above
        if context.current_parameters[discriminator.name] == identity:
            return value
    return default

...

class Instructor(User):
    __mapper_args__ = {"polymorphic_identity": "instructor"}
    reputation = db.Column(
        db.Integer, default=polymorphic_default(User.discriminator, 'instructor', 1))
    approved_for_teaching = db.Column(
        db.Boolean, default=polymorphic_default(User.discriminator, 'instructor', False))

but that seems like a lot of work to avoid a rather small issue.

Upvotes: 1

Related Questions