Interagir avec les bases de données
Ce chapitre aborde les bases de données SQL. Si toutefois ce type de base de données vous est inconnu, un chapitre permettant de vous y introduire est disponible en annexe.
Introduction à la base de données SQL
Peu à peu, nos programmes manipuleront un très grand nombre de données et nécessiteront un système plus performant pour stocker et lire ces données. Pour cela, nous ferons appel à des bases de données. Les bases de données permettent de stocker de grands volumes de données sous une forme normalisée et qui peut être utilisée par plusieurs programmes différents. Nous utiliserons dans ce cours deux SGBD gratuits :
- MariaDB
- Copie de MySQL, disponible gratuitement et sous licence libre (GPL). Le couple MariaDB et MySQL fait partie des SGBD les plus utilisés et les plus massivement déployés.
- SQLite3
- Il s'agit d'une bibliothèque écrite en C dans le domaine public permettant d'interagir avec des bases de données stockées dans des fichiers. À l'inverse de MariaDB, il ne repose pas sur une architecture client/serveur. Ce système est particulièrement adapté pour les petites bases de données stockées localement, comme alternative aux fichiers texte.
Utiliser une base de données SQLite3
Créer la base et insérer des données
Nous allons tout d'abord importer le module sqlite3. Dans l'exemple qui suit, nous allons créer une base de données avec une table contenant un répertoire téléphonique contenant le nom, le prénom, l'adresse et le numéro de téléphone fixe des contacts. Nous verrons comment insérer quelques enregistrements.
import sqlite3
baseDeDonnees = sqlite3.connect('contacts.db')
curseur = baseDeDonnees.cursor()
curseur.execute("CREATE TABLE Contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, nom TEXT NOT NULL, prenom TEXT NOT NULL, adresse TEXT NOT NULL, telephoneFixe TEXT)") # Création de la base de données
baseDeDonnees.commit() # On envoie la requête SQL
curseur.execute("INSERT INTO Contacts (nom, prenom, adresse, telephoneFixe) VALUES (?, ?, ?, ?)", ("Dupont", "Paul", "15 rue Louis Pasteur 10000 Troyes", "0325997452")) # On ajoute un enregistrement
baseDeDonnees.commit()
baseDeDonnees.close()
Il est possible d'ajouter un enregistrement depuis un dictionnaire. Dans l'exemple, on ajoute plusieurs enregistrements avec une boucle :
import sqlite3
baseDeDonnees = sqlite3.connect('contacts.db')
curseur = baseDeDonnees.cursor()
personnes = [
{"nom":"Chabot", "prenom":"Martin", "adresse":"18 rue Général Leclerc 13600 La Ciotat", "telephoneFixe":"0499506373"},
{"nom":"Delbois", "prenom":"Julie", "adresse":"35 rue du Château 77176 Savigny le Temple", "telephoneFixe":"0199836074"},
{"nom":"Rivard", "prenom":"Christelle", "adresse":"83 rue de Québec 83400 Hyères", "telephoneFixe":"0499687013"}
]
for contact in personnes:
curseur.execute("INSERT INTO Contacts (nom, prenom, adresse, telephoneFixe) VALUES (:nom, :prenom, :adresse, :telephoneFixe)", contact) # On ajoute un enregistrement depuis un dictionnaire
baseDeDonnees.commit()
idDernierEnregistrement = curseur.lastrowid # Récupère l'ID de la dernière ligne insérée.
baseDeDonnees.close()
L'exemple suivant illustre comment modifier des données :
import sqlite3
baseDeDonnees = sqlite3.connect('contacts.db')
curseur = baseDeDonnees.cursor()
curseur.execute("UPDATE Contacts SET telephoneFixe = ? WHERE id = ?", ("0598635076", 2))
baseDeDonnees.commit()
baseDeDonnees.close()
Récupérer des données
Pour récupérer les données, il est possible de récupérer le premier résultat avec fetchone ou de retourner tous les résultats avec fetchall. Voici un premier exemple utilisant fetchone :
>>> import sqlite3
>>> baseDeDonnees = sqlite3.connect('contacts.db')
>>> curseur = baseDeDonnees.cursor()
>>> curseur.execute("SELECT nom, prenom, telephoneFixe FROM Contacts WHERE id = ?", ("2",))
>>> contact = curseur.fetchone()
>>> print(contact)
('Chabot', 'Martin', '0598635076')
>>> baseDeDonnees.close()
Dans l'exemple ci-dessus, la variable contact contient un tuple avec les valeurs du premier enregistrement retourné par la requête.
Voyons à présent comment récupérer plusieurs enregistrements avec la commande fetchall :
>>> import sqlite3
>>> baseDeDonnees = sqlite3.connect('contacts.db')
>>> curseur = baseDeDonnees.cursor()
>>> curseur.execute("SELECT nom, prenom, telephoneFixe FROM Contacts")
>>> for contact in curseur.fetchall():
... print(contact)
...
('Dupont', 'Paul', '0325997452')
('Chabot', 'Martin', '0598635076')
('Delbois', 'Julie', '0199836074')
('Rivard', 'Christelle', '0499687013')
>>> baseDeDonnees.close()
Utiliser une base de données MariaDB/MySQL
Pour cette partie, vous devez avoir installé le paquet python3-mysql.connector.
Créer la base et insérer des données
Nous allons utiliser le module mysql.connector pour nous connecter au serveur MariaDB ou MySQL et créer notre base de données permettant de stocker un catalogue de produits.
import mysql.connector
baseDeDonnees = mysql.connector.connect(host="localhost",user="catalogue",password="JieTh8Th", database="Catalogue")
cursor = baseDeDonnees.cursor()
cursor.execute("CREATE TABLE Produits (reference CHAR(5) NOT NULL PRIMARY KEY, nom TINYTEXT NOT NULL, prix FLOAT NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;")
baseDeDonnees.close()
Nous allons à présent insérer des données dans cette table.
import mysql.connector
baseDeDonnees = mysql.connector.connect(host="localhost",user="catalogue",password="JieTh8Th", database="Catalogue")
curseur = baseDeDonnees.cursor()
curseur.execute("INSERT INTO Produits (reference, nom, prix) VALUES (%s, %s, %s)", ("ARB42", "Canapé deux places noir", 199.99))
baseDeDonnees.commit()
baseDeDonnees.close()
Il est également possible d'insérer des données depuis un dictionnaire :
import mysql.connector
baseDeDonnees = mysql.connector.connect(host="localhost",user="catalogue",password="JieTh8Th", database="Catalogue")
curseur = baseDeDonnees.cursor()
produits = [
{"reference":"EIS3P", "nom":"Chaise de salle à manger", "prix":25},
{"reference":"BA9KI", "nom":"Commode blanche", "prix":139.90},
{"reference":"OI4HE", "nom":"Table basse", "prix":24.95},
{"reference":"IOM9X", "nom":"Lit double", "prix":699.99}
]
for fiche in produits:
curseur.execute("INSERT INTO Produits (reference, nom, prix) VALUES (%(reference)s, %(nom)s, %(prix)s)", fiche)
baseDeDonnees.commit()
baseDeDonnees.close()
Récupérer des données
À l'instar de SQLite, on peut utiliser fetchone pour récupérer le premier résultat ou retourner tous les résultats avec fetchall. Voici comment récupérer le premier résultat d'une requête SELECT.
>>> import mysql.connector
>>> baseDeDonnees = mysql.connector.connect(host="localhost",user="catalogue",password="JieTh8Th", database="Catalogue")
>>> curseur = baseDeDonnees.cursor()
>>> curseur.execute("SELECT reference, nom, prix FROM Produits")
>>> print(curseur.fetchone())
('ARB42', 'Canapé deux places noir', 199.99)
>>> baseDeDonnees.close()
On peut retourner tous les résultats avec fetchall :
>>> import mysql.connector
>>> baseDeDonnees = mysql.connector.connect(host="localhost",user="catalogue",password="JieTh8Th", database="Catalogue")
>>> curseur = baseDeDonnees.cursor()
>>> curseur.execute("SELECT reference, nom, prix FROM Produits")
>>> for ligne in curseur.fetchall():
... print(ligne)
...
('ARB42', 'Canapé deux places noir', 199.99)
('BA9KI', 'Commode blanche', 139.9)
('EIS3P', 'Chaise de salle à manger', 25.0)
('IOM9X', 'Lit double', 699.99)
('OI4HE', 'Table basse', 24.95)
>>> baseDeDonnees.close()
Exercices
Vous êtes nouvellement embauché dans le secrétariat de scolarité d'une université. Votre travail est d'optimiser la gestion des étudiants, des enseignants, des matières enseignées, des inscriptions des étudiants à ces dernières et des résultats obtenus. À votre arrivée, une collègue vous fournit les fichiers tableurs permettant d'accomplir ces tâches (au format CSV) :
- Liste des étudiants etudiants.csv
- Télécharger le fichier
- Liste des enseignants enseignants.csv
- Télécharger le fichier
- Liste des matières matieres.csv
- Télécharger le fichier
- Liste des inscriptions inscriptions.csv
- Télécharger le fichier
- Liste des résultats resultats.csv
- Télécharger le fichier
Les exercices suivant permettront d'effectuer cela en scindant le travail en différentes sous-tâches. Chaque sous-tâche fera l'objet d'un nouveau programme. Tous ces programmes utiliseront la même base de données SQLite3. Toutes les moyennes auront deux décimales.
Voici le schéma UML de la base de données de l'université :
Écrivez un programme permettant de créer une base de données SQLite3 nommée universite.db et de créer la structure de table adaptée au stockage des données. Importez le contenu des fichiers CSV dans cette base.
Chaque table aura le même nom que le fichier CSV source.
Écrivez un programme permettant de générer des statistiques pour l'université au format JSON dans un fichier nommé statistiques.json dont nous stockerons les moyennes par matière moyenneMatiere, la moyenne maximale moyenneMax et minimale moyenneMin par matière, le nombre d'étudiants inscrits par matière nbEtudiants, la moyenne de toutes les matières moyenneTotale et le nombre d'étudiants par département nbEtudiantsParDepartement (les deux premiers nombres du code postal).
Exemple :
{ "nbEtudiantsParDepartement":{ "56":8, "74":3, … }, "moyenneMax":{ "CF19":14.96, "VS02":12.14, … }, "moyenneMatiere":{ "CF19":13.37, "VS02":14.4, … }, "moyenneMin":{ "CF19":3.99, "VS02":4.21, … }, "nbEtudiants":{ "CF19":8, "VS02":17, … }, "moyenneTotale":12.41 }Écrivez un programme permettant de générer un bulletin de notes par étudiant sous la forme d'un courrier stocké dans un fichier texte individuel. Chaque fichier aura pour nom le nom et le prénom de l'étudiant, séparés par un trait d'union (-) et pour extension .txt et sera stocké dans un dossier nommé courriersEtudiants créé pour cela. Chaque courrier adoptera ce modèle :
Université Claude Chappe 15 avenue de Moulincourbe 28094 Clairecombe Lionel Paulin 48 Ruelle de Locvaux 74019 Mivran Madame, Monsieur, Veuillez trouver dans le récapitulatif ci-dessous les résultats de vos examens. Matière Moyenne AI90 16.93 PQ84 12.7 UE21 12.0 VO38 12.49 XO83 13.05 ZI51 16.33 Moyenne générale 13.92 Ce document constitue les résultats officiels. Pour toute contestation, contactez le service scolarité.
Écrivez un programme permettant l'inscription d'un nouveau étudiant à l'université et son inscription aux matières.
Exemple :
Entrez le nom du nouvel étudiant : Durand Entrez le prénom du nouvel étudiant : Michel Entrez l'adresse du nouvel étudiant : 15 rue Jean Moulin Entrez le code postal du nouvel étudiant : 18543 Entrez la ville du nouvel étudiant : Moulincourbe Entrez le téléphone fixe du nouvel étudiant : 0574960180 Entrez le téléphone portable du nouvel étudiant : 0641238074 Entrez le code de matière dans laquelle inscrire l'étudiant (laissez vide pour arrêter, tapez '?' pour afficher la liste) : ? AD23 AF04 … Entrez le code de matière dans laquelle inscrire l'étudiant (laissez vide pour arrêter, tapez '?' pour afficher la liste) : LK27 Entrez le code de matière dans laquelle inscrire l'étudiant (laissez vide pour arrêter, tapez '?' pour afficher la liste) : AD24 La matière n'existe pas Entrez le code de matière dans laquelle inscrire l'étudiant (laissez vide pour arrêter, tapez '?' pour afficher la liste) : ZB17 Entrez le code de matière dans laquelle inscrire l'étudiant (laissez vide pour arrêter, tapez '?' pour afficher la liste) : Voulez-vous saisir un nouvel étudiant (O/N) ? n
Écrivez un programme permettant la saisie des notes obtenues aux examens.
Exemple :
Entrez le code de matière de l'examen (laissez vide pour arrêter, tapez '?' pour afficher la liste) : AF04 Entrez la note obtenue par Maxime Bey : 17 … Entrez la note obtenue par Margot Ferrero : 14 Voulez-vous saisir un nouveau résultat (O/N) ? n