La connexion, une table et l'insertion de données : Connection, CREATE TABLE et INSERT

Dans cette leçon#

Cette leçon se compose de deux chapitres, dans le même ordre que les diapositives :

1. Chapitre 1 — ouvrir une connexion, vérifier qu’elle fonctionne et la fermer correctement
2. Chapitre 2 — le SQL de base : CREATE TABLE et INSERT

Chapitre 1 : Ouvrir une connexion, vérifier qu’elle fonctionne et la fermer correctement#

Qu’allons-nous apprendre ?#

Le cycle de vie de tout travail avec une base de données :

1. open a connection  →  2. create a cursor  →  3. run a query  →  4. read the result  →  5. close

• La connexion (Connection) et le curseur (Cursor) : ce qu’est chacun.
• Une requête de vérification (Health Check) : « la base de données répond-elle seulement ? »
• Fermer correctement, même en cas d’erreur.
• get_connection() — une seule fonction que tout le projet utilisera.

Pourquoi en avons-nous besoin ?#

Chaque fonction CRUD et chaque endpoint du serveur (le thème de l’API des étudiants) commence par ouvrir une connexion et se termine en la fermant. Une connexion non fermée est un bug classique : chaque connexion à MySQL consomme des ressources — de notre côté comme sur le serveur — et MySQL a un nombre limité de connexions simultanées. Sur un serveur qui traite des milliers de requêtes, au bout d’un moment les connexions s’épuisent (Too many connections) et tout se bloque. C’est exactement comme les fichiers ouverts de la leçon sur les fichiers, mais à l’échelle d’un serveur.

Explication à partir des bases#

La connexion — la ligne ouverte#

Connection = un objet qui représente un canal ouvert entre notre code et le serveur MySQL. Par elle, on crée un curseur, on confirme les modifications (commit, plus loin dans ce thème), et on ferme. Tant qu’elle est ouverte, elle consomme des ressources.

Le curseur (Cursor)#

Cursor = un objet qui exécute une requête et en conserve les résultats, et permet de les lire ligne par ligne. Imaginez un doigt qui parcourt les résultats de la requête.

On le crée à partir de la connexion, et on exécute à travers lui :

cursor = connection.cursor()        # create a cursor
cursor.execute("SELECT 1")          # run a query through it

Lire les résultats#

Chaque ligne est un tuple (souvenez-vous des tuples !) : ("Dana", 24). (Plus loin dans ce thème, nous apprendrons à obtenir les lignes sous forme de dict.)

Une requête de vérification (Health Check)#

Pour vérifier que la connexion fonctionne vraiment (et pas seulement que l’objet a été créé), on exécute une requête simple qui ne dépend d’aucune table :

SELECT 1
SELECT VERSION()

Si une réponse est revenue, la base est vivante et répond. C’est exactement ainsi que fonctionne l’endpoint /health des vrais serveurs (le thème backend et FastAPI).

Fermer correctement — try/finally#

Que se passe-t-il si la requête échoue après que nous avons ouvert une connexion ?

connection = get_connection()
cursor = connection.cursor()
cursor.execute("SELEC 1")          # typo -> exception
connection.close()                 # never reached!

L’erreur arrête la fonction, et close ne s’exécute pas. La solution que nous avons apprise dans le thème des exceptions : finally, qui s’exécute toujours :

connection = get_connection()
try:
    ...                            # work with the database
finally:
    connection.close()             # runs even if an exception happened

Syntaxe#

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()     # create a cursor
    cursor.execute("SELECT 1")       # run a query
    row = cursor.fetchone()          # read one row -> (1,)
finally:
    connection.close()               # always close

Premier exemple de code#

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

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("SELECT VERSION()")
    row = cursor.fetchone()
    print("Row returned:", row)
    print("MySQL version:", row[0])

    cursor.execute("SELECT 1 + 1")
    print("The database can calculate: 1 + 1 =", cursor.fetchone()[0])
    print("Connection works!")
finally:
    connection.close()
    print("Connection closed.")

Sortie :

Row returned: ('8.4.11',)
MySQL version: 8.4.11
The database can calculate: 1 + 1 = 2
Connection works!
Connection closed.

Analyse du code ligne par ligne#

Un autre exemple#

Une erreur au milieu, et la preuve que finally ferme. Et aussi : ce qui se passe quand on essaie d’utiliser une connexion fermée.

import mysql.connector

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

def run_check(sql):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        value = cursor.fetchone()[0]
        print("OK:", sql, "->", value)
    except mysql.connector.Error as error:
        print("Query failed:", sql, "|", type(error).__name__ + ":", error)
    finally:
        connection.close()
        print("  (connection closed)")
    return connection

run_check("SELECT 40 + 2")
closed = run_check("SELEC 1")

try:
    closed.cursor()
except mysql.connector.Error as error:
    print("Using a closed connection:", error)

Sortie :

OK: SELECT 40 + 2 -> 42
  (connection closed)
Query failed: SELEC 1 | ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEC 1' at line 1
  (connection closed)
Using a closed connection: MySQL Connection not available

Analyse :

• mysql.connector.Error — la classe de base de toutes les erreurs du pilote (le thème POO : une hiérarchie d’exceptions !). ProgrammingError (une erreur dans le SQL), IntegrityError (une violation de contrainte, plus loin) et OperationalError (un problème de connexion) sont ses sous-classes.
• Dans la requête avec une faute de frappe (SELEC), l’erreur a été attrapée, et finally a quand même fermé la connexion.
• 1064 est le numéro d’erreur de MySQL pour une erreur de syntaxe, et near 'SELEC 1' indique exactement où est le problème. C’est une erreur de syntaxe SQL (la même idée qu’en Python, dans un autre langage).
• Utiliser la connexion après close → MySQL Connection not available. La connexion est « morte », et il faut en ouvrir une nouvelle.

get_connection — un seul point de connexion#

Dans un vrai projet, chaque fonction a besoin d’une connexion. Au lieu d’écrire les cinq informations de connexion à des dizaines d’endroits, on écrit une seule fonction (DRY). Ce sera la base du fichier db.py tout au long de ces thèmes :

import mysql.connector

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

def check_database():
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT VERSION()")
        return {"status": "ok", "mysql_version": cursor.fetchone()[0]}
    except mysql.connector.Error as error:
        return {"status": "error", "message": str(error)}
    finally:
        connection.close()

if __name__ == "__main__":
    print(check_database())

{'status': 'ok', 'mysql_version': '8.4.11'}

check_database renvoie un dict. Dans le thème de l’API des étudiants, on peut le relier à un endpoint GET /health, et FastAPI le transformera en JSON.

Erreur courante#

Erreur 1 : close sans finally. Cela fonctionne dans le cas où tout va bien, mais chaque erreur au milieu laisse une connexion ouverte. Sur un serveur, après quelques centaines de requêtes échouées, MySQL refuse les nouvelles connexions : Too many connections.

Erreur 2 : oublier [0].

cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print("Version: " + version)

TypeError: can only concatenate str (not "tuple") to str

fetchone() renvoie une ligne (un tuple), même quand elle n’a qu’une colonne. La valeur elle-même = [0].

Erreur 3 : fetchone() quand il n’y a pas de résultats. fetchone() renvoie None s’il n’y a pas de ligne, et alors None[0] → TypeError: 'NoneType' object is not subscriptable. Plus loin dans ce thème (la recherche par id), nous vérifierons toujours if row is None.

Erreur 4 : une nouvelle requête avant que tous les résultats aient été lus.

cursor.execute("SELECT id FROM students")    # 3 rows
first = cursor.fetchone()                    # read only the first one
cursor.execute("SELECT 1")

mysql.connector.errors.InternalError: Unread result found

Dans MySQL, les résultats arrivent du serveur progressivement, et une nouvelle requête ne peut pas partir avant que la précédente ait été lue jusqu’au bout. Quand on attend plusieurs lignes — fetchall(). Quand on a besoin d’une seule ligne — filtrer avec WHERE pour qu’une seule ligne revienne.

Résumé#

• Le cycle : ouverture (connect) → un curseur (cursor()) → une requête (execute) → lecture (fetchone/fetchall) → fermeture (close).
• Connection = le canal vers le serveur de base de données. Cursor = exécute une requête et conserve les résultats.
• fetchone() = une ligne (un tuple) ou None. fetchall() = une liste de tuples. Une seule valeur : cursor.fetchone()[0].
• Une requête de vérification : SELECT 1 / SELECT VERSION(). La base de /health.
• Toujours try/finally + close().
• Un seul get_connection() pour tout le projet (DRY). Les erreurs : mysql.connector.Error et ses sous-classes.

Documentation officielle#

• Connector/Python — Connecting to MySQL
• MySQLConnection.is_connected()
• MySQLConnection.close()

Chapitre 2 : Le SQL de base — CREATE TABLE et INSERT#

Qu’allons-nous apprendre ?#

Les deux premières commandes SQL :

Dans cette leçon, nous nous concentrons sur le SQL lui-même, et l’exécutons dans Database Client dans VS Code. À la fin, nous verrons aussi comment exécuter le même SQL depuis Python. (Dans les leçons suivantes, nous apprendrons à travailler depuis Python correctement — paramètres, commit et résultats.)

Pourquoi en avons-nous besoin ?#

La base school tourne dans le conteneur — mais elle est vide. Une table est le « tiroir » où les données seront rangées, et INSERT est la première action de tout système : l’inscription d’un utilisateur, l’ajout d’un produit, une commande. Dans l’API que nous construirons dans le thème de l’API des étudiants, POST /students = INSERT INTO students.

Explication à partir des bases#

La structure d’une commande SQL#

• Les mots-clés comme CREATE, TABLE, INSERT, INTO, VALUES s’écrivent, par convention, en majuscules, pour les distinguer des noms. (SQL n’est pas sensible à la casse pour les mots-clés, mais c’est plus lisible.)
• Les noms des tables et des colonnes : snake_case, en minuscules, et généralement au pluriel pour les tables (students).
• Les chaînes en SQL s’écrivent entre guillemets simples : 'Dana'.
• Une commande se termine par ; (obligatoire quand on exécute plusieurs commandes à la suite, et toujours recommandé).

CREATE TABLE#

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

Chaque ligne entre les parenthèses = une colonne : <nom> <TYPE> <contraintes>, séparées par des virgules. (Pas de virgule après la dernière colonne !)

IF NOT EXISTS#

CREATE TABLE students échoue si la table existe déjà. Sur un serveur qui redémarre chaque jour, il faut une commande que l’on peut relancer sans risque :

CREATE TABLE IF NOT EXISTS students (...);

« Crée la table seulement si elle n’existe pas encore ». Si elle existe, rien ne se passe, et sans erreur. (Remarquez : si la table existe avec une structure différente, elle ne sera pas modifiée.)

INSERT INTO#

INSERT INTO students (name, age, email)
VALUES ('Dana', 24, 'dana@mail.com');

• INSERT INTO <table> — dans quelle table.
• (name, age, email) — quelles colonnes on remplit, et dans quel ordre.
• VALUES (...) — les valeurs, exactement dans le même ordre.
• id n’est pas dans la liste — AUTO_INCREMENT le remplit tout seul.

On peut ajouter plusieurs lignes en une seule commande :

INSERT INTO students (name, age, email) VALUES
    ('Avi', 31, 'avi@mail.com'),
    ('Noa', 22, 'noa@mail.com');

Syntaxe#

CREATE TABLE IF NOT EXISTS table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column_a VARCHAR(100) NOT NULL,
    column_b INT NOT NULL,
    column_c VARCHAR(100) UNIQUE
);

INSERT INTO table_name (column_a, column_b, column_c)
VALUES ('text value', 42, 'unique value');

Comment exécuter du SQL dans Database Client :

1. Créez un nouveau fichier avec l’extension .sql, par exemple school.sql.
2. En haut du fichier, choisissez la connexion et la base school (ou : clic droit sur school dans l’arbre → Open Query).
3. Écrivez les commandes, sélectionnez-les et cliquez sur Run (ou Ctrl+Enter).
4. Dans l’arbre sur le côté, cliquez sur Refresh — et la table apparaît. Un clic dessus ouvre les lignes comme un tableur.

Premier exemple de code#

school.sql :

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

SHOW TABLES;

Exécutez-le dans Database Client. Le résultat de SHOW TABLES :

Tables_in_school
----------------
students

La table existe ! Et dans l’arbre sur le côté, students apparaît avec ses quatre colonnes.

Analyse du code ligne par ligne#

Relancez la même commande : il n’y a pas d’erreur. Grâce à IF NOT EXISTS.

Un autre exemple#

Ajouter des lignes, et ce qui se passe quand on enfreint les contraintes. Ajoutez à school.sql et exécutez :

INSERT INTO students (name, age, email) VALUES ('Dana', 24, 'dana@mail.com');

INSERT INTO students (name, age, email) VALUES
    ('Avi', 31, 'avi@mail.com'),
    ('Noa', 22, 'noa@mail.com');

SELECT * FROM students;

id | name | age | email
---+------+-----+--------------
 1 | Dana |  24 | dana@mail.com
 2 | Avi  |  31 | avi@mail.com
 3 | Noa  |  22 | noa@mail.com

La base a attribué l’id elle-même : 1, 2, 3. (SELECT * = « affiche tout » — dans la leçon suivante, nous l’apprendrons en détail.)

Maintenant, enfreignons des contraintes — exécutez chaque commande séparément :

INSERT INTO students (name, age, email) VALUES ('Tom', 27, 'dana@mail.com');

1062 (23000): Duplicate entry 'dana@mail.com' for key 'students.email'

INSERT INTO students (name, email) VALUES ('Rina', 'rina@mail.com');

1364 (HY000): Field 'age' doesn't have a default value

INSERT INTO students (name, age, email) VALUES ('Ron', 30);

1136 (21S01): Column count doesn't match value count at row 1

Nous avons demandé 3 colonnes, mais n’avons donné que 2 valeurs. Le nombre de valeurs doit être identique au nombre de colonnes.

Analyse :

• Duplicate entry ... for key 'students.email' — l’e-mail de Dana existe déjà. La base a refusé, et la ligne n’a pas été ajoutée.
• Field 'age' doesn't have a default value — nous n’avons pas rempli age, et il est obligatoire (NOT NULL, sans valeur par défaut).
• Le nombre au début de chaque message (1062, 1364, 1136) est le code d’erreur de MySQL — on peut le rechercher sur Internet.
• Depuis Python, une violation de UNIQUE arrive sous forme d’IntegrityError (Integrity = l’intégrité des données). Nous l’attraperons plus loin dans ce thème, et renverrons 409 depuis l’API dans le thème de l’API des étudiants.
• Les données sont restées valides. C’est la force des contraintes : même s’il y a un bug dans le code, la base respecte les règles.

Le même SQL — depuis Python#

Sur un serveur, nous n’exécuterons pas le SQL à la main — c’est le code qui l’exécutera. Voici à quoi ressemblent le même CREATE TABLE et le même INSERT depuis Python :

import mysql.connector

CREATE_STUDENTS = """
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
)
"""

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute(CREATE_STUDENTS)
    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES ('Tom', 27, 'tom@mail.com')")
    connection.commit()
    print("Inserted Tom")
finally:
    connection.close()

• """...""" — une chaîne sur plusieurs lignes, pratique pour un long SQL.
• cursor.execute(...) exécute une seule commande à chaque appel.
• connection.commit() — enregistre l’insertion dans la base. Sans commit, la ligne disparaît à la fermeture de la connexion. (Dans la leçon « requêtes depuis Python », nous développerons ce point.)

Erreur courante#

Erreur 1 : des guillemets doubles pour les chaînes.

INSERT INTO students (name, age, email) VALUES ("Dana", 24, "dana@mail.com");

En SQL standard, les guillemets doubles sont pour les noms (des tables et des colonnes), et les guillemets simples pour les chaînes. MySQL « pardonne » et l’accepte, mais d’autres bases (PostgreSQL) lanceront une erreur. Toujours '...' pour les chaînes.

Erreur 2 : une virgule après la dernière colonne.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
);

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4

Erreur 3 : oublier la liste des colonnes dans un INSERT.

INSERT INTO students VALUES ('Dana', 24, 'dana@mail.com');

1136 (21S01): Column count doesn't match value count at row 1

Sans liste de colonnes, SQL attend une valeur pour chaque colonne, y compris id, et dans l’ordre de la table. Écrivez toujours explicitement la liste des colonnes. C’est aussi robuste face aux futures modifications de la table.

Erreur 4 : modifier CREATE TABLE IF NOT EXISTS et s’attendre à ce que la table soit mise à jour. Vous avez ajouté une colonne city à la commande et l’avez exécutée : rien ne s’est passé, car la table existe déjà. Au stade de l’apprentissage, la solution simple : supprimez la table — DROP TABLE students; — et relancez. (Cela supprime aussi toutes les lignes ! Dans les vrais systèmes, on utilise des migrations (Migrations), des outils qui modifient la structure d’une table existante sans perdre de données. Un sujet pour plus tard.)

Résumé#

• CREATE TABLE IF NOT EXISTS name (col TYPE constraints, ...); — crée une structure. IF NOT EXISTS = peut être relancé sans risque (mais ne met pas à jour une structure existante).
• INSERT INTO name (cols) VALUES (...); — ajoute une ligne. Toujours avec une liste de colonnes. Un id avec AUTO_INCREMENT se remplit tout seul.
• Les chaînes en SQL sont entre guillemets simples '...'. Pas de virgule après la dernière colonne.
• Enfreindre une contrainte → une erreur avec un code (1062 un doublon, 1364 un champ obligatoire manquant). La ligne n’est pas ajoutée, et les données restent valides.
• Exécutez le SQL dans Database Client, et depuis Python avec cursor.execute(...) et commit().

Documentation officielle#

• MySQL — CREATE TABLE
• MySQL — INSERT

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: Health Check#

Écrivez un code qui se connecte à la base school, exécute deux requêtes de vérification — SELECT 1 + 1 et SELECT 'ok' — affiche les résultats, et ferme la connexion dans tous les cas.

Résultat attendu :

The database computed: 2
Status: ok
Connection closed

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("SELECT 1 + 1")
    two = cursor.fetchone()[0]
    print("The database computed:", two)
    cursor.execute("SELECT 'ok'")
    status = cursor.fetchone()[0]
    print("Status:", status)
finally:
    connection.close()
    print("Connection closed")

The database computed: 2
Status: ok
Connection closed

Exercice 2: Connection et Cursor#

1. Quelle est la différence entre une Connection et un Cursor ?
2. Quelle est la différence entre fetchone() et fetchall() ?

Exercice 3: CREATE TABLE IF NOT EXISTS#

Écrivez un code Python qui crée la table students (id, nom, âge, un e-mail unique) dans la base school seulement si elle n’existe pas. Lancez la création deux fois, et montrez qu’il n’y a pas d’erreur.

Résultat attendu :

table ready
table ready

import mysql.connector

CREATE_STUDENTS = """
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
)
"""

for _ in range(2):
    connection = mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")
    try:
        cursor = connection.cursor()
        cursor.execute(CREATE_STUDENTS)
        print("table ready")
    finally:
        connection.close()

table ready
table ready

Exercice 4: INSERT INTO#

Continuez : ajoutez trois étudiants — Dana (24), Avi (31) et Noa (22) — et enregistrez. Comptez ensuite combien de lignes il y a dans la table.

Résultat attendu :

Rows: 3

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
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
            ('Dana', 24, 'dana@mail.com'),
            ('Avi', 31, 'avi@mail.com'),
            ('Noa', 22, 'noa@mail.com')
    """)
    connection.commit()
    cursor.execute("SELECT * FROM students")
    print("Rows:", len(cursor.fetchall()))
finally:
    connection.close()

Rows: 3

Exercice 5: La base refuse#

Insérez un étudiant dans la table, puis essayez d’insérer un autre étudiant avec le même e-mail, et un étudiant avec un âge NULL. Attrapez l’erreur de chaque tentative et affichez son message (error.msg).

Résultat attendu :

Rejected: Duplicate entry 'dana@mail.com' for key 'students.email'
Rejected: Column 'age' cannot be null

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
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 ('Dana', 24, 'dana@mail.com')")
    attempts = [
        "INSERT INTO students (name, age, email) VALUES ('Copy', 20, 'dana@mail.com')",
        "INSERT INTO students (name, age, email) VALUES ('Noa', NULL, 'noa@mail.com')",
    ]
    for sql in attempts:
        try:
            cursor.execute(sql)
        except mysql.connector.IntegrityError as error:
            print("Rejected:", error.msg)
    connection.commit()
finally:
    connection.close()

Rejected: Duplicate entry 'dana@mail.com' for key 'students.email'
Rejected: Column 'age' cannot be null