Préparer une base de données d'exercice

Leçon 3
50 min
Module 1 : Sources et structure des données

Leçon 3 sur 18 · SQL et préparation des données pour l'IAPréparer une base de données d'exercice

Aperçu du cours

1. Sources et structure des données

2. L'essentiel de SQL

3. Des requêtes d'analyse utiles

4. Nettoyer et transformer les données

5. Préparer la modélisation sans risque

6. Contrats de données et passation

7. Études de cas

Ce que vous saurez faire

Obtenez sur votre ordinateur la même base de données que dans le navigateur. Exécutez des requêtes enregistrées dans des fichiers. Vous pouvez les réexécuter, les comparer et les réviser. Ce n’est pas possible avec une requête saisie une seule fois dans une zone.

Carnet JupyterVoir chaque étape de préparation dans un carnet JupyterColab · Kaggle · GitHub · en anglais

Le carnet (notebook) Jupyter fait les étapes de get_data.py une à la fois : téléchargement, somme de contrôle, création et connexion en lecture seule. Ensuite, il exécute get_data.py, la réinitialisation et q.py, et contient les exercices du module avec leurs vérifications. Vous n’avez rien à installer sur votre ordinateur. Pour créer le projet sur votre ordinateur, suivez les étapes de cette page dans un terminal.

Ouvrir dans Colab Ouvrir dans Kaggle Voir sur GitHub

Le carnet est en anglais. Colab et Kaggle ouvrent votre propre copie. Il faut un compte Google ou Kaggle gratuit. Vous êtes bloqué ? Consultez le carnet de solution.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 50 minutes

Ce que vous devez savoir

Les clés primaires, les clés étrangères et pourquoi l’exportation n’a aucune contrainte. Voir Les concepts relationnels : les clés et Pourquoi l’exportation n’a pas de contraintes.
La granularité de chaque table, dans Les éléments d’un jeu de données.
Les dossiers, les chemins et le dossier personnel ~, dans Les fichiers et le terminal : dossiers et chemins. Les commandes avec des arguments, dans Commandes et arguments.

Ce qu’il vous faut sur votre ordinateur (seulement pour le parcours local; le carnet et les zones de cette page n’en ont pas besoin)

Un terminal. Pour en ouvrir un, consultez Les fichiers et le terminal : ouvrir un terminal.
Python 3.12 ou une version plus récente. Pour l’installer, consultez Installer et vérifier Python : étape 1. Pour vérifier la version, consultez Étape 2 : vérifier quel Python s’exécute.
Un éditeur de code, par exemple VS Code, dans Étape 3 : obtenir un éditeur de code.
Une connexion Internet. Tout est gratuit et vous n’avez besoin d’aucun compte.
Aucun environnement virtuel n’est nécessaire dans ce module. Les deux scripts utilisent seulement la bibliothèque standard de Python. Vous créerez l’environnement virtuel au module 4, quand vous aurez besoin de pandas pour la première fois.
Cette leçon crée le dossier ~/projects/ticket-data. Si une étape échoue, vous pouvez recommencer : réinitialisez la base de données, ou supprimez le dossier et suivez de nouveau Créer la base de données.

Testé sur macOS avec Python 3.14.6 (SQLite 3.50.4 dans Python). Les étapes pour Windows et Linux n’ont pas été testées. Les zones du navigateur utilisent SQLite 3.49.1. Le carnet a été testé dans Jupyter.

Deux parcours pour une même base de données

Jusqu’ici, chaque requête s’exécutait dans votre navigateur. La zone charge le fichier larkfield.sqlite et exécute SQLite dans la page. C’est rapide pour des questions courtes. Le travail réel sur les données demande aussi des fichiers. Par exemple, une requête enregistrée dans queries/, que vous réexécutez la semaine suivante, ou un script Python qui lit la base. À partir du module 3, vous écrirez de tels scripts.

Vous créez donc la même base de données sur votre ordinateur. Deux petits scripts font le travail :

Script	Ce qu’il fait
`get_data.py`	Télécharge les trois fichiers CSV de l’exportation dans `data/raw/`, vérifie que chaque fichier est correct et les charge dans `larkfield.db`
`q.py`	Exécute une requête SQL, depuis un fichier `.sql` ou saisie entre guillemets, et affiche le résultat sous forme de tableau

Le fichier du navigateur et votre fichier larkfield.db contiennent les mêmes données. Une requête donne les mêmes nombres dans les deux parcours.

À la fin de cette leçon, votre dossier de projet ressemble à ceci. Le cours ajoutera d’autres dossiers plus tard.

projects
└── ticket-data
    ├── get_data.py         downloaded
    ├── q.py                downloaded
    ├── larkfield.db        built by get_data.py
    ├── data
    │   └── raw             customers.csv, tickets.csv, outcomes.csv
    └── queries             your .sql files: 01-first-look.sql, ...

Quelle commande Python saisir

La commande qui lance Python dépend de votre système :

macOS et Linux : python3
Windows : python

La suite du cours utilise python. Sur macOS et Linux, saisissez python3 jusqu’à la création de l’environnement virtuel au module 4. Dans un environnement virtuel actif, python fonctionne sur tous les systèmes.

Créer la base de données

Ouvrez un terminal.
Créez le dossier de projet et placez-vous dedans :
Fenêtre de terminal
mkdir -p ~/projects/ticket-data cd ~/projects/ticket-data
Fenêtre de terminal
mkdir ~\projects\ticket-data cd ~\projects\ticket-data
Si le dossier existe déjà, mkdir affiche une erreur. Vous pouvez l’ignorer.
Fenêtre de terminal
mkdir -p ~/projects/ticket-data cd ~/projects/ticket-data

Téléchargez les deux scripts :

curl -o get_data.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/get_data.py
curl -o q.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/q.py

curl.exe -o get_data.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/get_data.py
curl.exe -o q.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/q.py

Saisissez curl.exe, et non curl. Dans Windows PowerShell, curl est une autre commande.

curl -o get_data.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/get_data.py
curl -o q.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/q.py

Si la sortie affiche curl: command not found (commande curl introuvable), exécutez d’abord sudo apt install curl (ou sudo dnf install curl).

Si vous ne pouvez pas utiliser curl, ouvrez chaque lien dans votre navigateur. Enregistrez la page sous le même nom de fichier dans le dossier de projet.

Créez le dossier pour vos requêtes :
Fenêtre de terminal
```
mkdir queries
```

Créez la base de données :

python3 get_data.py

python get_data.py

python3 get_data.py

Downloading customers.csv ...
Downloading tickets.csv ...
Downloading outcomes.csv ...
customers    240 rows
tickets     1417 rows
outcomes    1874 rows
Built larkfield.db. Check: customers 240, tickets 1417, outcomes 1874.

Le script compare chaque nombre de lignes avec la ligne Check: (vérification). Ils doivent être identiques.

Vérifiez la base de données avec une première requête. Sur Windows, saisissez python plutôt que python3 :
Fenêtre de terminal
```
python3 q.py "SELECT COUNT(*) FROM tickets"
```
Sortie
```
COUNT(*)
--------
1417

1 row
```

Si vous voyez 1417, la préparation a réussi. C’est le même nombre que dans le navigateur.

Le script vérifie chaque fichier téléchargé avec une somme de contrôle. Une somme de contrôle est un long code calculé à partir des octets d’un fichier. Si un seul octet change, le code change. Si un fichier est endommagé, get_data.py s’arrête avec ... has the wrong checksum (la somme de contrôle est incorrecte). Il vous indique quoi faire. Si le téléchargement échoue, il s’arrête avec Could not download ... (impossible de télécharger). Vérifiez votre connexion Internet et exécutez-le de nouveau. Le carnet exécute chaque étape de get_data.py dans une cellule distincte, pour vous montrer ce que fait le script.

Exécuter une requête enregistrée

Saisir une requête entre guillemets fonctionne pour une seule ligne. Pour une requête plus longue, enregistrez-la dans un fichier. Le cours numérote les fichiers pour garder l’ordre dans lequel vous les avez écrits.

Ouvrez le dossier ticket-data dans VS Code (File › Open Folder (Fichier › Ouvrir un dossier)).
Dans le dossier queries, créez un fichier nommé 01-first-look.sql.

Saisissez cette requête dans le fichier et enregistrez-le :

-- The first five tickets in the export
SELECT ticket_id, customer_id, created_at, team
FROM tickets
LIMIT 5;

Dans le terminal, placez-vous dans le dossier de projet et exécutez le fichier :
Fenêtre de terminal
```
python3 q.py queries/01-first-look.sql
```

ticket_id  customer_id  created_at           team
---------  -----------  -------------------  --------
T-10001    C-0228       2026-01-05 07:30:24  returns
T-10002    C-0207       2026-01-05 09:09:23  delivery
T-10003    C-0119       2026-01-05 13:22:27  delivery
T-10004    C-0048       2026-01-05 16:14:56  returns
T-10005    C-0039       2026-01-05 17:31:57  returns

5 rows

Une ligne qui commence par -- est un commentaire. SQL ne l’exécute pas. Utilisez un commentaire pour indiquer à quelle question la requête répond.

La requête nomme ses colonnes au lieu d’utiliser SELECT *. Une requête enregistrée doit donner les mêmes colonnes le mois prochain, même si l’exportation ajoute une colonne. À partir du module 2, chaque requête enregistrée nomme ses colonnes.

q.py affiche au plus 20 lignes, puis le total. Par exemple, python3 q.py "SELECT ticket_id FROM tickets" se termine par 1417 rows (showing 20; add --all to see every row) (1 417 lignes; 20 affichées; ajoutez –all pour toutes les voir). Ajoutez --all à la fin de la commande pour afficher toutes les lignes.

La base de données est protégée et vous pouvez la réinitialiser

q.py ouvre la base de données en lecture seule : il peut lire, mais il ne peut rien modifier. Essayez de supprimer tous les billets de soutien :

python3 q.py "DELETE FROM tickets"

SQL error: attempt to write a readonly database

Rien n’a changé. Cette protection empêche une erreur dans une requête de modifier les données.

D’autres outils peuvent modifier le fichier. Si vous doutez que larkfield.db soit encore l’original, recréez-le à partir des fichiers CSV. C’est la réinitialisation :

python3 get_data.py --reset

Deleted larkfield.db.
customers    240 rows
tickets     1417 rows
outcomes    1874 rows
Built larkfield.db. Check: customers 240, tickets 1417, outcomes 1874.

Les fichiers CSV sont déjà dans data/raw/ et leurs sommes de contrôle sont correctes. Le script ne les télécharge donc pas de nouveau. Si vous exécutez get_data.py sans --reset alors que larkfield.db existe, il s’arrête et vous demande d’utiliser --reset. Il ne remplace jamais la base de données par accident.

D’autres façons d’ouvrir la base de données

q.py est le seul outil nécessaire dans ce cours. Deux autres outils sont facultatifs :

L’interpréteur SQLite de Python. Exécutez python3 -m sqlite3 larkfield.db. Vous obtenez l’invite sqlite>, où vous saisissez des instructions SQL qui se terminent par ;. Saisissez .quit pour quitter. Cet interpréteur peut modifier la base de données. Si vous la modifiez par erreur, réinitialisez-la.
DB Browser for SQLite est un programme gratuit avec des fenêtres et des tables sur lesquelles cliquer. Il est utile pour examiner les données, mais le cours n’en a pas besoin.

SQLite et PostgreSQL

SQLite est la base de données de ce cours, car elle tient dans un seul fichier et ne nécessite aucun serveur. De nombreuses entreprises utilisent PostgreSQL, un serveur de bases de données. La plupart des instructions SQL de ce cours fonctionnent dans les deux. Voici les différences que vous rencontrerez :

Sujet	SQLite	PostgreSQL
Types	Souples : une colonne de type `INTEGER` peut quand même stocker du texte	Stricts : un mauvais type provoque une erreur
Dates et heures	Aucun type date. Les dates sont du texte, et `date()` et `strftime()` traitent ce texte	Types `date` et `timestamp`, avec des fonctions comme `date_trunc` et `EXTRACT`
Joindre du texte avec `\|\|`	Oui	Oui, de la même façon
Comparer du texte sans tenir compte de la casse : `ILIKE`	Non	Oui
`LIMIT`	Oui	Oui, de la même façon
Vrai et faux	`1` et `0`	Un type `BOOLEAN` avec `true` et `false`
Vérifications des clés étrangères	Seulement après `PRAGMA foreign_keys = ON;`	Toujours
Paramètres dans Python	`?` ou `:name` (module `sqlite3`)	`%s` ou `%(name)s` (module `psycopg`)

Vous n’installez pas PostgreSQL dans ce cours. Lorsqu’une requête ultérieure diffère dans PostgreSQL, la leçon ajoute une courte note.

Erreur courante

larkfield.db n'est pas dans ce dossier

Symptôme : vous exécutez une requête et vous voyez ce message :

larkfield.db is not in this folder. Run the script from your project folder, or build the database first: python get_data.py

Cause : q.py cherche larkfield.db dans le répertoire de travail, le dossier où se trouve le terminal. Si vous exécutez python3 ticket-data/q.py depuis le dossier projects, le fichier n’y est pas. C’est aussi le cas depuis un nouveau terminal ouvert dans votre dossier personnel.

Solution :

Exécutez pwd pour voir le répertoire de travail.
Placez-vous dans le dossier de projet : cd ~/projects/ticket-data.
Exécutez ls (ou dir sur Windows). Vous devez voir larkfield.db. S’il manque, exécutez python3 get_data.py.
Exécutez la requête de nouveau.

Dans le travail en IA : les scripts qui utilisent des chemins relatifs dépendent du répertoire de travail. Exécutez toujours les scripts du cours depuis le dossier de projet. Un script qui ne trouve aucun fichier et s’arrête est une bonne chose. Un script qui crée discrètement une nouvelle base de données vide est bien pire.

Vérifiez votre compréhension

Vous avez exécuté un DELETE dans l'interpréteur SQLite de Python. Le nombre de billets est maintenant 0. Comment récupérer les données d'origine, et pourquoi cela fonctionne-t-il?

Afficher la réponse

Exécutez python get_data.py --reset (ou python3 sur macOS et Linux). Le script supprime larkfield.db et le recrée à partir des trois fichiers CSV dans data/raw/. Les fichiers CSV constituent l’exportation. La base de données est seulement une copie créée à partir d’eux. Vous pouvez donc toujours la recréer. La ligne de vérification doit afficher customers 240, tickets 1417, outcomes 1874.

Vérifiez votre compréhension

Pourquoi le cours enregistre-t-il les requêtes dans des fichiers .sql numérotés plutôt que de seulement les saisir?

Afficher la réponse

Une requête enregistrée peut être réexécutée sur le même instantané et doit donner le même résultat. Une autre personne peut la lire, la réviser et l’exécuter. Le nom du fichier et le commentaire indiquent à quelle question la requête répond. Une requête saisie une seule fois est perdue, et personne ne peut vérifier comment un nombre a été obtenu.

Exercices du module

Exemple détaillé : lire le schéma en SQL

Mei veut voir le schéma de tickets sans ouvrir get_data.py. SQLite décrit chaque table avec pragma_table_info. Vous l’utilisez comme une table : une ligne pour chaque colonne.

Exécutez du SQL dans votre navigateur

Essayez : les colonnes de tickets

Exécutez la requête. Vous devriez voir 11 lignes, une pour chaque colonne, de ticket_id à closed_at. Examinez la colonne pk.

Requête SQL (vous pouvez la modifier)

SELECT cid, name, type, pk
FROM pragma_table_info('tickets');

Mei enregistre la même requête sous queries/02-schema.sql et l’exécute sur son ordinateur :

cid  name                 type     pk
---  -------------------  -------  --
0    ticket_id            TEXT     0
1    customer_id          TEXT     0
2    created_at           TEXT     0
3    channel              TEXT     0
4    team                 TEXT     0
5    priority             INTEGER  0
6    order_value          REAL     0
7    word_count           INTEGER  0
8    first_reply_minutes  INTEGER  0
9    priority_now         INTEGER  0
10   closed_at            TEXT     0

11 rows

cid est la position de la colonne, à partir de 0. pk vaut 1 pour une colonne dans une contrainte PRIMARY KEY, et 0 sinon. Toutes les valeurs sont 0 : la table n’a aucune contrainte de clé primaire. Cela confirme votre constat de la dernière leçon : vous devez vérifier les clés avec des requêtes.

Ensuite, elle compte les lignes des trois tables dans une seule requête. UNION ALL place les résultats de plusieurs requêtes les uns sous les autres, dans une seule table.

-- Rows in each table of the export
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'tickets', COUNT(*) FROM tickets
UNION ALL
SELECT 'outcomes', COUNT(*) FROM outcomes;

table_name  row_count
----------  ---------
customers   240
tickets     1417
outcomes    1874

3 rows

Les nombres sont les mêmes que dans la ligne Check: de get_data.py. Le chargement est donc complet.

Votre tâche : les deux autres tables

Sur votre ordinateur, dans le dossier de projet :

Enregistrez une requête queries/04-schema-outcomes.sql qui montre le name et le type de chaque colonne de outcomes. Exécutez-la avec q.py.
Faites la même chose pour customers dans queries/05-schema-customers.sql.
Exécutez queries/03-counts.sql.
Dans un fichier notes.md du dossier de projet, écrivez une ligne pour chaque table : son nom, son nombre de lignes, sa granularité et sa clé primaire.

Vérifiez aussi votre première requête dans la zone :

Exécutez du SQL dans votre navigateur

À vous : le schéma de outcomes

Votre résultat doit avoir deux colonnes, le nom et le type, et une ligne pour chaque colonne de outcomes.

Requête SQL (vous pouvez la modifier)

-- Show the name and the type of each column of outcomes

Afficher la réponse modèle

-- The columns of the outcomes table, with their types
SELECT name, type
FROM pragma_table_info('outcomes');

Le résultat a 6 lignes : outcome_id TEXT, ticket_id TEXT, recorded_at TEXT, status TEXT, resolution_code TEXT et csat INTEGER. Pour customers, le résultat a 4 lignes : customer_id, segment, region et joined_on, toutes de type TEXT.

# Larkfield export of 2026-07-01 06:00

- customers: 240 rows. One row = one customer. Primary key: customer_id (checked: unique, no NULL).
- tickets: 1417 rows, 1403 tickets. One row = one ticket. Primary key: ticket_id, but 14 IDs appear twice (exact duplicates).
- outcomes: 1874 rows. One row = one event in a ticket's life. Primary key: outcome_id (checked: unique).

Vérification du module

Vérifiez votre compréhension

Rappel : que sont une clé primaire et une clé étrangère, et laquelle peut se répéter?

Afficher la réponse

Une clé primaire identifie une ligne de sa table : elle est unique et jamais NULL, par exemple customers.customer_id. Une clé étrangère est une colonne qui pointe vers la clé primaire d’une autre table, par exemple tickets.customer_id. La clé étrangère peut se répéter, car un client a plusieurs billets. Elle peut aussi être NULL lorsqu’une ligne peut n’avoir aucune correspondance, comme pour un billet d’invité.

Vérifiez votre compréhension

Prédisez : le service de soutien enregistre un nouvel événement pour le billet T-10014 (il est encore rouvert). Quels nombres changent : les lignes dans outcomes, les valeurs distinctes de ticket_id dans outcomes, les lignes dans tickets?

Afficher la réponse

Seules les lignes dans outcomes changent, passant de 1 874 à 1 875. T-10014 a déjà des événements. Le nombre de valeurs distinctes de ticket_id dans outcomes reste donc le même. La table tickets ne change pas : la granularité de tickets est un billet, et aucun nouveau billet n’a été créé. Une relation un-à-plusieurs signifie que le côté « plusieurs » grandit, mais pas le côté « un ».

Vérifiez votre compréhension

Vérification finale : pour chaque table, nommez sa granularité et les clés qui la relient aux autres. Indiquez quelles liaisons ne sont pas sûres dans cette exportation, avec les nombres.

Afficher la réponse

Table	Granularité (une ligne représente)	Clé primaire	Liaison
`customers`	un client	`customer_id` : 240 lignes, 240 valeurs distinctes, aucun NULL. Sûre.	`tickets.customer_id` pointe ici
`tickets`	un billet	`ticket_id` : 1 417 lignes, mais 1 403 valeurs distinctes. 14 billets apparaissent deux fois comme doublons exacts. Pas sûre avant la suppression des doublons.	`customer_id` → `customers.customer_id` : 12 lignes sont NULL (invités) et 9 lignes pointent vers des clients supprimés (C-0241 à C-0244)
`outcomes`	un événement dans la vie d’un billet	`outcome_id` : 1 874 lignes, 1 874 valeurs distinctes. Unique.	`ticket_id` → `tickets.ticket_id` : un-à-plusieurs, et 4 événements pointent vers des billets absents de l’exportation (T-9990 à T-9993)

Une liaison est sûre lorsque la clé du côté « un » est unique. Vous devez aussi savoir ce qui arrive aux lignes du côté « plusieurs » qui n’ont aucune correspondance. Ici, tickets.ticket_id n’est pas encore unique, et les deux clés étrangères ont des lignes sans correspondance. Le module 2 montre les effets de ces problèmes sur une jointure.

Récapitulatif

Dans ce module, vous avez appris à lire un jeu de données avant de l’interroger :

Chaque table a des lignes et des colonnes, et chaque colonne a un type. NULL signifie « aucune valeur ». Le schéma décrit les tables; seules les données montrent ce qu’elles contiennent réellement.
La granularité indique ce que représente une ligne : un client, un billet ou un événement dans la vie d’un billet. Précisez-la avant de compter.
Les clés primaires identifient les lignes, et les clés étrangères relient les tables dans des relations un-à-plusieurs. L’exportation n’a aucune contrainte, alors vous vérifiez les clés vous-même.
L’exportation du 1 juillet 2026 à 6 h contient 240 clients, 1 417 lignes de billets (1 403 billets) et 1 874 événements de résultat. Elle contient 14 billets en double, 9 billets de clients supprimés, 12 billets d’invités et 4 événements sans billet.
Vous avez la même base de données dans le navigateur et dans ~/projects/ticket-data/larkfield.db. Vous exécutez les requêtes enregistrées avec python q.py et réinitialisez la base avec python get_data.py --reset.

Ensuite, vous poserez de vraies questions à la base de données : sélectionner des colonnes, filtrer des lignes et les trier. Vous découvrirez aussi les premières erreurs discrètes dans les filtres.

Leçon suivante : Sélectionner et filtrer