Flask数据库的对对多查询 、钩子函数及其分页

分页、钩子函数及其数据库的多对多和模糊查询等

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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
import random

import pymysql
from flask import Blueprint, render_template, request, url_for, redirect, abort, g

from app.models import Student, db, Grade, Course
from sqlalchemy import and_, not_, or_

blue = Blueprint('app', __name__)


@blue.route('/create_all/')
def create_all():
    db.create_all()
    return '创建成功'


@blue.route('/')
def hello():
    return 'hello'


@blue.route('/list/', methods=['GET'])
def stu_list():
    # students = Student.query.all()
    # 分页
    # pre_page:一页多少条
    page = int(request.args.get('page', 1))
    pre_page = 5
    paginate = Student.query.paginate(page, pre_page)
    # paginate.items  获取当前页面的内容
    students = paginate.items
    return render_template('list.html', paginate=paginate, students=students)


@blue.route('/add/', methods=['GET', 'POST'])
def stu_add():
    if request.method == 'GET':
        return render_template('add.html')
    if request.method == 'POST':
        # 创建学生信息
        # 1.获取数据
        username = request.form.get('username')
        phone = request.form.get('phone')
        age = request.form.get('age')
        # 2.保存
        stu = Student()
        stu.s_name = username
        stu.s_phone = phone
        stu.s_age = age
        db.session.add(stu)
        db.session.commit()
        return redirect(url_for('app.stu_list'))


@blue.route('/edit/<int:id>/', methods=['GET', 'POST'])
def stu_edit(id):
    if request.method == 'GET':
        # stu = Student.query.filter(Student.id == id).first()
        stu = Student.query.filter_by(id=id).first()
        return render_template('add.html', stu=stu)

    if request.method == 'POST':
        # 1.获取页面的参数
        username = request.form.get('username')
        phone = request.form.get('phone')
        age = request.form.get('age')
        # 2.获取对象
        # stu = Student.query.filter(Student.id == id).first()
        stu = Student.query.filter_by(id=id).first()
        # 3.修改属性
        stu.s_name = username
        stu.s_phone = phone
        stu.s_age = age
        db.session.add(stu)
        db.session.commit()
        return redirect(url_for('app.stu_list'))


@blue.route('/del/<int:id>/', methods=['GET'])
def stu_del(id):
    if request.method == 'GET':
        # 获取删除对象
        # stu = Student.query.filter(Student.id == id).first()
        stu = Student.query.filter_by(id=id).first()
        # 使用delete(对象)
        db.session.delete(stu)
        db.session.commit()
        return redirect(url_for('app.stu_list'))


@blue.route('/add_all/', methods=['GET'])
# 批量创建
def add_all():
    stus = []
    for i in range(10):
        stu = Student()
        stu.s_age = random.randint(18,28)
        stu.s_name = '小明%s' % random.randint(0,10000)
        stu.s_phone = '12345678911'
        stus.append(stu)
        # db.session.add(stu)
    db.session.add_all(stus)
    db.session.commit()
    return '创建成功'


@blue.route('/change_stu/', methods=['GET'])
# 修改时db.session.add(对象)可以不用写
def change_stu():
    stu = Student.query.filter(Student.id == 8).first()
    stu.s_name = '小花'
    # db.session.add(stu)
    db.session.commit()
    return '修改成功'


@blue.route('sel_stu', methods=['GET'])
def sel_stu():
    # 查询id为6的信息
    # first(): 获取第一个
    stu = Student.query.filter(Student.id == 3).first()
    stu = Student.query.filter_by(id=3).first()
    # get(主键)：只能获取主键那一行的信息
    stu = Student.query.get(5)
    # 查询所有数据
    # all()返回所有数据的列表结果
    stus = Student.query.all()
    # 排序:升序
    stus = Student.query.order_by('s_age')
    stus = Student.query.order_by('s_age asc')
    # 排序：降序
    stus = Student.query.order_by('-s_age')
    stus = Student.query.order_by('s_age desc')
    # 实现分页 offset(跳过几个参数) limit(截取几条数据):
    stu = Student.query.offset(0).limit(2)

    # 模糊查询contains
    stus = Student.query.filter(Student.s_name.contains('小花')).all()
    # 以小开头的学生信息 startswith
    stus = Student.query.filter(Student.s_name.startswith('小')).all()
    # 以3结尾的学生信息
    stus = Student.query.filter(Student.s_name.endswith('3')).all()

    # 第二位为‘明’ 的学生信息, like '_明%'
    stus = Student.query.filter(Student.s_name.like('_明%')).all()

    # 查询id为1,2,3，4,5,的学生信息 in_([1,2,3,4,5])
    stus = Student.query.filter(Student.id.in_([1, 2, 3, 4, 5]))
    # 查询年龄小于21的信息
    # 小于/小于等于 lt le  大于/大于等于 gt/ge
    stus = Student.query.filter(Student.s_age.__le__(21)).all()
    stus = Student.query.filter(Student.s_age <= 21).all()

    # 查询年龄小于22且姓名以6结束
    stus = Student.query.filter(Student.s_age<22).filter(Student.s_name.endswith('6')).all()
    stus = Student.query.filter(Student.s_age<22, Student.s_name.endswith('6')).all()
    # and_
    stus = Student.query.filter(and_(Student.s_age < 22, Student.s_name.endswith('6'))).all()
    # or_
    stus = Student.query.filter(or_(Student.s_age < 22, Student.s_name.endswith('6'))).all()
    # not_
    stus = Student.query.filter(not_(Student.s_age < 22)).all()
    # 分页
    stus = Student.query.paginate(1, 5)

    names = [stu.s_name for stu in stus]
    print(names)

    return '查询成功'


@blue.route('/add_grade/')
def add_grade():
    # 批量添加班级信息
    g_list = []
    grades = ['Python', 'Java', 'PHP', 'C++', 'C', 'Ruby', 'GO', '易语言', 'lua']
    for grade in grades:
        g = Grade()
        g.g_name = grade
        g_list.append(g)
    db.session.add_all(g_list)
    db.session.commit()
    return '创建成功'


@blue.route('/stu_gra/')
def stu_grade():
    # 给id为4，56的学生分配到python班
    stus = Student.query.filter(Student.id.in_([4, 5, 6])).all()
    for stu in stus:
        stu.grade_id = 1
        db.session.add(stu)
    db.session.commit()
    return '分配成功'


@blue.route('/sel_stu_by_grade/', methods=['GET'])
def sel_stu_by_grade():
    # 通过班级查学生
    # 查询python班级的学生信息
    grade = Grade.query.filter(Grade.g_name == 'Python').first()
    students = grade.stus
    return '查询成功'


@blue.route('/sel_grade_by_stu/', methods=['GET'])
def sel_grade_by_stu():
    # 查询id=12的学生对于的班级信息
    stu = Student.query.get(12)
    grade = stu.g
    return '学生查询成功'


# 多对多，批量创建课程
@blue.route('/add_cou/', methods=['GET'])
def add_cou():
    cou_list = []
    courses = ['语文', '数学', '英语', 'MySQL', 'Flask']
    for course in courses:
        cou = Course()
        cou.c_name = course
        cou_list.append(cou)
    db.session.add_all(cou_list)
    db.session.commit()
    return '创建课程成功'


# 向中间表插入数据
@blue.route('/add_s_c/', methods=['GET'])
def add_s_c():
    # 给id为8的学生选择语文这门课
    stu = Student.query.get(8)
    cous = Course.query.filter(Course.c_name == '语文').first()
    # 学生查询课程
    stu.cou
    # 课程去查学生
    cous.stus
    # 学生添加课程
    stu.cou.append(cous)
    # 课程添加学生
    cous.stus.append(stu)
    db.session.commit()
    return '添加学生课程成功'


# 钩子函数
# @blue.before_request
# def before_req():
#     print('请求前执行代码')
#
#
# @blue.route('/index/')
# def index():
#     # a = 1
#     # b = 0
#     # try:
#     #     a/b
#     # except:
#     #     # 抛出异常 abort()
#     #     abort(500)
#     return 'index'
#
#
# @blue.after_request
# # 返回响应内容
# def after_req(response):
#     print('请求之后执行代码')
#     return response
#
#
# @blue.teardown_request
# def teardown_req(e):
#     print('teardown request')
#
#
# # # 捕获异常
# # @blue.errorhandler(500)
# # def error(e):
# #     return e


@blue.before_request
def before_req():

    conn = pymysql.connect(host='127.0.0.1', port=3306,
                           database='flask7', user='root',
                           password='123456')
    cursor = conn.cursor()
    g.cursor = cursor
    g.conn = conn


@blue.route('/my_sel_stu/')
def my_sel_stu():
    sql = 'select * from student;'
    g.cursor.execute(sql)
    data = g.cursor.fetchall()
    return '查询成功'


@blue.teardown_request
def teardown_req(e):
    g.conn.close()
    return e

创建多多函数

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

from flask_sqlalchemy import SQLAlchemy


db = SQLAlchemy()


class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    s_name = db.Column(db.String(80), unique=True, nullable=False)
    s_phone = db.Column(db.String(11), nullable=True)
    s_age = db.Column(db.Integer, nullable=False)
    s_gender = db.Column(db.Integer, default=1)
    grade_id = db.Column(db.Integer, db.ForeignKey('grade.id'), nullable=False)


class Grade(db.Model):
    # relationship存在'一'的一方
    __tablename__ = 'grade'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    g_name = db.Column(db.String(10), unique=True, nullable=False)
    # backref:反向解析
    stus = db.relationship('Student', backref='g')


s_c = db.Table('s_c',
               db.Column('s_id', db.Integer, db.ForeignKey('student.id')),
               db.Column('c_id', db.Integer, db.ForeignKey('course.id')))


class Course(db.Model):
    __tablename__='course'
    id = db.Column(db.Integer,primary_key=True, autoincrement=True)
    c_name = db.Column(db.String(10), unique=True, nullable=False)
    stus = db.relationship('Student', secondary=s_c, backref='cou')

    def save(self):
        db.session.add(self)
        db.session.commit()

###manage.py 里面配置数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

from flask import Flask
from flask_script import Manager

from app.models import db
from app.views import blue

app = Flask(__name__)

app.register_blueprint(blueprint=blue, url_prefix='/app/')

# 配置数据库
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1:3306/flask7'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['PRESERVE_CONTEXT_ON_EXCEPTION'] = False

db.init_app(app)


manage = Manager(app)
if __name__ == '__main__':
    manage.run()