TP : bases de données et Python

Partie 1 - Explorer la base bibliothèque depuis Python

Rappel : le workflow sqlite3

import sqlite3

# 1. Connexion (ou création) de la base
conn = sqlite3.connect("bibliotheque.db")

# 2. Création d'un curseur
cursor = conn.cursor()

# 3. Exécution d'une requête
cursor.execute("SELECT * FROM Livres")

# 4. Récupération des résultats
resultats = cursor.fetchall()
for ligne in resultats:
    print(ligne)

# 5. Fermeture de la connexion
conn.close()

Questions

Télécharger la base bibliotheque.db et la placer dans le même dossier que le script Python.

1. Écrire un programme qui se connecte à la base bibliotheque.db et affiche tous les livres (titre et auteur) sous la forme :

   Le Petit Prince - Antoine de Saint-Exupéry
   Les Misérables - Victor Hugo
   ...
   

   Indication : chaque élément renvoyé par fetchall() est un tuple. Si ligne = ("Le Petit Prince", "Antoine de Saint-Exupéry"), on accède au titre par ligne[0] et à l’auteur par ligne[1].

   Indice

   Reprendre le programme du rappel en changeant deux choses : la requête, qui ne sélectionne plus que les colonnes titre et auteur, et l’affichage dans la boucle, qui devient une f-string.

2. Écrire un programme qui demande à l’utilisateur un genre (avec input) et affiche les titres des livres de ce genre.

   Attention : ne jamais insérer directement la variable dans la requête avec une f-string ou une concaténation. Utiliser un paramètre :

   genre = input("Genre recherché : ")
   cursor.execute("SELECT titre FROM Livres WHERE genre = ?", (genre,))
   

   Pourquoi cette précaution est-elle importante ? Rechercher le terme injection SQL.

   Indice

   Chaque ligne renvoyée est un tuple d’un seul élément : le titre est ligne[0]. Pour l’injection, se demander ce qui se passerait si l’utilisateur tapait un morceau de requête SQL et que ce texte était recopié tel quel dans la requête.

3. Écrire un programme qui affiche le nombre total d’emprunts de chaque élève, sous la forme :

   Alice Martin : 3 emprunt(s)
   Lucas Bernard : 2 emprunt(s)
   ...
   

   Indication : utiliser une jointure entre Emprunts et Eleves avec COUNT et GROUP BY.

   Indice

   GROUP BY forme un groupe par élève et COUNT(*) compte les emprunts de chaque groupe. Chaque ligne renvoyée est un triplet (prénom, nom, nombre), que la boucle for peut déballer en trois variables.

   Code à copier puis compléter

   import sqlite3
   
   conn = sqlite3.connect("bibliotheque.db")
   cursor = conn.cursor()
   cursor.execute("""SELECT prenom, nom, COUNT(*)
                     FROM Emprunts JOIN Eleves ON ... = ...
                     GROUP BY ...""")
   for prenom, nom, nb in cursor.fetchall():
       print(...)
   conn.close()
   

4. Écrire un programme qui affiche la liste des livres actuellement empruntés (non rendus) avec le nom de l’emprunteur :

   "Harry Potter à l'école des sorciers" emprunté par Alice Martin
   "Fondation" emprunté par Léa Petit
   "1984" emprunté par Emma Dubois
   

   Indice

   Deux jointures relient Emprunts à Livres par id_livre et à Eleves par id_eleve ; la condition date_retour IS NULL ne garde que les emprunts non rendus. Les guillemets qui entourent le titre font partie du texte affiché : délimiter la f-string par des apostrophes, f'"{titre}" emprunté par ...'.

# 1.
import sqlite3

conn = sqlite3.connect("bibliotheque.db")
cursor = conn.cursor()
cursor.execute("SELECT titre, auteur FROM Livres")
for titre, auteur in cursor.fetchall():
    print(f"{titre} - {auteur}")

# 2.
genre = input("Genre recherché : ")
cursor.execute("SELECT titre FROM Livres WHERE genre = ?", (genre,))
for (titre,) in cursor.fetchall():
    print(titre)

# 3.
cursor.execute("""SELECT prenom, nom, COUNT(*)
                  FROM Emprunts JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
                  GROUP BY Eleves.id_eleve""")
for prenom, nom, nb in cursor.fetchall():
    print(f"{prenom} {nom} : {nb} emprunt(s)")

# 4.
cursor.execute("""SELECT titre, prenom, nom
                  FROM Emprunts
                  JOIN Livres ON Emprunts.id_livre = Livres.id_livre
                  JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
                  WHERE date_retour IS NULL""")
for titre, prenom, nom in cursor.fetchall():
    print(f'"{titre}" emprunté par {prenom} {nom}')
conn.close()

Partie 2 - Créer une base de données depuis Python

On souhaite créer de toutes pièces une base de données pour gérer les notes d’un professeur.

Étape 1 : création des tables

5. Écrire un programme qui crée une base notes.db contenant deux tables :

   • Matieres(id_matiere INTEGER, nom TEXT NOT NULL)
   • Notes(id_note INTEGER, #id_matiere INTEGER, eleve TEXT NOT NULL, note REAL NOT NULL, date TEXT NOT NULL, FOREIGN KEY (id_matiere) REFERENCES Matieres(id_matiere))

   Indication : ne pas oublier conn.commit() après les ordres CREATE TABLE pour valider les modifications.

   Indice

   Le cours sur le module sqlite3 exécute un CREATE TABLE depuis Python : une requête par table, écrite entre triples guillemets. Dans le schéma, l’attribut souligné devient INTEGER PRIMARY KEY, et la ligne FOREIGN KEY ... se recopie à la fin de la table Notes.

import sqlite3

conn = sqlite3.connect("notes.db")
cursor = conn.cursor()
cursor.execute("""CREATE TABLE Matieres (
                      id_matiere INTEGER PRIMARY KEY,
                      nom TEXT NOT NULL)""")
cursor.execute("""CREATE TABLE Notes (
                      id_note INTEGER PRIMARY KEY,
                      id_matiere INTEGER,
                      eleve TEXT NOT NULL,
                      note REAL NOT NULL,
                      date TEXT NOT NULL,
                      FOREIGN KEY (id_matiere) REFERENCES Matieres(id_matiere))""")
conn.commit()

Étape 2 : insertion des données

6. Ajouter les matières suivantes : Mathématiques (1), NSI (2), Physique-Chimie (3).

   Indice

   Un ordre INSERT INTO Matieres VALUES (?, ?) par matière, ou un seul cursor.executemany avec la liste des trois couples (identifiant, nom), puis conn.commit().

   Puis ajouter les notes suivantes avec executemany :

   notes = [
       (1, 2, "Alice Martin", 16.5, "2025-09-15"),
       (2, 2, "Lucas Bernard", 12.0, "2025-09-15"),
       (3, 1, "Alice Martin", 14.0, "2025-09-18"),
       (4, 1, "Lucas Bernard", 9.5, "2025-09-18"),
       (5, 2, "Emma Dubois", 18.0, "2025-09-15"),
       (6, 3, "Emma Dubois", 11.0, "2025-09-20"),
       (7, 2, "Alice Martin", 15.0, "2025-10-10"),
       (8, 1, "Emma Dubois", 13.5, "2025-10-12"),
   ]
   cursor.executemany("INSERT INTO Notes VALUES (?, ?, ?, ?, ?)", notes)
   conn.commit()
   

cursor.executemany("INSERT INTO Matieres VALUES (?, ?)",
                   [(1, "Mathématiques"), (2, "NSI"), (3, "Physique-Chimie")])
conn.commit()

Étape 3 : requêtes

7. Écrire un programme qui affiche, pour chaque matière, la moyenne des notes :

   Mathématiques : 12.33
   NSI : 15.38
   Physique-Chimie : 11.00
   

   Indication : utiliser AVG, JOIN et GROUP BY. Pour formater l’affichage, utiliser f"{moyenne:.2f}".

   Indice

   Même structure qu’à la question 3 : la jointure relie Notes et Matieres par id_matiere, AVG(note) remplace COUNT(*), et l’on forme un groupe par matière.

8. Écrire un programme qui affiche la meilleure note et le nom de l’élève correspondant pour chaque matière.

   Indice

   Reprendre la requête de la question 7 avec MAX(note) à la place de AVG(note) et la colonne eleve en plus. En SQLite, avec MAX et GROUP BY, les autres colonnes sélectionnées proviennent de la ligne qui réalise le maximum.

# 7.
cursor.execute("""SELECT nom, AVG(note)
                  FROM Notes JOIN Matieres ON Notes.id_matiere = Matieres.id_matiere
                  GROUP BY Matieres.id_matiere""")
for nom, moyenne in cursor.fetchall():
    print(f"{nom} : {moyenne:.2f}")

# 8.
cursor.execute("""SELECT nom, eleve, MAX(note)
                  FROM Notes JOIN Matieres ON Notes.id_matiere = Matieres.id_matiere
                  GROUP BY Matieres.id_matiere""")
for nom, eleve, meilleure in cursor.fetchall():
    print(f"{nom} : {meilleure} ({eleve})")

Mathématiques : 14.0 (Alice Martin)
NSI : 18.0 (Emma Dubois)
Physique-Chimie : 11.0 (Emma Dubois)

Étape 4 : contraintes d’intégrité

9. Activer les clés étrangères avec cursor.execute("PRAGMA foreign_keys = ON") juste après la connexion.

   Puis tenter d’insérer une note avec un id_matiere inexistant (par exemple 99). Que se passe-t-il ?

   Entourer cette insertion dans un bloc try ... except pour capturer l’erreur proprement :

   try:
       cursor.execute("INSERT INTO Notes VALUES (9, 99, 'Test', 10, '2025-11-01')")
       conn.commit()
   except sqlite3.IntegrityError as e:
       print(f"Erreur d'intégrité : {e}")
   

   Les mots as e donnent le nom e à l’erreur capturée : print(e) en affiche le message, qui explique la cause du refus.

   Indice

   La matière 99 existe-t-elle dans la table Matieres ? Exécuter l’insertion avant puis après l’activation des clés étrangères, et lire le message affiché par le bloc except.

10. Écrire une fonction ajouter_note(conn, id_note, id_matiere, eleve, note, date) qui insère une note dans la base en gérant les erreurs d’intégrité. La fonction doit renvoyer True si l’insertion a réussi, False sinon.

    Indice

    Reprendre le bloc try ... except de la question 9, avec une requête à paramètres ? comme à la question 2 : si l’insertion viole une contrainte, l’exécution passe directement au bloc except.

    Code à copier puis compléter

    def ajouter_note(conn, id_note, id_matiere, eleve, note, date):
        cursor = conn.cursor()
        try:
            cursor.execute("INSERT INTO Notes VALUES (?, ?, ?, ?, ?)", (...))
            conn.commit()
            return ...
        except sqlite3.IntegrityError:
            return ...
    

Erreur d'intégrité : FOREIGN KEY constraint failed

def ajouter_note(conn, id_note, id_matiere, eleve, note, date):
    try:
        conn.execute("INSERT INTO Notes VALUES (?, ?, ?, ?, ?)",
                     (id_note, id_matiere, eleve, note, date))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False

Partie 3 - Pour aller plus loin

11. Écrire un programme interactif qui propose un menu à l’utilisateur :

    === Gestion de la bibliothèque ===
    1. Rechercher un livre par titre
    2. Afficher les emprunts en cours
    3. Enregistrer un nouvel emprunt
    4. Enregistrer un retour
    5. Quitter
    

    Le programme doit boucler jusqu’à ce que l’utilisateur choisisse de quitter. On utilisera des fonctions pour structurer le code.

    Indice

    La boucle while répète l’affichage du menu et la lecture du choix tant que ce choix n’est pas "5" : input renvoie une chaîne, que l’on compare donc à "5" et non à 5. Chaque autre choix appelle une fonction. Pour la recherche, la condition titre LIKE ? avec la valeur debut + "%" trouve les titres qui commencent par debut.

    Code à copier puis compléter

    import sqlite3
    
    conn = sqlite3.connect("bibliotheque.db")
    cursor = conn.cursor()
    
    
    def rechercher_livre():
        debut = input("Titre (ou début de titre) : ")
        ...
    
    
    def afficher_emprunts_en_cours():
        ...
    
    
    choix = ""
    while choix != "5":
        print("=== Gestion de la bibliothèque ===")
        print("1. Rechercher un livre par titre")
        ...
        choix = input("Votre choix : ")
        if choix == "1":
            rechercher_livre()
        elif choix == "2":
            ...
    
    conn.close()
    

12. Améliorer le programme précédent pour qu’il vérifie, avant d’enregistrer un emprunt, que le livre n’est pas déjà emprunté (c’est-à-dire qu’il n’existe pas d’emprunt sans date de retour pour ce livre).

    Indice

    Avant l’insertion, compter avec COUNT(*) les emprunts de ce livre dont date_retour IS NULL : cursor.fetchone() renvoie l’unique ligne du résultat, un tuple dont l’élément d’indice 0 est ce nombre. Le livre est disponible s’il vaut 0.

def est_disponible(id_livre):
    cursor = conn.execute(
        "SELECT COUNT(*) FROM Emprunts WHERE id_livre = ? AND date_retour IS NULL",
        (id_livre,))
    return cursor.fetchone()[0] == 0