142 - Langage SQL (FR, 2020) .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -------------------------------------------------------- Module 1 \n",
    "# ---------------------------- Introduction aux bases de données relationnelles\n",
    "\n",
    "# -------------------- 1. Système de gestion de base de données relationnelles\n",
    "\n",
    "# Un système de gestion de base de données (SGBD) est un logiciel permettant à un \n",
    "# utilisateurs d'accéder à une base de données\n",
    "\n",
    "# -------------------- 2. Schémas \n",
    "\n",
    "# -------------------- 3. Clés \n",
    "\n",
    "# Exécutez cette cellule pour importer les fonctions que l'on va utiliser\n",
    "from IPython.display import Markdown, display\n",
    "\n",
    "# --------------------\n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "display(Markdown(\"Utilisateurs ( <u>UtilisateurId</u> , Prenom, Nom , Pays de residence )\"))\n",
    "\n",
    "# --------------------\n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "display(Markdown(\"Modules ( <u>ModuleId</u> , Nom , Difficulte )\"))\n",
    "display(Markdown(\"Utilisateurs (<u>UtilisateurId</u>, Nom, Prénom, #MetierId )\")) \n",
    "display(Markdown(\"Metiers( <u>MetierId</u>, Nom )\")) \n",
    "display(Markdown(\"L'attribut *MetierId* de la table **Utilisateurs** fait référence à la clé primaire de la table **Metiers**. \")) \n",
    "\n",
    "# --------------------\n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "\n",
    "display(Markdown(\"Candidature(<u>#IdEtudiant</u>, Session, <u>#IdUniversite</u>, Adresse, Nom Parcours)\"))\n",
    "\n",
    "display(Markdown(\"* Un étudiant peut effectuer plusieurs candidatures dans une même université à des parcours différents\"))\n",
    "display(Markdown(\"* L’attribut **IdEtudiant** fait référence à la clé primaire de la table **Etudiants**\"))\n",
    "display(Markdown(\"* L’attribut **IdUniversite** fait référence à la clé primaire de la table **Universite**\"))\n",
    "display(Markdown(\"Etudiants(Nom, Prenom, Adresse, <u>IdEtudiant</u>)\"))\n",
    "display(Markdown(\"Universite (<u>IdUnversite</u>, NomUni, AdresseUniversite, Rang )\"))\n",
    "\n",
    "# -------------------- 4. Schéma relationnel \n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "display(Markdown('Relation entre Universite et Etudiants: One to many '))\n",
    "display(Markdown('En effet, un étudiant  est affecte a une seule université  et une université  est elle associé à plusieurs étudiants .'))\n",
    "display(Markdown('Relation entre Etudiant et Candidature : many to many '))\n",
    "display(Markdown('En effet, un étudiant  peut soumettre sa candidature a plusieurs formations universitaire et une Candidature est unique elle est associé à un seul étudiant.'))\n",
    "\n",
    "# --------------------\n",
    "\n",
    "print(\"C'est la contrainte d'intégrité référentielle. En effet l'utilisateur 4 n'existe pas dans la table Utilisateurs.\")\n",
    "\n",
    "# -------------------- Conclusion\n",
    "# \n",
    "# Ce qu'il faut retenir \n",
    "#Les clés primaires identifient de manière une unique un tuple d'une table.\n",
    "#Les clés étrangères sont les clés primaires d'autres tables.\n",
    "#Les clés peuvent être composites.\n",
    "#Les différents schémas relationnels\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -------------------------------------------------------- Module 2 \n",
    "# -------------------------------------------------- Introduction à SQL Alchemy\n",
    "\n",
    "\n",
    "\n",
    "# -------------------- 1. SQL Alchemy\n",
    "\n",
    "# Exécutez cette cellule pour importer les fonctions que l'on va utiliser\n",
    "import sqlite3, sqlalchemy\n",
    "from sqlalchemy import Table, Column, Integer, String, ForeignKey, MetaData, create_engine, text, inspect\n",
    "from IPython.display import Markdown, display\n",
    "\n",
    "# SQL Alchemy est une librairie Python très utilisée pour se connecter à un \n",
    "# système de gestion de bases de données relationnelles. \n",
    "# Il permet d'interpréter du code SQL\n",
    "\n",
    "# --------------------\n",
    "\n",
    "# En ajoutant l'argument echo = True, cela nous permet d'afficher la traduction\n",
    "# en langage SQL des commandes du module\n",
    "engine = create_engine('sqlite:///college.db', echo=True)\n",
    "\n",
    "# --------------------\n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "meta = MetaData()\n",
    "\n",
    "# -------------------- Création de table\n",
    "\n",
    "students = Table(\n",
    "   'students', meta, \n",
    "   Column('id', Integer, primary_key=True), \n",
    "   Column('name', String), \n",
    "   Column('lastname', String),\n",
    ")\n",
    "meta.create_all(engine)\n",
    "\n",
    "# --------------------\n",
    "\n",
    "parcours = Table(\n",
    "   'parcours', meta, \n",
    "   Column('id_parcours', Integer, primary_key=True), \n",
    "   Column('name', String), \n",
    "   Column('student_id', Integer, ForeignKey(\"students.id\")),\n",
    ")\n",
    "meta.create_all(engine)\n",
    "\n",
    "# -------------------- 2. Transactions simples\n",
    "\n",
    "# Une transaction ACID est une transaction qui respecte les points suivants:\n",
    "\n",
    "# Atomicité : une transaction doit être effectuée en entier ou abandonnée, c'est à dire que si une opération est refusée, toutes les opérations de la transaction ne seront pas validées.\n",
    "# Cohérente : l'état initial et final doivent être cohérents, c'est à dire respecter les contraintes d'intégrité.\n",
    "# Isolation : Une transaction doit s'effectuer indépendamment des transactions qui s'exécutent en même temps, c'est à dire que les états intermédiaires d'une transaction sont cachés aux autres.\n",
    "# Durabilité : lorsqu'une transaction est validée, ses effets ne peuvent être perdus.\n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "\n",
    "values = [\n",
    "    (1, \"Jean\", \"Dubois\"),\n",
    "    (2, \"Florent\", \"Desbiolles\"),\n",
    "    ]\n",
    "\n",
    "# --------------------\n",
    "\n",
    "# on crée la connection\n",
    "with engine.connect() as connection:\n",
    "    # début de la transaction\n",
    "    with connection.begin() as transaction:\n",
    "        # on tente d'éxécuter une transaction\n",
    "        try:\n",
    "            # On indique le format d'un tuple de cette table\n",
    "            markers = ','.join('?' * len(values[0])) \n",
    "            \n",
    "            # On utilise le langage SQL en format texte où markers est le format d'un tuple\n",
    "            ins = 'INSERT INTO {tablename} VALUES ({markers})'\n",
    "            \n",
    "            # On précise ce format particulier grâce à la fonction membre format\n",
    "            ins = ins.format(tablename=students.name, markers=markers)\n",
    "           \n",
    "            # Enfin on peut utiliser les tuples créés en éxécutant la commande SQL\n",
    "            connection.execute(ins, values)\n",
    "        # si la transaction échoue\n",
    "        except:\n",
    "            transaction.rollback()\n",
    "            raise\n",
    "        # si la transaction réussit\n",
    "        else:\n",
    "            transaction.commit()\n",
    "\n",
    "# --------------------\n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "\n",
    "values_parcours = [(1, \"Data engineering\", 1),(2, \"Data Science\", 2) ]\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    with connection.begin() as transaction:\n",
    "        try:\n",
    "            markers = ','.join('?' * len(values_parcours[0]))\n",
    "            ins = 'INSERT INTO {tablename} VALUES ({markers})'\n",
    "            \n",
    "            ins = ins.format(tablename=parcours.name, markers=markers)\n",
    "            connection.execute(ins, values_parcours)\n",
    "        except:\n",
    "            transaction.rollback()\n",
    "            raise\n",
    "        else:\n",
    "            transaction.commit()\n",
    "            \n",
    "# --------------------\n",
    "\n",
    "# Exécuter cette cellule\n",
    "with engine.connect() as connection:\n",
    "    results = connection.execute(\"SELECT * FROM students;\")\n",
    "    print(results.fetchall())\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    results = connection.execute(\"SELECT * FROM parcours;\")\n",
    "    print(results.fetchall())\n",
    "    \n",
    "# --------------------\n",
    "\n",
    "# Exécuter la cellule pour afficher la réponse\n",
    "\n",
    "sql = text('DROP TABLE IF EXISTS students;')\n",
    "result = engine.execute(sql)\n",
    "\n",
    "###Vérification que la table students n'existe plus en réutilisant le code d'insertion de tuple\n",
    "\n",
    "values = [\n",
    "    (1,\"Jean\", \"Dubois\"),\n",
    "    (2,\"Florent\", \"Desbiolles\"),\n",
    "    ]\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    with connection.begin() as transaction:\n",
    "        try:\n",
    "            markers = ','.join('?' * len(values[0])) \n",
    "            ins = 'INSERT INTO students VALUES ({markers})'\n",
    "            ins = ins.format(tablename=students.name, markers=markers)\n",
    "            connection.execute(ins, values)\n",
    "        except:\n",
    "            transaction.rollback()\n",
    "            raise\n",
    "        else:\n",
    "            transaction.commit()\n",
    "\n",
    "# -------------------- Conclusion\n",
    "# la structure de connection à une base de données avec SQL Alchemy\n",
    "# la notion de transaction ACID\n",
    "# la syntaxe pour créer ou supprimer une table en SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -------------------------------------------------------- Module 3 \n",
    "# ---------------------------- Les bases du langage SQL\n",
    "\n",
    "\n",
    "# -------------------- Préliminaires\n",
    "import sqlite3, sqlalchemy\n",
    "from sqlalchemy import inspect, create_engine, text\n",
    "\n",
    "engine = create_engine(\"sqlite:///chinook.db\")\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# -------------------- 1. Utilisation de l'inspecteur\n",
    "#Une classe très pratique pour afficher des informations \n",
    "inspector = inspect(engine)\n",
    "\n",
    "# --------------------\n",
    "#afficher les nom des tables\n",
    "inspector.get_table_names()\n",
    "\n",
    "# --------------------\n",
    "# get_columns de la classe Inspector permet d'afficher \n",
    "# les caractéristiques des différents attributs\n",
    "inspector.get_columns(table_name='albums')\n",
    "\n",
    "# --------------------\n",
    "# La méthode get_foreign_keys de l'objet inspector permet \n",
    "# d'afficher les clés étrangères \n",
    "inspector.get_foreign_keys(table_name='albums')\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# -------------------- 2. Requêtes simples\n",
    "\n",
    "SELECT colonne1, colonne2 FROM table1;     #import les col 1 et 2\n",
    "SELECT * FROM table1;     #import toutes les col\n",
    "SELECT DISTINCT * FROM table1;     #import toutes les col sans les doublons\n",
    "SELECT * FROM table1 LIMIT 10;     #import les col (10lignes)\n",
    "\n",
    "# --------------------\n",
    "conn = engine.connect()\n",
    "\n",
    "result = conn.execute(\"SELECT Title FROM albums LIMIT 10;\")\n",
    "result.fetchall() \n",
    "#Cette commande renvoie tous les tuples de la table de l'objet ResultProxy\n",
    "\n",
    "\n",
    "\n",
    "# -------------------- 3. Utilisation de WHERE\n",
    "stmt = text( \"SELECT * FROM albums WHERE albums.ArtistId = 1\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text( \"SELECT * FROM invoices WHERE Total > 20;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text( \"SELECT * FROM invoices WHERE Total > 20 AND Total < 22;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text( \"SELECT InvoiceId, BillingCountry, Total FROM invoices WHERE Total BETWEEN 10 AND 20;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# -------------------- Les expressions régulières\n",
    " stmt = text ( \"SELECT * FROM tracks WHERE Name LIKE '%Wild%';\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text ( \"SELECT tracks.Name, tracks.Composer FROM tracks WHERE Composer LIKE '%Freddie%' AND Composer LIKE '%Mercury%'\" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text ( \"SELECT tracks.Name, tracks.Composer FROM tracks\"\n",
    "             \" WHERE Name LIKE 'P%' AND ((Composer LIKE '%/%' AND Composer NOT LIKE '__/__')OR Composer LIKE '%-%') \")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# -------------------- ORDER BY\n",
    "# On peut simplement ajouter les noms des attributs à extraire les uns à la suite des autres séparés par une virgule\n",
    "stmt = text ( \"SELECT InvoiceId, CustomerId , Total FROM invoices ORDER BY Total DESC LIMIT 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# -------------------- 4. Agrégation de requête\n",
    "# on a aussi choisi de classer les résultats par montant total décroissant\n",
    "stmt = text ( \"SELECT invoices.CustomerId , SUM(Total) AS tot FROM invoices GROUP BY CustomerId ORDER BY tot DESC;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text ( \"SELECT BillingCountry, AVG(Total) AS PaysMoy FROM invoices GROUP BY BillingCountry \" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text ( \"SELECT BillingCountry, MAX(Total) AS tot FROM invoices GROUP BY BillingCountry ORDER BY tot DESC \" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text ( \"SELECT BillingCountry, COUNT(*) AS p FROM invoices GROUP BY BillingCountry ORDER BY p\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "# Chaque paire (PAYS, VILLE) existant dans la base est représentée.\n",
    "stmt = text ( \"SELECT BillingCountry,BillingCity, COUNT(InvoiceId) FROM invoices GROUP BY BillingCountry,BillingCity\" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------HAVING\n",
    "stmt = text ( \"SELECT CustomerId, COUNT(CustomerId) AS tot FROM invoices GROUP BY CustomerId HAVING tot > 5 ORDER BY tot\" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text ( \"SELECT CustomerId, SUM(Total) AS tot FROM invoices GROUP BY CustomerId HAVING tot > 20;\" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# --------------------\n",
    "stmt = text ( \"SELECT CustomerId, SUM(Total) AS tot FROM invoices WHERE Total > 20 GROUP BY CustomerId \" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# -------------------- Conclusion\n",
    "#La commande SELECT retourne des enregistrements dans un tableau de résultat.\n",
    "#La commande WHERE dans une requête SQL permet d’extraire les lignes d’une base de données qui respectent un prédicat.\n",
    "#La commande ORDER BY permet de trier les lignes dans un résultat d’une requête SQL.\n",
    "#La commande GROUP BY est utilisée en SQL pour grouper plusieurs résultats et utiliser une fonction de totaux sur un groupe de résultat.\n",
    "#La commandeHAVING permet de filtrer en utilisant des fonctions telles que SUM(), COUNT(), AVG(), MIN() ou MAX() utilisées par la commande GROUP BY.\n",
    "#L'opérateur LIKE est utilisé dans la clause WHERE des requêtes SQL. A l'aide de Wildcards, on peut rechercher des agencements de mots ou lettres dans les chaînes de caractères d'attributs.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -------------------------------------------------------- Module 4 \n",
    "# -------------------------------------------------------- Langage SQL avancé\n",
    "\n",
    "# Exécutez cette cellule pour charger les modules nécessaires\n",
    "import sqlite3, sqlalchemy\n",
    "from sqlalchemy import inspect, create_engine, text\n",
    "\n",
    "# On utilise //// 4 slash pour accéder à un fichier par son chemin absolu\n",
    "engine = create_engine(\"sqlite:///chinook.db\")\n",
    "\n",
    "# Création de connexion avec l'engine \n",
    "conn = engine.connect()\n",
    "\n",
    "\n",
    "# -------------------- 1. Requêtes imbriquées\n",
    "\n",
    "stmt = text(\"SELECT * FROM (SELECT CustomerId, SUM(Total) AS total FROM invoices GROUP BY CustomerId ORDER BY Total LIMIT 10) table_temp ORDER BY Total\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "\n",
    "\n",
    "# -------------------- 2. Jointures\n",
    "\n",
    "SELECT * FROM table1 CROSS JOIN table2;\n",
    "stmt = text(\"SELECT * FROM tracks  CROSS JOIN genres;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "# -------------------- Jointure interne ou INNER JOIN\n",
    "\n",
    "#Ce type de jointure va permettre de concaténer les tuples \n",
    "#des 2 tables deux à deux si une condition est satisfaite. \n",
    "\n",
    "stmt = text ( \"SELECT * FROM artists INNER JOIN albums ON artists.ArtistId = albums.ArtistId LIMIT 10;\" )\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "\n",
    "\n",
    "# -------------------- Jointure à droite ou RIGHT JOIN\n",
    "stmt =text (\"SELECT * FROM (SELECT CustomerId, Total  FROM 'invoices' WHERE InvoiceId BETWEEN 21 AND 40) AS A \"\n",
    "            \"LEFT JOIN (SELECT CustomerId, Total FROM 'invoices' \"\n",
    "            \"WHERE InvoiceId BETWEEN 1 AND 20) AS B ON A.CustomerId=B.CustomerId\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n",
    "\n",
    "\n",
    "# -------------------- 3. Modifier une table\n",
    "Modifier une ligne avec SET et UPDATE\n",
    "\n",
    "On peut utiliser UPDATE et SET pour modifier une ou plusieurs ligne(s). Par exemple, si on veut modifier la colonne colonne1 pour qu'elle prenne la valeur nouvelle_valeur lorsque la colonne colonne2 vaut valeur, on peut écrire:\n",
    "\n",
    "UPDATE table1\n",
    "SET colonne1 = nouvelle_valeur WHERE colonne2 = valeur;\n",
    "\n",
    "\n",
    "\n",
    "Supprimer des lignes avec DELETE\n",
    "Sur le même principe, on peut supprimer les lignes pour lesquelles la colonne colonne2 vaut valeur en faisant:\n",
    "\n",
    "DELETE FROM table WHERE colonne2 = valeur;\n",
    "\n",
    "\n",
    "# --------------------Conclusion\n",
    "\n",
    "# les différents types de jointure\n",
    "# comment modifier des valeurs\n",
    "# comment supprimer des lignes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "L'examen est composé de 2 exercices. Le premier consiste à créer une base de données et de manipuler des informations sur cette base. Le dernier traite principalement des deux derniers cours. Il consiste à ce connecter à une base de données de films et de récupérer des informations de cette base. Les exercices sont indépendants l'un de l'autre. N'hésitez pas à contacter l'équipe DataScientest si vous rencontrez des problèmes sur help@datascientest.com\n",
    "\n",
    "(a) Importer les modules et fonctions suivantes : sqlaclhemy, sqlite3, inspect, create_engine, text,Table, Column, Integer, String, ForeignKey, MetaData, Float."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3, sqlalchemy\n",
    "from sqlalchemy import inspect, create_engine, text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Table, Column, Integer, String, ForeignKey, MetaData, create_engine, text, inspect"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Exercice 1¶\n",
    "(a) Créer une base de données nommée Distributeur.db et créer une table Boissons(BoissonId, Nom, Contenance, Prix) en choisissant de manière adéquate la clé primaire. Il faudra justifier votre réponse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///Distributeur.db\", echo=True)\n",
    "\n",
    "# -------------------- \n",
    "\n",
    "meta = MetaData()\n",
    "\n",
    "Boisson = Table(\n",
    "   'Boisson', meta, \n",
    "    Column('boissonID', Integer, primary_key=True),  #cle car unique \n",
    "   Column('Nom', String), \n",
    "   Column('Contenance', Integer), \n",
    "   Column('Prix', String),\n",
    ")\n",
    "meta.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Examen SQL\n",
    "import sqlite3, sqlalchemy\n",
    "from sqlalchemy import Table, Column, Integer, String, ForeignKey, MetaData, create_engine, text, inspect\n",
    "from IPython.display import Markdown, display\n",
    "\n",
    "engine = create_engine(\"sqlite:///Distributeur.db\", echo=True)\n",
    "meta = MetaData()\n",
    "boissons = Table(\n",
    "   'Boissons', meta, \n",
    "   Column('BoissonId', Integer, primary_key=True), \n",
    "   Column('Nom', String), \n",
    "   Column('Contenance', Integer),\n",
    "   Column('prix', Integer),    \n",
    ")\n",
    "meta.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(b) Ajouter les tuples (1, Coca, 33, 0.99) ; (2, Perrier, 100, 2) ; (3, Perrier, 33, 1), (4, Vittel, 150, 1.50) , (5, Badoit, 50, 2). Pour cela, il faudra gérer les exceptions qui peuvent être levées."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = [\n",
    "    (1, \"Coca\", 33,0.99),\n",
    "    (2, \"Perrier\", 100,2),\n",
    "    (3, \"Perrier\", 33,1),\n",
    "    (4, \"Vittel\", 150,1.50),\n",
    "    (5, \"Badoit\", 50,2),\n",
    "    ]\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    with connection.begin() as transaction:\n",
    "        try:\n",
    "            markers = ','.join('?' * len(values[0])) \n",
    "            ins = 'INSERT INTO {tablename} VALUES ({markers})'\n",
    "            ins = ins.format(tablename=\"Boissons\", markers=markers)\n",
    "            connection.execute(ins, values)\n",
    "        except:\n",
    "            transaction.rollback()\n",
    "            raise\n",
    "        else:\n",
    "            transaction.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(c) Créer une table Facture(FactureId,BoissId,Heure). Il faudra faire référence à une clé primaire de la table Boissons."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "facture = Table(\n",
    "   'Facture', meta, \n",
    "   Column('FactureId', Integer, primary_key=True), \n",
    "   Column('BoissId', Integer, ForeignKey(\"Boissons.BoissonId\")), \n",
    "   Column('Heure', Integer),\n",
    ")\n",
    "meta.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(d) Ajouter les tuples (1,1,7), (2,1,8) , (3,4,12) dans la table Facture."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = [(1,1,7), \n",
    "          (2,1,8) , \n",
    "          (3,4,12)]\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    with connection.begin() as transaction:\n",
    "        try:\n",
    "            markers = ','.join('?' * len(values[0])) \n",
    "            ins = 'INSERT INTO {tablename} VALUES ({markers})'\n",
    "            ins = ins.format(tablename=Facture, markers=markers)\n",
    "            connection.execute(ins, values)\n",
    "        except:\n",
    "            transaction.rollback()\n",
    "            raise\n",
    "        else:\n",
    "            transaction.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(e) Peut-on supprimer la table Boissons ? Justifier votre réponse.\n",
    "\n",
    "(f) Peut-on ajouter le tuple dans la table Boissons (1,Sprite,50,1) ? Justifier votre réponse.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#non on peut pas supprimer la table boissons on violerait la règle de cohérence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#non car on violerait la règle de la clé car la clé primaire existe déjà "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(g) Donner en langage SQL, la requête permettant de savoir quelles boissons du distributeur n'ont pas été vendu depuis sa mise en place, puis l'exécuter. (On peut utiliser NOT IN pour vérifier qu'une colonne n'appartient pas à une table.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = engine.connect()\n",
    "\n",
    "stmt = text(\"select * from Boissons where BoissonId NOT IN (select BoissId from Facture)\")\n",
    "\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(h) Donner en langage SQL, la requête permettant de savoir la proportion des différentes boissons vendues dans l'état actuel du distributeur, puis l'exécuter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"select BoissId, count(*) from Facture group by BoissId\")\n",
    "\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "i) Supprimer les deux tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text('DROP TABLE IF EXISTS Facutre;')\n",
    "stmt1 = text('DROP TABLE IF EXISTS Boissons;')\n",
    "result = conn.execute(stmt)\n",
    "result = conn.execute(stmt1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Exercice 2¶\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La base de données newIMDB.db est déjà chargée dans votre espace de travail. Cette base de données regroupent les informations de tous les titres référencés sur le site IMDB.\n",
    "\n",
    "   La base de données est composée des caractéristiques de films, séries TV ainsi que d'autres type de production.\n",
    "(a) Connecter vous à la base de données newIMDB.db."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///newIMDB.db\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(b) Effectuer une première analyse de la base de données en déterminant les différentes tables, leurs attributs, les clés primaires associées aux tables ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspector = inspect(engine)\n",
    "inspector.get_table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inspector.get_columns(table_name='crew')\n",
    "inspector.get_foreign_keys(table_name='crew')\n",
    "inspector.get_columns(table_name='episodes')\n",
    "inspector.get_foreign_keys(table_name='episodes')\n",
    "inspector.get_columns(table_name='people')\n",
    "inspector.get_foreign_keys(table_name='people')\n",
    "inspector.get_columns(table_name='titles')\n",
    "inspector.get_foreign_keys(table_name='titles')\n",
    "inspector.get_columns(table_name='ratings')\n",
    "inspector.get_foreign_keys(table_name='ratings')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#les diffrents attributs par table\n",
    "print(\"CREW\",inspector.get_columns(table_name='crew'), \"\\n\")\n",
    "print(\"EPISODES\",inspector.get_columns(table_name='episodes'), \"\\n\")\n",
    "print(\"PEOPLE\",inspector.get_columns(table_name='people'), \"\\n\")\n",
    "print(\"RATINGS\",inspector.get_columns(table_name='ratings'), \"\\n\")\n",
    "print(\"TITLES\",inspector.get_columns(table_name='titles'), \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREW [{'name': 'title_id', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'person_id', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'category', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'job', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'characters', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}] \n",
    "\n",
    "EPISODES [{'name': 'episode_title_id', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'show_title_id', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'season_number', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'eposide_number', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}] \n",
    "\n",
    "PEOPLE [{'name': 'person_id', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 1}, {'name': 'name', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'born', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'died', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}] \n",
    "\n",
    "RATINGS [{'name': 'title_id', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 1}, {'name': 'rating', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'votes', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}] \n",
    "\n",
    "TITLES [{'name': 'title_id', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 1}, {'name': 'type', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'primary_title', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'original_title', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'is_adult', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'premiered', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'ended', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'runtime_minutes', 'type': INTEGER(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}, {'name': 'genres', 'type': VARCHAR(), 'nullable': True, 'default': None, 'autoincrement': 'auto', 'primary_key': 0}] \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(c) Quels sont les titres les mieux notés dans la base de données ? On indiquera le nom, la note, et le nombre de votants. Pour que la sélection soit représentative, il faudra filtrer pour les titres ayant un nombre de votants > 5000. (On limitera les résultats aux 10 premières lignes.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = engine.connect()\n",
    "stmt = text(\"\"\"select primary_title, rating, votes \n",
    "                from ((SELECT * FROM ratings WHERE votes > 5000 ) as A\\\n",
    "                 inner join (SELECT * FROM titles) as B \n",
    "                 on A.title_id = B.title_id) \n",
    "                 order by rating desc \n",
    "                 limit 10;\"\"\")\n",
    "\n",
    "#stmt = text(\"select title_id, rating, votes from ratings where votes >5000 order by rating desc limit 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(d) Quelle est la note moyenne des titres par catégorie, classer ce résultat par ordre décroissant des notes moyennes ? (On limitera les résultats aux 10 premières lignes.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"select category, avg(rating) as moy from (SELECT * from crew as c inner join ratings as r on c.title_id = r.title_id) temp group by category  ORDER BY moy desc LIMIT 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"select genres, avg(rating) as moy from (SELECT * from titles as t inner join ratings as r on t.title_id = r.title_id) temp group by genres ORDER BY moy desc LIMIT 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(e) Quelles sont les notes moyennes des titres associés aux différents directeurs/metteurs en scène/réalisateurs ... ? (On limitera les résultats aux 10 premières lignes.)\n",
    "\n",
    "Le mot clé dans la base de données pour désigner ces groupes de personnes est : director."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"select category, avg(rating) as moy from (SELECT * from crew as c inner join ratings as r on c.title_id = r.title_id) temp group by category having category like '%director%' ORDER BY moy desc LIMIT 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insérez votre code ici\n",
    "#stmt = text(\"select distinct(category) from crew;\")\n",
    "stmt = text(\"select temp.title_id, temp.category, temp.rating from ((select c.title_id, c.category from crew as c where category like '%director%') as A\\\n",
    "             inner join (select * from ratings ) as B on A.title_id=B.title_id) as temp  limit 10;\")\n",
    "#stmt = text(\"select category,person_id, avg(rating) as moy from (SELECT * from crew as c inner join ratings as r on c.title_id = r.title_id) temp group by category having category like '%director%' ORDER BY moy desc LIMIT 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(f) Combien y a t-il de titres par type ? (Utiliser l'attribut titleType)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"select t.type, count(*) from titles as t group by t.type;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(g) Quels sont les titres sur lesquels Quentin Tarantino a travaillé ? On précisera le nom (primary) et la note du titre."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#les titres sur lesquels Quentin Tarantino a travaillé\n",
    "#stmt1= text(\"select person_id from people where name like '%Quentin%' and name like '%Tarantino%'\")\n",
    "#stmt2 = text(\"select title_id from crew where person_id in (select person_id from people where name like '%Quentin%' and name like '%Tarantino%'\")\")\n",
    "#code à tester à l'examen\n",
    "stmt3 = text (\"select primary_title, rating from (select title_id from crew where person_id in (select person_id from people where name like '%Quentin%' and name like '%Tarantino%')) as A\\\n",
    "             inner join ratings as r where A.title_id = r.title_id \")\n",
    "\n",
    "#stmt = text(\"select primary_title, rating as moy from (SELECT * from crew as c inner join ratings as r on c.title_id = r.title_id) temp group by category  ORDER BY moy desc LIMIT 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text (\"select primary_title, rating from ( ((select * from crew where person_id in \\\n",
    "              (select person_id from people where name like '%Quentin%' and name like '%Tarantino%')) as A)\\\n",
    "             inner join (select * from ratings) as B on A.title_id = B.title_id ) as C \\\n",
    "             inner join (select * from titles ) as D on C.title_id = D.title_id;\")\n",
    "\n",
    "#stmt = text(\"select primary_title, rating as moy from (SELECT * from crew as c inner join ratings as r on c.title_id = r.title_id) temp group by category  ORDER BY moy desc LIMIT 10;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(h) Quels sont les acteurs ou actrices ayant obtenus les meilleurs notes (>9) ? La variable category de la table crew détermine le métier de l'individu auquel le tuple est associé. (On limitera les résultats aux 10 premières lignes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"select C.title_id, C.person_id, C.category , C.rating from ((select title_id, person_id, category from crew where category='actor' or category='actress') as A\\\n",
    "              inner join (select * from ratings where rating>9) as B on A.title_id=B.title_id) as C\") \n",
    "\n",
    "#stmt=text(\"select name, rating, rating from (select title_id, person_id from crew where category='actor' or category=''actress)\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(i) Quelle est la note moyenne des contenus pour les actrices et les acteurs ? Regrouper ces deux informations dans un seul tuple?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"select category, avg (rating) from crew  as c inner join (select * from ratings  ) as r on c.title_id = r.title_id  group by category having category='actor' or category='actress' ;\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Question bonus\n",
    "(a) Quels sont les directeurs/metteurs en scène/réalisateurs (si ils existent) qui ont obtenu une note supérieur à 9 pour tous leurs titres ? (On limitera les résultats aux 10 premières lignes.)\n",
    "   Le mot clé dans la base de données pour désigner ces groupes de personnes est : director."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = text(\"\"\"select name, rating from (select category, person_id, rating from crew  as c inner join \n",
    "            (select * from ratings where rating > 9 ) as r on c.title_id = r.title_id  where  category='director' Limit 10) \n",
    "            as tab1 inner join (select * from people) limit 10\n",
    "            \"\"\")\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}