目录
1.数据库查询-进阶
深圳市宏鑫市政工程建设有限公司 1.常用的SQLAlchemy查询过滤器
2.常用的SQLAlchemy查询结果的方法
3.filter
4.order_by
5.count
深圳市宏鑫市政工程建设有限公司 6.limit&offset
7.paginate
8.group_by
Tip:在flask中执行原生SQL语句
2.关联查询
1.常用的SQLAlchemy关系选项
2.一对一
3.一对多
4.多对多
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 | 指定如何加载关联模型数据的方式 。参数值: select（立即加载 ，查询所有相关数据显示 ，相当于lazy=True） subquery（立即加载，但使用子查询） dynamic（不加载记录 ，但提供加载记录的查询对象） |
| uselist | 如果为False ，不使用列表，而使用标量值。 一对一关系中 ，需要设置relationship中的uselist=Flase ，其他数据库操作一样 。 |
| secondary | 指定多对多关系中关系表的名字 。 多对多关系中，需建立关系表 ，设置 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()
# 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()
