Day60 - Flask模型关系与查询、钩子函数、装饰器登录校验

一对一关系

uselist=True

其他与一对多关系一样

一对多关系

一对多模型定义

from flask_sqlalchemy import SQLAlchemy


db = SQLAlchemy()


class Student(db.Model):
    __tablename__ = 'student'  # 默认表名就为student
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    s_name = db.Column(db.String(20), 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=True)


class Grade(db.Model):
    __tablename__ = 'grade'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    g_name = db.Column(db.String(10), unique=True, nullable=False)
    stus = db.relationship('Student', backref='g')

一对多关系查询

@blue.route('/sel_stu_by_grade/', methods=['GET'])
def sel_stu_by_grade():
    # 通过班级查找学生
    # 查询pyhton班级的学生信息
    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 '学生查询班级成功'

多对多关系

多对多关系模型定义

定义中间表s_c
stus = db.relationship('Student', secondary=s_c, backref='cou')
指定关联模型名Student、secondary中间表、backref反向应用

from flask_sqlalchemy import SQLAlchemy


db = SQLAlchemy()


class Student(db.Model):
    __tablename__ = 'student'  # 默认表名就为student
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    s_name = db.Column(db.String(20), 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=True)


class Grade(db.Model):
    __tablename__ = 'grade'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    g_name = db.Column(db.String(10), unique=True, nullable=False)
    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()

多对多关系查询、添加、删除

查询结果为列表，使用append和remove进行添加和删除

@blue.route('/add_s_c/', methods=['GET'])
def add_s_c():
    # 给id=3的学生选择线代这门课
    stu = Student.query.get(3)
    cou = Course.query.filter(Course.c_name == '线代').first()
    # 学生查询课程
    stu.cou
    # 课程查询学生
    cou.stus
    # 学生添加课程
    # stu.cou.append(cou)
    # 课程添加学生
    cou.stus.append(stu)
    # 学生删除某门课
    stu.cou.remove(cou)
    db.session.commit()
    return '添加学生课程'

钩子函数（类似于Django的中间件）

before_request - 在请求之前执行 - 执行顺序从前到后

after_request - 在请求之后执行 - 执行顺序从后到前 - 如果程序本身出错则不执行

teardown_request - 不管是否出现异常都会执行 - 类似于try-except-finally中的finally

必须配置

app.config['PRESERVE_CONTEXT_ON_EXCEPTION'] = False

该配置表示无论如何都会执行teardown_request装饰器

errorhandler - 捕获异常

@blue.before_request
def before_req():
    print('请求之前执行代码1')


@blue.before_request
def before_req1():
    print('请求之前执行代码2')


@blue.route('/index/')
def index1():
    a = 1
    b = 0

    try:
        a / b
    except:
        abort(500)
    return 'index'


@blue.after_request
def after_req(response):
    print('请求之后执行的代码3')
    return response


@blue.after_request
def after_req1(response):
    print('请求之后执行的代码4')
    return response


@blue.teardown_request
def teardown_req(exception):
    print('teardown request')


@blue.errorhandler(500)
def error(e):
    print(e)

使用钩子函数连接MySQL

@blue.before_request
def before_req():
    conn = pymysql.Connection(host='127.0.0.1',
                              port=3306,
                              username='root',
                              password='123456',
                              database='flask')
    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

使用装饰器做登录校验

from functools import wraps

def login_status(func):
    @wraps(func)
    def inner(*args, **kwargs):
        try:
            user_id = session['user_id']
        except:
            # return jsonify(status_code.LOGIN_STATUS_NOT_LOGIN)
            return redirect(url_for('user.login'))
        user = User.query.filter(User.id == user_id).first()
        if not user:
            # return jsonify(status_code.LOGIN_STATUS_NOT_LOGIN)
            return redirect(url_for('user.login'))
        return func(*args, **kwargs)
    return inner