目录
1.数据库查询-进阶
1.常用的SQLAlchemy查询过滤器
| 过滤器 | 说明 |
|---|---|
| filter() | 把过滤器添加到原查询上，返回一个新查询 |
| filter_by() | 把等值过滤器添加到原查询上，返回一个新查询 |
| limit() | 使用指定的值限定原查询返回的结果 |
| offset() | 偏移原查询返回的结果，返回一个新查询 |
| order_by() | 根据指定条件对原查询结果进行排序，返回一个新查询 |
| group_by() | 根据指定条件对原查询结果进行分组，返回一个新查询 |
2.常用的SQLAlchemy查询结果的方法
| all() | 以列表形式返回查询的所有结果 |
| 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))
其中realtionship描述了Course和Teacher的关系。第一个参数为对应参照的类"Course"
第二个参数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()