Flask 数据库orm关联查询,一对多、多对多..._flask 一对多多态关联

作者：Cpp五条 | 2024-05-02 03:18:35

踩

flask 一对多多态关联

模型之间的关联

常用的SQLAlchemy关系选项

选项名	说明
backref	在关系的另一模型中添加反向引用,用于设置外键名称,在1查多的
primary join	明确指定两个模型之间使用的连表条件
lazy	指定如何加载关联模型数据的方式。参数值: select（立即加载，查询所有相关数据显示，相当于lazy=True） subquery（立即加载，但使用子查询） dynamic（不加载记录，但提供加载记录的查询对象）
uselist	如果为False，不使用列表，而使用标量值。一对一关系中，需要设置relationship中的uselist=Flase，其他数据库操作一样。
secondary	指定多对多关系中关系表的名字。多对多关系中，需建立关系表，设置 secondary=关系表
secondary join	在SQLAlchemy中无法自行决定时，指定多对多关系中的二级连表条件

一对一

代码:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)

class Config():
    # DEBUG调试模式
    DEBUG = True
    # json多字节转unicode编码
    JSON_AS_ASCII = False
    # 数据库链接配置
    # SQLALCHEMY_DATABASE_URI = "mysql://账号:密码@IP/数据库名?编码"
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@127.0.0.1:3306/students?charset=utf8mb4"
    # 动态追踪修改设置，如未设置只会提示警告
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    # 查询时会显示原始SQL语句
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)
db = SQLAlchemy()
db.init_app(app)

"""创建模型类"""
class Student(db.Model):
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True,comment="主键ID")
    name = db.Column(db.String(250), comment="姓名")
    age = db.Column(db.Integer, comment="年龄")
    sex = db.Column(db.Boolean, default=False, comment="性别")
    money = db.Column(db.DECIMAL(8,2), nullable=True, comment="钱包")
    # 关联属性，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，不存在于mySQL中！！！
    # backref 反向引用，类似django的related，通过外键模型查询主模型数据时的关联属性
    info = db.relationship("StudentInfo", backref="own", uselist=False)
    def __repr__(self):
        return self.name

class StudentInfo(db.Model):
    __tablename__ = "tb_student_info"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    sid= db.Column(db.Integer,db.ForeignKey(Student.id), comment="学生")
    address = db.Column(db.String(255), nullable=True, comment="家庭住址")
    mobile = db.Column(db.String(15), unique=True, comment="紧急联系电话")

    def __repr__(self):
        return self.own.name

class Teacher(db.Model):
    __tablename__ = "tb_teacher"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    name = db.Column(db.String(250), comment="姓名")
    sex = db.Column(db.Boolean, default=False, comment="性别")
    option = db.Column(db.Enum("讲师","助教","班主任"), default="讲师", comment="教职")

    def __repr__(self):
        return self.name

class Course(db.Model):
    __tablename__ = "tb_course"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    name = db.Column(db.String(250), unique=True, comment="课程名称")
    price = db.Column(db.Numeric(6, 2))

    def __repr__(self):
        return self.name

@app.route("/")
def index():
    """添加数据"""
    student = Student(
        name='小灰灰',
        age=16,
        sex=False,
        money=1000,
        info=StudentInfo(
            mobile='18696607037',
            address='xx地区',
        )
    )
    db.session.add(student)
    db.session.commit()

    """查询/读取"""
    # student = Student.query.first()
    # print(student.age)
    # print(student.info.mobile)

    """修改更新"""
    # student = Student.query.get(1)
    # student.age = 18
    # student.info.address = '狼村小灰街道99号'
    # db.session.commit()

    """删除"""
    # student = Student.query.get(1)
    # print(student)
    # db.session.delete(student.info)
    # db.session.delete(student)
    # db.session.commit()
    return "Ok"


if __name__ == '__main__':
    # with app.app_context():
    #     db.drop_all()
    #     db.create_all()
    app.run()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

一对多

class Teacher(db.Model):
	...
    # 关联属性，一的一方添加模型关联属性
    course_list = db.relationship("Course", uselist=True, backref="teacher",lazy='dynamic')
   
class Course(db.Model):
	...
    # 外键，多的一方模型中添加外间
    teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id))
1
2
3
4
5
6
7
8
9

其中realtionship描述了Course和Teacher的关系。第一个参数为对应参照的类"Course"
第二个参数backref为类Teacher申明新属性的方法
第三个参数lazy决定了什么时候SQLALchemy从数据库中加载数据
- lazy=‘subquery’，查询当前数据模型时，采用子查询(subquery)，把外键模型的属性也瞬间查询出来了。
- lazy=True或lazy=‘select’，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性时，才进行连表查询数据[执行SQL]
- lazy=‘dynamic’，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性并操作外键模型具体属性时，才进行连表查询数据[执行SQL]

代码：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)

class Config():
    # DEBUG调试模式
    DEBUG = True
    # json多字节转unicode编码
    JSON_AS_ASCII = False
    # 数据库链接配置
    # SQLALCHEMY_DATABASE_URI = "mysql://账号:密码@IP/数据库名?编码"
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@127.0.0.1:3306/students?charset=utf8mb4"
    # 动态追踪修改设置，如未设置只会提示警告
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    # 查询时会显示原始SQL语句
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)
db = SQLAlchemy()
db.init_app(app)

"""创建模型类"""
class Student(db.Model):
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True,comment="主键ID")
    name = db.Column(db.String(250), comment="姓名")
    age = db.Column(db.Integer, comment="年龄")
    sex = db.Column(db.Boolean, default=False, comment="性别")
    money = db.Column(db.DECIMAL(8,2), nullable=True, comment="钱包")
    # 关联属性，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，不存在于mySQL中！！！
    # backref 反向引用，类似django的related，通过外键模型查询主模型数据时的关联属性
    info = db.relationship("StudentInfo", backref="own", uselist=False)
    def __repr__(self):
        return self.name

class StudentInfo(db.Model):
    __tablename__ = "tb_student_info"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    sid= db.Column(db.Integer,db.ForeignKey(Student.id), comment="学生")
    address = db.Column(db.String(255), nullable=True, comment="家庭住址")
    mobile = db.Column(db.String(15), unique=True, comment="紧急联系电话")

    def __repr__(self):
        return self.own.name

class Teacher(db.Model):
    __tablename__ = "tb_teacher"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    name = db.Column(db.String(250), comment="姓名")
    sex = db.Column(db.Boolean, default=False, comment="性别")
    option = db.Column(db.Enum("老师","助教","班主任"), default="老师", comment="教职")
    course_list = db.relationship("Course",uselist=True,backref='teacher',lazy='subquery')

    def __repr__(self):
        return self.name

class Course(db.Model):
    __tablename__ = "tb_course"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    name = db.Column(db.String(250), unique=True, comment="课程名称")
    price = db.Column(db.Numeric(6, 2))
    teacher_id = db.Column(db.Integer,db.ForeignKey(Teacher.id),comment='老师')

    def __repr__(self):
        return self.name

@app.route("/")
def index():
    """1对多，多对1"""
    """添加数据"""
    # 添加主模型数据，同时也添加外键模型
    # teacher = Teacher(
    #     name='灰太狼',
    #     option='班主任',
    #     course_list=[
    #         Course(name='抓羊',price='9.9'),
    #         Course(name='挨打', price='19.9'),
    #         Course(name='搞发明', price='999.9'),
    #
    #     ],
    # )
    # )
    # db.session.add(teacher)
    # db.session.commit()
    # course = Course(
    #     name='平底锅108种用法',
    #     price='19.9',
    #     teacher = Teacher(name='红太狼',option='班主任'),
    #
    #
    # )
    # db.session.add(course)
    # db.session.commit()
    #

    """查询数据"""
    # teacher = Teacher.query.filter(Teacher.name=='灰太狼').first()
    # print(teacher.name,teacher.option)
    # for i in teacher.course_list:
    #     print(i.name)

    # course = Course.query.filter(Course.name=='搞发明').first()
    # print(course.teacher.name,course.price)

    """更新数据"""
    # teacher = Teacher.query.filter(Teacher.name=='灰太狼').first()
    # teacher.course_list[0].name = '抓懒羊羊'
    # db.session.commit()


    """删除数据"""
    teacher = Teacher.query.filter(Teacher.name=='灰太狼').first()
    for i in teacher.course_list:
        db.session.delete(i)
    db.session.delete(teacher)
    db.session.commit()





    return "Ok"


if __name__ == '__main__':
    # with app.app_context():
        # db.drop_all()
        # db.create_all()
    app.run()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130

多对多

achievement = db.Table('tb_achievement',  
    db.Column('student_id', db.Integer, db.ForeignKey('tb_student.id')),  
    db.Column('course_id', db.Integer, db.ForeignKey('tb_course.id')),
)

class Course(db.Model):
    ...
	students = db.relationship('Student',secondary=achievement,  
                                    backref='courses',  
                                    lazy='dynamic')
class Student(db.Model):

    ...
1
2
3
4
5
6
7
8
9
10
11
12
13

多对多，也可以拆解成3个模型，其中tb_achievement作为单独模型存在。

练习代码:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)

class Config():
    # DEBUG调试模式
    DEBUG = True
    # json多字节转unicode编码
    JSON_AS_ASCII = False
    # 数据库链接配置
    # SQLALCHEMY_DATABASE_URI = "mysql://账号:密码@IP/数据库名?编码"
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@127.0.0.1:3306/students?charset=utf8mb4"
    # 动态追踪修改设置，如未设置只会提示警告
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    # 查询时会显示原始SQL语句
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)
db = SQLAlchemy()
db.init_app(app)


"""创建模型类"""
# db.Table(
# 表名，
# db.Column("字段名",字段类型，外键声明),
# db.Column("字段名",字段类型，外键声明),
# )
"""以db.Table关系表来确定模型之间的多对多关联"""
achievement = db.Table(
    "tb_achievement",
    db.Column("student_id",db.Integer,db.ForeignKey('tb_student.id')),
    db.Column("course_id",db.Integer,db.ForeignKey('tb_course.id')),

    # 这里的表信息，在主键模型中，仅仅表达的是关联关系，所以中间表的字段，无法通过主模型来获取
    db.Column("created_time",db.DateTime,comment="考试时间"),
    db.Column("score",db.DECIMAL(5,2),comment="成绩")
)


class Student(db.Model):
    __tablename__ = "tb_student"
    id = db.Column(db.Integer, primary_key=True,comment="主键ID")
    name = db.Column(db.String(250), comment="姓名")
    age = db.Column(db.Integer, comment="年龄")
    sex = db.Column(db.Boolean, default=False, comment="性别")
    money = db.Column(db.DECIMAL(8,2), nullable=True, comment="钱包")
    # 关联属性，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，不存在于mySQL中！！！
    # backref 反向引用，类似django的related，通过外键模型查询主模型数据时的关联属性
    info = db.relationship("StudentInfo", backref="own", uselist=False)
    #course_list = db.relationship("Course", secondary=achievement,backref="student_list",lazy="dynamic")
    def __repr__(self):
        return self.name

class StudentInfo(db.Model):
    __tablename__ = "tb_student_info"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    sid= db.Column(db.Integer,db.ForeignKey(Student.id), comment="学生")
    address = db.Column(db.String(255), nullable=True, comment="家庭住址")
    mobile = db.Column(db.String(15), unique=True, comment="紧急联系电话")

    def __repr__(self):
        return self.own.name

class Teacher(db.Model):
    __tablename__ = "tb_teacher"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    name = db.Column(db.String(250), comment="姓名")
    # sex = db.Column(db.Boolean, default=False, comment="性别")
    option = db.Column(db.Enum("老师","助教","班主任"), default="老师", comment="教职")
    course_list = db.relationship("Course",uselist=True, backref="teacher",lazy="subquery")
    def __repr__(self):
        return self.name

class Course(db.Model):
    __tablename__ = "tb_course"
    id = db.Column(db.Integer, primary_key=True, comment="主键ID")
    name = db.Column(db.String(250), unique=True, comment="课程名称")
    price = db.Column(db.Numeric(6, 2))
    teacher_id = db.Column(db.Integer,db.ForeignKey(Teacher.id),comment='老师')
    student_list = db.relationship('Student', secondary=achievement,backref='course_list',lazy='dynamic')

    def __repr__(self):
        return self.name

@app.route("/")
def index():
    """多对多"""
    # 添加
    # course1 = Course(name='坑爹',price='9.99', teacher=Teacher(name='灰太狼',option='老师'))
    # course2 = Course(name='坑娘', price='9.99', teacher=Teacher(name='灰太狼', option='老师'))
    # course3 = Course(name='坑友', price='99.99', teacher=Teacher(name='喜羊羊', option='老师'))
    # student = Student(
    #     name='小灰灰',
    #     age=6,
    #     sex=False,
    #     money=1000,
    #     info=StudentInfo(
    #         mobile='15025555123',
    #         address='狼村afsadsaasffa',
    #     ),
    #     course_list=[course1,course2,course3,]
    # )
    # db.session.add(student)
    # db.session.commit()
    
   """添加数据"""
    # course1 = Course(name="坑爹", price="9.99", teacher=Teacher(name="灰太狼", option="讲师"))
    # course2 = Course(name="坑娘", price="9.99", teacher=Teacher(name="灰太狼", option="讲师"))
    # course3 = Course(name="和羊做朋友，一起坑爹", price="99.99", teacher=Teacher(name="喜洋洋", option="讲师"))
    # student = Student(
    #     name="xiaohuihui",
    #     age=5,
    #     sex=False,
    #     money=1000,
    #     info=StudentInfo(
    #         mobile="13066666666",
    #         address="狼村1号别墅",
    #     ),
    #     achievement_list= [
    #         Achievement(course=course1,score=100),
    #         Achievement(course=course2,score=80),
    #         Achievement(course=course3,score=85),
    #     ]
    # )
    # db.session.add(student)
    # db.session.commit()

    # course = Course.query.filter(Course.name=="坑爹").first()
    # student = Student.query.filter(Student.name=="xiaohuihui").first()
    # achievement = Achievement(
    #     course=course,
    #     student=student,
    #     score=78
    # )
    # db.session.add(achievement)
    # db.session.commit()

    # 修改xiaohuihui的成绩
    student = Student.query.filter(Student.name == "xiaohuihui").first()
    achievement_list = student.achievement_list
    achievement_list[0].score = 85.7
    db.session.commit()




    """查询"""
    # student = Student.query.filter(Student.name=='小灰灰').first()
    # print(student)      # 小灰灰
    # print(student.course_list)  # [坑爹, 坑娘, 坑友]

    # course = Course.query.filter(Course.name=='坑爹').first()
    # print(course)
    # print(course.students_list.all())

    """更新"""
    course = Course.query.filter(Course.name=='坑爹').first()
    print(course.student_list.all())
    course.student_list[0].name='小灰灰1'
    # db.session.commit()


    return "Ok"


if __name__ == '__main__':
    # with app.app_context():
    #     # db.drop_all()
    #     db.create_all()
    app.run()


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/521957