TP3 - Manipuler avec python

Objectifs

1. Manipuler des requêtes SQL;
2. Comprendre les fonctions d'agrégation;
3. Utiliser les BDD en programmation.

Au préalable

1. Se créer un dossier Terminale NSI sur votre ordinateur ou clé USB
2. Dans ce dossier, créer un dossier BDD
3. Enregistrer le code python ainsi que la base dans ce dossier.

Tutoriel

SQLite est un moteur de base de données léger intégré à Python. La bibliothèque sqlite3 permet de gérer des bases de données SQL sans nécessiter un serveur externe.

Importer sqlite3 et créer une base de données

SQLite crée automatiquement un fichier de base de données s'il n'existe pas encore.

import sqlite3

# Connexion à la base de données
conn = sqlite3.connect('ma_base_de_donnees.db')

# Création d'un curseur pour exécuter des requêtes SQL
cursor = conn.cursor()

print("Base de données connectée avec succès.")

Créer une table

On va créer une table appelée utilisateurs avec des colonnes id, nom, et age :

# Création de la table
cursor.execute('''
CREATE TABLE IF NOT EXISTS utilisateurs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nom TEXT NOT NULL,
    age INTEGER
)
''')

print("Table 'utilisateurs' créée.")

Dans cette requête, la première ligne indique que l'on crée une table SI celle-ci n'existe pas.

Attributs

• id, de type integer, en clé primaire (primary key) avec en plus la valeur autoincrement ;
• nom, de type text qui ne doit pas être vide ;
• age de type integer.

Clés étrangères

On rajoute à la fin de la requête :

foreign key(attribut_cle_etrangere) references table_cle_primaire(attribut_cle_primaire) on update cascade on delete cascade,

On indique la création d'une clé étrangère, en précisant dans les parenthèses l'attribut clé étrangère, faisant référence à l'attribut clé primaire, en précisant sa table d'origine.
on update cascade on delete cascade permette de supprimer ou de mettre à jour des données dans la table s'il y a du changement au niveau de la clé primaire.

Insérer des données

cursor.execute('''
INSERT INTO utilisateurs (nom, age)
VALUES ('Alice', 30), ('Bob', 25), ('Charlie', 35)
''')

# Sauvegarder les modifications
conn.commit()
print("Données insérées avec succès.")

Lire des données

cursor.execute('SELECT * FROM utilisateurs')
utilisateurs = cursor.fetchall()

print("Liste des utilisateurs :")
for utilisateur in utilisateurs:
    print(utilisateur)

La variable utilisateurs devient une matrice que l'on peut parcourir avec une boucle. Chaque élément de la liste contient un tuple contenant les éléments sélectionnés par la requête SQL.

Mettre à jour des données

cursor.execute('''
UPDATE utilisateurs
SET age = 40
WHERE nom = 'Alice'
''')

conn.commit()
print("Données mises à jour.")

Supprimer des données

cursor.execute('''
DELETE FROM utilisateurs
WHERE nom = 'Bob'
''')

conn.commit()
print("Données supprimées.")

Fermer la connexion

Une fois le programme terminé, on ferme la connexion à la base :

conn.close()
print("Connexion fermée.")

TP

Vous êtes un bibliothécaire souhaitant moderniser la gestion des emprunts de livres. Après avoir suivi une formation dans la gestion de bases de données (donnée par un super prof chauve), vous décidez de créer votre propre base.
La base de données contient trois tables principales :

• Auteurs : informations sur les auteurs, avec comme attributs :
  • id_auteur (clé primaire, entier)
  • nom (texte)
  • pays (texte)
• Livres : informations sur les livres, avec comme attributs :
  • id_livre (clé primaire, entier)
  • titre (texte)
  • annee_publication (entier)
  • id_auteur (clé étrangère, référencé depuis auteurs)
• Emprunts : registre des livres empruntés, avec comme attributs :
  • id_emprunt (clé primaire, entier)
  • id_livre (clé étrangère, référencé depuis livres)
  • date_emprunt (texte, format YYYY-MM-DD)
  • date_retour (texte, format YYYY-MM-DD, peut être NULL)

1. Dans un premier temps, écrire le code permettant de créer la base bibliotheque.db, puis créer un "curseur" permettant l'exécution de requêtes.
2. Écrire les requêtes permettant la création des 3 tables décrites ci-dessus.
   Visualiser les tables

   Le site accessible ici permet de déposer un fichier .db et de visualiser les tables et leur contenu.

3. Insérer les données suivantes dans les tables :
   • Auteurs :
     • 1, "Victor Hugo", "France"
     • 2, "Jane Austen", "Angleterre"
     • 3, "George Orwell", "Angleterre"
   • Livres :
     • 1, "Les Misérables", 1862, 1
     • 2, "Orgueil et Préjugés", 1813, 2
     • 3, "1984", 1949, 3
     • 4, "Notre-Dame de Paris", 1831, 1
   • Emprunts :
     • 1, 1, "2024-11-20", NULL
     • 2, 2, "2024-11-15", "2024-11-25"
     • 3, 3, "2024-11-10", NULL
4. Écrire la requête listant tous les livres, leurs auteurs et leurs années de publication, et l'afficher dans la console.
5. Afficher les livres actuellement empruntés (ceux dont date_retour est NULL).
6. Compter le nombre de livres écrits par chaque auteur.
7. Trouver l'auteur dont les livres sont les plus empruntés.
8. Déterminer le nombre total d'emprunts par pays des auteurs.