Base de données SQLite vers dictionnaire Python à l'aide de l'instruction SQL SELECT

Question

J'ai cherché pendant des jours. Merci de m'aider. J'ai une requête SQL :

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

Qui revient :

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

Ce dont j'ai besoin, c'est de créer de nouvelles colonnes pour les valeurs possibles de RecordStatus afin de pouvoir réduire le tableau résultant à ceci :

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

Le format de données final sera une liste de dictionnaires en python :

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

Je n'ai aucun problème à créer le dictionnaire à partir des résultats de la requête. J'ai juste besoin de connaître la bonne instruction SQL pour formater les résultats de la requête.

Toute aide serait très appréciée !

Answer 1

Pour ce faire, vous devez procéder à un regroupement par WorkList et utiliser l'agrégation conditionnelle :

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;

Answer 2

Vous pouvez également conserver la requête telle quelle et utiliser pandas pour la transformer en une liste d'enregistrements telle que vous la souhaitez

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