使用SQL SELECT语句将SQLite数据库转换为Python字典

Damien • 2 年前 • 916 次点击

已经找了好几天了。请帮忙。我有一个SQL查询:

SELECT
WorkList, RecordStatus, COUNT(*) as QTY
FROM worklist_reports
WHERE WorkList IS NOT NULL
GROUP BY WorkList, RecordStatus

| WorkList | RecordStatus | QTY |
| -------- | ------------ | ----|
|ADM       |Closed        |41   |
|ADM       |Open          |1    |
|BIL       |Closed        |16   |
|BIL       |Hold          |1    |
|BIL       |Open          |2    |

我需要的是为RecordStatus的可能值创建新的列,以便将结果表折叠为:

| WorkList | Open | Closed | Hold |
| -------- | ---- | ------ | ---- |
|ADM       |1     |41      |0     |
|BIL       |2     |16      |1     |

最终的数据格式将是python字典列表:

data = [
    {'x': 'ADM', 'open': 1, 'closed': 41, 'hold': 0}, 
    {'x': 'BIL', 'open': 2, 'closed': 16, 'hold': 1}
]

我可以根据查询结果创建字典。我只需要知道正确的SQL语句来格式化查询结果。

任何帮助都将不胜感激!

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/129960

916 次点击

文章 [ 2 ] | 最新文章 2 年前

• 1 楼

bert wassink 2 年前

您还可以保持查询的原样,并使用pandas将其转换为您想要的记录列表

import pandas as pd

df = pd.DataFrame({
    "WorkList": ["ADM", "ADM", "BIL", "BIL", "BIL"],
    "RecordStatus": ["Closed", "Open", "Closed", "Hold", "Open"],
    "QTY": [41, 1, 16, 1, 2]
})

list_records = df.pivot(
    index="WorkList", columns="RecordStatus", values="QTY"
).fillna(0).astype(int).reset_index().to_dict(orient='records')

list_records
[{'WorkList': 'ADM', 'Closed': 41, 'Hold': 0, 'Open': 1}, {'WorkList': 'BIL', 'Closed': 16, 'Hold': 1, 'Open': 2}]

• 2 楼

forpas 2 年前

为此,你需要分组 WorkList 仅限和使用条件聚合:

SELECT WorkList, 
       SUM(RecordStatus = 'Open') AS Open,
       SUM(RecordStatus = 'Closed') AS Closed,
       SUM(RecordStatus = 'Hold') AS Hold
FROM worklist_reports
WHERE WorkList IS NOT NULL
GROUP BY WorkList;

登录后回复