數(shù)據(jù)庫查詢

1.常用的SQLAlchemy查詢過濾器

過濾器.png

2.常用的SQLAlchemy查詢結(jié)果的方法

方法.png

3.filter

3.1 filter設(shè)置判斷條件

== != >= <= < >

student = Student.query.filter(Student.name=="xiaohui32號").first()
    if student is None:
        return jsonify({"error":"100404","errmsg":"沒有該學(xué)生信息！"})

3.2 filter設(shè)置模糊查詢

# like模糊條件
    # 模型.字段.like("%值%")  等價于  模型.字段.contains("值")    包含xxx
    # 模型.字段.like("值%")   等價于  模型.字段.startswith("值")  以xxx開頭
    # 模型.字段.like("%值")   等價于  模型.字段.endswith("值")    以xxx結(jié)尾
    # 模型.字段.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.3 filter_by設(shè)置精確條件查找數(shù)據(jù)

filter_by 只支持一個等號作為判斷條件困后，而且字段左邊不需要聲明模型類名（money=1000）
可以用于獲取一條數(shù)據(jù)搅裙，也可以獲取多條數(shù)據(jù)

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

3.4 filter多條件查詢

"""filter多條件查詢"""
    # 多條件需要基于邏輯運算來編寫睦刃，當(dāng)然搬俊，可以其他的聲明方式
    """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()

    """ IS NULL"""
session.query(Order).filter(Order.date_shipped == None).all()
#[<Order:1>, <Order:2>, <Order:3>, <Order:4>, <Order:5>, <Order:6>, <Order:7>]
session.query(Order).filter(Order.date_shipped.is_(None)).all()
# [<Order:1>, <Order:2>, <Order:3>, <Order:4>, <Order:5>, <Order:6>, <Order:7>]

    """ IS NOT NULL"""
session.query(Order).filter(Order.date_shipped != None).all()
#[]
session.query(Order).filter(Order.date_shipped.isnot(None)).all()
#[]

企業(yè)微信截圖_16546067066959.png

3.5 filter值范圍查詢

 """filter值范圍查詢"""
# 查詢年齡= 15或者17或者19的
student_list = Student.query.filter(Student.age.in_([15,17,19])).all()

 """ NOT IN""
session.query(Customer).filter(Customer.first_name.notin_(['Toby', 'Sarah'])).all()

 """ BETWEEN""
session.query(Item).filter(Item.cost_price.between(10, 50)).all()

 """ NOT BETWEEN""
session.query(Item).filter(not_(Item.cost_price.between(10, 50))).all()

4.order_by

    """order_by結(jié)果排序"""
    # 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 統(tǒng)計結(jié)果數(shù)量"""
    ret = Student.query.filter(Student.age>17).count()

6.limit&offset

"""limit 結(jié)果數(shù)量進(jìn)行限制"""
"""offset 對查詢開始位置進(jìn)行設(shè)置"""
# 對學(xué)生的錢包進(jìn)行從大到小排名户敬，第3-第5名的學(xué)生
student_list = Student.query.order_by(Student.money.desc()).offset(2).limit(3).all()

7.paginate

"""paginate分頁器"""
    # paginate(page=當(dāng)前頁碼, per_page=每一頁數(shù)據(jù)量, max_per_page=每一頁最大數(shù)據(jù)量)
    # 當(dāng)前頁碼，默認(rèn)是從request.args["page"]睁本，如果當(dāng)前參數(shù)沒有值尿庐，則默認(rèn)為1
    # 每一頁數(shù)據(jù)量，默認(rèn)是100條
    # 因為分頁器有提供了一個  request.args.["per_page"]給客戶端設(shè)置每一頁數(shù)據(jù)量呢堰，所以可以限定客戶端最多能設(shè)置的每一頁數(shù)據(jù)量
    pagination = Student.query.filter(Student.sex==True).paginate(per_page=1)
   # isinstance(data, Pagination)
    print( pagination.items ) # 獲取當(dāng)前頁數(shù)據(jù)量
    print( pagination.has_next ) # 如果還有下一頁數(shù)據(jù)抄瑟，則結(jié)果為True
    print( pagination.has_prev ) # 如果有上一頁數(shù)據(jù)，則結(jié)果為True
    print( pagination.page ) # 當(dāng)前頁頁碼 request.args.get("page",1)
    print( pagination.total ) # 本次查詢結(jié)果的數(shù)據(jù)總量[被分頁的數(shù)據(jù)量總數(shù)]
    print( pagination.pages )   # 總頁碼
    print( pagination.prev() ) # 上一頁的分頁器對象枉疼，如果沒有上一頁锐借，則默認(rèn)為None
    print( pagination.next() ) # 下一頁的分頁器對象，如果沒有下一頁往衷，則默認(rèn)為None
    if pagination.has_next:
        print( pagination.next().items ) # 下一頁的數(shù)據(jù)列表

8.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的人數(shù)
# having是針對分組的結(jié)果進(jìn)行過濾處理钞翔，所以having能調(diào)用的字段，必須是分組查詢結(jié)果中的字段席舍，否則報錯２冀巍！
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中執(zhí)行原生SQL語句

"""執(zhí)行原生SQL語句来颤，返回結(jié)果不是模型對象, 是列表和元組"""
    # 查詢多條
    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()
    
    sql_one = "select count(*) from xxx where bus_id=:bus_id and user_id=:user_id and create_time>:create_time and  state = 1"
    lists_one = db.session.execute(sql_one, {"bus_id": bus_id, "user_id": user_id, "create_time": time_before_day})
    data = lists_one.fetchone()

    # 添加/修改/刪除
    db.session.execute("UPDATE tb_student SET money=(money + %s) WHERE age = %s" % (200, 22))
    db.session.commit()

    # 查詢出女生和男生中大于18歲的人數(shù)
    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()[圖片上傳中...(關(guān)系選項.png-676c6-1629264660767-0)]

多表查詢

print( session.query(User.username,UserDetails.lost_login).join(UserDetails,UserDetails.id==User.id).all() )  #這個是inner join
 
@cache.memoize(timeout=3600 * 12)
def _get_activites(activity_type, today):
    query = Activity.query \
                    .join(ActivityType,
                          Activity.id == ActivityType.activity_id) \
                    .filter(ActivityType.show_type == activity_type,
                            Activity.is_publish == 1,
                            Activity.start <= today,
                            Activity.end > today) \
                    .order_by(Activity.publish_time)
    return query.all()

query 中參數(shù)的順序很重要汰扭，
第一個參數(shù)所代表的 table 就是 JOIN 時放在前面的那個 table。
因此福铅，此處 JOIN 的目標(biāo)應(yīng)該是 Activity 而不應(yīng)該是 ActivityType 自身萝毛。

print( session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id).all() )  #這個才是左連接，sqlalchemy沒有右連接

關(guān)聯(lián)查詢

1.常用的SQLAlchemy關(guān)系選項

關(guān)系選項.png

2.一對一

一對一：分為主表和附加表
1.主表中寫relationship滑黔，附加表中寫Foreignkey
2.relationship:關(guān)聯(lián)屬性笆包，是SQLAlchemy提供給開發(fā)者快速引用外鍵模型的一個對象屬性，不存在于mySQL中
3.relationship的參數(shù)backref: 反向引用略荡，類似django的related庵佣，通過外鍵模型查詢主模型數(shù)據(jù)時的關(guān)聯(lián)屬性,因為是一對一，所以值為own

class Student(db.Model):own
    """個人信息主表"""
    ....
    # 關(guān)聯(lián)屬性汛兜，這個不會被視作表字段巴粪，只是模型的屬性。
    # 因為StudentInfo和Student是一對一的關(guān)系，所以uselist=False表示關(guān)聯(lián)一個數(shù)據(jù)
    info = db.relationship("StudentInfo",uselist=False,backref="own")


class StudentInfo(db.Model):
    """個人信息附加表"""

    # 外鍵肛根，
    # 1.如果是一對一辫塌，則外鍵放在附加表對應(yīng)的模型中
    # 2.如果是一對多，則外鍵放在多的表對象的模型中
    uid = db.Column(db.Integer, db.ForeignKey(Student.id),comment="外鍵")

一對一模型操作

def index():
    """1對1模型操作"""
    # 1.獲取數(shù)據(jù)[從主表讀取數(shù)據(jù)派哲，獲取附加表數(shù)據(jù)]
    student = Student.query.get(3)
    print( student.info.address )
    print( student.info.edu )

    # 2.獲取數(shù)據(jù)[從附加表讀取數(shù)據(jù)臼氨，獲取主表數(shù)據(jù)]
    student_info = StudentInfo.query.filter(StudentInfo.address=="象牙山村").first()
    print(student_info.own.name)

    # 3.添加數(shù)據(jù)[添加數(shù)據(jù)，把關(guān)聯(lián)模型的數(shù)據(jù)也一并添加]
    student = Student(name="liu", sex=True, age=22, email="33523@qq.com", money=100)
    student.info = StudentInfo(address="深圳市寶安區(qū)創(chuàng)業(yè)2路103號", edu="本科")
    db.session.add(student)
    db.session.commit()

    # 4.修改數(shù)據(jù)[通過主表可以修改附加表的數(shù)據(jù)狮辽，也可以通過附加表模型直接修改主表的數(shù)據(jù)]
    student = Student.query.get(4)
    student.info.address = "廣州市天河區(qū)天河?xùn)|路103號"
    db.session.commit()

    """刪除數(shù)據(jù)"""
    student = Student.query.get(2)
    db.session.delete(student.info)  # 先刪除外鍵模型，再刪主模型
    db.session.delete(student)
    db.session.commit()

3.一對多

一對多表關(guān)系建立

class Teacher(db.Model):
    ...
    # 關(guān)聯(lián)屬性巢寡，一的一方添加模型關(guān)聯(lián)屬性
    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的關(guān)系。
在relationship()函數(shù)中通過’foreign_keys=’指定外鍵關(guān)系抑月，在多外鍵的情況下這個參數(shù)必須指定树叽，否則無法對應(yīng)關(guān)聯(lián)關(guān)系。
第一個參數(shù)為對應(yīng)參照的類"Course"
第二個參數(shù)backref為類Teacher申明新屬性的方法
第三個參數(shù)lazy決定了什么時候SQLALchemy從數(shù)據(jù)庫中加載數(shù)據(jù)
lazy='subquery'谦絮，查詢當(dāng)前數(shù)據(jù)模型時题诵，采用子查詢(subquery)，把外鍵模型的屬性也瞬間查詢出來了层皱。
lazy=True或lazy='select'性锭，查詢當(dāng)前數(shù)據(jù)模型時，不會把外鍵模型的數(shù)據(jù)查詢出來叫胖，只有操作到外鍵關(guān)聯(lián)屬性時草冈，才進(jìn)行連表查詢數(shù)據(jù)[執(zhí)行SQL]
lazy='dynamic'，查詢當(dāng)前數(shù)據(jù)模型時瓮增，不會把外鍵模型的數(shù)據(jù)查詢出來怎棱，只有操作到外鍵關(guān)聯(lián)屬性并操作外鍵模型具體屬性時，才進(jìn)行連表查詢數(shù)據(jù)[執(zhí)行SQL]

一對多模型操作

def more():
    """一對多/多對一模型操作"""
    # 1.從'一'的一方的模型中獲取'多'的一方模型的數(shù)據(jù)
    teacher = Teacher.query.get(1)
    for course in teacher.course:
        print(course.name,course.price)

    # 2.從'多'的一方獲取'一'的一方數(shù)據(jù)
    course = Course.query.get(1)
    print(course.teacher)
    print(course.teacher.name)

    # 3.添加數(shù)據(jù)
    # 從'一'的一方添加數(shù)據(jù)绷跑，同時給'多'的一方也添加
    teacher = Teacher(name="藍(lán)老師",option="講師")
    teacher.course = [Course(name="插畫入門",price=199.00),Course(name="素描入門",price=129.00),]
    db.session.add(teacher)
    db.session.commit()

"""更新數(shù)據(jù)"""
    teacher = Teacher.query.filter(Teacher.name == "灰太狼").first()
    teacher.course_list[0].name="抓懶洋洋"
    db.session.commit()

    """刪除數(shù)據(jù)"""
    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.多對多

多對多表關(guān)系建立

"""以db.Table關(guān)系表來確定模型之間的多對多關(guān)聯(lián)"""
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關(guān)聯(lián)第三張表'''
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()) # 獲取所有學(xué)生信息

    """更新"""
    course = Course.query.filter(Course.name == "和羊做朋友谬运，一起坑爹").first()
    course.student_list[0].name="小灰灰"
    db.session.commit()

在relationship()函數(shù)中通過’foreign_keys=’指定外鍵關(guān)系，在多外鍵的情況下這個參數(shù)必須指定垦藏，否則無法對應(yīng)關(guān)聯(lián)關(guān)系吩谦。

1.jpg

flask多對多關(guān)系的查詢、添加膝藕、刪除

#角色模型

class Role(db.Model):

__tablename__='role'
r_id=db.Column(db.Integer,autoincrement=True,primary_key=True)
r_name=db.Column(db.String(10))
user=db.relationship('User',backref='role')

 

#角色和權(quán)限的(多對多的)關(guān)聯(lián)表
#r_p為關(guān)聯(lián)表的表名
r_p=db.Table('r_p',
db.Column('role_id',db.Integer,db.ForeignKey('role.r_id'),primary_key=True),
db.Column('permission_id',db.Integer,db.ForeignKey('permission.p_id'),primary_key=True))

 

#權(quán)限模型表
class Permission(db.Model):
__tablename__='permission'
p_id=db.Column(db.Integer,autoincrement=True,primary_key=True)
p_name=db.Column(db.String(16),unique=True)
p_er=db.Column(db.String(16),unique=True)
#添加多對多的反向引用,必須使用secondary指定中間關(guān)聯(lián)表
#用權(quán)限查詢角色時用查詢到的權(quán)限對象：“權(quán)限對象.roles.all()”得到其對應(yīng)的所有角色
roles=db.relationship('Role',secondary=r_p,backref=db.backref('permission',lazy=True))
#db.backref('permission', 中的permission用來反向關(guān)聯(lián)式廷，用角色查詢其對應(yīng)的所有權(quán)限。用查詢到的 '角色對象.permission.all()'得到芭挽。
###relationship可以放到任意一個類中都行滑废，與之相反蝗肪。###

 

#多對多關(guān)系查詢

#根據(jù)角色找權(quán)限
####多對多關(guān)系中獲取對象,只能用get(id)方法,不能通過filter或者filter_by來獲取###
role=Role.query.get(id)
per=role.permission.all()
return ','.join(c.name for c in per)

 

 

#根據(jù)權(quán)限來找角色
per=Permission.query.get(id)
role=per.roles.all()
return ','.join(i.name for i in role)


#多對多關(guān)系添加
role=Role.query.get(id)
per=Permission.query.get(id)
#給角色添加權(quán)限
role.permission.append(per)


#多對多關(guān)系刪除
role=Role.query.get(id)
per=Permission.query.get(id)
#給角色刪除權(quán)限
role.permission.remove(per)

總結(jié):ORM操作時,多對多關(guān)系的角色權(quán)限表(中間關(guān)聯(lián)表)，不需要用戶維護.