Différences

Ci-dessous, les différences entre deux révisions de la page.

--- sian:2022:tpm1sql [2022/10/21 11:40] – [En python] fabien.rico
+++ sian:2022:tpm1sql [2022/11/24 22:17] (Version actuelle) – [En python] fabien.rico
@@ Ligne 1: / Ligne 1: @@
 ====== TP introduction SQL ======
-Vous pouvez utiliser le serveur [[https://bdd.imst.os.univ-lyon1.fr]] avec les login et mot de passe fourni par votre enseignant.
+Vous pouvez utiliser le serveur [[http://bdd.imst.os.univ-lyon1.fr]] avec les login et mot de passe fourni par votre enseignant.
@@ Ligne 14: / Ligne 14: @@
+<hidden>
+<code>
+# Listez le titre de toutes les catégories
+SELECT categories.title FROM categories WHERE 1;
+# Listez les catégories qui sont des sujets ? Combien y en a-t-il ?
+SELECT categories.title AS titre FROM categories WHERE genre='subjects';
+# Listez les sujets qui contiennent le mot science (On ne tiens plus compte de la casse).
+SELECT categories.title AS titre FROM categories WHERE genre='subjects' AND title LIKE '%science%';
+</code>
+</hidden>
 ===== Requêtes avec jointure =====
@@ Ligne 21: / Ligne 31: @@
   * Trouvez les cours qui ont lieu après octobre 2022
   * Trouvez les cours qui ont lieu après octobre 2022 et parlent de données ou de mathématiques
+<hidden>
+<code>
+###############################################
+# Trouvez la date de début de tous les cours.
+###############################################
+SELECT courses.title, courses_runs.start
+  FROM courses, courses_runs
+ WHERE courses.id=courses_runs.id_course;
+# meme chose avec un join
+SELECT courses.title, courses_runs.start
+  FROM courses JOIN courses_runs ON courses.id=courses_runs.id_course
+ WHERE 1;
+########################################################
+# Trouvez les cours qui ont lieu après octobre 2022
+########################################################
+########################################################
+# Trouvez les cours qui ont lieu après octobre 2022 et parlent de données
+########################################################
+</code>
+</hidden>
 ===== Requête plus complexes =====
   * Trouvez tous les mots clefs utilisés par chacun des cours de science en n'affichant que le mot clef et le titre du cours
+  * On cherche à obtenir une liste avec chaque mots clef utilisé par toutes les organisations avec l'affichage uniquement du nom de l'organisation et de la valeur du mot clef. Faites la requête
+Pour vous aider:
-On cherche à obtenir une liste avec chaque mots clef utilisé par toutes les organisations avec l'affichage uniquement du nom de l'organisation et de la valeur du mot clef.
+  - Un mot clef est une catégorie de genre ''subject''.
-  * Faites la requête
-Rappel:
-  - Un mot clef est une catégorie de genre ''subject''
   - Les catégories sont liées aux cours qui sont liées au organisations.
+  - On peut considérer qu'un cours est un cours de science si son titre ou son introduction contiennent le mot ''science''.
+<hidden>
+<code>
+##################################################
+#   * Trouvez tous les mots clefs utilisés par chacun des cours de science en #   n'affichant que le mot clef et le titre du cours
+##################################################
+SELECT categories.title AS motClef, courses.title AS titrecours
+  FROM courses JOIN courses_categories
+                    ON courses_categories.id_course=courses.id
+               JOIN categories
+                    ON courses_categories.id_categorie=categories.id
+WHERE courses.title LIKE '%science%'
+   OR courses.introduction LIKE '%science%';
+</code>
+</hidden>
+  * Faites une requète pour voirs les cours d'une école des mines (école avec mines dans le nom)
+<hidden>
+<code>
+SELECT courses.title AS NomCours, organizations.title AS NomOrga FROM courses
+	JOIN courses_organizations ON courses.id=courses_organizations.id_course
+	JOIN organizations ON courses_organizations.id_organization=organizations.id
+	WHERE organizations.title REGEXP ".*mines.*";
+</code>
+</hidden>
 ===== En python =====
-Faire un programme python qui exécute la requête précédente et affiche un tableau:
+Pour python, vous pouvez utiliser le notebook https://jupyter.imst.os.univ-lyon1.fr
+  * Faire un programme python qui affiche pour chaque mot clef le nombre de fois qu'il est utilisé dans un cours.
+<hidden>
+<code>
+#!/usr/bin/env python
+# coding: utf-8
+# ## Connexion à la base et curseur
+#
+# In[1]:
+import mysql.connector
+import json
+conn = mysql.connector.connect(
+    host="bdd.imst.os.univ-lyon1.fr",
+    user="etudiant",
+    password="sqlavance",
+    database="funmooc")
+cursor = conn.cursor()
+# ## Éxécution de la requète
+# In[2]:
+cursor.execute("""
+SELECT categories.title AS motClef, courses.title AS titrecours
+  FROM courses JOIN courses_categories
+                    ON courses_categories.id_course=courses.id
+               JOIN categories
+                    ON courses_categories.id_categorie=categories.id
+  WHERE categories.genre="subjects"
+  ORDER BY courses.id
+""")
+# ## Comptage des mots clef
+#
+# Pour chaque mot clef trouvé, j'ajoute 1 dans le tableau qui contient le nombre de fois qu'on l'a déjà vu.
+# Si le mot n'a jamais été vu, j'utilise le fait qu'il est possible d'avoir une valeur par défaut dans un dictionnaire via `get`
+#
+# Au passage j'affiche le mot et le nombre de fois qu'on l'a vu.
+#
+# In[3]:
+tab = {}
+while True:
+    res = cursor.fetchone()
+    if res is None:
+        break
+    # Le mot clef est la première colonne demandée dans le SELECT alors il sera
+    # dans la première case du résultat
+    mot = res[0]
+    # On récupère le nmbre de fois où il a été vu ou 0 s'il n'a jamais été vu
+    nbdeja = tab.get(mot, 0)
+    # on affiche
+    print(f"On trouve le mot clef '{mot}' déjà vu {nbdeja} fois")
+    # on ajoute un et on change la valeur du tableau
+    tab[mot] = nbdeja+1
+# Affichage
+print(tab)
+# Affichage plus joli
+print(json.dumps(tab, indent=2))
+# ## on fait un nuage de mot
+#
+# Ce n'est pas demander mais à partir d'un dictionnaire qui compte le nombre d'ocurence des mots on peut facilement faire un nuage de mot avec la librairie `wordcloud`
+# In[5]:
- ^ Nom organisation ^ liste mot clef ^
+# génération du nuage
-| INSA | ... |
+import wordcloud
-| UCBL | ... |
+import matplotlib.pyplot as plt
+wordcloud = wordcloud.WordCloud().generate_from_frequencies(tab)
+# affichage
+plt.imshow(wordcloud, interpolation='bilinear')
+plt.axis("off")
+plt.show()
+</code>
+</hidden>
+  * Faire un programme python qui obtient la liste des mots clef de chaque cours de chaque organisation et affiche le tableau suivant:
-^          Tableau avec alignement         ^^^
+^ Nom organisation ^ liste mot clef ^
-|        droite|    centré    |gauche        |
+| MINES ParisTech                                      | Economie et gestion, Environnement et dévelop... |
-|gauche        |        droite|    centré    |
+| Inria | Informatique et programmation, Numérique et technologie, Droit, Numérique et technologie, ... |
-| xxxxxxxxxxxx | xxxxxxxxxxxx | xxxxxxxxxxxx |
+| ... | ... |