黄兵的个人博客

Set single_parent=True on the relationship().

今天在使用SQLAlchemy查询多对多的时候，出现了如下错误：

sqlalchemy.exc.ArgumentError: On PrivateNumberProperty.PrivateNumberTag, delete-orphan cascade is not supported on a many-to-many or many-to-one relationship when single_parent is not set. Set single_parent=True on the relationship().

出现这个问题的原因：

出现级联删除的时候，delete-orphan cascade意味着每个子对象一次只能有一个父对象，所以在绝大多数情况下配置为一对多关系。将它设置为多对一或多对多关系更加尴尬; 对于此用例，SQLAlchemy要求使用single_parent = True函数配置relationship（），该函数建立Python端验证，以确保对象一次只与一个父对象关联。

下面是源代码：

class PrivateNumberProperty(db.Model):
    __tablename__ = 'private_number_property'
    id = db.Column(db.Integer, primary_key=True)
    property_name_id = db.Column(db.Integer, db.ForeignKey('property_name.id'))
    subtitle = db.Column(db.String(64))
    icon = db.Column(db.String(128))
    private_number_catalog_id = db.Column(db.Integer, db.ForeignKey('private_number_catalog.id'))
    PrivateNumberPrice = db.relationship('PrivateNumberPrice', backref='private_number_property', lazy='joined')

    PrivateNumberTag = db.relationship('PrivateNumberTag', secondary=tp,
                                            backref=db.backref('PrivateNumberProperty', lazy='dynamic'), lazy='dynamic',
                                            cascade='all, delete-orphan')

修改之后：

class PrivateNumberProperty(db.Model):
    __tablename__ = 'private_number_property'
    id = db.Column(db.Integer, primary_key=True)
    property_name_id = db.Column(db.Integer, db.ForeignKey('property_name.id'))
    subtitle = db.Column(db.String(64))
    icon = db.Column(db.String(128))
    private_number_catalog_id = db.Column(db.Integer, db.ForeignKey('private_number_catalog.id'))
    PrivateNumberPrice = db.relationship('PrivateNumberPrice', backref='private_number_property', lazy='joined')

    PrivateNumberTag = db.relationship('PrivateNumberTag', secondary=tp,
                                            backref=db.backref('PrivateNumberProperty', lazy='dynamic'), lazy='dynamic',
                                            single_parent=True,
                                            cascade='all, delete-orphan')

参考资料：

1、Setting delete-orphan on SQLAlchemy relationship causes AssertionError: This AttributeImpl is not configured to track parents