TP introduction SQL

# 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%'; 

###############################################
# 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 
########################################################

##################################################
#   * 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%';
   

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.*";

#!/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]:


# génération du nuage
import wordcloud
import matplotlib.pyplot as plt
wordcloud = wordcloud.WordCloud().generate_from_frequencies(tab)
# affichage
plt.imshow(wordcloud, interpolation='bilinear')
plt.axis("off")
plt.show()