day95:flask:SQLAlchemy数据库查询进阶&关联查询
目录
1.数据库查询-进阶
1.常用的SQLAlchemy查询过滤器
| 说明 | |
|---|---|
| filter() | 把过滤器添加到原查询上，返回一个新查询 |
| filter_by() | 把等值过滤器添加到原查询上，返回一个新查询 |
| limit() | 使用指定的值限定原查询返回的结果 |
| offset() | 偏移原查询返回的结果，返回一个新查询 |
| order_by() | 根据指定条件对原查询结果进行排序，返回一个新查询 |
| group_by() | 根据指定条件对原查询结果进行分组，返回一个新查询 |
2.
| 以列表形式返回查询的所有结果 | |
| first() | 返回查询的第一个结果，如果未查到，返回None |
| first_or_404() | 返回查询的第一个结果，如果未查到，返回404 |
| get() | 返回指定主键对应的行，如不存在，返回None |
| get_or_404() | 返回指定主键对应的行，如不存在，返回404 |
| count() | 返回查询结果的数量 |
| paginate() | 返回一个Paginate分页器对象，它包含指定范围内的结果 |
| having | 返回结果中符合条件的数据，必须跟在group by后面，其他地方无法使用。 |
3.filter
1.filter设置判断条件
== != >= <= < >
student = Student.query.filter(Student.name=="xiaohui32号").first() if student is None: return jsonify({"error":"100404","errmsg":"没有该学生信息！"})
2.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()
3.filter_by设置精确条件查找数据
filter_by 只支持一个等号作为判断条件，而且字段左边不需要声明模型类名（money=1000）
可以用于获取一条数据，也可以获取多条数据
student = Student.query.filter_by(money=1000).first()
4.filter多条件查询
多条件需要基于逻辑运算来编写，当然，可以其他的声明方式
"""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() """or_ 或者，或""" from sqlalchemy import or_ student_list = Student.query.filter( or_(Student.age > 17, Student.age < 15) ).all() """not_ 排除，非""" from sqlalchemy import not_ student_list = Student.query.filter(not_(Student.age > 17)).all()
5.filter值范围查询
"""filter值范围查询""" # 查询年龄= 15或者17或者19的 student_list = Student.query.filter(Student.age.in_([15,17,19])).all()
4.order_by
order_by:对结果进行排序
"""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()
5.count
count:统计结果数量
"""count 统计结果数量""" ret = Student.query.filter(Student.age>17).count()
6.limit&offset
limit:对结果数量进行限制
offset:对查询开始位置进行设置[偏移量]
"""limit 结果数量进行限制""" """offset 对查询开始位置进行设置""" # 对学生的钱包进行从大到小排名，第3-第5名的学生 student_list = Student.query.order_by(Student.money.desc()).offset(2).limit(3).all()
7.paginate
paginate:分页器
paginate的参数:
paginate(page=当前页码, per_page=每一页数据量, max_per_page=每一页最大数据量)
关于分页paginate需要知道的:
1.当前页码，默认是request.args["page"]，如果当前参数没有值，则默认为1
2.每一页数据量，默认是100条
3.因为分页器有提供了一个 request.args.["per_page"]给客户端设置每一页数据量，所以可以限定客户端最多能设置的每一页数据量
"""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.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 ) # 下一页的数据列表
8.group_by
group_by:分组查询
group_by
""" group_by 分组查询""" # 查询男生和女生的最大年龄 ret = db.session.query(Student.sex,func.max(Student.age)).group_by(Student.sex).all()
group_by+having
# 查询出男生和女生年龄大于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()
Tip:在flask中执行原生SQL语句
"""执行原生SQL语句，返回结果不是模型对象, 是列表和元组""" # 查询多条 ret = db.session.execute("select id,name,age,IF(sex,'男','女') from tb_student").fetchall() # 查询单条 ret = db.session.execute("select * from tb_student where id = 3").fetchone() # 添加/修改/删除 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()
2.关联查询
1.常用的SQLAlchemy关系选项
| 说明 | |
|---|---|
| backref | 在关系的另一模型中添加反向引用,用于设置外键名称,在1查多的 |
| primary join | 明确指定两个模型之间使用的连表条件 |
| lazy | 指定如何加载关联模型数据的方式。参数值:<br>select（立即加载，查询所有相关数据显示，相当于lazy=True）<br>subquery（立即加载，但使用子查询）<br>dynamic（不加载记录，但提供加载记录的查询对象） |
| uselist | 如果为False，不使用列表，而使用标量值。<br>一对一关系中，需要设置relationship中的uselist=Flase，其他数据库操作一样。 |
| secondary | 指定多对多关系中关系表的名字。<br>多对多关系中，需建立关系表，设置 secondary=关系表 |
| secondary join | 在SQLAlchemy中无法自行决定时，指定多对多关系中的二级连表条件 |
2.一对一
一对一：分为主表和附加表
1.主表中写relationship，附加表中写Foreignkey
2.relationship:关联属性，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，不存在于mySQL中
3.relationship的参数backref: 反向引用，类似django的related，通过外键模型查询主模型数据时的关联属性,因为是一对一，所以值为own
一对一表关系建立
class Student(db.Model):own """个人信息主表""" .... # 关联属性，这个不会被视作表字段，只是模型的属性。 # 因为StudentInfo和Student是一对一的关系，所以uselist=False表示关联一个数据 info = db.relationship("StudentInfo",uselist=False,backref="own") class StudentInfo(db.Model): """个人信息附加表""" # 外键， # 1.如果是一对一，则外键放在附加表对应的模型中 # 2.如果是一对多，则外键放在多的表对象的模型中 uid = db.Column(db.Integer, db.ForeignKey(Student.id),comment="外键")
一对一模型操作
def index(): """1对1模型操作"""
# 1.获取数据[从主表读取数据，获取附加表数据] student = Student.query.get(3) print( student.info.address ) print( student.info.edu ) # 2.获取数据[从附加表读取数据，获取主表数据] student_info = StudentInfo.query.filter(StudentInfo.address=="象牙山村").first() print(student_info.own.name) # 3.添加数据[添加数据，把关联模型的数据也一并添加] student = Student(name="liu", sex=True, age=22, email="33523@qq.com", money=100) student.info = StudentInfo(address="深圳市宝安区创业2路103号", edu="本科") db.session.add(student) db.session.commit() # 4.修改数据[通过主表可以修改附加表的数据，也可以通过附加表模型直接修改主表的数据] student = Student.query.get(4) student.info.address = "广州市天河区天河东路103号" db.session.commit()
"""删除数据""" student = Student.query.get(2) db.session.delete(student.info) # 先删除外键模型，再删主模型 db.session.delete(student) db.session.commit()
3.一对多
一对多表关系建立
class Teacher(db.Model): ... # 关联属性，一的一方添加模型关联属性 course = db.relationship("Course", uselist=True, backref="teacher",lazy='dynamic') class Course(db.Model): ... # 外键，多的一方模型中添加外键 teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id))
-
-
第二个参数backref为类Teacher申明新属性的方法
-
第三个参数lazy决定了什么时候SQLALchemy从数据库中加载数据
-
lazy='subquery'，查询当前数据模型时，采用子查询(subquery)，把外键模型的属性也瞬间查询出来了。
-
lazy=True或lazy='select'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性时，才进行连表查询数据[执行SQL]
-
lazy='dynamic'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性并操作外键模型具体属性时，才进行连表查询数据[执行SQL]
一对多模型操作
def more(): """一对多/多对一模型操作""" # 1.从'一'的一方的模型中获取'多'的一方模型的数据 teacher = Teacher.query.get(1) for course in teacher.course: print(course.name,course.price) # 2.从'多'的一方获取'一'的一方数据 course = Course.query.get(1) print(course.teacher) print(course.teacher.name) # 3.添加数据 # 从'一'的一方添加数据，同时给'多'的一方也添加 teacher = Teacher(name="蓝老师",option="讲师") teacher.course = [Course(name="插画入门",price=199.00),Course(name="素描入门",price=129.00),] db.session.add(teacher) db.session.commit()
"""更新数据""" 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()
4.多对多
多对多表关系建立
"""以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')), ) '''两张表通过secondary关联第三张表''' class Course(db.Model): ... students = db.relationship('Student',secondary=achievement, backref='courses', lazy='dynamic') class Student(db.Model): course_list = db.relationship("Course", secondary=achievement,backref="student_list",lazy="dynamic")
多对多模型操作
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(course.student_list.all()) # 获取所有学生信息 """更新""" course = Course.query.filter(Course.name == "和羊做朋友，一起坑爹").first() course.student_list[0].name="小灰灰" db.session.commit()
