数据库查询

1.常用的SQLAlchemy查询过滤器

过滤器.png

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

方法.png

3.filter

3.1 filter设置判断条件

== != >= <= < >

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

3.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.3 filter_by设置精确条件查找数据

filter_by 只支持一个等号作为判断条件，而且字段左边不需要声明模型类名（money=1000）
可以用于获取一条数据，也可以获取多条数据

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

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

    """ 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()
#[]

企业微信截图_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结果排序"""
    # 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 统计结果数量"""
    ret = Student.query.filter(Student.age>17).count()

6.limit&offset

"""limit 结果数量进行限制"""
"""offset 对查询开始位置进行设置"""
# 对学生的钱包进行从大到小排名，第3-第5名的学生
student_list = Student.query.order_by(Student.money.desc()).offset(2).limit(3).all()

7.paginate

"""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)
   # isinstance(data, Pagination)
    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 分组查询"""
# 查询男生和女生的最大年龄
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()
    
    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岁的人数
    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()[图片上传中...(关系选项.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 中参数的顺序很重要，
第一个参数所代表的 table 就是 JOIN 时放在前面的那个 table。
因此，此处 JOIN 的目标应该是 Activity 而不应该是 ActivityType 自身。

print( session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id).all() )  #这个才是左连接，sqlalchemy没有右连接

关联查询

1.常用的SQLAlchemy关系选项

关系选项.png

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的关系。
在relationship()函数中通过’foreign_keys=’指定外键关系，在多外键的情况下这个参数必须指定，否则无法对应关联关系。
第一个参数为对应参照的类"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()

在relationship()函数中通过’foreign_keys=’指定外键关系，在多外键的情况下这个参数必须指定，否则无法对应关联关系。

1.jpg

flask多对多关系的查询、添加、删除

#角色模型

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

 

#角色和权限的(多对多的)关联表
#r_p为关联表的表名
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))

 

#权限模型表
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指定中间关联表
#用权限查询角色时用查询到的权限对象：“权限对象.roles.all()”得到其对应的所有角色
roles=db.relationship('Role',secondary=r_p,backref=db.backref('permission',lazy=True))
#db.backref('permission', 中的permission用来反向关联，用角色查询其对应的所有权限。用查询到的 '角色对象.permission.all()'得到。
###relationship可以放到任意一个类中都行，与之相反。###

 

#多对多关系查询

#根据角色找权限
####多对多关系中获取对象,只能用get(id)方法,不能通过filter或者filter_by来获取###
role=Role.query.get(id)
per=role.permission.all()
return ','.join(c.name for c in per)

 

 

#根据权限来找角色
per=Permission.query.get(id)
role=per.roles.all()
return ','.join(i.name for i in role)


#多对多关系添加
role=Role.query.get(id)
per=Permission.query.get(id)
#给角色添加权限
role.permission.append(per)


#多对多关系删除
role=Role.query.get(id)
per=Permission.query.get(id)
#给角色删除权限
role.permission.remove(per)

总结:ORM操作时,多对多关系的角色权限表(中间关联表)，不需要用户维护.