Flask数据库关系:一对多,多对多

1 一对多

1.0 数据表设计

一：Project类
多：Camera类（外键属性）

序号	类型	表
1	一	projectinfo
2	多	camera_device_info

from app import db
from sqlalchemy import Column
class Project(db.Model):
    __tablename__ = "projectInfo"
    __table_args__ = {'mysql_collate':'utf8_general_ci'}
    id = db.Column(db.Integer, primary_key=True)
    uu_id = db.Column(db.Integer, unique=True)
    project_code = db.Column(db.String(320), unique=True)
    project_name = db.Column(db.String(320), unique=False)
    project_describe = db.Column(db.String(320), unique=False)
    # relation with peopleInfo
    peoples = db.relationship("People", 
    secondary=people_project,
    back_populates="projects")
    '''
    classname
    tablename
    return query object
    '''
    camera_devices = db.relationship("CameraDevice", backref="projectInfo", lazy="dynamic", cascade="all, delete-orphan", passive_deletes=True)

class CameraDevice(db.Model):
    __table_name__ = "camera_device_info"
    __table_args__ = {"mysql_collate":"utf8_general_ci"}
    id = db.Column(db.Integer, primary_key=True)
    camera_device_code = db.Column(db.String(320), unique=False)
    camera_device_name = db.Column(db.String(320), unique=False)
    camera_device_location = db.Column(db.String(320), unique=False)
    camera_device_describe = db.Column(db.String(320), unique=False)
    project_id = db.Column(db.Integer, db.ForeignKey("projectInfo.uu_id", ondelete="CASCADE"))

1.2 查询

project_info = Project.query.filter_by(uu_id=1).first()
camera_infos = project_info.camera_devices
for camera_info in camera_infos.all():
	camera = camera_info.camera_device_name

1.3 删除孤儿数据

删除主键同时删除外键对应的数据。
主一表：

camera_devices = db.relationship("CameraDevice", backref="projectInfo", lazy="dynamic", cascade="all, delete-orphan", passive_deletes=True)

多外键表：

project_id = db.Column(db.Integer, db.ForeignKey("projectInfo.uu_id", ondelete="CASCADE"))

2 多对多

2.1 数据表设计

多：Student类
多：Teacher类

序号	类型	表
1	多	students_info
2	多	teachers_info
3	中间	student_teacher

from app import db
from sqlalchemy import Column

student_teacher = db.Table('student_teacher', 
    db.Column("student_id", db.Integer, db.ForeignKey("students_info.id")),
    db.Column("teacher_id", db.Integer, db.ForeignKey("teachers_info.id"))
    )


class Student(db.Model):
    __tablename__ = "students_info"
    __table_args__ = {'mysql_collate':'utf8_general_ci'}
    id = db.Column(db.Integer, primary_key=True)
    u_id = db.Column(db.String(320), unique=True)
    name = db.Column(db.String(320), unique=False)
    grade = db.Column(db.String(320), unique=False)
    teachers = db.relationship("Teacher", 
    secondary=student_teacher,
    back_populates="students")
    def __repr__(self):
        return "<Student %r>" %self.name

class Teacher(db.Model):
    __tablename__ = "teachers_info"
    __table_args__ = {'mysql_collate':'utf8_general_ci'}
    id = db.Column(db.Integer, primary_key=True)
    u_id = db.Column(db.String(320), unique=True)
    name = db.Column(db.String(320), unique=False)
    office = db.Column(db.String(320), unique=False)
    students = db.relationship("Student", 
    secondary=student_teacher,
    back_populates="teachers")

2.2 查询数据

students_info = Student.query.filter_by(u_id="001").first()
teachers = students_info.teachers
for teacher in teachers:
	name = teacher.name

2.3 插入数据

stu1 = Student(u_id="001", name="小一", grade="100")
stu2 = Student(u_id="002", name="小二", grade="100")
stu3 = Student(u_id="003", name="小三", grade="100")

teach1 = Teacher(u_id="001", name="师一", office="001")
teach2 = Teacher(u_id="002", name="师二", office="002")
teach3 = Teacher(u_id="003", name="师三", office="003")
# 多对多数据添加
stu1.teachers = [teach1, teach2]
stu2.teachers = [teach2]
stu3.teachers = [teach1, teach2, teach3]
# 数据库事务
db.session.add_all([stu1, stu2, stu2])
db.session.add_all([teach1, teach2, teach3])

---

【参考文献】
[1]https://www.jianshu.com/p/ac065ac70f34
[2]https://www.cnblogs.com/notzy/p/9187232.html