day95:flask:SQLAlchemy数据库查询进阶&关联查询

一时失言乱红尘 2022-09-12 01:48 475阅读 0赞

## 1.数据库查询-进阶 ##

### 1.常用的SQLAlchemy查询过滤器 ###

<table> 
 <thead> 
  <tr> 
   <th>过滤器</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>filter()</td> 
   <td>把过滤器添加到原查询上，返回一个新查询</td> 
  </tr> 
  <tr> 
   <td>filter_by()</td> 
   <td>把等值过滤器添加到原查询上，返回一个新查询</td> 
  </tr> 
  <tr> 
   <td>limit()</td> 
   <td>使用指定的值限定原查询返回的结果</td> 
  </tr> 
  <tr> 
   <td>offset()</td> 
   <td>偏移原查询返回的结果，返回一个新查询</td> 
  </tr> 
  <tr> 
   <td>order_by()</td> 
   <td>根据指定条件对原查询结果进行排序，返回一个新查询</td> 
  </tr> 
  <tr> 
   <td>group_by()</td> 
   <td>根据指定条件对原查询结果进行分组，返回一个新查询</td> 
  </tr> 
 </tbody> 
</table>

### 2.常用的SQLAlchemy查询结果的方法 ###

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

### 3.filter ###

1.filter设置判断条件

**== != >= <= < >**

student = Student.query.filter(Student.name=="xiaohui32号").first()
        if student is None:
            return jsonify({"error":"100404","errmsg":"没有该学生信息！"})

2.filter设置模糊查询

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

# 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()

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

3.filter\_by设置精确条件查找数据

filter\_by 只支持一个等号作为判断条件，而且字段左边不需要声明模型类名（money=1000）

可以用于获取一条数据，也可以获取多条数据

student = Student.query.filter_by(money=1000).first()

4.filter多条件查询

多条件需要基于逻辑运算来编写，当然，可以其他的声明方式

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

"""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()

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

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关系选项 ###

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

### 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的关系。第一个参数为对[ python教程][python]应参照的类"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()

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

"""更新数据"""
        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()

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

### 4.多对多 ###

多对多表关系建立

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

"""以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")

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

多对多模型操作

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

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()

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

[48304ba5e6f9fe08f3fa1abda7d326ab.png]: /images/20220828/20089a1820ce4f5b88efd6cf6bd8f96e.png
[python]: https://www.xin3721.com/eschool/pythonxin3721/