Exécuter une requête depuis Python : execute, paramètres, commit et fetch

Qu’allons-nous apprendre ?#

Jusqu’à présent, nous avons écrit du SQL « à la main » dans Database Client. Maintenant, nous le relions au code, en suivant le parcours sur lequel insiste le programme :

Python  →  Connection  →  Query  →  Database  →  Result  →  Python
(values)   (open)         (SQL +    (runs it)     (rows)     (tuples,
                           params)                            ints, strs)

• execute avec des paramètres (%s).
• L’injection SQL (SQL Injection) : le danger, et pourquoi les paramètres le résolvent.
• commit : enregistrer les modifications.
• lastrowid : l’id que la base a donné à la nouvelle ligne.
• fetchone / fetchall sur de vraies données.

Pourquoi en avons-nous besoin ?#

Dans une API, les valeurs viennent de l’utilisateur : un nom, un âge et un e-mail du body d’un POST. Le code doit les mettre dans la requête en toute sécurité, enregistrer, et renvoyer ce qui a été enregistré, y compris le nouvel id (201 Created + l’étudiant). Tout cela se passe dans cette leçon, avec du vrai code.

Explication à partir des bases#

execute(sql, params)#

cursor.execute(
    "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
    ("Dana", 24, "dana@mail.com"),
)

• Dans le SQL, on écrit %s à la place de chaque valeur (un emplacement, Placeholder). Dans MySQL, c’est toujours %s — pour tous les types, les nombres aussi.
• Les valeurs sont passées séparément, sous forme de tuple, dans le même ordre que les emplacements.
• Le pilote (mysql.connector) les met en place en toute sécurité : les chaînes comme des chaînes, et les nombres comme des nombres. Pas besoin de guillemets, ni de conversion.

L’injection SQL — pourquoi ne pas construire du SQL par concaténation#

Il paraît « simple » de construire la requête nous-mêmes :

name = input("Student name to search: ")
sql = "SELECT id, name FROM students WHERE name = '" + name + "'"

Si l’utilisateur tape Dana, on obtient la requête ... WHERE name = 'Dana'. Très bien. Mais s’il tape :

x' OR '1'='1

On obtient la requête :

SELECT id, name FROM students WHERE name = 'x' OR '1'='1'

'1'='1' est toujours vrai, donc tous les étudiants sont renvoyés. L’utilisateur a changé le sens de la requête par son entrée. C’est l’injection SQL, l’une des failles de sécurité les plus courantes et les plus dangereuses au monde. Par elle, on pénètre dans des bases de données, on vole des mots de passe, et on supprime des tables.

Avec %s et des paramètres, le pilote traite l’entrée toujours comme une valeur, et jamais comme une partie du SQL. L’utilisateur peut taper ce qu’il veut, et la base cherchera simplement un étudiant dont le nom est cette chaîne étrange.

Comment %s protège-t-il exactement ?#

Avec la concaténation, l’attaque fonctionne à cause d’une seule apostrophe : l’apostrophe que l’utilisateur a tapée après x ferme notre texte, et tout ce qui suit (OR '1'='1') est déjà lu comme une partie de la commande.

Avec %s, le pilote prépare la valeur avant de l’envoyer : il ajoute une barre oblique inverse (\) devant chaque apostrophe de l’entrée. Une barre oblique inverse devant une apostrophe dit à MySQL : « c’est une apostrophe ordinaire à l’intérieur du texte, pas la fin du texte ».

L’apostrophe de l’utilisateur ne peut plus fermer le texte, donc toute l’entrée reste à l’intérieur comme un seul nom. Il n’y a pas de OR devenu partie de la commande, et MySQL cherche un étudiant portant ce nom — et n’en trouve pas ([]).

Et pourquoi ne pas le faire soi-même ? Parce qu’il est facile de manquer un cas : un guillemet double, une barre oblique inverse dans l’entrée, un encodage spécial. Le pilote sait tous les gérer, à chaque fois — c’est pourquoi c’est toujours %s.

commit — enregistrer vraiment#

Les commandes qui modifient des données (INSERT, UPDATE, DELETE) entrent d’abord dans une transaction (Transaction) ouverte. La modification est visible dans la connexion courante, mais elle n’est pas enregistrée définitivement tant que l’on n’appelle pas connection.commit(). Si vous fermez la connexion sans commit, les modifications sont jetées.

• SELECT n’a pas besoin de commit, car il ne modifie rien.
• CREATE TABLE est enregistré dans MySQL immédiatement, même sans commit.

lastrowid et rowcount#

Après execute, le curseur contient des informations sur ce qui s’est passé :

Syntaxe#

connection = get_connection()
try:
    cursor = connection.cursor()

    # change data: execute + commit
    cursor.execute("INSERT INTO t (a, b) VALUES (%s, %s)", (value_a, value_b))
    connection.commit()
    new_id = cursor.lastrowid

    # read data: execute + fetch
    cursor.execute("SELECT a, b FROM t WHERE b > %s", (10,))
    rows = cursor.fetchall()
    cursor.execute("SELECT a, b FROM t WHERE id = %s", (new_id,))
    row = cursor.fetchone()
finally:
    connection.close()

Remarquez le (10,) : un tuple d’un seul élément doit avoir une virgule (souvenez-vous des tuples !). (10) n’est que le nombre 10, et execute échouera.

Premier exemple de code#

week-4/school-db/students_demo.py :

import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")

connection = get_connection()
try:
    cursor = connection.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE
        )
        """
    )

    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
        ("Dana", 24, "dana@mail.com"),
    )
    connection.commit()
    print("Inserted student with id", cursor.lastrowid)

    cursor.execute("SELECT id, name, age, email FROM students")
    print("All rows:", cursor.fetchall())
finally:
    connection.close()

Sortie (la première exécution, sur une table vide) :

Inserted student with id 1
All rows: [(1, 'Dana', 24, 'dana@mail.com')]

Analyse du code ligne par ligne#

Relancez-le :

Traceback (most recent call last):
  ...
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry 'dana@mail.com' for key 'students.email'

À la deuxième exécution, l’INSERT de Dana échoue, car l’e-mail existe déjà, et la base a imposé UNIQUE. (Et malgré l’erreur, la connexion a été fermée, grâce à finally.) Plus loin dans ce thème, nous gérerons correctement cette erreur. En attendant, pour repartir de zéro : dans Database Client, exécutez DROP TABLE students;.

Un autre exemple#

Ajouter plusieurs étudiants, des recherches avec paramètres, et une démonstration de l’injection SQL.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("DROP TABLE IF EXISTS students")
    cursor.execute(
        "CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, "
        "name VARCHAR(100) NOT NULL, age INT NOT NULL)")
    for name, age in [("Dana", 24), ("Avi", 31), ("Noa", 22)]:
        cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()

    cursor.execute("SELECT id, name, age FROM students WHERE id = %s", (2,))
    print("By id 2:", cursor.fetchone())

    cursor.execute("SELECT name FROM students WHERE age > %s ORDER BY age", (23,))
    print("Older than 23:", cursor.fetchall())

    cursor.execute("SELECT id, name FROM students WHERE id = %s", (99,))
    print("By id 99:", cursor.fetchone())

    attack = "x' OR '1'='1"

    unsafe_sql = "SELECT name FROM students WHERE name = '" + attack + "'"
    print("UNSAFE query:", unsafe_sql)
    cursor.execute(unsafe_sql)
    print("UNSAFE result:", cursor.fetchall())

    cursor.execute("SELECT name FROM students WHERE name = %s", (attack,))
    print("SAFE result:", cursor.fetchall())
finally:
    connection.close()

Sortie :

By id 2: (2, 'Avi', 31)
Older than 23: [('Dana',), ('Avi',)]
By id 99: None
UNSAFE query: SELECT name FROM students WHERE name = 'x' OR '1'='1'
UNSAFE result: [('Dana',), ('Avi',), ('Noa',)]
SAFE result: []

Analyse :

• DROP TABLE IF EXISTS students — supprime la table, si elle existe, pour que chaque exécution reparte de zéro. (Pratique pour les démonstrations. On ne fait pas cela dans du vrai code !)
• Un INSERT dans une boucle, et un seul commit à la fin — les trois insertions sont enregistrées ensemble.
• fetchone() sur un id existant → un tuple. Sur un id qui n’existe pas → None. C’est le « introuvable » qui deviendra un 404.
• (2,), (23,) — un tuple d’une seule valeur, avec une virgule.
• La version non sûre a renvoyé tous les étudiants, car l’entrée a modifié la requête elle-même.
• La version sûre a cherché un étudiant dont le nom est exactement x' OR '1'='1, et il n’y en a pas, donc []. L’attaque a échoué.

Erreur courante#

Erreur 1 : oublier commit.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom", 27))
connection.close()

Il n’y a pas d’erreur, et Tom n’a pas été enregistré. Fermer sans commit = annuler les modifications. C’est l’une des erreurs les plus déroutantes, car « tout a fonctionné ».

Erreur 2 : un seul paramètre sans virgule.

cursor.execute("SELECT name FROM students WHERE id = %s", (2))

mysql.connector.errors.ProgrammingError: Could not process parameters: int(2), it must be of type list, tuple or dict

(2) est un int, pas un tuple. → (2,).

Erreur 3 : un nombre de paramètres qui ne correspond pas.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom",))

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Erreur 4 : %s entre guillemets.

cursor.execute("SELECT * FROM students WHERE name = '%s'", ("Dana",))

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; ... near 'Dana''' at line 1

Le pilote ajoute déjà les guillemets lui-même, et maintenant il y en a deux paires. %s toujours sans guillemets.

Erreur 5 : %s à la place du nom d’une table ou d’une colonne. "SELECT * FROM %s" ne fonctionne pas : les emplacements sont uniquement pour les valeurs — le pilote transforme students en 'students' (une chaîne), et c’est une erreur de syntaxe. Les noms des tables et des colonnes s’écrivent dans le code, de façon fixe.

Exercices#

Les exercices n’utilisent que ce qui a été vu dans cette leçon et les précédentes. Essayez d’abord seul, et n’ouvrez la solution qu’ensuite.

Exercice 1: execute avec des paramètres#

Écrivez une fonction add_student(connection, name, age) qui ajoute un étudiant avec des paramètres (%s), enregistre, et renvoie le nouvel id.

cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

Résultat attendu :

1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]

import mysql.connector


def add_student(connection, name, age):
    cursor = connection.cursor()
    cursor.execute(
        "INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()
    return cursor.lastrowid


connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())
connection.close()

1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]

Exercice 2: L’injection SQL#

La table contient deux étudiants — Dana et Avi. Créez-la ainsi :

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()
connection.close()

Un code qui construit une requête à partir d’une saisie de l’utilisateur :

name = "x' OR '1'='1"
sql = "SELECT name FROM students WHERE name = '" + name + "'"

1. À quoi ressemblera la commande créée, et que renverra-t-elle ?
2. Montrez la différence en code : exécutez la requête par concaténation et affichez Unsafe: et le résultat, puis la même requête avec %s et affichez Safe: et le résultat.

Résultat attendu :

Unsafe: [('Dana',), ('Avi',)]
Safe: []

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()

name = "x' OR '1'='1"

sql = "SELECT name FROM students WHERE name = '" + name + "'"
cursor.execute(sql)
print("Unsafe:", cursor.fetchall())

cursor.execute("SELECT name FROM students WHERE name = %s", (name,))
print("Safe:", cursor.fetchall())
connection.close()

Unsafe: [('Dana',), ('Avi',)]
Safe: []

Exercice 3: Le commit oublié#

connection = mysql.connector.connect(...)
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()

Le programme s’est exécuté sans erreur, mais Noa n’est pas dans la base. Pourquoi ?

Montrez-le en code : partez d’une table students vide (avec name et age). Insérez Noa sans commit, fermez, et dans une nouvelle connexion comptez combien de lignes il y a dans la table. Puis insérez-la à nouveau avec commit, et comptez à nouveau.

Résultat attendu :

Without commit: 0 rows
With commit: 1 rows

import mysql.connector


def connect():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def count_rows():
    connection = connect()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students")
        return len(cursor.fetchall())
    finally:
        connection.close()


setup = connect()
setup.cursor().execute("CREATE TABLE IF NOT EXISTS students (name VARCHAR(100), age INT)")
setup.close()

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()
print("Without commit:", count_rows(), "rows")

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.commit()
connection.close()
print("With commit:", count_rows(), "rows")

Without commit: 0 rows
With commit: 1 rows

Exercice 4: Une recherche sûre par id#

La table contient une étudiante — Dana, 24 ans. Créez-la ainsi :

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
connection.commit()
connection.close()

Écrivez find_by_id(student_id) qui ouvre une connexion, cherche un étudiant avec un paramètre, et ferme toujours. Elle renvoie la ligne ou None.

print(find_by_id(1))
print(find_by_id(99))

Résultat attendu :

(1, 'Dana', 24)
None

import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def find_by_id(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
        return cursor.fetchone()
    finally:
        connection.close()


setup = get_connection()
cursor = setup.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
setup.commit()
setup.close()

print(find_by_id(1))
print(find_by_id(99))

(1, 'Dana', 24)
None

Exercice 5: Le parcours d’une requête#

Remettez dans l’ordre les étapes de l’exécution d’une requête depuis Python :

• (a) connection.commit() — seulement si l’on a modifié des données
• (b) mysql.connector.connect(...) puis connection.cursor()
• (c) connection.close()
• (d) cursor.execute(sql, params)
• (e) cursor.fetchone() / fetchall() — seulement si l’on a lu des données

Résumé#

• Le parcours : Python → connexion → requête (avec %s et des paramètres) → base de données → résultat → Python.
• cursor.execute(sql, params) — les valeurs uniquement par %s et un tuple. Un tuple d’une seule valeur : (x,).
• L’injection SQL = une entrée qui modifie la requête. Ne concaténez jamais dans du SQL des valeurs venant de l’extérieur.
• Modifier des données (INSERT/UPDATE/DELETE) → commit(), sinon la modification est jetée à la fermeture.
• cursor.lastrowid = le nouvel id. fetchone() = une ligne ou None. fetchall() = une liste de tuples.
• Une fonction pour chaque action, avec sa propre connexion et try/finally.

Documentation officielle#

• MySQLCursor.execute()
• Inserting Data Using Connector/Python
• Querying Data Using Connector/Python