SQLite Database to Python Dictionary using SQL SELECT statement-CodePudding

Have been searching for days. Please help. I have an SQL query:

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

Which returns:

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

What I need is to create new columns for the possible values of RecordStatus so I can collapse the resulting table to this:

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

And the eventual data format will be a python list of dictionaries:

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

I have no problem creating the dictionary from the results of the query. I just need to know the right SQL statement to format the results of the query.

Any help would be MOST appreciated!

CodePudding user response：

For this you need to group by WorkList only and use conditional aggregation:

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;

CodePudding user response：

You can also keep the query as it is and use pandas to transform it into a list of records as you like to have it

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}]