Mettre à jour et supprimer : UPDATE et DELETE par id — en SQL et depuis Python

Dans cette leçon#

Cette leçon se compose de deux chapitres, dans le même ordre que les diapositives :

1. Chapitre 1 — UPDATE par id : mettre à jour un enregistrement en SQL et depuis Python
2. Chapitre 2 — DELETE par id : supprimer un enregistrement en SQL et depuis Python

Chapitre 1 : UPDATE par id — mettre à jour un enregistrement en SQL et depuis Python#

Qu’allons-nous apprendre ?#

UPDATE = le U du CRUD : modifier un enregistrement existant.

• La syntaxe UPDATE ... SET ... WHERE ....
• Mettre à jour une colonne, et plusieurs colonnes.
• Pourquoi WHERE est obligatoire.
• Mettre à jour depuis Python, et rowcount.

Pourquoi en avons-nous besoin ?#

Tout système doit mettre à jour : un étudiant a changé d’e-mail, le prix d’un produit a changé, une commande est passée à « expédiée ». Dans l’API du thème de l’API des étudiants, PUT /students/7 = UPDATE students SET ... WHERE id = 7. Et UPDATE est aussi la commande avec laquelle il est le plus facile de détruire des données, si l’on oublie un détail.

Explication à partir des bases#

La syntaxe#

UPDATE students
SET age = 25
WHERE id = 1;

• UPDATE students — quelle table.
• SET age = 25 — quelle colonne reçoit quelle nouvelle valeur. (Ici = est une affectation, et dans WHERE c’est une comparaison. Le même signe, deux rôles.)
• WHERE id = 1 — quelle ligne (ou quelles lignes) on met à jour.

Plusieurs colonnes — séparées par des virgules :

UPDATE students
SET name = 'Dana Cohen', age = 25, email = 'dana.cohen@mail.com'
WHERE id = 1;

Une valeur calculée à partir de la valeur existante :

UPDATE products SET stock = stock - 1 WHERE id = 3;

« Le nouveau stock = le stock actuel moins 1 ». (Comme x = x - 1 en Python.)

WHERE — obligatoire !#

UPDATE students SET age = 25;

Sans WHERE, la modification s’applique à toutes les lignes : tous les étudiants ont maintenant 25 ans. Il n’y a pas de « êtes-vous sûr ? », ni d’annulation. (Après un commit, les anciennes données sont perdues.)

rowcount — combien de lignes ont changé ?#

Après un UPDATE depuis Python, le curseur contient cursor.rowcount = le nombre de lignes réellement mises à jour :

Un UPDATE sur un id qui n’existe pas n’est pas une erreur en SQL. La commande a « réussi » et a mis à jour 0 ligne.

Remarquez la deuxième ligne du tableau : dans MySQL, rowcount ne compte que les lignes qui ont vraiment changé. Si vous mettez l’âge d’Avi à 31, et qu’il a déjà 31 ans — rowcount vaut 0, alors qu’Avi existe. Donc, pour savoir si un enregistrement existe (pour le 404 de PUT), la façon sûre est de le récupérer (SELECT ... WHERE id = %s) et de vérifier si None est revenu.

Syntaxe#

UPDATE table_name
SET column_a = value_a, column_b = value_b
WHERE id = 7;

cursor = connection.cursor()
cursor.execute(
    "UPDATE students SET name = %s, age = %s WHERE id = %s",
    (new_name, new_age, student_id),
)
connection.commit()
cursor.rowcount        # 1 = changed, 0 = nothing changed

Remarquez l’ordre des paramètres : ils suivent l’ordre des %s dans toute la requête : d’abord le SET, et à la fin l’id du WHERE.

Premier exemple de code#

La table avant :

id | name | age | email
 1 | Dana |  24 | dana@mail.com
 2 | Avi  |  31 | avi@mail.com
 3 | Noa  |  22 | noa@mail.com

SELECT id, name, age FROM students WHERE id = 2;
UPDATE students SET age = 32 WHERE id = 2;
SELECT id, name, age FROM students WHERE id = 2;

Résultat :

id | name | age
 2 | Avi  |  31

id | name | age
 2 | Avi  |  32

Analyse du code ligne par ligne#

Un autre exemple#

Mettre à jour depuis Python, avec rowcount, et une ligne qui n’existe pas. update_demo.py :

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("DROP TABLE IF EXISTS students")
    cursor.execute(
        "CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, "
        "age INT NOT NULL, email VARCHAR(100) NOT NULL UNIQUE)"
    )
    cursor.executemany(
        "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
        [("Dana", 24, "dana@mail.com"), ("Avi", 31, "avi@mail.com"), ("Noa", 22, "noa@mail.com")],
    )
    connection.commit()

    cursor.execute(
        "UPDATE students SET name = %s, age = %s, email = %s WHERE id = %s",
        ("Dana Cohen", 25, "dana.cohen@mail.com", 1),
    )
    connection.commit()
    print("Updated rows for id 1:", cursor.rowcount)

    cursor.execute("UPDATE students SET age = %s WHERE id = %s", (40, 99))
    connection.commit()
    print("Updated rows for id 99:", cursor.rowcount)

    cursor.execute("UPDATE students SET age = %s WHERE id = %s", (31, 2))
    connection.commit()
    print("Updated rows for id 2, same age:", cursor.rowcount)

    cursor.execute("UPDATE students SET age = age + 1 WHERE age < %s", (30,))
    connection.commit()
    print("Birthday for everyone under 30:", cursor.rowcount, "rows")

    cursor = connection.cursor(dictionary=True)
    cursor.execute("SELECT id, name, age, email FROM students ORDER BY id")
    for row in cursor.fetchall():
        print(row)
finally:
    connection.close()

Sortie :

Updated rows for id 1: 1
Updated rows for id 99: 0
Updated rows for id 2, same age: 0
Birthday for everyone under 30: 2 rows
{'id': 1, 'name': 'Dana Cohen', 'age': 26, 'email': 'dana.cohen@mail.com'}
{'id': 2, 'name': 'Avi', 'age': 31, 'email': 'avi@mail.com'}
{'id': 3, 'name': 'Noa', 'age': 23, 'email': 'noa@mail.com'}

Analyse :

• DROP TABLE IF EXISTS — chaque exécution part d’une table vide (seulement pour la démonstration !).
• executemany(sql, list_of_tuples) — exécute la même requête pour chaque tuple de la liste. Pratique pour insérer plusieurs lignes.
• Mettre à jour 3 colonnes — 4 emplacements : trois pour le SET et un pour le WHERE, dans le même ordre dans le tuple.
• id 99 — rowcount = 0. Sans erreur.
• id 2 avec le même âge — aussi rowcount = 0, alors qu’Avi existe ! Rien n’a changé. C’est pourquoi « introuvable » se vérifie avec SELECT, et non avec rowcount.
• age = age + 1 WHERE age < 30 — WHERE peut choisir plusieurs lignes. Dana (25, après la mise à jour) et Noa (22) ont moins de 30 ans, donc rowcount = 2. Avi n’a pas changé.
• connection.cursor(dictionary=True) — un nouveau curseur, pour obtenir à la fin les lignes sous forme de dictionnaires.

Erreur courante#

Erreur 1 (la grande) : un UPDATE sans WHERE.

cursor.execute("UPDATE students SET email = %s", (new_email,))

Tous les étudiants reçoivent le même e-mail. (Ici, grâce à UNIQUE, la base refuse en fait, car deux étudiants ne peuvent pas recevoir le même e-mail. Les contraintes sauvent la mise ! Mais il n’y avait aucune contrainte sur age pour l’arrêter.) Vérifiez chaque UPDATE : où est le WHERE ?

Erreur 2 : un mauvais ordre des paramètres.

cursor.execute("UPDATE students SET age = %s WHERE id = %s", (student_id, new_age))

Maintenant, vous mettez à jour l’étudiant dont l’id est égal à l’âge, avec un âge égal à l’id. Il n’y a pas d’erreur, et c’est une Logical Error très difficile à trouver. L’ordre dans le tuple = l’ordre des %s dans la requête, de gauche à droite.

Erreur 3 : croire qu’un UPDATE sur un id inexistant lance une erreur. Ce n’est pas le cas. Un code qui ne vérifie pas renverra « mis à jour avec succès » pour un étudiant qui n’existe pas. Et inversement : rowcount == 0 ne signifie pas forcément « n’existe pas » — peut-être qu’aucune valeur n’a simplement changé. Vérifiez l’existence avec SELECT.

Erreur 4 : oublier le commit (comme pour INSERT). La mise à jour est visible dans la même connexion, et disparaît après la fermeture.

Résumé#

• UPDATE table SET col = %s, col2 = %s WHERE id = %s — mettre à jour un enregistrement existant. Dans SET, = est une affectation.
• Sans WHERE, toutes les lignes sont mises à jour. Une habitude : un SELECT avec le même WHERE avant de l’exécuter à la main.
• Une nouvelle valeur peut être calculée : stock = stock - 1.
• Depuis Python : les paramètres dans l’ordre des %s (SET puis WHERE), et commit.
• cursor.rowcount = combien de lignes ont changé. 0 = elle n’existe pas, ou aucune valeur n’a changé. Le 404 du PUT se décide avec un SELECT qui renvoie None.

Documentation officielle#

• MySQL — UPDATE
• MySQLConnection.commit()

Chapitre 2 : DELETE par id — supprimer un enregistrement en SQL et depuis Python#

Qu’allons-nous apprendre ?#

DELETE = le D du CRUD : supprimer des enregistrements.

• La syntaxe DELETE FROM ... WHERE ....
• DELETE sans WHERE — ce qu’il fait, et comment l’éviter.
• Supprimer depuis Python, et rowcount.
• Ce qu’il advient de l’id après la suppression.

Pourquoi en avons-nous besoin ?#

Un utilisateur supprime un compte, un responsable supprime un produit qui n’est plus vendu, un étudiant quitte le cours. Dans l’API : DELETE /students/7 = DELETE FROM students WHERE id = 7. Et la suppression est la seule action du CRUD sans retour possible : après un commit, les données n’existent plus.

Explication à partir des bases#

La syntaxe#

DELETE FROM students WHERE id = 3;

• DELETE FROM students — de quelle table.
• WHERE id = 3 — quelles lignes supprimer.
• Il n’y a pas de liste de colonnes : on supprime des lignes entières, pas des colonnes. (Pour « vider » un champ, on utilise UPDATE ... SET field = NULL.)

DELETE sans WHERE#

DELETE FROM students;

Supprime toutes les lignes de la table. La table elle-même reste (vide), mais toutes les données disparaissent. C’est probablement la commande qui a « détruit » le plus de données dans l’histoire du logiciel. La même règle que pour UPDATE : d’abord un SELECT avec le même WHERE.

rowcount — encore#

Qu’advient-il de l’id ?#

Avec AUTO_INCREMENT, MySQL ne réutilise pas un id. Si vous avez supprimé l’étudiant 3, le prochain étudiant ajouté reçoit 4 (et non 3). Pourquoi est-ce une bonne chose ? Parce que si l’id 3 passait à quelqu’un d’autre, tout ce qui pointait vers « l’étudiant 3 » (un lien, une commande, un journal) pointerait soudain vers une autre personne.

Le Soft Delete — à un niveau d’introduction#

Dans de nombreux systèmes, on ne supprime pas vraiment. On ajoute à la table une colonne comme is_active — 1 = actif, 0 = « supprimé » — et la « suppression » est en fait un UPDATE. Ainsi, on peut restaurer, et garder l’historique (par exemple, les anciennes commandes d’un client parti restent valides).

En SQL :

-- the table has: is_active INT NOT NULL DEFAULT 1
-- "delete" = mark the row, do not remove it
UPDATE students SET is_active = 0 WHERE id = 3;

-- every read shows only active students
SELECT id, name FROM students WHERE is_active = 1;
-- 1 | Dana
-- 2 | Avi

-- restore = one more UPDATE
UPDATE students SET is_active = 1 WHERE id = 3;

Depuis Python — exactement comme delete_student, simplement avec UPDATE au lieu de DELETE :

def soft_delete_student(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute(
            "UPDATE students SET is_active = 0 "
            "WHERE id = %s", (student_id,))
        connection.commit()
        return cursor.rowcount > 0
    finally:
        connection.close()

DEFAULT 1 signifie que chaque nouvel étudiant est créé actif, sans avoir à l’écrire dans l’INSERT. Le prix d’une suppression douce : chaque requête qui affiche une liste doit penser à WHERE is_active = 1, sinon les étudiants « supprimés » réapparaîtront. Ici, nous utiliserons un vrai DELETE, mais il est bon de savoir que l’option existe.

Syntaxe#

DELETE FROM table_name WHERE id = 7;

cursor = connection.cursor()
cursor.execute("DELETE FROM students WHERE id = %s", (student_id,))
connection.commit()
cursor.rowcount        # 1 = deleted, 0 = no such row

Premier exemple de code#

SELECT id, name FROM students WHERE id = 3;
DELETE FROM students WHERE id = 3;
SELECT id, name FROM students;

Résultat :

id | name
 3 | Noa

id | name
 1 | Dana
 2 | Avi

Analyse du code ligne par ligne#

Un autre exemple#

Supprimer depuis Python : rowcount, une double suppression, et un id qui n’est pas réutilisé. delete_demo.py :

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("DROP TABLE IF EXISTS students")
    cursor.execute(
        "CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL)"
    )
    cursor.executemany("INSERT INTO students (name) VALUES (%s)", [("Dana",), ("Avi",), ("Noa",)])
    connection.commit()

    cursor.execute("DELETE FROM students WHERE id = %s", (3,))
    connection.commit()
    print("Deleted id 3:", cursor.rowcount, "row(s)")

    cursor.execute("DELETE FROM students WHERE id = %s", (3,))
    connection.commit()
    print("Deleted id 3 again:", cursor.rowcount, "row(s)")

    cursor.execute("INSERT INTO students (name) VALUES (%s)", ("Tom",))
    connection.commit()
    print("Tom got id:", cursor.lastrowid)

    cursor.execute("SELECT id, name FROM students ORDER BY id")
    print("Rows now:", cursor.fetchall())
finally:
    connection.close()

Sortie :

Deleted id 3: 1 row(s)
Deleted id 3 again: 0 row(s)
Tom got id: 4
Rows now: [(1, 'Dana'), (2, 'Avi'), (4, 'Tom')]

Analyse :

• DROP TABLE IF EXISTS — chaque exécution part d’une table vide (seulement pour la démonstration !).
• [("Dana",), ("Avi",), ("Noa",)] — chaque ligne a un tuple d’une seule valeur, il y a donc une virgule après chaque nom.
• La première suppression → rowcount = 1.
• Une seconde suppression du même id → rowcount = 0, sans erreur. Dans l’API : la première requête renvoie 204, et la seconde 404.
• Tom a reçu 4, et non 3. AUTO_INCREMENT ne réutilise pas. Il y a un « trou » dans la numérotation, et c’est très bien.

Erreur courante#

Erreur 1 : DELETE sans WHERE.

cursor.execute("DELETE FROM students")
connection.commit()

Tous les étudiants ont été supprimés. Dans chaque morceau de code qui contient un DELETE : où est le WHERE, et quel est son paramètre ?

Erreur 2 : un WHERE qui attrape plus que prévu.

DELETE FROM students WHERE name = 'Dana';

S’il y a deux Dana, les deux sont supprimées. Supprimez par la Primary Key (id), qui est garantie unique. Un nom n’est pas un identifiant.

Erreur 3 : oublier le commit. La suppression « fonctionne » dans la connexion, et disparaît après la fermeture. Dans le cas d’une suppression, c’est en fait un « bug qui sauve », mais c’est quand même un bug.

Erreur 4 : lire un enregistrement après l’avoir supprimé. get_student(id) après la suppression renvoie None. Si le code suppose qu’il existe (student["name"]), il plantera. C’est pourquoi delete_student renverra True/False et non l’étudiant supprimé.

Résumé#

• DELETE FROM table WHERE id = %s — supprime des lignes entières. Après un commit, il n’y a pas de retour possible.
• Sans WHERE, toutes les lignes sont supprimées. Supprimez par la Primary Key, et vérifiez avec un SELECT avant.
• cursor.rowcount : 1 = supprimée, 0 = elle n’existait pas, sans erreur. Dans l’API : 204 / 404.
• AUTO_INCREMENT ne réutilise pas un id. Des « trous » dans la numérotation, c’est très bien.
• Le Soft Delete (is_deleted) est une alternative courante dans les vrais systèmes.

Documentation officielle#

• MySQL — DELETE
• MySQLCursor.rowcount

Exercices#

Les exercices n’utilisent que ce qui a été vu dans cette leçon et les précédentes. Essayez d’abord seul, et n’ouvrez la solution qu’ensuite.

Exercice 1: UPDATE en SQL#

Écrivez une commande SQL qui change l’âge de l’étudiant dont l’id vaut 2 en 32. Que se passera-t-il si vous oubliez le WHERE ?

UPDATE students SET age = 32 WHERE id = 2;

Exercice 2: UPDATE depuis Python et rowcount#

La table contient une étudiante — Dana (id 1, 24 ans). Créez-la ainsi :

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
connection.commit()
connection.close()

Écrivez update_age(student_id, new_age) qui met à jour avec des paramètres, et renvoie combien de lignes ont été mises à jour (rowcount).

print("Updated rows for id 1:", update_age(1, 25))
print("Updated rows for id 99:", update_age(99, 40))

Résultat attendu :

Updated rows for id 1: 1
Updated rows for id 99: 0

import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def update_age(student_id, new_age):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute(
            "UPDATE students SET age = %s WHERE id = %s", (new_age, student_id))
        connection.commit()
        return cursor.rowcount
    finally:
        connection.close()


setup = get_connection()
cursor = setup.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
setup.commit()
setup.close()

print("Updated rows for id 1:", update_age(1, 25))
print("Updated rows for id 99:", update_age(99, 40))

Updated rows for id 1: 1
Updated rows for id 99: 0

Exercice 3: update_student#

Repartez d’une table contenant seulement Dana (id 1, 24 ans), comme dans l’exercice 2. Écrivez update_student(student_id, name, age) qui met à jour les deux champs et renvoie l’étudiant mis à jour sous forme de dictionnaire, ou None s’il n’existe pas.

print(update_student(1, "Dana Cohen", 26))
print(update_student(99, "Nobody", 1))

Résultat attendu :

{'id': 1, 'name': 'Dana Cohen', 'age': 26}
None

import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def update_student(student_id, name, age):
    connection = get_connection()
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(
            "UPDATE students SET name = %s, age = %s WHERE id = %s",
            (name, age, student_id))
        connection.commit()
        cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
        return cursor.fetchone()
    finally:
        connection.close()


setup = get_connection()
cursor = setup.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
setup.commit()
setup.close()

print(update_student(1, "Dana Cohen", 26))
print(update_student(99, "Nobody", 1))

{'id': 1, 'name': 'Dana Cohen', 'age': 26}
None

Exercice 4: delete_student#

La table contient trois étudiants — Dana (1), Avi (2) et Noa (3). Créez-la ainsi :

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi'), ('Noa')")
connection.commit()
connection.close()

Écrivez delete_student(student_id) qui renvoie True si une ligne a été supprimée et False sinon. Supprimez deux fois le même étudiant (Noa, id 3).

print(delete_student(3))
print(delete_student(3))

Résultat attendu :

True
False

import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def delete_student(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("DELETE FROM students WHERE id = %s", (student_id,))
        connection.commit()
        return cursor.rowcount > 0
    finally:
        connection.close()


setup = get_connection()
cursor = setup.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi'), ('Noa')")
setup.commit()
setup.close()

print(delete_student(3))
print(delete_student(3))

True
False

Exercice 5: Qu’advient-il d’un id supprimé ?#

Avec trois étudiants (1, 2, 3) : supprimez le 3, ajoutez Tom, et affichez son id et toutes les lignes.

Résultat attendu :

Tom got id: 4
[(1, 'Dana'), (2, 'Avi'), (4, 'Tom')]

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi'), ('Noa')")
connection.commit()

cursor.execute("DELETE FROM students WHERE id = %s", (3,))
cursor.execute("INSERT INTO students (name) VALUES (%s)", ("Tom",))
connection.commit()
print("Tom got id:", cursor.lastrowid)
cursor.execute("SELECT id, name FROM students ORDER BY id")
print(cursor.fetchall())
connection.close()

Tom got id: 4
[(1, 'Dana'), (2, 'Avi'), (4, 'Tom')]