INSERT, UPDATE, DELETE : trois verbes puissants, à utiliser avec une extrême prudence.
⏱️ Durée : environ 75 minutes · 🎓 Niveau : intermédiaire · 🏅 Badge : Éditeur de données
🎬 L'histoire
Un vendredi, Kodi lance DELETE FROM membres pour supprimer un test. Il a oublié le WHERE. Tous les membres disparaissent. Heureusement, une sauvegarde existe.
« L'écriture est irréversible tant qu'on n'a pas de filet, dit Sam. Avant chaque modification : un SELECT avec la même condition, une transaction, et une sauvegarde. »
Tu apprends à modifier les données proprement et sans te mettre en danger.
🎯 Ta mission
À la fin, je serai capable de :
- utiliser INSERT, UPDATE et DELETE avec leurs clauses
- insérer en masse avec des paramètres
- utiliser l'upsert (INSERT ... ON CONFLICT)
- prévisualiser une modification avec un SELECT
- utiliser une transaction pour pouvoir annuler
🧩 Défi d'échauffement (5 XP)
Tu dois supprimer tous les élèves qui ont quitté l'école d'un fichier de 3 000 lignes. Quelles précautions prends-tu avant de supprimer ? Comment vérifies-tu que tu supprimeras les bonnes lignes ? Comment pourrais-tu revenir en arrière si tu te trompes ?
💡 Le concept
INSERT
INSERT INTO membres (nom, ville) VALUES ('Ada', 'Lyon');
INSERT INTO membres (nom, ville) VALUES ('Bob', 'Lille'), ('Cléo', 'Nice');
INSERT INTO archive SELECT * FROM membres WHERE actif = 0; -- depuis une requête
UPDATE
UPDATE membres SET ville = 'Paris' WHERE id = 3;
UPDATE ventes SET prix_centimes = prix_centimes * 110 / 100 WHERE produit = 'Sac';
DELETE
DELETE FROM membres WHERE id = 3;
⚠️ Sans
WHERE, UPDATE et DELETE concernent TOUTES les lignes.
La règle d'or
- Écrire un
SELECTavec la même condition et vérifier les lignes concernées. - Ouvrir une transaction.
- Exécuter la modification.
- Vérifier le nombre de lignes affectées (
rowcount). - Valider (
COMMIT) ou annuler (ROLLBACK).
Upsert : insérer ou mettre à jour
INSERT INTO scores (membre_id, points) VALUES (1, 10)
ON CONFLICT(membre_id) DO UPDATE SET points = points + excluded.points;
Si la clé existe déjà, on met à jour au lieu d'échouer. Utile pour les compteurs et les synchronisations.
Les paramètres et l'insertion en masse
bd.executemany("INSERT INTO membres (nom, ville) VALUES (?, ?)", [("Ada", "Lyon"), ("Bob", "Lille")])
C'est plus rapide et plus sûr qu'une boucle de requêtes construites à la main.
Suppression logique
Au lieu de supprimer, on peut marquer : une colonne supprime_le (date) ou actif = 0. Avantages : récupération possible, historique. Inconvénient : toutes les requêtes doivent filtrer.
Valeurs générées
lastrowid donne l'identifiant de la ligne insérée. La clause RETURNING (SQLite ≥ 3.35, PostgreSQL) renvoie les colonnes modifiées.
INSERT INTO membres (nom) VALUES ('Dan') RETURNING id;
🔍 Exemple pas à pas
Problème : mettre à jour les scores en toute sécurité, avec prévisualisation et transaction.
import sqlite3
bd = sqlite3.connect(":memory:")
bd.executescript('''
CREATE TABLE membres (id INTEGER PRIMARY KEY, nom TEXT, ville TEXT, score INTEGER DEFAULT 0, actif INTEGER DEFAULT 1);
INSERT INTO membres (nom, ville, score) VALUES ('Ada','Lyon',90), ('Bob','Lille',45), ('Cléo','Lyon',60), ('Dan','Nice',10);
''')
# 1. Prévisualiser
condition = "ville = ? AND score < ?"
params = ("Lyon", 80)
print("à modifier :", bd.execute(f"SELECT nom, score FROM membres WHERE {condition}", params).fetchall())
# 2. Modifier dans une transaction
try:
with bd: # BEGIN ... COMMIT (ROLLBACK si exception)
cur = bd.execute(f"UPDATE membres SET score = score + 5 WHERE {condition}", params)
print("lignes modifiées :", cur.rowcount)
if cur.rowcount > 10:
raise RuntimeError("trop de lignes, on annule")
except RuntimeError as e:
print(e)
print(bd.execute("SELECT nom, score FROM membres ORDER BY id").fetchall())
Upsert pour un compteur :
bd.executescript('''
CREATE TABLE visites (page TEXT PRIMARY KEY, total INTEGER NOT NULL);
''')
for page in ("accueil", "cours", "accueil", "accueil"):
bd.execute("INSERT INTO visites VALUES (?, 1) ON CONFLICT(page) DO UPDATE SET total = total + 1", (page,))
print(bd.execute("SELECT * FROM visites ORDER BY page").fetchall()) # [('accueil', 3), ('cours', 1)]
Suppression logique :
bd.execute("ALTER TABLE membres ADD COLUMN supprime_le TEXT")
bd.execute("UPDATE membres SET supprime_le = '2025-03-01' WHERE nom = 'Dan'")
print(bd.execute("SELECT nom FROM membres WHERE supprime_le IS NULL ORDER BY id").fetchall())
Dan n'est plus visible, mais ses données existent toujours.
✍️ À toi, avec aide (10 XP)
Dans la table membres(id, nom, ville, score, actif) :
- Prévisualise puis augmente de 10 % le score des membres de Lille.
- Ajoute un membre « Eve » à Nice avec un score par défaut et récupère son
id. - Désactive (suppression logique,
actif = 0) les membres dont le score est inférieur à 20. - Montre ce que renverrait un
DELETEsansWHEREdans une transaction annulée (on vérifie sans danger).
💡 Indice 1
score * 110 / 100 pour +10 % (entiers).
💡 Indice 2
cursor.lastrowid après un INSERT.
💡 Indice 3
bd.rollback() annule la transaction en cours.
✅ Réponse
import sqlite3
bd = sqlite3.connect(":memory:")
bd.executescript('''
CREATE TABLE membres (id INTEGER PRIMARY KEY, nom TEXT, ville TEXT, score INTEGER DEFAULT 0, actif INTEGER DEFAULT 1);
INSERT INTO membres (nom, ville, score) VALUES ('Ada','Lyon',90), ('Bob','Lille',45), ('Cléo','Lille',60), ('Dan','Nice',10);
''')
print(bd.execute("SELECT nom, score FROM membres WHERE ville = 'Lille'").fetchall())
with bd:
bd.execute("UPDATE membres SET score = score * 110 / 100 WHERE ville = 'Lille'")
cur = bd.execute("INSERT INTO membres (nom, ville) VALUES ('Eve', 'Nice')")
bd.commit()
print("id d'Eve :", cur.lastrowid)
with bd:
bd.execute("UPDATE membres SET actif = 0 WHERE score < 20")
print(bd.execute("SELECT nom, score, actif FROM membres ORDER BY id").fetchall())
bd.execute("BEGIN")
cur = bd.execute("DELETE FROM membres")
print("un DELETE sans WHERE aurait supprimé", cur.rowcount, "lignes")
bd.rollback()
print(bd.execute("SELECT COUNT(*) FROM membres").fetchone()[0], "lignes toujours là")
🚀 À toi, en solo (20 XP)
Synchronisation de données. Tu reçois chaque nuit un fichier nouveautes (liste de couples (email, nom)) à fusionner dans la table contacts(email PRIMARY KEY, nom, mis_a_jour_le).
Écris synchroniser(bd, nouveautes, date) qui :
- insère les nouveaux e-mails ;
- met à jour le nom des e-mails existants seulement s'il a changé ;
- renvoie
(inseres, modifies, inchanges); - est atomique : si une ligne est invalide (e-mail sans
@), rien n'est modifié.
✅ Pistes de correction
import sqlite3
def synchroniser(bd, nouveautes, date):
for email, nom in nouveautes:
if "@" not in email:
raise ValueError(f"e-mail invalide : {email}")
inseres = modifies = inchanges = 0
with bd: # tout ou rien
for email, nom in nouveautes:
ligne = bd.execute("SELECT nom FROM contacts WHERE email = ?", (email,)).fetchone()
if ligne is None:
bd.execute("INSERT INTO contacts VALUES (?, ?, ?)", (email, nom, date))
inseres += 1
elif ligne[0] != nom:
bd.execute("UPDATE contacts SET nom = ?, mis_a_jour_le = ? WHERE email = ?", (nom, date, email))
modifies += 1
else:
inchanges += 1
return inseres, modifies, inchanges
bd = sqlite3.connect(":memory:")
bd.execute("CREATE TABLE contacts (email TEXT PRIMARY KEY, nom TEXT NOT NULL, mis_a_jour_le TEXT)")
print(synchroniser(bd, [("[email protected]", "Ada"), ("[email protected]", "Bob")], ))
(synchroniser(bd, [(, ), (, ), (, )], ))
:
synchroniser(bd, [(, ), (, )], )
ValueError e:
(e, bd.execute().fetchone()[], )
⚠️ Pièges classiques
| Piège | Pourquoi c'est un problème | Comment l'éviter |
|---|---|---|
| UPDATE/DELETE sans WHERE | Toutes les lignes modifiées | SELECT de prévisualisation + transaction |
| Pas de transaction | Opération à moitié faite en cas d'erreur | with bd: ou BEGIN/COMMIT |
| Supprimer sans sauvegarde | Perte irréversible | Sauvegarde ou suppression logique |
| Coller des valeurs dans le SQL | Injection, erreurs de guillemets | Paramètres ? |
🐉 Mini-boss (40 XP)
Outil de migration sûr. Écris une fonction appliquer(bd, requete, params, max_lignes) qui exécute une modification en suivant la règle d'or :
- prévisualise (transforme l'UPDATE/DELETE en SELECT de comptage) ;
- refuse si le nombre de lignes dépasse
max_lignes; - exécute dans une transaction ;
- vérifie que
rowcountcorrespond à la prévisualisation ; - journalise (dans une table
journal) la requête, les paramètres, le nombre de lignes et l'horodatage ; - propose
annuler_dernier()qui restaure les lignes modifiées à partir d'une copie de sauvegarde prise avant l'opération.
Teste avec un UPDATE normal, un DELETE sans WHERE (refusé), et un rollback.
✅ Pistes de correction
Un bon travail : pour un DELETE FROM t WHERE c, la prévisualisation est SELECT COUNT(*) FROM t WHERE c ; un DELETE/UPDATE sans WHERE est détecté par analyse du texte (ou refusé d'office) ; la sauvegarde est faite avec CREATE TABLE sauvegarde_n AS SELECT * FROM t WHERE c avant l'opération ; annuler_dernier réinsère ou rétablit à partir de cette copie dans une transaction ; le journal garde l'opération dans la même transaction pour rester cohérent. Les tests montrent : modification acceptée (n lignes), tentative refusée (trop de lignes), rollback après erreur simulée.
⚡ Quiz éclair (15 XP si 4/5 ou plus)
1. Un UPDATE sans WHERE :
- A. modifie toutes les lignes de la table
- B. ne fait rien
- C. provoque une erreur
2. Un upsert est :
- A. supprimer puis insérer
- B. insérer ou mettre à jour si la clé existe
- C. un type d'index
3. Avant une modification risquée, on devrait :
- A. exécuter directement
- B. désactiver la base
- C. prévisualiser avec un SELECT et utiliser une transaction
4. 🔁 Révision mission 03 : Pour tester l'absence de valeur on écrit :
- A. IS NULL
- B. = NULL
- C. == NULL
5. 🔁 Révision mission 03 : Pour éviter l'injection SQL, on utilise :
- A. des guillemets doubles
- B. des requêtes paramétrées
- C. des majuscules
✅ Réponses
- A.
- B.
- C.
- A.
- B.
🪞 Je réfléchis (10 XP)
- Quelle action irréversible as-tu déjà regretté d'avoir faite trop vite ?
- Quelles précautions prends-tu avant de modifier un fichier important ?
🎒 À retenir
- INSERT, UPDATE, DELETE : toujours avec précaution.
- Prévisualiser (SELECT), transaction, vérifier
rowcount. - Upsert : ON CONFLICT DO UPDATE.
- Suppression logique pour garder l'historique.
- Paramètres plutôt que concaténation.
flowchart LR
T(["SQL d'écriture : modifier les données"])
T --> R1["1. INSERT, UPDATE, DELETE : toujours avec précaution."]
T --> R2["2. Prévisualiser SELECT, transaction, vérifier rowcount."]
T --> R3["3. Upsert : ON CONFLICT DO UPDATE."]
T --> R4["4. Suppression logique pour garder l'historique."]
T --> R5["5. Paramètres plutôt que concaténation."]
Description du schéma : la mission au centre, reliée aux cinq idées à retenir.
🏅 Récompenses
Total possible : 100 XP · 🏅 Badge Éditeur de données
← Mission 03 — SQL de lecture : interroger les données · Mission 05 — Contraintes et intégrité →
HEF-070 — Bases de données

