sqlalchemy中多对多关系级联删除的问题

「爱情、让人受尽委屈。」 2022-03-08 07:35 503阅读 0赞

## 版本一 ##

①多对多表的建立：  
sqlalchemy中的多对多关系，在这里是使用双向关联的方式，双向关联采用的是 `backref`进行反向双向关系的联系

from app import db
    
    """第三张表"""
    TC = db.Table("TC",
                  db.Column("C_id", db.Integer, db.ForeignKey("classes.id")),
                  db.Column("T_id", db.Integer, db.ForeignKey("teachers.id")))
    
    """班级表"""
    class Class(db.Model):
        __tablename__ = "classes"
        id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
        content = db.Column(db.String(255))
        rela_b = db.relationship("Teacher", secondary=TC, backref=db.backref('classes', lazy='dynamic'),lazy='dynamic')
    
    """教师表"""
    class Teacher(db.Model):
        __tablename__ = "teachers"
        id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
        content = db.Column(db.String(255))

②多对多数据的添加

tt = Teacher(content="张老师") 
    cc = Class(content="二年三班")
    tt.classes.append(cc)
    db.session.add(cc)
    db.session.add(tt)
    try:
    	db.session.commit()
    except Exception as e:
    	print(e)
    	db.session.rollback()

## 版本二 ##

①多对多表在建立的时候，第三张表，在外键中加入了ondelete,作用是在删除Class或者Teacher表中的数据时，第三张表关联的部分也会被删除，实现级联删除的效果

from app import db
    
    TC = db.Table("TC",
                  db.Column("C_id", db.Integer, db.ForeignKey("classes.id", ondelete='CASCADE')),
                  db.Column("T_id", db.Integer, db.ForeignKey("teachers.id", ondelete='CASCADE')))
    
    
    class Class(db.Model):
        __tablename__ = "classes"
        id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
        content = db.Column(db.String(255))
        rela_b = db.relationship("Teacher", secondary=TC, backref=db.backref('classes', lazy='dynamic'),
                                 # cascade="delete, delete-orphan", single_parent=True,
                                 lazy='dynamic')
    
    
    class Teacher(db.Model):
        __tablename__ = "teachers"
        id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
        content = db.Column(db.String(255))

## 版本三 ##

①关于多对多表的级联删除问题，第三张表加上`ondelete`，在`relationship`中，关于`cascade`的配置，如下：

from app import db
    
    TC = db.Table("TC",
                  db.Column("C_id", db.Integer, db.ForeignKey("classes.id", ondelete='CASCADE')),
                  db.Column("T_id", db.Integer, db.ForeignKey("teachers.id", ondelete='CASCADE')))
    
    
    class Class(db.Model):
        __tablename__ = "classes"
        id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
        content = db.Column(db.String(255))
        rela_b = db.relationship("Teacher", secondary=TC, backref=db.backref('classes', lazy='dynamic'),
                                 cascade="delete, delete-orphan", single_parent=True,
                                 lazy='dynamic')
    
    
    class Teacher(db.Model):
        __tablename__ = "teachers"
        id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
        content = db.Column(db.String(255))

经过数据测试，`cascade="delete, delete-orphan", single_parent=True`,代码在没有的情况下，也是可以进行级联删除的，至于有和没有对于数据的删除情况是否作用相同，待验证。。