一次使用 SQLAlchemy 实现分类以及计数的业务过程

airborne007 发布于2019-07-30 16:38 / 1764人阅读

摘要：在编写业务逻辑代码的时候我不幸遇到下面的表结构已经将主要逻辑抽离出来了分类分类名称产品产品名称分类现在需要实现的业务是返回分类的列表结果分类这是一个一对多的模型一般的笨拙思路就是明眼人一看就知道可以把换成但是根据这篇文章似乎这样写会有更好的

在编写业务逻辑代码的时候, 我不幸遇到下面的表结构(已经将主要逻辑抽离出来了):

class Category(Model):
    __tablename__ = "category"
    # 分类ID
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 分类名称
    name = Column(String(length=255))
    
class Product(Model):
    __tablename__ = "product"
    # 产品 ID
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 产品名称
    name = Column(String(length=255))
    # 分类 ID
    category_id = Column(Integer)

现在需要实现的业务是返回分类的列表结果：

[
    {
        "id": 1,
        "name": "分类1",
        "product_count": 1
    },
    ...
]

这是一个一对多的模型.
一般的笨拙思路就是:

data = []
categorys = Category.query.all()
for category in categorys:
    product_count = len(Product.query.filter(Product.category_id == category.id).all())
    data.append({
        "id": category.id,
        "name": category.name,
        "product_count": product_count
    })

明眼人一看就知道可以把len(Product.query.filter(Product.category_id == category.id).all())换成:

product_count = Product.query.filter(Product.category_id == category.id).count()

但是, 根据这篇文章:[Why is SQLAlchemy count() much slower than the raw query?
](https://stackoverflow.com/que... 似乎这样写会有更好的性能:

from sqlalchemy import func
session.query(func.count(Product.id)).filter(Product.category_id == category.id).scalar()

但是, 稍微有点经验的人就会对上面的写法嗤之以鼻, 因为product_count是放在for category in categorys:里面的, 这意味着如果categorys有成千上万个, 就要发出成千上万个session.query(), 而数据库请求是在网络上的消耗, 请求时间相对较长, 有的数据库没有处理好连接池, 建立连接和断开连接又是一笔巨大的开销, 所以 query 的请求应该越少越好. 像上面这样把 query 放到 for 循环中显然是不明智的选择.
于是有了下面一个请求的版本：

result = db.session.query(Product, Category) 
    .filter(Product.category_id == Category.id)
    .order_by(Category.id).all()
id_list = []
data = []
for product, category in result:
    if category and product:
        if category.id not in id_list:
            id_list.append(category.id)
            data.append({
                "id": category.id,
                "name": category.name,
                "product_count": 0
            })
        idx = id_list.index(category.id)
        data[idx]["product_count"] += 1

这样的写法十分难看, 而且同样没有合理利用 SQLAlchemy 的 count 函数. 于是改成:

product_count = func.count(Product.id).label("count")
results = session.query(Category, product_count) 
    .join(Product, Product.category_id == Category.id) 
    .group_by(Category).all()
data = [
    {
        "id": category.id,
        "name": category.name,
        "product_count": porduct_count
    } for category, product_count in results]

不过这里还有一个问题, 就是如果先添加一个Category, 而属于这个Category下没有Product, 那么这个Category就不会出现在data里面, 所以join必须改成outerjoin. 即:

results = session.query(Category, product_count) 
    .outerjoin(Product, Product.category_id == Category.id) 
    .group_by(Category).all()

需求又来了!!!
现在考虑设计Product为伪删除模式, 即添加一个is_deleted属性判断Product是否被删除.
那么count函数就不能简单地count(Product.id), 而是要同时判断Product.is_deleted是否为真和Product是否为None, 经过悉心研究, 发现使用func.nullif可以实现这个需求,即用下面的写法:

product_count = func.count(func.nullif(Product.is_deleted.is_(False), False)).label("count")
results = session.query(Category, product_count) 
    .join(Product, Product.category_id == Category.id) 
    .group_by(Category).all()
data = [
    {
        "id": category.id,
        "name": category.name,
        "product_count": porduct_count
    } for category, product_count in results]

可见使用 ORM 有的时候还是需要考虑很多东西.

idc机房托管云服务器数据仓库的实现过程亚马逊云服务器租用以及使用自然分类算法实现 js实现视频分类

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/41775.html

一次使用 SQLAlchemy 实现分类以及计数的业务过程

摘要：在编写业务逻辑代码的时候我不幸遇到下面的表结构已经将主要逻辑抽离出来了分类分类名称产品产品名称分类现在需要实现的业务是返回分类的列表结果分类这是一个一对多的模型一般的笨拙思路就是明眼人一看就知道可以把换成但是根据这篇文章似乎这样写会有更好的在编写业务逻辑代码的时候, 我不幸遇到下面的表结构(已经将主要逻辑抽离出来了): class Category(Model): __tab...

MadPecker 2019-07-31 18:05 评论0 收藏0
scrapy爬虫使用总结——技巧和天坑

摘要：简介爬虫这个东西我就不多做介绍了，总之是一个很好用的爬虫库，且关于也有较多的教程。这篇文章记录一下我个人的项目规划和天坑心得。然后执行就会自动去爬数据了。常用配置这里要结合一些原因来进行说明。简介 scrapy爬虫这个东西我就不多做介绍了，总之是一个很好用的Python爬虫库，且关于scrapy也有较多的教程。这篇文章记录一下我个人的项目规划和天坑心得。通常来说，我们执行了scra...

vvpvvp 2019-07-30 16:50 评论0 收藏0
记一次tornado QPS 优化

摘要：初步分析提升可从两方面入手，一个是增加并发数，其二是减少平均响应时间。大部分的时间花在系统与数据库的交互上，到这，便有了一个优化的主题思路最大限度的降低平均响应时间。不要轻易否定一项公认的技术真理，要拿数据说话。本文最早发表于个人博客：PylixmWiki 应项目的需求，我们使用tornado开发了一个api系统，系统开发完后，在8核16G的虚机上经过压测qps只有200+。与我们当...

Doyle 2019-07-30 14:30 评论0 收藏0
软件测试基础——非功能测试入门

摘要：可靠性测试或称稳定性测试，健壮性测试。分析诊断和调节阶段主要测量系统性能并使负载测试进入下一级别，重点查找问题原因以帮助开发工程师迅速解决问题，并实时调节系统参数以提高性能。 ...

Zachary 2021-09-24 09:47 评论0 收藏0
[原]解密Airbnb 自助BI神器：Superset 颠覆 Tableau

摘要：概述我非常认同前百度数据工程师现神策分析创始人桑老师最近谈到的数据分析三重境界统计计数多维分析机器学习数据分析的统计计数和多维分析，我们通常称之为数据探索式分析，这个步骤旨在了解数据的特性，有助于我们进一步挖掘数据的价值。 showImg(https://camo.githubusercontent.com/f98421e503a81176b003ddd310d97e1e1214625...

Keagan 2019-07-25 10:21 评论0 收藏0