DUT en Statistique et Informatique Décisionnelle, Université de la Côte d'Azur

Cours de Bases de données avancées 2018/19

Enseignant: prof. Malchiodi (Università degli Studi di Milano)

TP recapitulatif: gestion de bases de données

1. Installer MAMP au cas où il ne soit pas disponible.
2. Lancer MAMP et noter le port du serveur mySQL (avec toute probabilité 3307).
3. Dans une fenêtre à commande, se positionner avec la commande CD dans le repertoire qui contient l'executable mysql MAMP. (ou mieux encore, modifier la variable d'environnement PATH de Windows en y ajoutant ce repertoire).
4. Lancer mysql spécifiant root comment usager.
5. Vérifier qu'il existe une BD nommée superheroes et un usager superheroadmin qui a tous les privilèges dans cette BD. dans la table heroes.
6. Si la BD n'existe pas, la créer y ajoutant une table heroes avec la structure suivante:
   1. id INTEGER UNIQUE NOT NULL
   2. name VARCHAR(100)
   3. identity VARCHAR(100)
   4. birth_place VARCHAR(100)
   5. publisher VARCHAR(100)
   6. height FLOAT
   7. weight FLOAT
   8. gender CHAR(1)
   9. first_appearance INTEGER
   10. eye_color VARCHAR(10)
   11. hair_color VARCHAR(
   10)
   12. strength FLOAT
   13. intelligence VARCHAR(20)
   et avec id comme clé primaire.
7. Si l'usager superheroadmin n'existe pas, le créer en lui allouant ALL PRIVILEGES sur la BD superheroes. Noter quelque part le mot clé de cet usager.
8. Vérifier que jupyter soit dipsonibile: lancez un Anaconda prompt, et s'il n'est pas disponible, installer Anaconda.
9. Lancer jupyter (en exécutant jupyter notebook dans l'Anaconda prompt et attendre que le navigateur Web affiche la page de jupyter. Créer un nouveau notebook ou ouvrir un notebook précédemment crée.
10. Vérifier que mysql.connector soit installé en exécutant import mysql.connector as mysql; si ça déclenche une erreur, sortir de jupyter: fermer la page dans le navigateur Web, retourner à l'Anaconda prompt, taper CTRL+C deux fois et après exécuter conda install -c anaconda mysql-connector-python et répondre affirmativement à la requête de confirmation. Après ça, relancer jupyter notebook et ouvrir un notebook.
11. Se connecter à la base de données superheroes utilisant mysql.connector et l'usager superheroadmin avec le mot de passe précédemment noté.
12. Afficher toutes les lignes de la table heroes. Vérifier que les résultats sont les mêmes que l'on obtient en utilisant directement mySQL. Notez que si la BD vient d'être créée, la table sera vide. Dans ce cas on peut utiliser mysql pour inserer directement une ou plusieurs lignes dans la table et après effectuer une lecture avec mysql.connector.
13. Verifier dans mysql s'il y a des lignes dans la table heroes qui font référence à Wonder Woman, Wolverine, Spider-Man ou Professor X, et les éliminer avec une requête SQL DELETE au cas où elle existent.
14. Écrire une query parametrisée qui permet d'ajouter une ligne à la table heroes, et ajouter la ligne suivante (ou une équivalente si elle existe déjà dans la BD):

    1, 'Wonder Woman', 'Diana Prince', 'Themyscira', 'DC Comics', 183.13, 74.74, 'F', 1941, 'Blue', 'Black', 100, 'high'

15. S'assurer de lancer une commit sur la base de données, et après vérifier que la ligne ajoutée ait étée ajoutée à la base, soit en la lisant avec mysql.connector, soit en effectuant une SELECT dans mysql.
16. Utiliser la query parametrisée pour ajouter avec une seule instruction les lignes suivantes à la table heroes:

    3, 'Wolverine', 'Logan', 'Alberta, Canada', 'Marvel Comics', 160.70, 135.21, 'M', None, 'Blue', 'Black', 35, 'good'
    4, 'Spider-Man', 'Peter Parker', 'New York, New York', 'Marvel Comics', 178.28, 74.25, 'M', None, 'Hazel', 'Brown', 55, 'high'
    5, 'Professor X', 'Charles Francis Xavier', 'New York, New York', 'Marvel Comics', 183.74, 86.89, 'M', 1963, 'Blue', 'No Hair', 10, 'high'

17. Verifiez dans mysql ce qui contient la table heroes avant et après avoir effectué une commit.
18. Exécuter une query de selection qui extrait un sous-ensemble d'attributes de la table heroes.
19. Exécuter une query de selection qui contient une clause WHERE.
20. Télécharger le fichier CSV qui contient la description d'un ensemble de superhéros. Lire ce fichier et créer une liste avec ses contenus, prenant soin de ne pas considérer la première ligne (car elle contient les noms des colonnes).
21. Écrire une fonction exists_in_db qui vérifie si dans la table heroes il y a une ligne dont l'attribut name a par valeur l'argument passé à cette fonction, en retournant une valeur booléenne selon le cas.
22. Écrire une fonction next_id qui retourne un ID pour une nouvelle ligne de la table heroes (il suffit de déterminer la valeur de l'ID le plus haut et de lui ajouter 1).
23. Écrire des fonctions qui transforment les valeurs obtenues du fichier CSV en expressions du type correcte pour les attributs de la table heroes (il faudra avoir une fonction pour le type entier, une pour le type réel et une pour les chaînes, si possible aussi tenant compte d'une longeur maximale).
24. Utiliser les fonctions créées aux points précédents pour ajouter à la table heroes tous les données lits du fichier CSV, au cas où ils ne soient déjà contenus dans la table.
25. La procédure décrite au point précédent va déclencher une erreur, car en python les chaînes sont encodées en utilisant un format différent de celui de mySQL. Il faudra donc modifier la fonction qui traite les chaînes afin de les convertir en ASCII: ça peut être fait sur la chaîne s en invocant s.encode('ascii', 'ignore').
26. Vérifier que sqlalchemy soit installé, et s'il le faut l'installer en exécutant conda install -c anaconda sqlalchemy dans Anaconda prompt. Se connecter avec create_engine à la BD et créer un DataFrame de pandas qui contient toutes les lignes de la table heroes, en utilisant la méthode read_sql.
27. Se connecter avec sqlalchemy à la BD Superheroes en utilisant l'usager superheroadmin.
28. Créer une classe pour les héros de la table Heroes.
29. Effectuer les opérations suivantes sur la table Heroes avec sqlalchemy:
    1. lire le contenu de la table;
    2. limiter le résultat de la requête précédente;
    3. effectuer l'équivalent des requêtes SELECT avec une clause WHERE en utilisant aussi des conditions qui contiennent des AND et des OR;
    4. trier les résultat d'une SELECT;
    5. modifier une ligne;
    6. ajouter une ligne;
    7. détruire une ligne;
    8. chercher tous les héros qui n'ont pas une identité et modifier celle-ci en la rendant égale au nom;
    9. chercher tous les héros dont le nom est composé de plusieurs mots qui ont tous la même lettre initiale;
    10. chercher tous les héros dont l'identité est composée de plusieurs mots qui ont tous la même lettre initiale;
    11. chercher tous les héros dont le nom et l'identité sont composés de plusieurs mots qui ont tous la même lettre initiale.