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

已经找了好几天了。请帮忙。 我有一个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语句来格式化查询结果。

任何帮助都将不胜感激!