Cours de Python / Se connecter à MariaDB avec PyMySQL et gérer une transaction

Pour utiliser MariaDB depuis Python, ouvrez une connexion avec un pilote et transmettez les valeurs séparément du texte SQL. Cet exercice utilise PyMySQL, un compte limité à une table et une transaction explicite ; il nécessite un serveur MariaDB en fonctionnement, contrairement à une base SQLite intégrée au programme.

Préparer le serveur et un compte d’exercice dédié

Utilisez une instance locale de MariaDB réservée à vos essais. Depuis une connexion d’administration, créez la base, le compte et la table ci-dessous. Si un nom existe déjà, vérifiez son usage et choisissez des noms neufs pour l’exercice ; adaptez alors aussi les noms dans le code Python. Ne réinitialisez pas une base inconnue pour faire fonctionner la démonstration.

Remplacez le mot de passe d’exemple par un secret local avant la préparation du compte, et ne le conservez pas dans un fichier partagé, un dépôt de code ou un journal de commandes. Le compte d’administration sert à préparer le schéma ; l’application utilisera le compte python_app.

CREATE DATABASE python_lab CHARACTER SET utf8mb4;
CREATE USER 'python_app'@'127.0.0.1' IDENTIFIED BY 'replace-with-a-local-secret';
CREATE TABLE python_lab.expenses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  external_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  amount BIGINT NOT NULL CHECK(amount >= 0)
) ENGINE=InnoDB;
GRANT SELECT, INSERT, UPDATE ON python_lab.expenses TO 'python_app'@'127.0.0.1';

La table utilise InnoDB, un moteur transactionnel. external_id est unique : il identifie l’opération que l’application insère ou met à jour. Le montant est un entier stocké dans un BIGINT signé, avec une contrainte refusant les valeurs négatives. Les noms et identifiants doivent aussi respecter la capacité des colonnes.

Le compte reçoit seulement SELECT, INSERT et UPDATE sur cette table. La référence MariaDB de GRANT décrit les niveaux de privilèges. Les préparatifs SQL et les tests de connexion sont également présentés dans le cours MariaDB sur la connexion Python et les requêtes paramétrées, en anglais.

Installer le pilote et transmettre les secrets à la session

Dans l’environnement virtuel de votre projet, installez le pilote :

python -m pip install PyMySQL

PyMySQL est un pilote externe écrit en Python. Ses exemples officiels montrent la connexion, les paramètres et la validation d’une transaction. Enregistrez d’abord mariadb_demo.py avec le code de la section suivante, puis utilisez l’un des blocs adaptés à votre shell pour le lancer.

Le programme se connecte en TCP à 127.0.0.1 ; le compte SQL est donc défini avec ce même hôte. Il utilise le port 3306 par défaut, ou la valeur de MARIADB_PORT si cette variable est présente. Vérifiez les paramètres de votre instance locale plutôt que de supposer qu’un service quelconque sur ce port est le bon serveur.

Sous Windows PowerShell, ce bloc demande le mot de passe sans l’afficher et le transmet au processus Python par son environnement :

$env:MARIADB_USER = "python_app"
$secret = Read-Host "MariaDB password" -AsSecureString
$credential = New-Object System.Net.NetworkCredential("", $secret)
$env:MARIADB_PASSWORD = $credential.Password
python mariadb_demo.py
Remove-Item Env:MARIADB_PASSWORD
Remove-Item Env:MARIADB_USER
Remove-Variable secret, credential

Dans Bash sous macOS ou Ubuntu, utilisez ce bloc dans la session courante :

export MARIADB_USER=python_app
read -r -s -p "MariaDB password: " MARIADB_PASSWORD
printf "\n"
export MARIADB_PASSWORD
python mariadb_demo.py
unset MARIADB_PASSWORD MARIADB_USER

Ces commandes évitent d’écrire le secret comme une valeur littérale dans l’historique du shell. La variable d’environnement est néanmoins lisible par le processus et transmise à ses enfants ; elle n’offre pas les garanties d’un gestionnaire de secrets dédié. Les commandes de fin retirent les variables de cette session. Si vous ajoutez MARIADB_PORT pour un port particulier, gérez aussi sa valeur et son retrait selon le contexte de votre session.

Cette connexion est prévue pour une machine locale. Pour un serveur distant, configurez TLS avec une autorité de certification et une vérification du nom d’hôte adaptées au serveur et au pilote. Remplacer seulement l’adresse ne préserve pas les hypothèses de l’exercice. La référence des options de connexion PyMySQL détaille notamment les délais et les réglages TLS.

Exécuter le code complet de connexion et d’enregistrement

Enregistrez ce fichier UTF-8 sous le nom mariadb_demo.py dans un dossier d’exercice séparé :

import os
import pymysql

con = pymysql.connect(
    host="127.0.0.1", port=int(os.environ.get("MARIADB_PORT", "3306")),
    user=os.environ["MARIADB_USER"], password=os.environ["MARIADB_PASSWORD"],
    database="python_lab", charset="utf8mb4", autocommit=False,
    connect_timeout=5, read_timeout=5, write_timeout=5,
)
try:
    try:
        with con.cursor() as cur:
            cur.execute("INSERT INTO expenses(external_id,name,amount) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATE name=VALUES(name), amount=VALUES(amount)", ("demo-1", "lunch", 8000))
        con.commit()
    except Exception:
        con.rollback()
        raise
    with con.cursor() as cur:
        cur.execute("SELECT name,amount FROM expenses WHERE external_id=%s", ("demo-1",))
        print(cur.fetchone())
finally:
    con.close()

Une fois l’environnement et les secrets préparés, la commande exécutée est :

python mariadb_demo.py

Avec le serveur, la table et le compte correctement préparés, le résultat attendu est ('lunch', 8000). Les montants de cet exemple sont exprimés en wons coréens entiers ; aucun taux de change n’est appliqué.

La première exécution insère l’identifiant externe demo-1. Une réexécution met à jour cette même opération au lieu de multiplier les lignes. Pour le vérifier, contrôlez que demo-1 correspond toujours à une seule ligne. La référence de INSERT ON DUPLICATE KEY UPDATE explique le déclenchement sur une clé unique. Ne déduisez pas de cette règle que les valeurs numériques AUTO_INCREMENT seront toujours consécutives.

Le marqueur de valeur PyMySQL est %s, pour les nombres comme pour les chaînes. Le tuple fourni au second argument de execute contient les valeurs ; ne les insérez pas vous-même avec une f-string ou l’opérateur Python %. Les noms de tables et de colonnes ne sont pas des valeurs à lier par ce mécanisme : s’ils doivent varier, choisissez-les dans une liste autorisée.

Vérifier les refus, la transaction et la fermeture

1. Exécutez deux fois le programme initial et vérifiez le résultat et l’unicité de demo-1.
2. Dans le tuple d’insertion, remplacez seulement 8000 par 9000. Après exécution, la lecture doit donner ('lunch', 9000).
3. Remplacez ce montant par -1. La contrainte doit refuser l’écriture ; vérifiez l’erreur et relisez la valeur précédemment validée, qui doit rester à 9 000. Rétablissez ensuite 8 000 et confirmez le résultat initial.
4. Dans la session d’essai, fournissez un mot de passe volontairement incorrect, constatez le refus de connexion, puis remettez le secret correct. Ne publiez pas sa valeur dans les relevés de test.
5. Avec un autre compte de test limité à la lecture, vérifiez qu’une lecture autorisée réussit et que l’insertion est refusée. N’élargissez pas les droits d’un compte de production pour cet essai.

Après une erreur de connexion, contrôlez d’abord le serveur et le port, puis l’hôte du compte et le mot de passe, et enfin les privilèges sur python_lab.expenses. Une erreur de table ne justifie pas des droits sur tout le serveur. Si connect échoue, aucun objet con n’a encore été créé par cet appel.

Après connexion, le programme ferme chaque curseur avec son contexte, appelle commit lorsque l’écriture réussit, et tente rollback avant de relancer une exception sur ce chemin d’écriture. Le finally ferme la connexion après la lecture ou en cas d’échec. Les principes des exceptions et de finally et des transactions SQLite aident à distinguer ces responsabilités, même si les pilotes et serveurs diffèrent.

Un refus de contrainte avant validation permet de vérifier la conservation de la valeur antérieure. Une perte de connexion au moment d’un commit demande davantage de prudence : examinez l’état de l’opération avec son identifiant avant de décider de la rejouer. Un rollback local ne peut pas annuler une requête externe ou une modification de fichier déjà effectuée.

La connexion contrôle ici la durée des opérations réseau avec des délais explicites, mais le programme ne remplace pas toute la validation métier. Définissez vos limites de montant, la politique des identifiants et les garanties de reprise avant d’étendre cet exemple à de vraies transactions.