Cours MariaDB / Connecter Python et utiliser des requêtes paramétrées avec PyMySQL

Pour connecter Python à MariaDB, transmettez séparément le texte SQL et les valeurs fournies par l’utilisateur à l’API du pilote. Les requêtes paramétrées évitent de construire du SQL à partir de ces valeurs ; un compte aux droits limités et un choix explicite entre commit et rollback encadrent ensuite les modifications.

Préparer l’environnement et le compte applicatif

L’exemple utilise Python 3, PyMySQL 1.1.2 et une connexion TCP locale à MariaDB. Son comportement central a été vérifié avec Python 3.12 et MariaDB 12.3.3. PyMySQL est le pilote Python qui transmet les requêtes et leurs valeurs à la base. Ses paramètres de valeurs sont représentés ici par %s : ne les remplacez pas par les points d’interrogation d’un autre pilote.

Exécutez la préparation suivante dans une connexion administrateur. Remplacez la valeur publique Replace_With_Your_Own_Lab_Password par votre propre mot de passe d’exercice. CREATE USER correspond à une première création ; si le compte ou la table existe déjà, examinez-le avant de relancer la préparation. Confirmez également qu’un éventuel schéma appops_lab existant est bien votre environnement d’apprentissage. Le guide des comptes et des autorisations MariaDB distingue ces responsabilités.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE app_notes (
  note_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  note_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE USER 'appops_app'@'127.0.0.1'
  IDENTIFIED BY 'Replace_With_Your_Own_Lab_Password';
GRANT SELECT, INSERT ON appops_lab.app_notes TO 'appops_app'@'127.0.0.1';

L’hôte du compte, 127.0.0.1, suppose un accès TCP à un serveur local natif. Un conteneur ou un serveur distant peut voir une autre origine de connexion. Vérifiez la correspondance des hôtes de compte et des chemins de connexion plutôt que d’accorder des droits d’administrateur depuis tous les hôtes pour contourner une erreur.

Créer un projet isolé et enregistrer le programme complet

Créez un nouveau dossier de projet et ouvrez-y PowerShell. Avec Python installé et accessible par la commande python, créez l’environnement puis installez la version du pilote utilisée par l’exemple :

python -m venv .venv
.venv\Scripts\python.exe -m pip install PyMySQL==1.1.2

Appeler directement le Python de cet environnement évite de modifier la politique d’exécution des scripts d’activation de PowerShell. Sous Linux ou macOS, utilisez python3 -m venv .venv pour créer l’environnement, puis .venv/bin/python -m pip install PyMySQL==1.1.2. Enregistrez le programme complet ci-dessous en UTF-8 dans notes_app.py, à la racine du dossier de projet :

import argparse
import getpass
import os
import pymysql

def save_and_find(conn, name, sort_key="id"):
    if not name or len(name) > 80:
        raise ValueError("Name must contain 1 to 80 characters")
    columns = {"id": "note_id", "name": "note_name, note_id"}
    if sort_key not in columns:
        raise ValueError("Unknown sort key")
    with conn.cursor() as cur:
        cur.execute("INSERT INTO app_notes (note_name) VALUES (%s)", (name,))
        cur.execute(
            "SELECT note_id, note_name FROM app_notes "
            "WHERE note_name = %s ORDER BY " + columns[sort_key],
            (name,),
        )
        return cur.fetchall()

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--name", default="O'Reilly")
    parser.add_argument("--sort", choices=("id", "name"), default="id")
    args = parser.parse_args()
    password = getpass.getpass("Database password: ")
    conn = pymysql.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        port=int(os.getenv("DB_PORT", "3306")),
        user=os.getenv("DB_USER", "appops_app"),
        password=password,
        database="appops_lab",
        charset="utf8mb4", autocommit=False,
        connect_timeout=5, read_timeout=10, write_timeout=10,
    )
    try:
        rows = save_and_find(conn, args.name, args.sort)
        conn.commit()
        for note_id, note_name in rows:
            print(note_id, note_name)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    main()

Le programme demande le mot de passe au lancement avec getpass, au lieu de le conserver dans le code source ou le texte SQL. La connexion fixe aussi des délais de connexion, de lecture et d’écriture, et utilise utf8mb4 pour les caractères Unicode.

Cette configuration concerne un exercice local sur le PC. Une connexion traversant un autre réseau nécessite également une configuration TLS qui vérifie le certificat du serveur : le seul fait de fournir un mot de passe ne protège pas le transport.

Essayer un nom ordinaire puis un nom avec apostrophe

Depuis le dossier contenant notes_app.py, lancez séparément ces deux commandes :

.venv\Scripts\python.exe notes_app.py --name "Notebook"
.venv\Scripts\python.exe notes_app.py --name "O'Reilly" --sort name

Saisissez le mot de passe de la base à chaque lancement. Le premier enregistre Notebook, le second O'Reilly avec son apostrophe, puis chacun recherche les lignes du même nom. Sur une table neuve et après une seule exécution de chaque commande, chaque résultat contient sa ligne correspondante. Ne fixez pas un identifiant AUTO_INCREMENT attendu : les données antérieures et les insertions annulées peuvent modifier la numérotation.

Relancer le programme avec le même nom ajoute une nouvelle ligne et renvoie toutes les lignes portant ce nom. L’exemple ne déclare pas les noms uniques. Si votre traitement exige une déduplication, définissez la règle d’identité et une contrainte de base adaptée, plutôt que de supposer que le programme l’assure déjà.

Distinguer les valeurs de la syntaxe SQL

L’appel central est cur.execute(sql, (name,)) : le SQL et le tuple des valeurs sont deux arguments séparés. La virgule crée un tuple à un seul élément. N’entourez pas manuellement le marqueur %s de guillemets SQL et n’insérez pas une valeur utilisateur avec l’opérateur Python %, une f-string ou une concaténation. Les exemples officiels PyMySQL illustrent cette séparation.

Une valeur telle que x' OR 1=1 -- doit rester une chaîne unique enregistrée et recherchée telle quelle. Elle ne doit pas devenir une condition sélectionnant toutes les lignes. Ici, « requête paramétrée » décrit l’API utilisée : cela ne signifie pas que tous les pilotes emploient nécessairement le protocole des instructions préparées côté serveur.

Un identifiant ou un mot-clé SQL, comme un nom de colonne, ASC ou DESC, ne se transmet pas comme un paramètre de valeur ordinaire. Le fragment d’ORDER BY provient uniquement du dictionnaire columns défini par le développeur. argparse et la fonction elle-même contrôlent le choix, ce qui empêche un autre appelant de contourner cette liste de valeurs autorisées. La référence Cursor.execute et ses arguments précise les formats acceptés.

Vérifier ensemble les transactions et les droits

Avec autocommit=False, le succès de save_and_find ne rend pas automatiquement l’insertion persistante. La fonction principale valide après la réussite de l’insertion et de la sélection, annule sur exception et ferme la connexion dans finally. Distinguez cette frontière de validation de ce qu’une autre connexion peut voir ; le guide des transactions InnoDB et de leurs frontières aide à interpréter ces observations.

La fonction refuse un nom vide, un nom de plus de 80 caractères ou une clé de tri inconnue avant d’insérer la ligne. Ces contrôles applicatifs complètent la transmission des paramètres et les contraintes de la table. Si une exception survient après une insertion mais avant le commit, le rollback de cette tentative doit empêcher sa conservation.

Le compte applicatif ne possède que SELECT et INSERT sur app_notes. Il n’a pas besoin de créer ou de supprimer une table, donc pas de droits CREATE ou DROP. Cette limitation ne remplace pas une liaison correcte des valeurs, mais réduit les opérations disponibles si le programme comporte un autre défaut. La référence des options de connexion PyMySQL détaille l’autocommit et les délais.

Diagnostiquer les erreurs et nettoyer l’exercice

Symptôme Vérifications
ModuleNotFoundError Exécutez le programme avec le même Python .venv que celui utilisé pour installer PyMySQL.
Access denied Vérifiez le compte, le mot de passe, l’hôte autorisé et l’adresse du client vue par le serveur.
Connexion refusée ou délai dépassé Examinez l’état du serveur, DB_HOST, DB_PORT, le pare-feu et le chemin réseau réel.
Aucune ligne après le traitement Vérifiez si une exception a provoqué un rollback et si vous interrogez la bonne base après validation.
Échec uniquement avec une apostrophe Recherchez une interpolation manuelle de la valeur au lieu de son passage à execute.

Pour un paramètre de connexion non secret, PowerShell permet par exemple $env:DB_PORT = "3307". Conservez la demande interactive de mot de passe. Supprimez une surcharge de port devenue inutile avec Remove-Item Env:DB_PORT, ou rétablissez sa valeur précédente si cette variable était déjà configurée avant l’exercice.

À la fin, arrêtez le programme et faites confirmer par l’administrateur que ce compte et cette table sont bien les objets d’exercice à supprimer :

DROP USER 'appops_app'@'127.0.0.1';
DROP TABLE appops_lab.app_notes;

Ne remplacez pas ces noms par ceux d’un compte ou d’une table métier. La suppression de ces objets ne se récupère pas par un ROLLBACK ordinaire ; conservez d’abord les données dont vous avez encore besoin.