day95:flask:SQLAlchemy資料庫查詢進階&關聯查詢

Poke發表於2020-11-23

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

20240719資料庫關聯查詢、條件查詢
2024-07-19
資料庫
資料庫高階查詢之子查詢
2018-07-15
資料庫
Flask——資料的查詢
2019-08-24
Flask
Python—Django：關於在Django框架中對資料庫的查詢函式，查詢集和關聯查詢
2020-10-31
PythonDjango框架資料庫函式
sqlalchemy 遞迴查詢
2024-07-15
SQL遞迴
多個異構資料庫如何關聯查詢
2020-06-16
資料庫
hyperf關聯子表查詢主表資料
2021-12-10
Mysql 資料庫 -------- SQL語句進階查詢 ------- 前部分
2020-10-16
MySql資料庫
langchain_chatchat+ollama部署本地知識庫，聯網查詢以及對資料庫(Oracle)資料進行查詢
2024-11-16
LangChain資料庫Oracle
資料庫基礎查詢--單表查詢
2018-07-15
資料庫
區分關聯子查詢和非關聯子查詢
2019-05-21
MySQL資料庫基礎——多表查詢：子查詢
2020-10-30
MySql資料庫
Jemter查詢資料庫
2024-03-15
資料庫
求助：資料庫查詢
2020-08-16
資料庫
ThinkPHP 資料庫查詢
2019-12-18
PHP資料庫
資料庫排序查詢
2020-10-02
資料庫排序
查詢資料庫大小
2021-09-09
資料庫
java 分庫關聯查詢工具類
2021-09-09
Java
python資料庫-MySQL資料庫高階查詢操作(51)
2019-07-11
Python資料庫MySql
Logtail：像查詢資料庫一樣查詢日誌
2022-02-22
AI資料庫
java中資料庫查詢，搭配簡單的圖形介面進行查詢
2021-10-10
Java資料庫
資料庫學習（六）聯合查詢ｕｎｉｏｎ
2019-01-29
資料庫
JPA多表關聯查詢
2018-05-15
Mongodb 關聯表查詢
2021-09-26
MongoDB
Laravel Eloquent 關聯模型查詢快取資料
2020-11-15
Laravel模型快取
關係型資料庫查詢語言 SQL 和圖資料庫查詢語言 nGQL 對比
2020-07-23
資料庫SQL
資料庫查詢語句
2018-12-21
資料庫
資料庫查詢優化
2020-10-05
資料庫優化
sqlalchemy在python中的使用(關於查詢)二
2019-01-03
SQLPython
MySQL - 資料查詢 - 簡單查詢
2020-12-27
MySql
資料庫全表查詢之-分頁查詢優化
2020-12-31
資料庫優化
如何使用Java Streams進行資料庫查詢？
2018-09-14
Java資料庫
MySQL 優化五（關聯查詢子查詢以及 in 的效率問題）（高階篇）
2020-04-11
MySql優化
海關資料查詢系統「查詢平臺分類」
2023-03-07
python資料庫-mongoDB的高階查詢操作(55)
2019-07-17
Python資料庫MongoDB
關於Oracle資料庫的時間查詢
2018-06-06
Oracle資料庫
SQLAlchemy in 查詢空列表問題分析
2019-02-16
SQL
在Sqlalchemy的查詢中新共享filter
2022-03-07
SQLFilter