SQLite Database to Python Dictionary using SQL SELECT statement

Question

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!

forpas · Accepted Answer · 2022-03-25 22:04:40Z

3

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;

answered Mar 25, 2022 at 22:04

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damien Over a year ago

Thank you @forpas That was EXACTLY what I needed!

bert wassink · Accepted Answer · 2022-03-25 22:26:15Z

2

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

answered Mar 25, 2022 at 22:26

bert wassink

3693 silver badges10 bronze badges

1 Comment

Damien Over a year ago

Thank you @bert, this was also helpful. I am already using pandas in my code and I may take advantage of this ability in the future. It's interesting because I was trying to make a pivot table / chart as your answer suggested.

Collectives™ on Stack Overflow

SQLite Database to Python Dictionary using SQL SELECT statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related