Flask-04-数据库查询进阶、分组查询和执行原生SQL语句、关联查询，常用的SQLAlchemy关系选项、模型之间的关联

最新推荐文章于 2023-01-02 00:00:51 发布

Hi-CWJ

最新推荐文章于 2023-01-02 00:00:51 发布

阅读量2k

点赞数 4

分类专栏： 9Flask 文章标签： flask flask数据库操作

本文链接：https://blog.csdn.net/qq_45957580/article/details/110334876

版权

9Flask 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、数据库查询进阶

filter模糊查询，支持各种运算符和查询方法
filter_by精确查询，只支持字段的值是否相等这种条件
filter多条件查询
filter值范围查询
order_by结果排序
count 统计结果数量
limit 结果数量进行限制
offse 对查询开始位置进行设置
paginate分页器


from flask import Flask,jsonify,render_template
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:123@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="钱包")

    def __repr__(self):
        return self.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():
    """数据库进阶操作"""
    """filter设置判断条件
    ==  判断相等
    >=
    <
    >
    <=
    !=
    """
    # student = Student.query.filter(Student.name=="xiaohui32号").first()
    # if student is None:
    #     return jsonify({"error":"100404","errmsg":"没有该学生信息！"})
    # print(student)
    """filter设置模糊查询"""
    # like模糊条件
    # 模型.字段.like("%值%")  等价于  模型.字段.contains("值")    包含xxx
    # 模型.字段.like("值%")   等价于  模型.字段.startswith("值")  以xxx开头
    # 模型.字段.like("%值")   等价于  模型.字段.endswith("值")    以xxx结尾
    # 模型.字段.like("__")    值长度为2个字符的.几个下划线代表几个字符

    # student_list = Student.query.filter(Student.name.like("%xiaohui%")).all()
    # student_list = Student.query.filter(Student.name.startswith("xiao")).all()
    # student_list = Student.query.filter(Student.name.like("________")).all()

    """filter_by设置精确条件查找数据"""
    # filter_by 只支持一个等号作为判断条件，而且字段左边不需要声明模型类名
    # 可以用于获取一条数据，也可以获取多条数据
    # student = Student.query.filter_by(money=1000).first()
    # print(student)

    """filter多条件查询"""
    # 多条件需要基于逻辑运算来编写，当然，可以其他的声明方式
    """and_ 并且, 与"""
    # from sqlalchemy import and_
    # # 方式1:
    # student_list1 = Student.query.filter(Student.money==1000,Student.sex==True).all()
    # # 方式2:
    # student_list2 = Student.query.filter(and_(Student.money==1000,Student.sex==True)).all()
    #
    # print(student_list1, student_list2)

    """or_ 或者，或"""
    # from sqlalchemy import or_
    # student_list = Student.query.filter( or_(Student.age > 17, Student.age < 15) ).all()
    # print(student_list)

    """not_ 排除，非"""
    from sqlalchemy import not_
    # student_list = Student.query.filter(not_(Student.age > 17)).all()
    # print(student_list)

    """filter值范围查询"""
    # 查询年龄=15或者17或者19的
    # student_list = Student.query.filter(Student.age.in_([15,17,19])).all()
    # print(student_list)

    """order_by结果排序"""
    # order_by(模型.字段.desc())   db.desc(模型.字段)    倒序
    # order_by(模型.字段.asc())    db.asc(模型.字段)     升序
    # student_list = Student.query.order_by(db.desc(Student.money)).all()
    # student_list = Student.query.order_by(Student.money.desc()).all()
    # print(student_list)

    """count 统计结果数量"""
    # ret = Student.query.filter(Student.age>17).count()
    # print(ret)

    """limit 结果数量进行限制"""
    """offse 对查询开始位置进行设置"""
    # 对学生的钱包进行从大到小排名，第3-第5名的学生
    # student_list = Student.query.order_by(Student.money.desc()).offset(2).limit(3).all()
    # print(student_list)

    """paginate分页器"""
    # paginate(page=当前页码, per_page=每一页数据量, max_per_page=每一页最大数据量)
    # 当前页码，默认是从request.args["page"]，如果当前参数没有值，则默认为1
    # 每一页数据量，默认是100条
    # 因为分页器有提供了一个  request.args.["per_page"]给客户端设置每一页数据量，所以再次限定客户端最多能设置的每一页数据量
    pagination = Student.query.filter(Student.sex==True).paginate(per_page=1)
    print(pagination)
    return render_template("list.html",pagination=pagination)
    # print( pagination.items ) # 获取当前页数据量
    # print( pagination.has_next ) # 如果还有下一页数据，则结果为True
    # print( pagination.has_prev ) # 如果有上一页数据，则结果为True
    # print( pagination.page ) # 当前页页码 request.args.get("page",1)
    # print( pagination.total ) # 本次查询结果的数据总量[被分页的数据量总数]
    # print( pagination.pages )   # 总页码
    # print( pagination.prev() ) # 上一页的分页器对象，如果没有上一页，则默认为None
    # print( pagination.next() ) # 下一页的分页器对象，如果没有下一页，则默认为None
    # if pagination.has_next:
    #     print( pagination.next().items ) # 下一页的数据列表

    return "Ok"

if __name__ == '__main__':
    # with app.app_context():
    #     db.create_all() # 根据模型创建所有的数据表
    #     # db.drop_all()   # 删除模型对应的所有数据表
    app.run()

二、分组查询和执行原生SQL语句

一般分组都会结合聚合函数来一起使用。SQLAlchemy中所有的聚合函数都在func模块中声明的。
from sqlalchemy import func

函数名	说明
func.count	统计总数
func.avg	平均值
func.min	最小值
func.max	最大值
func.sum	和

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:123@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="钱包")

    def __repr__(self):
        return self.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():
    from sqlalchemy import func
    """ group_by 分组查询"""
    # 查询男生和女生的最大年龄
    # ret = db.session.query(Student.sex,func.max(Student.age)).group_by(Student.sex).all()
    # print(ret)

    # 查询出男生和女生年龄大于18的人数
    # having是针对分组的结果进行过滤处理，所以having能调用的字段，必须是分组查询结果中的字段，否则报错！！
    # ret = db.session.query(Student.sex,Student.age, func.count(Student.age)).group_by(Student.sex,Student.age).having(Student.age>18).all()
    # print(ret)

    """执行原生SQL语句，返回结果不是模型对象, 是列表和元祖"""
    # 查询多条
    # ret = db.session.execute("select id,name,age,IF(sex,'男','女') from tb_student").fetchall()
    # print(ret)
    # # 查询单条
    # ret = db.session.execute("select * from tb_student where id = 3").fetchone()
    # print(ret)

    # 添加/修改/删除
    # db.session.execute("UPDATE tb_student SET money=(money + %s) WHERE age = %s" % (200, 22))
    # db.session.commit()

    # 查询出女生和男生中大于18岁的人数
    ret = db.session.execute("SELECT IF(sex,'男','女'), count(id) from (SELECT id,name,age,sex FROM `tb_student` WHERE age>18) as stu group by sex").fetchall()
    print(ret)
    return "Ok"

if __name__ == '__main__':

    app.run()

三、关联查询

1.常用的SQLAlchemy关系选项

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

2.模型之间的关联

①一对一

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:123@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= "xiaohuang",
    #     age = 13,
    #     sex = True,
    #     money = 1000,
    #     info = StudentInfo(
    #         mobile="13312345678",
    #         address="北京市昌平区白沙路103号"
    #     )
    # )
    # db.session.add(student)
    # db.session.commit()

    """查询/读取"""
    # student = Student.query.first()
    # print(student.age)
    # print(student.info.mobile)
    #
    # student = StudentInfo.query.filter(StudentInfo.mobile=="13312345678").first()
    # print(student.own.name)

    """修改更新"""
    # student = Student.query.get(1)
    # student.age = 18
    # student.info.address = "北京市昌平区沙河镇白沙路103号"
    # db.session.commit()

    """删除"""
    # student = Student.query.get(2)
    # 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()

②一对多

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:123@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="姓名")
    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.90"),
    #         Course(name="挨打",price="19.90"),
    #         Course(name="炸房子",price="29.90"),
    #     ]
    # )
    # db.session.add(teacher)
    # db.session.commit()

    # course = Course(
    #     name="平底锅108种用法",
    #     price="99.99",
    #     teacher=Teacher(name="红太狼",option="班主任")
    # )
    # db.session.add(course)
    # db.session.commit()

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

    # course = Course.query.filter(Course.name=="炸房子").first()
    # print(course)
    # print("%s在教%s" % (course.teacher.name,course.name))

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

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

    

    return "Ok"

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

③多对多

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:123@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="姓名")
    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="xiaohuihui",
    #     age=5,
    #     sex=False,
    #     money=1000,
    #     info=StudentInfo(
    #         mobile="13066666666",
    #         address="狼村1号别墅",
    #     ),
    #     course_list = [
    #         course1,
    #         course2,
    #         course3,
    #     ]
    # )
    # db.session.add(student)
    # db.session.commit()

    """查询"""
    # student = Student.query.filter(Student.name=="xiaohuihui").first()
    # print(student)
    # print(student.course_list) # [坑爹, 坑娘, 和羊做朋友，一起坑爹]

    # course = Course.query.filter(Course.name=="和羊做朋友，一起坑爹").first()
    # print("-----------------------------------------------")
    # print(course.student_list.all()) # 获取所有学生信息

    """更新"""
    # course = Course.query.filter(Course.name == "和羊做朋友，一起坑爹").first()
    # course.student_list[0].name="小灰灰"
    # db.session.commit()

    return "Ok"

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

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:123@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)

"""创建模型类"""
from datetime import datetime
class Achievement(db.Model):
    __tablename__ = "tb_achievement"
    id = db.Column(db.Integer, primary_key=True,comment="主键")
    student_id = db.Column(db.Integer, db.ForeignKey("tb_student.id"), comment="学生")
    course_id = db.Column(db.Integer, db.ForeignKey("tb_course.id"), comment="课程")
    score = db.Column(db.DECIMAL(5,2), nullable=True, comment="成绩分数")
    created_time = db.Column(db.DateTime, default=datetime.now(), comment="考试时间")

    def __repr__(self):
        return "[%s],%s进行了一次%s科目考试，成绩:%s" % (self.created_time,self.student.name,self.course.name,self.score)

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="钱包")
    achievement_list = db.relationship("Achievement",uselist=True, backref="student", lazy="select")
    # 关联属性，是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="姓名")
    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="老师")
    achievement_list = db.relationship("Achievement", backref="course", uselist=True, lazy="select")
    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="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()
    return "Ok"

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

Hi-CWJ

关注

4
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
Flask-04-数据库查询进阶、分组查询和执行原生SQL语句、关联查询，常用的SQLAlchemy关系选项、模型之间的关联

一、数据库查询进阶filter模糊查询，支持各种运算符和查询方法filter_by精确查询，只支持字段的值是否相等这种条件filter多条件查询filter值范围查询order_by结果排序count 统计结果数量limit 结果数量进行限制offse 对查询开始位置进行设置paginate分页器from flask import Flask,jsonify,render_templatefrom flask_sqlalchemy import SQLAlchemyapp = Fl
复制链接

扫一扫

专栏目录