时间戳

首页 > 解决方案 > 我将如何返回表中所有行的计数，然后返回每次找到特定状态的计数？

sqlite - 我将如何返回表中所有行的计数，然后返回每次找到特定状态的计数？

问题描述

请原谅我对 sqlalchemy 的无知，到目前为止，我已经能够很好地在海洋中航行。我要做的是：

我目前正在使用 sqlalchemy，但即使是纯 sqlite 解决方案也有助于弄清楚我缺少什么。

这是我的表的配置方式：

class KbStatus(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    status = db.Column(db.String, nullable=False)

这是一个非常基本的表格，但我很难取回我正在寻找的数据。我有两个单独的查询，但我必须相信有一种方法可以在一个查询中完成这一切。

以下是我正在运行的单独查询：

total = len(cls.query.all())
status_count = cls.query.with_entities(KbStatus.status, func.count(KbStatus.id).label("total")).group_by(KbStatus.status).all()

从这里我将其转换为 dict 并将其组合以使输出如下所示：

{
  "data": {
    "status_count": {
        "Assigned": 1,
        "In Progress": 1,
        "Peer Review": 1,
        "Ready to Publish": 1,
        "Unassigned": 4
    },
    "total_requests": 8
  }

}

任何帮助是极大的赞赏。

标签： sqlitesqlalchemy

解决方案

我不了解 sqlalchemy，但可以使用JSON1扩展在纯 sqlite 的单个查询中生成您想要的结果：

给定下表和数据：

CREATE TABLE data(id INTEGER PRIMARY KEY, status TEXT);
INSERT INTO data(status) VALUES ('Assigned'),('In Progress'),('Peer Review'),('Ready to Publish')
                               ,('Unassigned'),('Unassigned'),('Unassigned'),('Unassigned');
CREATE INDEX data_idx_status ON data(status);

这个查询

WITH individuals AS (SELECT status, count(status) AS total FROM data GROUP BY status)
SELECT json_object('data'
                 , json_object('status_count'
                              , json_group_object(status, total)
                              , 'total_requests'
                              , (SELECT sum(total) FROM individuals)))
FROM individuals;

将返回一行保存（通过 JSON 漂亮打印机运行后；实际字符串更紧凑）：

{
  "data": {
    "status_count": {
      "Assigned": 1,
      "In Progress": 1,
      "Peer Review": 1,
      "Ready to Publish": 1,
      "Unassigned": 4
    },
    "total_requests": 8
  }
}

如果您使用的 sqlite 实例不是在支持 JSON1 的情况下构建的：

SELECT status, count(status) AS total FROM data GROUP BY status;

会给

status                total
--------------------  ----------
Assigned              1
In Progress           1
Peer Review           1
Ready to Publish      1
Unassigned            4

您可以在 python 中遍历它，将每一行插入到您的 dict中，并在最后total获取total_requests值时将另一个变量中的所有值相加。不需要另一个查询来计算这个数字；手动完成。我敢打赌，用现有的第二个 sqlachemy 查询做同样的事情真的很容易。

推荐阅读