Flask-SQLAlchemy polymorphic association

Question

I have two main table which is role and users, and on users I making 3 associate to table operator, teacher and student.

So far, I making it like this:

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    permissions = db.Column(db.Integer)
    users = db.relationship('User',
                            backref='role', lazy='dynamic')


class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), index=True)
    email = db.Column(db.String(64), unique=True, index=True)

    password_hash = db.Column(db.String(128))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

    __mapper_args__ = {
        'polymorphic_identity': 'users',
        'with_polymorphic': '*',
    }


class Operator(User):
    __tablename__ = 'operator'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    __mapper_args__ = {
        'polymorphic_identity': 'operator',
        'with_polymorphic': '*'
    }


class Teacher(User):
    __tablename__ = 'teacher'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    phone_number = db.Column(db.Integer)
    other_teacher_data = db.Column(db.String)

    __mapper_args__ = {
        'polymorphic_identity': 'teacher',
        'with_polymorphic': '*'
    }


class Student(User):
    __tablename__ = 'student'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    other_student_data = db.Column(db.String)

    __mapper_args__ = {
        'polymorphic_identity': 'student',
        'with_polymorphic': '*'
    }

But I got this error message:

Attempting to flush an item of type as a member of collection "Role.users". Expected an object of type or a polymorphic subclass of this type. If is a subclass of , configure mapper "Mapper|User|users" to load this subtype polymorphically, or set enable_typechecks=False to allow any subtype to be accepted for flush.

I have tried to set enable_typechecks=False on users field on Role table, and then I got this error message:

psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ix_users_email" DETAIL: Key (email)=(zidanecr7kaka2@gmail.com) already exists. [SQL: 'INSERT INTO users (confirmed, first_name, last_name, email, password_hash, role_id, date_of_birth, address, created_at, updated_at) VALUES (%(confirmed)s, %(first_name)s, %(last_name)s, %(email)s, %(password_hash)s, %(role_id)s, %(date_of_birth)s, %(address)s, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) RETURNING users.id'] [parameters: {'confirmed': False, 'first_name': 'Tri', 'last_name': 'Nanda', 'email': 'zidanecr7kaka2@gmail.com', 'password_hash': 'pbkdf2:sha1:1000$PtpuVYh4$b5bbb03939cf6ca9013308b62276889d35a8cc1b', 'role_id': 5, 'date_of_birth': None, 'address': None}]

I got that message even when I try with different data, but it still say duplicate key value.

Please, what's wrong with my code..?, or any example with similliar case..?

Answer 1

Spot the difference :)

from app import db
from flask_login import UserMixin

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    permissions = db.Column(db.Integer)
    users = db.relationship('User',
                            backref='role', lazy='dynamic')


class User(UserMixin, db.Model): 
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(50))
    name = db.Column(db.String(64), index=True)
    email = db.Column(db.String(64), unique=True, index=True)

    password_hash = db.Column(db.String(128))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

    __mapper_args__ = {
        'polymorphic_identity': 'users',
        'with_polymorphic': '*',
        "polymorphic_on": type
    }


class Operator(User):
    __tablename__ = 'operator'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    __mapper_args__ = {
        'polymorphic_identity': 'operator',
        'with_polymorphic': '*'
    }


class Teacher(User):
    __tablename__ = 'teacher'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    phone_number = db.Column(db.Integer)
    other_teacher_data = db.Column(db.String)

    __mapper_args__ = {
        'polymorphic_identity': 'teacher',
        'with_polymorphic': '*'
    }


class Student(User):  
    __tablename__ = 'student'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    other_student_data = db.Column(db.String)

    __mapper_args__ = {
        'polymorphic_identity': 'student',
        'with_polymorphic': '*'
    }

It's not a good error message, but you have missed the type field on the base class. It needs somewhere to store the type of the children, otherwise if you ran a query on the base class and expected polymorphism, it would have to search all the other child tables to match up the ids. See:

https://docs.sqlalchemy.org/en/13/orm/inheritance.html

Above, an additional column type is established to act as the discriminator, configured as such using the mapper.polymorphic_on parameter. This column will store a value which indicates the type of object represented within the row. The column may be of any datatype, though string and integer are the most common.

While a polymorphic discriminator expression is not strictly necessary, it is required if polymorphic loading is desired. Establishing a simple column on the base table is the easiest way to achieve this, however very sophisticated inheritance mappings may even configure a SQL expression such as a CASE statement as the polymorphic discriminator.

They also recommend in the tutorial that you don't use a separate id column in the children and make the child id columns both primary and foreign keys back to the base.

You may want to remove the "with_polymorphic": "*" as it loads all the subfields upfront (inefficient). You may want this in certain cases when you are doing filters but you can turn it on as you are doing the queries:

https://docs.sqlalchemy.org/en/13/orm/inheritance_loading.html