Modifications de données et transactions

Modifications de données et transactions#

Les requêtes SELECT lisent la base sans la modifier. Les commandes INSERT, UPDATE et DELETE constituent le langage de manipulation des données (DML). Regroupées dans des transactions, elles permettent de garantir la cohérence de la base même en cas d’erreur ou de panne.

Hide code cell source

 
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)

conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA journal_mode=WAL")

conn.executescript("""
CREATE TABLE clients (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    nom     TEXT NOT NULL,
    email   TEXT UNIQUE,
    solde   REAL NOT NULL DEFAULT 0.0
);

CREATE TABLE comptes (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER REFERENCES clients(id),
    type      TEXT NOT NULL,     -- 'courant' ou 'epargne'
    solde     REAL NOT NULL DEFAULT 0.0
);

CREATE TABLE transactions_log (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    compte_src  INTEGER,
    compte_dst  INTEGER,
    montant     REAL,
    statut      TEXT,
    horodatage  TEXT DEFAULT (datetime('now'))
);

INSERT INTO clients(nom, email, solde) VALUES
    ('Alice Martin', 'alice@example.com', 0),
    ('Bob Dupont',   'bob@example.com',   0),
    ('Carla Petit',  'carla@example.com', 0);

INSERT INTO comptes(client_id, type, solde) VALUES
    (1, 'courant', 5000),
    (1, 'epargne', 12000),
    (2, 'courant', 3200),
    (3, 'courant', 800);
""")

pd.read_sql("SELECT * FROM comptes", conn)
id client_id type solde
0 1 1 courant 5000.0
1 2 1 epargne 12000.0
2 3 2 courant 3200.0
3 4 3 courant 800.0

INSERT#

Définition 77

La commande INSERT INTO ajoute une ou plusieurs lignes dans une table. Trois variantes principales :

  • INSERT INTO t VALUES (...) — ligne unique avec toutes les colonnes.

  • INSERT INTO t (col1, col2) VALUES (...), (...) — lignes multiples, colonnes explicites.

  • INSERT INTO t SELECT ... — insertion à partir d’une requête.

# INSERT ligne unique
conn.execute("INSERT INTO clients(nom, email, solde) VALUES ('David Noir', 'david@example.com', 0)")

# INSERT multiple
conn.executemany(
    "INSERT INTO comptes(client_id, type, solde) VALUES (?,?,?)",
    [(4, 'courant', 2500), (4, 'epargne', 7000)]
)
conn.commit()

pd.read_sql("SELECT * FROM clients", conn)
id nom email solde
0 1 Alice Martin alice@example.com 0.0
1 2 Bob Dupont bob@example.com 0.0
2 3 Carla Petit carla@example.com 0.0
3 4 David Noir david@example.com 0.0 
# INSERT ... SELECT : créer une table de sauvegarde depuis une requête
conn.execute("""
CREATE TABLE IF NOT EXISTS comptes_archive AS
SELECT *, datetime('now') AS archive_date
FROM comptes WHERE solde = 0
""")

# INSERT INTO table existante depuis SELECT
conn.execute("""
INSERT INTO transactions_log(compte_src, compte_dst, montant, statut)
SELECT NULL, id, solde, 'initialisation'
FROM comptes
""")
conn.commit()

pd.read_sql("SELECT * FROM transactions_log", conn)
id compte_src compte_dst montant statut horodatage
0 1 None 1 5000.0 initialisation 2026-03-31 22:46:59
1 2 None 2 12000.0 initialisation 2026-03-31 22:46:59
2 3 None 3 3200.0 initialisation 2026-03-31 22:46:59
3 4 None 4 800.0 initialisation 2026-03-31 22:46:59
4 5 None 5 2500.0 initialisation 2026-03-31 22:46:59
5 6 None 6 7000.0 initialisation 2026-03-31 22:46:59

Remarque 38

En SQLite, INSERT OR REPLACE INTO combine un INSERT avec un remplacement automatique de la ligne existante si la contrainte d’unicité est violée. Cela équivaut à un DELETE + INSERT. À ne pas confondre avec INSERT OR IGNORE qui ignore silencieusement les doublons.

# INSERT OR REPLACE : si l'email existe déjà, la ligne est remplacée
conn.execute("""
INSERT OR REPLACE INTO clients(nom, email, solde)
VALUES ('Alice Martin-Leblanc', 'alice@example.com', 0)
""")
conn.commit()

pd.read_sql("SELECT * FROM clients WHERE email='alice@example.com'", conn)
id nom email solde
0 5 Alice Martin-Leblanc alice@example.com 0.0

UPDATE#

Définition 78

La commande UPDATE modifie les valeurs d’une ou plusieurs colonnes pour les lignes qui satisfont la clause WHERE. Sans WHERE, toutes les lignes de la table sont modifiées — une erreur classique et souvent catastrophique.

Remarque 39

Bonne pratique : avant d’exécuter un UPDATE, formuler d’abord le SELECT correspondant pour vérifier les lignes ciblées. Cette habitude prévient les mises à jour accidentelles sur l’ensemble de la table.

# UPDATE simple : augmentation de 5% du solde du compte épargne d'Alice
conn.execute("""
UPDATE comptes
SET solde = solde * 1.05
WHERE type = 'epargne' AND client_id = (
    SELECT id FROM clients WHERE email = 'alice@example.com'
)
""")
conn.commit()

pd.read_sql("""
SELECT cl.nom, co.type, co.solde
FROM comptes co
JOIN clients cl ON co.client_id = cl.id
ORDER BY cl.nom, co.type
""", conn)
nom type solde
0 Bob Dupont courant 3200.0
1 Carla Petit courant 800.0
2 David Noir courant 2500.0
3 David Noir epargne 7000.0

Exemple 29

UPDATE avec sous-requête : mettre à jour le solde agrégé d’un client à partir de la somme de ses comptes.

conn.execute("""
UPDATE clients
SET solde = (
    SELECT COALESCE(SUM(solde), 0)
    FROM comptes
    WHERE comptes.client_id = clients.id
)
""")
conn.commit()

pd.read_sql("SELECT nom, solde FROM clients ORDER BY nom", conn)
nom solde
0 Alice Martin-Leblanc 0.0
1 Bob Dupont 3200.0
2 Carla Petit 800.0
3 David Noir 9500.0

DELETE#

Définition 79

La commande DELETE FROM supprime les lignes satisfaisant la clause WHERE. Sans WHERE, elle vide la table entière (mais conserve la structure, contrairement à DROP TABLE). En SQLite, TRUNCATE TABLE n’existe pas ; on utilise DELETE FROM table sans condition.

# DELETE ciblé : supprimer les logs d'initialisation
conn.execute("DELETE FROM transactions_log WHERE statut = 'initialisation'")
conn.commit()

print("Lignes restantes dans transactions_log :",
      pd.read_sql("SELECT COUNT(*) AS n FROM transactions_log", conn).iloc[0,0])

Lignes restantes dans transactions_log : 0

Remarque 40

Si des contraintes de clé étrangère avec ON DELETE CASCADE sont définies, la suppression d’une ligne parente déclenche automatiquement la suppression des lignes enfants associées. En SQLite, les contraintes de clé étrangère doivent être activées explicitement avec PRAGMA foreign_keys = ON.

UPSERT — INSERT … ON CONFLICT#

Définition 80

Un UPSERT (update or insert) insère une ligne si elle n’existe pas encore, ou la met à jour si une contrainte d’unicité est violée.

  • SQLite : INSERT OR REPLACE ou INSERT ... ON CONFLICT(col) DO UPDATE SET ...

  • PostgreSQL : INSERT ... ON CONFLICT (col) DO UPDATE SET ... (syntaxe standard moderne)

# UPSERT SQLite : ON CONFLICT DO UPDATE (syntaxe moderne >= SQLite 3.24)
conn.execute("""
INSERT INTO clients(nom, email, solde)
VALUES ('Eva Blanc', 'eva@example.com', 1500)
ON CONFLICT(email)
DO UPDATE SET nom = excluded.nom, solde = excluded.solde
""")
conn.commit()

# Deuxième appel avec la même clé : déclenche le DO UPDATE
conn.execute("""
INSERT INTO clients(nom, email, solde)
VALUES ('Eva Blanc-Dupont', 'eva@example.com', 2000)
ON CONFLICT(email)
DO UPDATE SET nom = excluded.nom, solde = excluded.solde
""")
conn.commit()

pd.read_sql("SELECT * FROM clients WHERE email='eva@example.com'", conn)
id nom email solde
0 6 Eva Blanc-Dupont eva@example.com 2000.0

Transactions — BEGIN, COMMIT, ROLLBACK#

Définition 81

Une transaction est une séquence d’opérations DML traitée comme une unité indivisible. En SQL :

  • BEGIN (ou BEGIN TRANSACTION) démarre la transaction.

  • COMMIT valide définitivement toutes les modifications.

  • ROLLBACK annule toutes les modifications depuis le dernier BEGIN.

Théorème 4

Les propriétés ACID garantissent la fiabilité des transactions :

  • Atomicité : la transaction réussit entièrement ou échoue entièrement — pas d’état intermédiaire.

  • Cohérence : la base passe d’un état valide à un autre état valide ; toutes les contraintes sont respectées.

  • Isolation : les transactions concurrentes ne s’interfèrent pas ; chacune voit un état cohérent.

  • Durabilité : une fois validée (COMMIT), une transaction survit à une panne système.

def virement(conn, compte_src_id, compte_dst_id, montant):
    """
    Effectue un virement entre deux comptes.
    La transaction garantit l'atomicité : soit les deux mises à jour
    réussissent, soit aucune n'est appliquée.
    """
    try:
        # SQLite en Python : isolation_level=None pour contrôle manuel
        conn.execute("BEGIN")

        # 1. Vérifier le solde suffisant
        solde_src = conn.execute(
            "SELECT solde FROM comptes WHERE id = ?", (compte_src_id,)
        ).fetchone()[0]

        if solde_src < montant:
            raise ValueError(f"Solde insuffisant : {solde_src} < {montant}")

        # 2. Débiter le compte source
        conn.execute(
            "UPDATE comptes SET solde = solde - ? WHERE id = ?",
            (montant, compte_src_id)
        )

        # 3. Créditer le compte destination
        conn.execute(
            "UPDATE comptes SET solde = solde + ? WHERE id = ?",
            (montant, compte_dst_id)
        )

        # 4. Logger la transaction
        conn.execute(
            "INSERT INTO transactions_log(compte_src, compte_dst, montant, statut) VALUES (?,?,?,'OK')",
            (compte_src_id, compte_dst_id, montant)
        )

        conn.execute("COMMIT")
        print(f"Virement de {montant} € de compte {compte_src_id} vers {compte_dst_id} : OK")

    except Exception as e:
        conn.execute("ROLLBACK")
        conn.execute(
            "INSERT INTO transactions_log(compte_src, compte_dst, montant, statut) VALUES (?,?,?,?)",
            (compte_src_id, compte_dst_id, montant, f"ECHEC: {e}")
        )
        conn.commit()
        print(f"Virement annulé (ROLLBACK) : {e}")


# Virement réussi : compte 1 (Alice courant) → compte 3 (Bob courant)
virement(conn, 1, 3, 1000)

# Virement échoué : compte 4 (Bob courant) n'a que 3200 + 1000 - tentative de 5000
virement(conn, 3, 2, 9999)

pd.read_sql("""
SELECT co.id, cl.nom, co.type, co.solde
FROM comptes co
JOIN clients cl ON co.client_id = cl.id
ORDER BY cl.nom, co.type
""", conn)

Virement de 1000 € de compte 1 vers 3 : OK
Virement annulé (ROLLBACK) : Solde insuffisant : 4200.0 < 9999
id nom type solde
0 3 Bob Dupont courant 4200.0
1 4 Carla Petit courant 800.0
2 5 David Noir courant 2500.0
3 6 David Noir epargne 7000.0 
pd.read_sql("SELECT * FROM transactions_log ORDER BY id", conn)
id compte_src compte_dst montant statut horodatage
0 7 1 3 1000.0 OK 2026-03-31 22:46:59
1 8 3 2 9999.0 ECHEC: Solde insuffisant : 4200.0 < 9999 2026-03-31 22:46:59

Niveaux d’isolation#

Définition 82

Les niveaux d’isolation contrôlent ce qu’une transaction peut voir des modifications faites par les transactions concurrentes. Du moins restrictif au plus restrictif :

  1. READ UNCOMMITTED : peut lire des données non encore validées (dirty reads).

  2. READ COMMITTED : ne lit que les données validées ; peut voir des modifications entre deux lectures (non-repeatable reads).

  3. REPEATABLE READ : garantit des lectures cohérentes au sein d’une transaction ; peut subir des phantom reads.

  4. SERIALIZABLE : isolation totale — équivalent à une exécution séquentielle des transactions.

Remarque 41

SQLite implémente l’isolation de niveau SERIALIZABLE par défaut via un système de verrous. En mode WAL (Write-Ahead Logging), les lecteurs ne bloquent pas les écrivains, offrant un bon compromis performance/cohérence. PostgreSQL utilise READ COMMITTED par défaut.

Exemple 30

Un dirty read se produit quand une transaction T2 lit une valeur modifiée par T1 avant que T1 n’ait validé. Si T1 fait un ROLLBACK, T2 a lu une donnée qui n’a jamais existé officiellement dans la base.

Savepoints#

Définition 83

Un savepoint est un point de sauvegarde nommé à l’intérieur d’une transaction. Il permet un ROLLBACK TO SAVEPOINT nom partiel, sans annuler toute la transaction. Syntaxe : SAVEPOINT nom_point, ROLLBACK TO SAVEPOINT nom_point, RELEASE SAVEPOINT nom_point.

# Démonstration de SAVEPOINT
conn.execute("BEGIN")
conn.execute("UPDATE comptes SET solde = solde + 100 WHERE id = 1")
conn.execute("SAVEPOINT avant_erreur")
conn.execute("UPDATE comptes SET solde = solde - 999999 WHERE id = 2")  # Erreur volontaire
# On revient au savepoint sans annuler la première opération
conn.execute("ROLLBACK TO SAVEPOINT avant_erreur")
conn.execute("RELEASE SAVEPOINT avant_erreur")
conn.execute("COMMIT")

pd.read_sql("""
SELECT co.id, cl.nom, co.type, co.solde
FROM comptes co
JOIN clients cl ON co.client_id = cl.id
WHERE co.id IN (1,2)
ORDER BY co.id
""", conn)
id nom type solde

Visualisation — timeline d’une transaction#

Hide code cell source

 
fig, ax = plt.subplots(figsize=(12, 4))
ax.set_xlim(0, 10)
ax.set_ylim(-0.5, 2.5)
ax.axis("off")
ax.set_title("Timeline d'une transaction bancaire avec ROLLBACK partiel",
             fontsize=12, fontweight="bold")

palette = sns.color_palette("muted", 5)
events = [
    (0.5, 1, "BEGIN", palette[0]),
    (2.0, 1, "UPDATE\ncompte src\n−1000€", palette[1]),
    (3.5, 1, "SAVEPOINT\nsp1", palette[2]),
    (5.0, 1, "UPDATE\ncompte dst\n+1000€", palette[1]),
    (6.5, 1, "INSERT\nlog OK", palette[3]),
    (8.0, 1, "COMMIT", palette[4]),
]
events_rb = [
    (0.5, 0, "BEGIN", palette[0]),
    (2.0, 0, "UPDATE\ncompte src", palette[1]),
    (3.5, 0, "Erreur !\nsolde insuf.", "tomato"),
    (5.0, 0, "ROLLBACK", "tomato"),
    (6.5, 0, "INSERT\nlog ECHEC", palette[3]),
    (8.0, 0, "COMMIT\n(log seul)", palette[2]),
]

ax.text(0, 1.9, "Scénario succès", fontsize=10, color=palette[4], fontweight="bold")
ax.text(0, 0.9, "Scénario échec", fontsize=10, color="tomato", fontweight="bold")

for events_list in [events, events_rb]:
    xs = [e[0] for e in events_list]
    y = events_list[0][1]
    ax.plot(xs, [y] * len(xs), color="lightgray", linewidth=2, zorder=0)
    for x, _, label, color in events_list:
        ax.scatter([x], [y], s=300, color=color, zorder=5, edgecolors="white", linewidth=1.5)
        ax.text(x, y + 0.2, label, ha="center", va="bottom", fontsize=8,
                bbox=dict(boxstyle="round,pad=0.2", facecolor=color, alpha=0.3, edgecolor="none"))

plt.show()
_images/ff8c7986e81ccdea9b6be8b97bf25a62d2c1abce68432f43df3ad4dd12fe1978.png

Résumé#

Définition 84

Récapitulatif des commandes DML et des transactions :

  • INSERT INTO ajoute des lignes (simple, multiple, depuis SELECT, UPSERT).

  • UPDATE SET ... WHERE modifie des lignes existantes — toujours vérifier le WHERE.

  • DELETE FROM ... WHERE supprime des lignes — sans WHERE, vide la table.

  • INSERT OR REPLACE / ON CONFLICT DO UPDATE : UPSERT SQLite.

  • BEGIN / COMMIT / ROLLBACK encadrent une transaction atomique.

  • Les propriétés ACID garantissent cohérence et fiabilité.

  • Les niveaux d’isolation contrôlent la visibilité inter-transactions (READ COMMITTED, SERIALIZABLE…).

  • SAVEPOINT permet des retours arrière partiels au sein d’une transaction.

Commande

Action

Risque sans WHERE

INSERT

Ajoute des lignes

N/A

UPDATE

Modifie des lignes

Modifie TOUTES les lignes

DELETE

Supprime des lignes

Vide la table entière

ROLLBACK

Annule la transaction courante

N/A
Contenu