Cours de Python / Enregistrer des données avec sqlite3 et les transactions

Le module sqlite3 permet de conserver des données SQL dans un fichier sans serveur de base de données séparé. Transmettez les valeurs par paramètres, puis gérez explicitement la validation ou l’annulation des écritures et la fermeture de la connexion.

Définir la connexion et la transaction

SQLite est un moteur embarqué dans le programme. Il convient notamment à des données locales, mais ses conditions de concurrence et d’exploitation diffèrent de celles d’un serveur de base de données. Ouvrir une connexion ne suffit pas à garantir que toutes les écritures futures seront validées.

Une transaction délimite des opérations à confirmer ensemble par commit ou à annuler par rollback. Ce programme exige Python 3.12 ou plus récent, car il fixe explicitement autocommit=False. Le bloc with con confirme la transaction si son corps réussit ; une exception qui en sort entraîne une annulation. Ce bloc ne ferme pas la connexion, que finally ferme séparément. La documentation du contrôle des transactions sqlite3 décrit cette politique.

Le module est fourni avec les distributions Python usuelles, mais peut manquer dans une construction particulière. Vérifiez l’interpréteur et sa distribution si import sqlite3 échoue ; aucun serveur SQL n’est à installer pour cet exercice. Utilisez un dossier d’essai neuf pour que ledger-demo.db ne désigne pas des données existantes.

Insérer puis relire une dépense

Enregistrez le programme complet en UTF-8 dans sqlite_demo.py. Depuis le dossier d’exercice, lancez python sqlite_demo.py avec l’environnement virtuel du projet sélectionné et un Python 3.12 ou ultérieur. Sous macOS ou Ubuntu hors environnement virtuel, utilisez python3 sqlite_demo.py si python3 est votre commande d’interpréteur.

import sqlite3
MAX_AMOUNT = 1_000_000_000_000
amount = 8000
if type(amount) is not int or not 0 <= amount <= MAX_AMOUNT:
    raise ValueError("amount outside practice range")
con = sqlite3.connect("ledger-demo.db", autocommit=False)
try:
    with con:
        con.execute("CREATE TABLE IF NOT EXISTS expenses (id TEXT PRIMARY KEY, name TEXT NOT NULL, amount INTEGER NOT NULL CHECK(amount BETWEEN 0 AND 1000000000000))")
        con.execute("INSERT INTO expenses VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, amount=excluded.amount", ("e1", "lunch", amount))
    print(con.execute("SELECT name, amount FROM expenses ORDER BY id").fetchall())
finally:
    con.close()

Résultat attendu dans ce nouveau dossier :

[('lunch', 8000)]

Le programme crée ledger-demo.db sous le dossier de travail du terminal, puis la table expenses si elle n’existe pas. Le champ id contient e1, name contient lunch, c’est-à-dire « déjeuner », et amount contient 8000. La lecture renvoie une liste de tuples avec les deux colonnes sélectionnées.

L’instruction INSERT comporte une clause de mise à jour en cas de conflit sur id. Au premier lancement, e1 est inséré ; aux suivants, son nom et son montant sont remplacés par les valeurs fournies. Le terme excluded désigne les valeurs proposées à l’insertion. La documentation SQLite de l’UPSERT précise le comportement de cette combinaison insertion-mise à jour.

La présence d’un même nom ou d’un même montant n’identifie pas automatiquement la même dépense : c’est ici l’identifiant e1 qui définit le conflit. Choisissez une règle d’identification adaptée si vous utilisez cette technique pour des données réelles.

Séparer paramètres, contraintes et types

Les points d’interrogation représentent des valeurs ; le second argument d’execute fournit le tuple correspondant. N’insérez pas une saisie utilisateur par concaténation dans le SQL. Pour une seule valeur, un tuple s’écrit (value,) avec une virgule. Un nom de table ou une expression de tri appartient à la structure SQL : un paramètre de valeur ne le remplace pas. Choisissez une telle structure dans une liste autorisée si elle doit varier.

Le contrôle Python exige un int exact, hors booléens, dans l’intervalle fermé de 0 à 1 000 000 000 000. Il précède la connexion et la liaison SQL : une valeur refusée ne doit pas modifier la base. Python peut manipuler des entiers plus grands selon la mémoire disponible, alors que la représentation INTEGER de SQLite est limitée à un entier signé de 64 bits. Cette borne d’exercice reste volontairement plus petite.

Les contraintes de la table complètent le contrôle de l’application : NOT NULL refuse un nom ou un montant nul et CHECK limite le montant enregistré. Elles restent pertinentes si une autre voie écrit directement dans la base. Toutefois, déclarer INTEGER dans cette table ordinaire ne suffit pas à imposer le type exact int de Python : l’affinité de SQLite et ses conversions suivent d’autres règles. Un réel positif comme 3.5 peut satisfaire le CHECK de plage. La documentation des types SQLite explique cette distinction.

Enfin, CREATE TABLE IF NOT EXISTS ne met pas à jour les contraintes d’une table déjà présente. Relancer une définition corrigée ne transforme donc pas un ancien schéma. Dans une base utile, prévoyez une sauvegarde et une migration contrôlée ; dans l’exercice, partez d’un nouveau dossier plutôt que d’ajouter DROP TABLE pour forcer le résultat.

Tester les relances, les bornes et le retour arrière

1. Exécutez le programme deux fois sans changement. Le résultat reste une seule ligne lunch, 8000 : le conflit sur e1 déclenche une mise à jour.
2. Remplacez uniquement amount = 8000 par amount = 9000 et relancez. La ligne existante affiche 9000, sans doublon. Rétablissez 8000 et relancez pour revenir au résultat initial.
3. Essayez -1, puis une valeur supérieure à MAX_AMOUNT. Chaque essai doit lever ValueError avant l’écriture. Rétablissez 8000 avant de relire normalement ; la valeur enregistrée doit être restée intacte.
4. Dans une copie du script, remplacez le tuple d’insertion par ("e2", "bus", amount). Avec le même fichier de base, vous obtenez deux lignes triées par id. L’affichage ne sélectionne pas id : la nouvelle ligne bus apparaît donc après lunch parce que e2 suit e1. Conservez ce scénario dans son dossier d’essai distinct pour ne pas confondre son résultat avec celui du premier lancement.

Pour vérifier une annulation, un essai contrôlé peut effectuer deux écritures dans un même with con puis provoquer une erreur sur la seconde. Si cette erreur sort du bloc, les deux écritures non confirmées doivent être annulées. Si vous capturez et ignorez l’erreur à l’intérieur du bloc, son résultat peut être différent : la frontière de gestion de l’exception compte. Le cours sur les exceptions et leur propagation explique ce parcours.

Sans ORDER BY, ne fondez pas le fonctionnement de l’application sur l’ordre observé. En cas de verrouillage, examinez les autres connexions et leurs longues transactions, puis vérifiez qu’elles sont bien fermées. Le finally du programme libère cette connexion même si une instruction SQL ou la lecture échoue ; la fermeture ne remplace pas la décision de commit ou de rollback.