[Python知识库]Day6 ---- SQLAlchemy进阶

昨日回顾

• RequestParser解析请求如何实现?
• 如何序列化数据?
• 如何定制响应的json格式?
• ORM是什么?
• Flask中模型类如何创建?
• 迁移脚本如何创建？如何实现数据库迁移?
• 增删改查语法是什么?

今日内容

• 单表查询详解
• 关系查询
  • 一对多关系
  • 多对多关系
• 优化查询
• Flask-SQLAlchemy执行自定义SQL语句

1. 单表数据查询

1.1 all(), first(), get()的使用

# all():查询所有数据，返回list
user_list = User.query.all()
# 如return marshal(user_list, resource_fields)

# first(): 表中第一条数据，返回对象
user = User.query.first() 

# get(): 根据主键值查询，查询不到返回None
user = User.query.get(1)

1.2 filter(), filter_by实现过滤操作

条件查询提供了filter()与filter_by来实现，filter_by可以看做是filter的简写方式.

# filter
# 获取一个对象
User.query.filter(User.username=='name').first()
# 获取所有的对象
User.query.filter(User.username=="jack").all()

# filter_by()实现 简单的 条件查询
# 必须使用first或all 获取对象
User.query.filter_by(username="tom").first()

1.3 分页与排序

# 表中的所有数据 的分页
# 请求url  /user/user?page=2&pageSize=3
page = request.args.get("page")
page = int(page)
pageSize = request.args.get("pageSize")
pageSize = int(pageSize)

data = User.query.offset((page-1)*pageSize).limit(pageSize).all()
return marshal(data, resource_fields)

# 查询 并分页 过滤返回的是查询语句
User.query.filter_by(age=18).offset(2).limit(3).all()  
# 跳过二条开始查询，限制输出3条
注意: offset与limit顺序调换, 不影响查询结果

# 排序 
User.query.order_by(User.age.asc()).all()

#按照年龄降序排序
User.query.order_by(User.age.desc()).all() 

# 注意顺序
books = Book.query.filter_by(title="红楼梦").order_by(Book.id.asc()).offset(0).limit(1).all()

最后一定要first()或者all()

1.4 逻辑运算与聚合

or_, 或
and_, 与
not_，非
_lt_ 小于

# 逻辑运算
from sqlalchemy import or_, and_, not_

# 查询名字为jeremy或年龄为19的
User.query.filter(or_(User.username =='jeremy',User.age==18)).all()

# 查询名字为tom且年龄为19
User.query.filter(and_(User.name=="j2", User.age==19)).all()

# 查询年龄不是18岁的
User.query.filter(not_(User.age==18)).all()

# 2.比较查询
User.query.filter(User.id.__lt__(5)) # 小于5
User.query.filter(User.id.__le__(5)) # 小于等于5
User.query.filter(User.id.__gt__(5)) # 大于5
User.query.filter(User.id.__ge__(5)) # 大于等于5
# 查询id>1 且id<3的 或者 id>5且id<7的用户
users = User.query.filter(or_(and_(User.id.__gt__(1), User.id.__lt__(3)), and_(User.id.__gt__(6), User.id.__lt__(8)))).all()


# 聚合查询
from sqlalchemy import func
class Index(Resource):
    def get(self):
    	# 统计多少行 [(n,)]
        ret1 = db.session.query(func.count(Stu.age)).all()
        ret2 = db.session.query(func.sum(Stu.age)).all()
        ret3 = db.session.query(func.avg(Stu.age)).all()
        print('统计数量: ', ret1[0][0])
        print('年龄总和:', ret2[0][0])
        print('年龄平均值:', ret3[0][0])
        return {'msg': 'ok'}

2. 关系查询与优化查询

2.1 一对多关系

# 1.一对多关系的建立: 主表建立关系, 子表建立外键

# 主表 模型类
class Sub(db.Model):  # 专业
    __tablename__ = 'sub'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))
    # 主表定义关系, 关联Stu模型, backref的值为子表名
    stu = db.relationship('Stu', backref='stu')   #

    def __str__(self):
        return self.name

# 子表 模型类
class Stu(db.Model): #学生
    __tablename__ = 'stu'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))
    age = db.Column(db.Integer)
    snum = db.Column(db.Integer, unique=True)
    # 子表定义外键, 
    sub_id = db.Column(db.Integer, db.ForeignKey('sub.id')) # 主表名.id
    

    def __str__(self):
        return self.name

# 2.一对多 的查询
# 主表的反向查询
    sub = Sub.query.get(1)
    stus = sub.stu.all()
    
# 子表正向查询:
    stu = Stu.query.get(2)
    stu.sub_id ??
    sub = stu.stu  # 第2个stu为backref的值, 返回一个Sub对象

xxx

2.2 多对多关系

# 多对多关系的建立
	1. 先把两个需要做多对多的模型定义出来
	2. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。
	3. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表模型名。 
    
# 以博客文章与标签为例, 示例如下:
# 中间表
article_tag = db.Table(
    'article_tag',
    db.Column('article_id', db.Integer, db.ForeignKey("article.id"),primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey("tag.id"), primary_key=True)
)


# 文章表
class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64))
    tags = db.relationship('Tag', secondary=article_tag, backref=db.backref('articles'))

    def __repr__(self):
        return self.title

# 标签表
class Tag(db.Model):
    __tablename__ = 'tag'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))

    def __repr__(self):
        return self.name

# 多对多关系的查询
# 1.查询文章所有的标签:
article = Article.query.filter(Article.title="xxx").first()
tags = article.tags

# 2.查询一个标签涉及的所有文章:
tag = Tag.query.get(2)
print('标签: ', tag)
articles = tag.articles  # tag.articles中的articles是Article表中关系中backref的值
print('文章: ', articles)

3.Flask-SQLAlchemy执行自定义SQL语句

# 定义SQL语句
sql = 'select name from stu where id=3'
# 指定SQL语句
db.session.excute(sql)