1CC1000 - Systèmes d'information et programmation - TD: Processus ETL avec Python

Activités de ce laboratoire :

Apprendre à manipuler des données semi-structurées avec Pandas.
Apprendre ce que sont les processus ETL.
Module ETL de PistusResa : nettoyer et importer des données dans une base de données relationnelle.
Module Student de PistusResa : lecture de données à partir d'une base de données relationnelle à l'aide de code Python.

Introduction à Pandas

Les données utilisées dans PistusResa sont stockées dans deux fichiers CSV. Nous utiliserons Pandas pour lire les données des fichiers CSV, les nettoyer et les charger dans la base de données Pistus.

L'objectif de cette section est d'apprendre à utiliser Pandas.

Téléchargez ce notebook et importez-le dans Visual Studio Code. Lisez le notebook et faites les exercices avant de passer à la section suivante.

⏰ Temps à consacrer à cette partie : 1h30.

ANSWER ELEMENTS

Vous pouvez télécharger ici le notebook avec le corrigé.

Le module ETL de PistusResa

L'objectif de cette section est d'extraire les données des fichiers CSV, de les transformer et de les charger dans la base de données Pistus.

Téléchargez ce notebook et importez-le dans Visual Studio Code. Lisez le notebook et faites les exercices avant de passer à la section suivante.

⏰ Temps à consacrer à cette partie : 30 minutes.

ANSWER ELEMENTS

Vous pouvez télécharger ici le notebook avec le corrigé.

Mise en œuvre de PistusResa

Vous êtes maintenant prêt à travailler sur l'implémentation de PistusResa.

Téléchargez le squelette de l'application PistusResa et importez-le dans Visual Studio Code.
Copiez le fichier de base de données pistus.db dans le dossier data.

⏰ Temps à consacrer à cette partie : 1h.

Le module Student

Le module Student contient les fonctions nécessaires pour ajouter et modifier les informations relatives aux étudiants dans la base de données.

Le code du module Student se trouve dans le fichier mstudent.py.

Dans le fichier mstudent.py, les fonctions suivantes sont définies mais non implémentées.

get_student, renvoie les données d'un étudiant stockées dans la base de données.
get_associations, renvoie toutes les associations stockées dans la base de données.
get_roles, renvoie les rôles des étudiants dans une association.
get_memberships, renvoie les associations d'un étudiant donné.
add_email_address, ajoute une adresse électronique à un étudiant.
add_student, ajoute un étudiant à la base de données.
add_membership, ajoute un étudiant à une association dans la base de données.
delete_email_address, supprime l'adresse électronique d'un étudiant.
delete_membership, supprime l'appartenance d'un étudiant à une association.
update_first_name, met à jour le prénom de l'étudiant.
update_last_name, met à jour le nom de famille d'un étudiant.
update_gender, met à jour le sexe d'un étudiant.
update_email_address, met à jour l'adresse électronique d'un étudiant.
update_membership, met à jour l'appartenance d'un étudiant à une association.

Dans le fichier mstudent_test.py nous avons défini et implémenté une fonction de test pour chacune des fonctions ci-dessus. Ces fonctions de test sont appelées dans la section principale du fichier mstudent.py : voir le code après la ligne 487.

Voici ce que vous devez faire :

Ouvrez le fichier mstudent.py.
Implémentez les fonctions dans l'ordre dans lequel elles sont définies : l'ordre est important car les tests en dépendent.
Chaque fonction est commentée dans le fichier mstudent.py. Lisez attentivement tous les commentaires pour comprendre comment implémenter chaque fonction.
Vous devrez utiliser des requêtes SQL dans chaque fonction. Un aide-mémoire SQL est à votre disposition.
Chaque fois que vous terminez l'implémentation d'une fonction, exécutez le fichier mstudent.py en tant que script Python. Cela déclenchera l'exécution des tests.
Si un test échoue, vous devrez comprendre les messages d'erreur et résoudre le problème.

N'oubliez pas de supprimer l'instruction raise NotImplementedError lorsque vous implémentez une fonction. Sinon, votre fonction sera toujours considérée comme non implémentée.

👉 Le module Registration (fichier mregistration.py) est déjà implémenté. Examinez le code pour comprendre comment le module Student doit être implémenté.

ANSWER ELEMENTS

Vous pouvez télécharger le squelette avec le corrigé ici.


def get_student(stud_number, cursor):
    stud_data = None
    # Gets the data from the table Student
    try:
        cursor.execute('SELECT * FROM student WHERE stud_number=?', \
            (stud_number,))
        stud_data = cursor.fetchone()
    except sqlite3.Error as error:
        print(error) 
        return None 
    # If no student can be found, return an empty tuple
    if stud_data is None:
        return () 

    email_addresses = []
    try:
        # Gets the email addresses of the student.
        cursor.execute("SELECT email FROM emailAddress WHERE stud_number=?", (stud_number,))
        result = cursor.fetchall()
        for row in result:
            email_addresses.append(row[0])
    except sqlite3.Error as error:
        print(error) 
        return None
    return (stud_data[0], stud_data[1], stud_data[2], stud_data[3], email_addresses)

#######

def get_associations(cursor):
    associations = []
    try:
        # Gets the data from the table Association.
        cursor.execute('SELECT * FROM association')
        result = cursor.fetchall()
        for row in result:
            associations.append((row[0], row[1]))
    except sqlite3.Error as error:
        print(error) 
        return None 
    return associations

#######

def get_roles(cursor):
    roles = []
    try:
        # Get the student roles in the associations.
        cursor.execute('SELECT DISTINCT stud_role FROM membership')
        result = cursor.fetchall()
        for row in result:
            roles.append(row[0])
    except sqlite3.Error as error:
        print(error) 
        return None
    return roles

#######

def get_memberships(stud_number, cursor):
    memberships = []
    try: 
        cursor.execute('SELECT asso_name, stud_role FROM membership WHERE stud_number=?', \
            (stud_number,))
        result = cursor.fetchall()
        for row in result:
            memberships.append((row[0], row[1]))
    except sqlite3.Error as error:
        print(error) 
        return None
    return memberships

#######

def add_email_address(stud_number, email_address, cursor):
    try:
       cursor.execute('INSERT INTO emailAddress VALUES (?, ?)',\
           (email_address, stud_number))
    except sqlite3.IntegrityError as error:
        return (False, DUPLICATE_EMAIL_ADDRESS, email_address)
    except sqlite3.Error as error:
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

#######

def add_student(stud_number, first_name, last_name, gender, email_addresses, cursor):
    try:
        # Insert the values in the table Student
        cursor.execute('INSERT INTO student VALUES (?, ?, ?, ?)', \
            (stud_number, first_name, last_name, gender))
    except sqlite3.IntegrityError as error: 
        return (False, DUPLICATE_STUD_NUMBER, None)
    except sqlite3.Error as error:
        return (False, UNEXPECTED_ERROR, error)

    # Add the email addresses of the student
    for email in email_addresses:
        res = add_email_address(stud_number, email, cursor)
        if res[0] == False:
            return res
    return (True, None, None)

#######

def add_membership(stud_number, membership, cursor):
    try:
        cursor.execute('INSERT INTO membership VALUES (?, ?, ?)', \
                (stud_number, membership[0], membership[1]))
    except sqlite3.IntegrityError as error:
        return (False, DUPLICATE_MEMBERSHIP, membership[0])
    except sqlite3.Error as error:
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

#######

def delete_email_address(stud_number, email_address, cursor):
    try:
       cursor.execute('DELETE FROM emailAddress WHERE stud_number=? AND email=?',\
           (stud_number, email_address))
    except sqlite3.Error as error:
        return (False, UNEXPECTED_ERROR, error)

    return (True, None, None)

#######

def delete_membership(stud_number, asso_name, cursor):
    try:
        cursor.execute('DELETE FROM membership WHERE stud_number=? and asso_name=?', \
            (stud_number, asso_name))
    except sqlite3.Error as error:
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

#######    

def update_first_name(stud_number, first_name, cursor):
    try: 
        cursor.execute('UPDATE student SET first_name=? WHERE stud_number=?',\
            (first_name, stud_number))
    except sqlite3.Error as error:
        print(error) 
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

#######

def update_last_name(stud_number, last_name, cursor):
    try:
        cursor.execute('UPDATE student SET last_name=? WHERE stud_number=?',\
            (last_name, stud_number))
    except sqlite3.Error as error:
        print(error) 
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

#######

def update_gender(stud_number, gender, cursor):
    try:
        cursor.execute('UPDATE student SET gender=? WHERE stud_number=?',\
            (gender, stud_number))
    except sqlite3.Error as error:
        print(error) 
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

#######

def update_email_address(stud_number, old_email_address, new_email_address, cursor):
    try:
        cursor.execute('UPDATE emailAddress SET email=? WHERE stud_number=? AND email=?',\
            (new_email_address, stud_number, old_email_address))
    except sqlite3.IntegrityError as error:
        return (False, DUPLICATE_EMAIL_ADDRESS, new_email_address)
    except sqlite3.Error as error:
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

#######    

def update_membership(stud_number, old_association, new_association, role, cursor):
    try:
        cursor.execute('UPDATE membership SET asso_name=?, stud_role=? WHERE stud_number=?\
            AND asso_name=?', (new_association, role, stud_number, old_association))
    except sqlite3.IntegrityError as error:
        return (False, DUPLICATE_MEMBERSHIP, new_association)
    except sqlite3.Error as error:
        return (False, UNEXPECTED_ERROR, error)
    return (True, None, None)

Prochain TD

Dans le prochain TD, nous allons :

coder une fênetre de connexion pour PistusResa.
coder les modules d'authentification et de gestion des échéances de paiement (si le temps le permettra).

👉 Si vous avez atteint la fin de ce TD, et qu'il vous reste du temps, vous pouvez commencer le prochain TD.