目錄
1.資料庫查詢-進階
1.常用的SQLAlchemy查詢過濾器
| 說明 | |
|---|---|
| filter() | 把過濾器新增到原查詢上，返回一個新查詢 |
| filter_by() | 把等值過濾器新增到原查詢上，返回一個新查詢 |
| limit() | 使用指定的值限定原查詢返回的結果 |
| offset() | 偏移原查詢返回的結果，返回一個新查詢 |
| order_by() | 根據指定條件對原查詢結果進行排序，返回一個新查詢 |
| group_by() | 根據指定條件對原查詢結果進行分組，返回一個新查詢 |
2.
| 以列表形式返回查詢的所有結果 | |
| 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))
-
-
第二個引數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()