mysql delete 级联删除_MySQL 数据库表的 ondelete 级联删除

首先，创建三个数据库表映射类 User 、Course、Lab

外键关联不用说，两个一对多的关系，ForeignKey 都是一样的，

注意 Course 类和 Lab 类中 relationship 的区别：

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship, sessionmaker, backref

engine = create_engine('mysql://root@localhost/shiyanlou?charset=utf8')

Base = declarative_base(engine)

session = sessionmaker(engine)()

class User(Base):

__tablename__ = 'user'

id = Column(Integer, primary_key=True)

name = Column(String(64))

email = Column(String(64))

def __repr__(self):

return ''.format(self.name)

class Course(Base):

__tablename__ = 'course'

id = Column(Integer, primary_key=True)

name = Column(String(64))

user_id = Column(Integer, ForeignKey('user.id', ondelete='CASCADE'))

user = relationship('User',

backref=backref('course', cascade='all, delete-orphan'))

def __repr__(self):

return ''.format(self.name)

class Lab(Base):

__tablename__ = 'lab'

id = Column(Integer, primary_key=True)

name = Column(String(64))

course_id = Column(Integer,

ForeignKey('course.id', ondelete='CASCADE'))

course = relationship('Course', backref='lab')

def __repr__(self):

return ''.format(self.name)

Base.metadata.create_all()

创建几个实例并传入数据库：

u = User(name='Kobe', email='kobe@qq.com')

c = Course(name='Flask 基础', user=u)

l = Lab(name='简单的 CRUD 操作', course=c)

session.add(u)

session.add(c)

session.add(l)

session.commit()

此时数据库的状态：

mysql> use shiyanlou

Database changed

mysql> select * from user;

+----+------+-------------+

| id | name | email |

+----+------+-------------+

| 1 | Kobe | kobe@qq.com |

+----+------+-------------+

1 row in set (0.00 sec)

mysql> select * from course;

+----+--------------+---------+

| id | name | user_id |

+----+--------------+---------+

| 1 | Flask 基础 | 1 |

+----+--------------+---------+

1 row in set (0.00 sec)

mysql> select * from lab;

+----+-----------------------+-----------+

| id | name | course_id |

+----+-----------------------+-----------+

| 1 | 简单的 CRUD 操作 | 1 |

+----+-----------------------+-----------+

1 row in set (0.00 sec)

删除 User 的实例 u ：

session.delete(u)

session.commit()

此时数据库状态，u 和 c 都没了，l 还在

也就是说，只在 ForeignKey 里设置 ondelete='CASCADE' 不行

还得在 relationship 里设置 cascade='all, delete-orphan' 才能实现级联删除：

mysql> select * from user;

Empty set (0.00 sec)

mysql> select * from course;

Empty set (0.00 sec)

mysql> select * from lab;

+----+-----------------------+-----------+

| id | name | course_id |

+----+-----------------------+-----------+

| 1 | 简单的 CRUD 操作 | NULL |

+----+-----------------------+-----------+

1 row in set (0.00 sec)