首先，创建三个数据库表映射类 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@')
c = Course(name='Flask 基础', user=u)
l = Lab(name='简单的 CRUD 操作', course=c)
session.add(u)
session.add(c)
session.add(l)
mit()
此时数据库的状态：
mysql> use shiyanlou
Database changed
mysql> select * from user;
+----+------+-------------+
| id | name | email |
+----+------+-------------+
| 1 | Kobe | kobe@ |
+----+------+-------------+
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)
mit()
此时数据库状态，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)
