Transformation d'un tableau en BD relationnelle et accès depuis Python

De nombreuses données sont gérées sous des feuilles de calcul, sous la forme de simples tableaux. C'est bien souvent le cas pour des applications de petite taille, ou lorsque les créateurs/utilisateurs n'ont pas les compétences ou connaissances nécessaires à la conception et exploitation d'une base de données normalisée. C'est au moment où les besoins évoluent, où les volumes augmentent, où les personnes se renouvellent, où les bugs apparaissent, ... que la décision est bien souvent prise d'une migration vers un SGBD. Dans cet exemple, on considère les données de résultats du premier tour des élections présidentielles 2017, par bureau de vote

Première séance

  1. Importez ces données (exportation CSV depuis la page WEB, uniquement les données du Rhône et de la Loire) sous PostgreSQL dans une relation unique nommée « election-csv » (créée par le script fourni plus loin).
  2. Étudiez les données. Dans un fichier texte, pour chaque colonne, décrivez la signification de l'attribut, son rôle dans les données, éventuellement la façon dont il est construit.
  3. Dressez un schéma Entité-Association modélisant les données. Vos choix seront justifiés en vérifiant que les DF impliquées par votre schéma sont bien satisfaites dans les données (voir requête plus loin).
  4. Traduisez en relationnel. Étudiez la forme normale de la modélisation proposée.
  5. Implémentez la base de données correspondante, avec les contraintes appropriées. Migrez les données par des requêtes SQL qui seront proprement conservées dans la documentation de la migration.

Deuxième séance

En SQL

  1. Pour chaque ville, pour chaque candidat : donnez le résultat du candidat (%) ainsi que son classement.

En Python

  1. Même question mais en passant par Python  (récupérer les données, puis coder l’aggrégation).
  2. Effectuez un clustering (kmeans) des différents bureaux de votes et projetez les résultats sur une carte (ou en projetant tout simplement les coordonnées des bureaux de votes de chaque cluster).

Modalités d’évaluation du TP

Le TP est sur 2 séances ; chaque binôme déposera trois fichiers dans TOMUSS au plus tard le 2 avril 2021 :

ANNEXES

Script de création de la table « election-csv » pour chargement des données brutes

DROP TABLE IF EXISTS "election-csv"; CREATE TABLE "election-csv" ( "Code du département" DECIMAL NOT NULL, "Département" VARCHAR(5) NOT NULL, "Code de la circonscription" DECIMAL NOT NULL, "Circonscription" VARCHAR(21) NOT NULL, "Code de la commune" DECIMAL NOT NULL, "Commune" VARCHAR(70) NOT NULL, "Bureau de vote" DECIMAL NOT NULL, "Inscrits" DECIMAL NOT NULL, "Abstentions" DECIMAL NOT NULL, "% Abs/Ins" DECIMAL NOT NULL, "Votants" DECIMAL NOT NULL, "% Vot/Ins" DECIMAL NOT NULL, "Blancs" DECIMAL NOT NULL, "% Blancs/Ins" DECIMAL NOT NULL, "% Blancs/Vot" DECIMAL NOT NULL, "Nuls" DECIMAL NOT NULL, "% Nuls/Ins" DECIMAL NOT NULL, "% Nuls/Vot" DECIMAL NOT NULL, "Exprimés" DECIMAL NOT NULL, "% Exp/Ins" DECIMAL NOT NULL, "% Exp/Vot" DECIMAL NOT NULL, "N°Panneau" DECIMAL NOT NULL, "Sexe" VARCHAR(1) NOT NULL, "Nom" VARCHAR(13) NOT NULL, "Prénom" VARCHAR(8) NOT NULL, "Voix" DECIMAL NOT NULL, "% Voix/Ins" DECIMAL NOT NULL, "% Voix/Exp" DECIMAL NOT NULL, "Code Insee" DECIMAL NOT NULL, "Coordonnées" VARCHAR(19), "Nom Bureau Vote" VARCHAR(70), "Adresse" VARCHAR(70), "Code Postal" DECIMAL, "Ville" VARCHAR(70), uniq_bdv VARCHAR(85) );

Import CSV

Pour importer un fichier csv en utilisant DBEAVER, placez-vous sur la table dans la colonne de gauche, clic droit « importer ». Attention les séparateurs du fichier csv sont des ";". Vous allez le faire deux fois : une fois pour le Rhône et une fois pour la Loire. Une option vous permet d'ajouter les tuples sans écraser ceux qui existent déjà (il faut donc décocher la case "truncate table before load").

Tester la satisfaction d'une DF

Faire afficher les contre-exemples d’une DF R : X --> Y dans une relation r sur R :

select distinct X , Y from r where X in (select X from r group by X having count(distinct Y)>1 ) order by X, Y ;

Le résultat est VIDE si la DF est satisfaite dans la relation. Si la DF est contredite, les valeurs posant problème apparaîtront (doublons sur X mais valeurs différentes sur Y).

Note : une DF peut être non satisfaite car l'hypothèse n'était pas bonne ; mais parfois, une DF peut être contredite par quelques contre-exemples issus d'erreurs dans les données. Si c'est le cas, vous pourrez corriger cette erreur par des requêtes (à ajouter au scritp).

Accéder à une BD depuis Python

Dans votre script Python, vous allez accéder à PostgreSQL en utilisant le package Python Psycopg2.

Vous procéderez à une installation qui se fera sur votre espace local avec la commande

pip install psycopg2

Les principales commandes dont vous aurez besoin pour la connection, l'envoi et la réception des requêtes sont ici.