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é :

universite
  1. É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.

    Démarrer l'activité avec Python Studio

  2. É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
    }
    
    

    Démarrer l'activité avec Python Studio

  3. É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é. 
    

    Démarrer l'activité avec Python Studio

  4. É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
    

    Démarrer l'activité avec Python Studio

  5. É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
    

    Démarrer l'activité avec Python Studio