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

目錄

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查詢過濾器

2.常用的SQLAlchemy查詢結果的方法

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關系選項

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="[email protected]", 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()

標籤：Python

上一篇：Python爬蟲初學：爬取電影天堂資料

下一篇：教你怎樣用Python畫了一棵圣誕樹，快來學習