---
jupytext:
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.16.0
kernelspec:
  name: python3
  display_name: Python 3
---

# 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.

```{code-cell} python
:tags: [hide-input]

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)
```

```{code-cell} python
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)
```

## INSERT

```{prf:definition}
:label: ch09-def-insert
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.
```

```{code-cell} python
# 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)
```

```{code-cell} python
# 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)
```

```{prf:remark}
:label: ch09-rem-insert-or-replace
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.
```

```{code-cell} python
# 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)
```

## UPDATE

```{prf:definition}
:label: ch09-def-update
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.
```

```{prf:remark}
:label: ch09-rem-update-where
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.
```

```{code-cell} python
# 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)
```

```{prf:example}
:label: ch09-ex-update-sous-requete
`UPDATE` avec sous-requête : mettre à jour le solde agrégé d'un client à partir de la somme de ses comptes.
```

```{code-cell} python
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)
```

## DELETE

```{prf:definition}
:label: ch09-def-delete
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.
```

```{code-cell} python
# 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])
```

```{prf:remark}
:label: ch09-rem-delete-cascade
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

```{prf:definition}
:label: ch09-def-upsert
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)
```

```{code-cell} python
# 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)
```

## Transactions — BEGIN, COMMIT, ROLLBACK

```{prf:definition}
:label: ch09-def-transaction
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`.
```

```{prf:theorem}
:label: ch09-thm-acid
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.
```

```{code-cell} python
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)
```

```{code-cell} python
pd.read_sql("SELECT * FROM transactions_log ORDER BY id", conn)
```

## Niveaux d'isolation

```{prf:definition}
:label: ch09-def-isolation-levels
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.
```

```{prf:remark}
:label: ch09-rem-sqlite-isolation
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.
```

```{prf:example}
:label: ch09-ex-dirty-read
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

```{prf:definition}
:label: ch09-def-savepoint
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`.
```

```{code-cell} python
# 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)
```

## Visualisation — timeline d'une transaction

```{code-cell} python
:tags: [hide-input]

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()
```

## Résumé

```{prf:definition}
:label: ch09-def-synthese
**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 |