WEB后端服务-Flask第五天

WEB后端服务-Flask第五天

一、聚合查询

SQLAlchemy 支持聚合查询， 使用db.session.query()查询和db.func.聚合函数()。

常用的聚合查询函数：

• db.func.count(模型类的字段) 统计
• db.func.sum() 求和
• db.func.min() 最小值
• db.func.max() 最大值
• db.func.avg() 平均值

以上的聚合函数与mysql中聚合函数一一对应的。可以在聚合函数后面使用.label()设置别名。

另外，聚合函数中使用模型类字段必须出现在 .group_by()函数中。当然也可以在group_by()之后使用having() 设置聚合字段结果的条件，如总财富不少于多少钱， 总人数不少于多人等。在having()中可以使用.label()设定的别名。

def test_sum_money(self):
    app.app_context().push()

    # 查询每个人的财富 riches
    result = db.session.query(Card.user_id,
                              db.func.sum(Card.money).label('total'))\
                       .group_by(Card.user_id)\
                       .having(db.Column('total').__ge__(20000))\
                       .order_by(db.Column('total').desc()).all()
    print(type(result))  # list[(user_id, total), ..]
    print(result)

    # 二次查询
    user_totals = [(User.query.get(user_id), money)
                   for user_id, money in result]

    for user, money in user_totals:
        print(user.name, user.phone, money)

def test_bank_money():
    # 汇总每个银行的开户的人数、总金额和每个银行的最小金额和最大金额银行卡绑定的用户信息
    # 返回结果： list[<sqlalchemy.uitl._collections.result>] 
    #           -> collections.namedtuple()
    results = db.session.query(Card.back_id,
                               db.func.min(Card.money).label('min_'),
                               db.func.max(Card.money).label('max_'),
                               db.func.count('*').label('cnt'),
                               db.func.sum(Card.money).label('total'))\
                        .group_by(Card.back_id).all()

     for item in results:
        print(Back.query.get(item.back_id).name,
              item.cnt, item.total, item.min_, item.max_)

        # 查询最小金额的用户信息
        print('------最小和最大的存款用户信息--------')
        for user_id, money in db.session.query(Card.user_id, Card.money)\
                              .filter(Card.back_id == item.back_id,
                                      db.or_(
                                          Card.money == item.min_,
                                          Card.money == item.max_
                                      )).all():
            print(User.query.get(user_id).name, money)

原生SQL查询

sqlalchemy库支持原生SQL查询，db.session.execute(sql) .

def test_card_user_bank(self):
      with app.app_context():
          # 查询用户名、手机号、银行名、银行地址、银行卡及存款
          sql = """
              select u.name, u.phone, b.name,b.address,c.number,c.money
              from user u 
              JOIN card c on (u.id = c.user_id)
              JOIN back b on (b.id =c.back_id)
              order by c.money desc
          """
          exc = db.session.execute(sql)
          for username, phone, backname,address,number,money in exc.cursor:

              print(username, phone, backname,address, number, money)

exc.cursor 是pymsyql.cursors.Cursor类型， 而且cursor可以被迭代。

思考： 如何让cursor查询结果是dict对象，而不是现在的元组。

连接查询

• join() 内连接
• outerjoin() 外连接

普通的db.session.query()+filter()也可以实现多个实体类进行连接查询

def test_user_card(self):
    app.app_context().push()
    result = db.session.query(User.name,
                              User.phone,
                              Back.name.label('backname'),
                              Back.address,
                              Card.money,
                              Card.number) \
                        .filter(User.id == Card.user_id)\
                        .filter(Back.id == Card.back_id)
    for item in result.all():
        print(item.name,
              item.phone,
              item.backname,
              item.address,
              item.number.replace(' ', ''),
              item.money)

总结db.Column() 对象的常用的方法

• label() 列的别名
• desc()/asc() 排序
• 条件方法
  • startswith()
  • endswith()
  • like()
  • contains
  • le()
  • ge()
  • lt()
  • gt()
  • eq()
  • isnot()
  • in_()
  • notin_()

flask.pocoo.org/docs/ 官方文档。

https://www.sqlalchemy.org/ 官方文档。

二、模型关系及反向引用

模型关系分四种：

• 一对一关系
• 一对多关系
• 多对一关系
• 多对多关系

配置用户、银行和银行卡的关联关系：

class Card(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    number = db.Column(db.String(20), unique=True)
    money = db.Column(db.Float(2), default=0, server_default='0')
    passwd = db.Column(db.String(100))

    # 模型的关系维扩（建立）在多端
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship(User, backref='cards')

    back_id = db.Column(db.Integer, db.ForeignKey('back.id'))
    back = db.relationship(Back, backref='cards')

两个模型类之间建立关系，必须存在一个外键约束。

单元测试

class TestRelationshap(TestCase):
    def test_1(self):
        app.app_context().push()
        user = User.query.get(1)
        print(user.name)
        # 在user端反向引用cards, cards是在Card模型中通过db.relationshap()建立的。
        for card in user.cards:
            print(card.back.name, card.number, card.money)

class TestRelationshap(TestCase):
    def test_2(self):
        app.app_context().push()
        for card in Card.query.all():
            # 显示用户名、用户手机号、银行名， 存款
            print(card.user.name, card.user.phone, card.back.name, card.money)

关于relationshap的用法（自关联）： https://www.jianshu.com/p/9477a28f5079

三、作业

3.1 日志模块配置

https://flask.palletsprojects.com/en/1.1.x/logging/#basic-configuration

3.2 创建教育项目表对应的模型

注意：只创建模型类，不要在数据库创建表

要求： 模型创建完成后，通过 db.createall() 方法一次性创建表。

3.3 实现所有的api文档中的接口

要求： 接口返回的数据是json, 且接收客户端上传的数据

from flask import request
from flask import jsonify

@blue.route('/user', methods=['POST'])
def addUser():
    data = request.get_json() # 获取上传的数据
    return jsonify({
        'result': '成功',
        'code': 200
    })

3.4 针对api接口文本，写出所有测试

要求： 接口请求使用requests库的get()/post等方法

说明： 如果使用requests.post()上传json数据，可以使用post()方法的json参数，json参数是字典类型，如:

json = {
	'name': 'disen'
  'age': 20
}

url = 'http://localhost:5000/user'
resp = requests.post(url, json=json)
...