8-flask框架-数据的基本查询

一、常用的SQLAlchemy查询过滤器

过滤器	说明
filter()	把过滤器添加到原查询上，返回一个新查询
filter_by()	把等值过滤器添加到原查询上，返回一个新查询
limit()	使用指定的值限定原查询返回的结果数量
offset()	设置结果范围的开始位置，偏移原查询返回的结果，返回一个新查询
order_by()	根据指定条件对原查询结果进行排序，返回一个新查询
group_by()	根据指定条件对原查询结果进行分组，返回一个新查询

二、常用的SQLAlchemy查询结果的方法

方法	说明
all()	以列表形式返回查询的所有结果
first()	返回查询的第一个结果，如果未查到，返回None
first_or_404()	返回查询的第一个结果，如果未查到，返回404
get()	返回指定主键对应的行，如不存在，返回None
get_or_404()	返回指定主键对应的行，如不存在，返回404
count()	返回查询结果的数量
paginate()	返回一个Paginate分页器对象，它包含指定范围内的结果
having	返回结果中符合条件的数据，必须跟在group by后面，其他地方无法使用。

2.1 get()

参数为数字，表示根据主键查询数据，如果主键不存在返回None

Student.query.get()

# # 根据主键获取一条数据
# student = Student.query.get(4)
# print(student)

2.2 all()

返回查询到的所有对象

Student.query.all()

# # 返回所有结果数据数据
# student_list = Student.query.all()
# print(student_list)

2.3count()

返回结果的数量

# 返回结果的数量
ret = Student.query.filter(Student.id<5).count()
print(ret)

2.4 first()

返回查询到的第一个对象【first获取一条数据,all获取多条数据】

Student.query.first()

# # 返回第一个结果数据
# first_student = Student.query.filter(Student.id<5).first()
# print(first_student)

2.5 filter条件查询

支持各种运算符和查询方法或者模糊查询方法。

# 模糊查询
# 使用163邮箱的所有用户
student_list = Student.query.filter(Student.email.endswith("@163.com")).all()
print(student_list)

# 姓名以"zh"开头的
student_list = Student.query.filter(Student.name.startswith("zh")).all()
print(student_list)

# 名字中带有"a"字母的数据
student_list = Student.query.filter(Student.name.contains("a")).all()
print(student_list)
    
# 也可以使用filter进行精确查找，
# 则需要指定条件格式为: 模型.字段 比较运算符 值。
# 运算符可以是: ==表示相等,!=不相等，> 表示大于  < 表示小于，>=大于等于，<=小于等于

# 单条件比较
student_list = Student.query.filter(Student.age>18).all()
print(student_list)

# 多条件比较
# 要求多个条件都要满足
student_list = Student.query.filter(Student.age>18, Student.sex==True).all()
print(student_list)

    
# 另一种写法的查询方式
# db.session.query(Student) 相当于 Student.query
# ret = db.session.query(Student).filte

2.6、filter_by精确查询

只支持字段的值是否相等这种条件

# 单条件
student_list = Student.query.filter_by(age=22).all()
print(student_list)

# 多条件
student_list = Student.query.filter_by(age=22,sex=True).all()
print(student_list)

练习

查询所有男生数据
# ret = Student.query.filter(Student.sex==True).all()
# ret = Student.query.filter_by(sex=True).all()

查询id为4的学生[3种方式]
# ret = Student.query.filter(Student.id==4).first()
# ret = Student.query.get(4)
# ret = Student.query.filter_by(id=4).first()
 
查询年龄等于22的所有学生数据
# ret = Student.query.filter_by(age=22).all()
# ret = Student.query.filter(Student.age==22).all()

查询name为liu的学生数据
# ret = Student.query.filter(Student.name == "liu").all()
# ret = Student.query.filter_by(name="liu").all()

三、多条件查询

3.1 逻辑非

返回名字不等于wang的所有数据

Student.query.filter(Student.name!='wang').all()

not_ 相当于取反

from sqlalchemy import not_
Student.query.filter(not_(Student.name=='wang')).all()

# # 查询年龄不等于22
# student_list = Student.query.filter(Student.age != 22).all()
# print(student_list)
# student_list = Student.query.filter(not_(Student.age==22)).all()
# print(student_list)

3.2 逻辑与

需要导入and，返回and()条件满足的所有数据

and_(条件1,条件2,....) 等价于 filter(条件1,条件2,.....)

from sqlalchemy import and_
Student.query.filter(and_(Student.name!='wang',Student.email.endswith('163.com'))).all()

3.3 逻辑或

from sqlalchemy import or_
Student.query.filter(or_(Student.name!='wang',Student.email.endswith('163.com'))).all()

# # 查询性别为True，或者年龄大于18
# sex = 1 or age > 18
# student_list = Student.query.filter(
#     or_(
#         Student.sex==True,
#         Student.age>18
#     )
# ).all()
# print(student_list)


# 复合条件的查询情况
# 查询18岁的女生或者22岁的男生
# (age=18 and sex=0) or (age = 22 and sex=1)
# student_list = Student.query.filter(
#     or_(
#         and_(Student.age==18, Student.sex==False),
#         and_(Student.age==22, Student.sex==True),
#     )
# ).all()

# print( student_list )

3.4 、in_范围查询

"""查询id为2, 3, 5, 7, 8这几个学生信息"""
# 查询id是 1 3 5 的学生信息
student_list = Student.query.filter(Student.id.in_([1, 3, 5])).all()
print(student_list)

# 查询id不是 1 3 5 的学生信息
student_list = Student.query.filter(not_(Student.id.in_([1, 3, 5]))).all()
print( student_list )

3.5 order_by 排序

# 倒序[值从大到小]
student_list = Student.query.order_by(Student.id.desc()).all()
# 升序[值从小到大]
student_list = Student.query.order_by(Student.id.asc()).all()

# 多字段排序[第一个字段值一样时，比较第二个字段，进行排序]
student_list = Student.query.order_by(Student.age.desc(), Student.id.asc() ).all()
print(student_list)

3.6 count统计

# 查询age>=19的男生的数量
from sqlalchemy import and_
# ret = Student.query.filter( and_(Student.age>=19,Student.sex==True) ).count()
ret = Student.query.filter( Student.age>=19, Student.sex==True ).count()

3.7 offset和limit

对结果进行偏移量和数量的限制

"""限制结果数量"""
student_list = Student.query.limit(2).all()
print(student_list)

 """结果返回的开始下标位置,从0开始"""
student_list = Student.query.offset(0).limit(2).all()
print(student_list)
student_list = Student.query.limit(2).offset(2).all()
print(student_list)

四、分页器

4.1 分页器常用属性

@app.route("/")
def index():
    # 分页器
    page = int(request.args.get("page",1))
    # page 当前页码  per_page:每页显示的数据个数
    paginate = Student.query.paginate(page=page,per_page=3)

    print(paginate.items) # 当前页显示的数据项
    print(paginate.pages) # 总页码
    print(paginate.page)  # 当前页码
    print(paginate.has_prev) # 是否有上一页
    print(paginate.prev_num) # 上一页页码
    print(paginate.prev)     # 上一页的分页器对象
    print(paginate.has_next) # 是否有下一页
    print(paginate.next_num) # 下一页页码
    print(paginate.next)     # 下一页的分页器对象

    data = {
        "error":0,
        "errmsg": "ok",
        "data": {
            "items": [data.to_dict for data in paginate.items],
            "pages": paginate.pages,
            "page": paginate.page,
            "has_prev": paginate.has_prev,
            "prev_num": paginate.prev_num,
            "has_next": paginate.has_next,
            "next_num": paginate.next_num,
        }
    }

    return data

4.2 前后端不分离实例

  1 from flask import Flask,render_template,request
  2 from flask_sqlalchemy import SQLAlchemy
  3 
  4 app = Flask(__name__)
  5 
  6 class Config(object):
  7     DEBUG = True
  8     # 数据库连接配置
  9     # SQLALCHEMY_DATABASE_URI = "数据库类型://数据库账号:密码@数据库地址:端口/数据库名称?charset=utf8mb4"
 10     SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
 11     # 动态追踪修改设置，如未设置只会提示警告
 12     SQLALCHEMY_TRACK_MODIFICATIONS = True
 13     # 查询时会显示原始SQL语句
 14     SQLALCHEMY_ECHO = True
 15 
 16 app.config.from_object(Config)
 17 
 18 
 19 """模型类定义"""
 20 db = SQLAlchemy(app=app)
 21 # 等同于
 22 # db = SQLAlchemy()
 23 # db.init_app(app)
 24 
 25 class Student(db.Model):
 26     """学生信息模型"""
 27     # 声明与当前模型绑定的数据表名称
 28     __tablename__ = "db_students"
 29     # 字段定义
 30     """
 31     create table db_student(
 32       id int primary key auto_increment comment="主键",
 33       name varchar(15) comment="姓名",
 34     )
 35     """
 36     id = db.Column(db.Integer, primary_key=True,comment="主键")
 37     name = db.Column(db.String(15), comment="姓名")
 38     age = db.Column(db.SmallInteger, comment="年龄")
 39     sex = db.Column(db.Boolean, default=True, comment="性别")
 40     email = db.Column(db.String(128), unique=True, comment="邮箱地址")
 41     money = db.Column(db.Numeric(10,2), default=0.0, comment="钱包")
 42 
 43     def __repr__(self):
 44         return f"{self.name}<Student>"
 45 
 46     @classmethod
 47     def add(cls):
 48         student = cls(name="小明", sex=True, age=17, email="123456@qq.com", money=100)
 49         db.session.add(student)
 50         db.session.commit()
 51         return student
 52 
 53     @property
 54     def to_dict(self):
 55         """把对象转化成字典"""
 56         return {
 57             "id": self.id,
 58             "name": self.name,
 59             "age": self.age,
 60             "sex": self.sex,
 61             "email": self.email,
 62             "money": float("%.2f" % self.money),
 63         }
 64 
 65 # 所有的模型必须直接或间接继承于db.Model
 66 class Course(db.Model):
 67     """课程数据模型"""
 68     __tablename__ = "db_course"
 69     id = db.Column(db.Integer, primary_key=True, comment="主键")
 70     name = db.Column(db.String(64), unique=True, comment="课程")
 71     price = db.Column(db.Numeric(7, 2))
 72     # repr()方法类似于django的__str__，用于打印模型对象时显示的字符串信息
 73     def __repr__(self):
 74         return f'{self.name}<Course>'
 75 
 76 class Teacher(db.Model):
 77     """老师数据模型"""
 78     __tablename__ = "db_teacher"
 79     id = db.Column(db.Integer, primary_key=True, comment="主键")
 80     name = db.Column(db.String(64), unique=True, comment="姓名")
 81     option = db.Column(db.Enum("讲师", "助教", "班主任"), default="讲师")
 82 
 83     def __repr__(self):
 84         return f"{self.name}< Teacher >"
 85 
 86 @app.route("/")
 87 def index():
 88     # 分页器
 89     page = int(request.args.get("page",1)) # 页码
 90     size = int(request.args.get("size",5)) # 每一页数据量
 91     pagination = Student.query.paginate(page=page,per_page=size)
 92     data = {}
 93     data["pagination"] = pagination
 94     return render_template("list.html",**data)
 95 
 96 
 97 if __name__ == '__main__':
 98     with app.app_context():
 99         # 检测数据库中是否存在和模型匹配的数据表。
100         # 如果没有，则根据模型转换的建表语句进行建表。
101         # 如果找到，则不会进行额外处理
102 
103         db.create_all()
104     app.run(debug=True)

服务器端

同一级目录下 templates/list.html

 1 <!DOCTYPE html>
 2 <html lang="en">
 3 <head>
 4     <meta charset="UTF-8">
 5     <title>Title</title>
 6     <style>
 7     .page a,.page span{
 8         padding: 2px 6px;
 9         color: #fff;
10         background: #6666ff;
11         text-decoration: none;
12     }
13     .page span{
14         color: #fff;
15         background: orange;
16     }
17 
18     </style>
19 </head>
20 <body>
21     <table border="1" align="center" width="600">
22         <tr>
23            <th>ID</th>
24            <th>age</th>
25            <th>name</th>
26            <th>sex</th>
27            <th>money</th>
28         </tr>
29         {% for student in pagination.items %}
30         <tr>
31            <td>{{ student.id }}</td>
32            <td>{{ student.age }}</td>
33            <td>{{ student.name }}</td>
34            <td>{{ "男" if student.sex else "女" }}</td>
35            <td>{{ student.money }}</td>
36         </tr>
37         {% endfor %}
38         <tr align="center">
39             <td colspan="5" class="page">
40                 {% if pagination.has_prev %}
41                 <a href="?page=1">首  页</a>
42                 <a href="?page={{ pagination.page-1 }}">上一页</a>
43                 <a href="?page={{ pagination.page-1 }}">{{ pagination.page-1 }}</a>
44                 {% endif %}
45                 <span>{{ pagination.page }}</span>
46                 {% if pagination.has_next %}
47                 <a href="?page={{ pagination.page+1 }}">{{ pagination.page+1 }}</a>
48                 <a href="?page={{ pagination.page+1 }}">下一页</a>
49                 <a href="?page={{ pagination.pages }}">尾  页</a>
50                 {% endif %}
51             </td>
52         </tr>
53     </table>
54 </body>
55 </html>

前端代码

五、分组查询

分组查询和分组查询结果过滤

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

from sqlalchemy import func

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

代码：

# 查询当前所有男生女生的数量
# ret = db.session.query(Student.sex,func.count(Student.id)).group_by(Student.sex).all()
# print(ret)

# 查看当前学生中各个年龄段的学生人数
# ret = db.session.query(Student.age, func.count(Student.id)).group_by(Student.age).all()
# print(ret)

# 查看当前男生女生的平均年龄
# ret = db.session.query(Student.sex, func.avg(Student.age)).group_by(Student.sex).all()
# ret = [{"sex":"男" if item[0] else "女","age":float(item[1])} for item in ret]
# print(ret)

# 分组后的过滤操作 having
# 在所有学生中，找出各个年龄中拥有最多钱的同学，并在这些同学里面筛选出money > 2000的数据
subquery = func.max(Student.money)
ret = db.session.query(Student.age, subquery).group_by(Student.age).having(subquery > 2000).all()
print(ret) # [(18, Decimal('1000.00')), (22, Decimal('26000.00')), (23, Decimal('1998.00'))]

六、执行原生SQL语句

# # 查询多条数据
# ret = db.session.execute("select * from db_students").fetchall()
# # 查询一条数据
# ret = db.session.execute("select * from db_students").fetchone()
#
# # 添加/删除/更新
# db.session.execute("UPDATE db_students SET money=(db_students.money + %s) WHERE db_students.age = %s" % (200, 22))
# db.session.commit()

# db.session.execute("insert db_students (name,age,sex,email,money) select name,age,sex,concat(now(),email),money from db_students")
# db.session.commit()
return "ok"