python - Flask-SQLAlchemy 多态关联

Question

我有两个主表，分别是role 和users，在users 上，我将3 个关联到表operator、老师和学生。

到目前为止，我是这样的:

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': '*'
    }

但是我收到了这个错误信息:

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.

我试图在 Role 表的 users 字段上设置 enable_typechecks=False，然后我收到此错误消息:

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}]

即使我尝试使用不同的数据，我也收到了该消息，但它仍然显示重复的键值。

拜托，我的代码有什么问题..？或者任何类似情况的例子..？

Answer 1

找出不同:)

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': '*'
    }

这不是一个好的错误消息，但您错过了基类上的类型字段。它需要在某处存储子表的类型，否则如果您在基类上运行查询并期望多态性，它将必须搜索所有其他子表以匹配 ID。见:

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.

他们还在教程中建议您不要在子项中使用单独的 id 列，并将子项 id 列的主键和外键都返回到基。

您可能想要删除“with_polymorphic”:“*”，因为它会预先加载所有子字段(效率低下)。在某些情况下，当您执行过滤器时您可能需要此功能，但您可以在执行查询时将其打开:

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