Tnsi_brouillon_SQL5_baseDataGouv.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "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.2"
    },
    "colab": {
      "name": "Tnsi_brouillon_SQL5_baseDataGouv.ipynb",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/srenouf/Tnsi/blob/master/Tnsi_brouillon_SQL5_baseDataGouv.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tClw5vNW60Ac",
        "colab_type": "text"
      },
      "source": [
        "# Exploiter un vrai jeu de donnée\n",
        "\n",
        "> Cours NSI Terminale - Thème 2.\n",
        "- toc: true \n",
        "- badges: true\n",
        "- comments: false\n",
        "- categories: [python,  NSI,   Terminale,   Bases de données,   SQL,   TP]\n",
        "- image: /images/nsi2.png"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "markdown",
          "checksum": "61023d6c59488bf418bd447ff16df51f",
          "grade": false,
          "grade_id": "cell-6c8bf1357b64bcc8",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "uu3w3VGc60Ae",
        "colab_type": "text"
      },
      "source": [
        "# Mise en pratique sur une vraie base\n",
        "\n",
        "A partir de maintenant, vous avez les connaissances pour réaliser une application s'appuyant sur une base de données. Il  ne reste plus qu'à les appliquer dans le cadre d'un projet ou un mini projet. Un exemple de mini-projet possible dans le domaine du *BigData* consiste à récupérer des données officielles et à s'aider du langage SQL pour les exploiter.\n",
        "\n",
        "## Exemple de jeu de données\n",
        "\n",
        "Une source intéressante pour récupérer des données est **data.gouv.fr**. Voici un exemple de jeu de données : https://www.data.gouv.fr/fr/datasets/resultats-du-controle-sanitaire-de-leau-distribuee-commune-par-commune/#_\n",
        "\n",
        "La description de chacun des fichiers se trouve ici : https://static.data.gouv.fr/resources/resultats-du-controle-sanitaire-de-leau-distribuee-commune-par-commune/20190710-030936/20190708-eau-distrib-documentation-v4.pdf\n",
        "\n",
        "0. Téléchargez le fichier dis-* le plus récent. Décompressez le et analysez sa structure.\n",
        "0. Créez une nouvelle base de données et 3 tables à l'intérieur :\n",
        "    - Communes\n",
        "    - Prelevements\n",
        "    - Resultats\n",
        "0. Créez dans chacune des tables une clé primaire nommée Id*NomTable* ainsi que des attributs correspondants aux intitulés de colonne\n",
        "0. A l'aide de Python, lisez chacun des fichiers et peuplez les tables correspondantes.\n",
        "0. Créez des requêtes SQL afin de vous renseigner sur la qualité de l'eau dans les communes proches de chez vous."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "markdown",
          "checksum": "8299642f2d9ceaeb936be9afc946dc6c",
          "grade": false,
          "grade_id": "cell-b4df4d57805fe93a",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "kWbnFpCn60Ag",
        "colab_type": "text"
      },
      "source": [
        "### Création des tables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "326b4ae47dd9d1070aa90295fe171dd8",
          "grade": false,
          "grade_id": "cell-7ff39ecbb7f1ad9f",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "V_ec4YkA60Ah",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import sqlite3\n",
        "bdd = sqlite3.connect(\"dis_db\")\n",
        "curseur = bdd.cursor()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "5ad05ffd36b8c55852b8c15821d6725a",
          "grade": false,
          "grade_id": "cell-e42f1c943863101d",
          "locked": false,
          "schema_version": 3,
          "solution": true,
          "task": false
        },
        "id": "aMkpdekI60Au",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Creation table Communes\n",
        "requete = \"\"\"\n",
        "CREATE TABLE Communes\n",
        "(\n",
        "    IdCommune INTEGER  PRIMARY KEY,\n",
        "    inseecommune TEXT,\n",
        "    nomcommune TEXT,\n",
        "    quartier TEXT,\n",
        "    cdreseau TEXT,\n",
        "    nomreseau TEXT,\n",
        "    debutalim DATE\n",
        ");\"\"\"\n",
        "curseur.execute(requete)\n",
        "\n",
        "# Creation des tables Prelevements et Resultats\n",
        "\n",
        "# YOUR CODE HERE\n",
        "raise NotImplementedError()\n",
        "\n",
        "# On enregistre les changements !\n",
        "\n",
        "bdd.commit()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "markdown",
          "checksum": "d760be86fdba16f5b061ea70a6bd69fa",
          "grade": false,
          "grade_id": "cell-dfe92ce1acfd686f",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "-Q7Xk_gU60A0",
        "colab_type": "text"
      },
      "source": [
        "### Alimentation de la base de données"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "42e5f8ea25d4cae541e091a4a2532f28",
          "grade": false,
          "grade_id": "cell-a07b782dade2baf3",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "X21qhYyu60A2",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Exemple d'import d'un fichier CSV dans une BDD\n",
        "\n",
        "import csv\n",
        "\n",
        "with open('DIS_COM_UDI_2020.txt', newline='') as csvfile:\n",
        "    nb_lignes = 0\n",
        "    lignes = csv.reader(csvfile)\n",
        "    entete = True\n",
        "    for l in lignes:\n",
        "        if entete:\n",
        "            entete = False\n",
        "        else:\n",
        "            nb_lignes += 1\n",
        "            requete = f\"\"\"\n",
        "            INSERT INTO Communes\n",
        "                (inseecommune,nomcommune,quartier,cdreseau,nomreseau,debutalim)\n",
        "            VALUES\n",
        "                (?, ?, ?, ?, ?, ?)\n",
        "            \"\"\"\n",
        "            curseur.execute(requete, l)\n",
        "    bdd.commit()\n",
        "    print(f\"{nb_lignes} enregistrements créés\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "4fca0cca408be2e55a9c27512b42266f",
          "grade": false,
          "grade_id": "cell-7a49c2a4b1597f90",
          "locked": false,
          "schema_version": 3,
          "solution": true,
          "task": false
        },
        "id": "ifFxrGnG60A6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Procédez de même pour le fichier prélèvement\n",
        "\n",
        "# YOUR CODE HERE\n",
        "raise NotImplementedError()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "74fa9e1ee128a9f9538035c9d3ca1f0a",
          "grade": true,
          "grade_id": "cell-1130f42061f7d411",
          "locked": true,
          "points": 1,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "U8fAu3hN60BC",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "curseur.execute(\"SELECT COUNT(*) FROM Prelevements;\")\n",
        "assert curseur.fetchone()[0] == 126730"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "4db4068f7503562f76be6af3f224ec56",
          "grade": false,
          "grade_id": "cell-dc6fc490d01852a1",
          "locked": false,
          "schema_version": 3,
          "solution": true,
          "task": false
        },
        "id": "2oFUPTHX60BI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# et enfin le fichier Resultat\n",
        "\n",
        "# YOUR CODE HERE\n",
        "raise NotImplementedError()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "7ec2eca15ce3b2adbb5cd24f54a4dda3",
          "grade": true,
          "grade_id": "cell-bf9b14ed7980b5c1",
          "locked": true,
          "points": 1,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "8CtYw1Fs60BN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "curseur.execute(\"SELECT COUNT(*) FROM Resultats;\")\n",
        "assert curseur.fetchone()[0] == 3683342"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "markdown",
          "checksum": "964f28c938bee4f08b80652052cb0279",
          "grade": false,
          "grade_id": "cell-9b803de2737b6210",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "VkxQVK8960BR",
        "colab_type": "text"
      },
      "source": [
        "### Interrogation de la base de données\n",
        "\n",
        "Nous avons à présent une vraie base de données avec de vraies données, en nombre conséquent. Une petite requête sur la table Resultats suffit pour s'en convaincre : Plus de 3 millions et demi d'enregistrements !\n",
        "\n",
        "### Quelques idées de fonctions à créer ! \n",
        "\n",
        "- une fonction **liste_communes** prenant en entrée un curseur et un numéro de département et renvoyant la liste des communes dans de département présentes dans la base.\n",
        "    - La fonction ne renverra pas plusieurs fois la même commune\n",
        "    - Attention, pour un numéro de département inférieur à 10, if faut penser à ajouter \"0\"...\n",
        "    - Le champ *numeroinsee* commence par le numéro du département mais est différent du code postal.\n",
        "\n",
        "- une fonction **anomalies()** prenant un curseur et un nom de commune et qui renvoie la date et la conclusion du prélévement (champ *conclusionprel* dans **Prelevements** dans la commune) et la référence du prélèvement (champ *referenceprel* dans **Prelevements**) en cas d'anomalie. Vous repèrerez dans la base la phrase type lorsque tout va bien !\n",
        "\n",
        "- une fonction **resultat()** prenant en paramètres un curseur et une référence de prélèvement et qui renvoie les résultats détaillés de l'analyse avec les informations suivantes :\n",
        "    - libellé associé à chaque code paramètre, composant la dénomination du paramètre sous forme de texte libre\n",
        "    - Le résultat de l’analyse physico-chimique ou microbiologique du paramètre\n",
        "    - L'unité de mesure du paramètre\n",
        "    - La limite(s) de qualité du paramètre concerné en vigueur  au moment du prélèvement\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "0a11536ac4dccbe082d0671cf9ca44b0",
          "grade": false,
          "grade_id": "cell-a4a52cda5f401d08",
          "locked": false,
          "schema_version": 3,
          "solution": true,
          "task": false
        },
        "id": "kbifYCK260BS",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# YOUR CODE HERE\n",
        "raise NotImplementedError()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "a6815e9c1d47ab25e0276bfac465bec1",
          "grade": true,
          "grade_id": "cell-bf4857703bf85a10",
          "locked": true,
          "points": 1,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "udSIc2GA60BW",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "assert \"CAEN\" in liste_communes(curseur, 14)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "9fa8a99952866b286936f070a7034967",
          "grade": false,
          "grade_id": "cell-3a53a7372379011b",
          "locked": false,
          "schema_version": 3,
          "solution": true,
          "task": false
        },
        "id": "ifhF3AGQ60Ba",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# YOUR CODE HERE\n",
        "raise NotImplementedError()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_JiSLvxv60Bh",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "anomalies(curseur, \"HEROUVILLE-SAINT-CLAIR\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "14e6b6314aad81b90f074b67bab7478c",
          "grade": false,
          "grade_id": "cell-cdaad81480c222d3",
          "locked": false,
          "schema_version": 3,
          "solution": true,
          "task": false
        },
        "id": "5thuHV2B60Bm",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# YOUR CODE HERE\n",
        "raise NotImplementedError()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aNWStBET60Br",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "resultats(curseur, \"01400207768\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xRoeGxq360Bx",
        "colab_type": "text"
      },
      "source": [
        "### Amusez vous\n",
        "\n",
        "Interrogez la base de données pour rechercher des anomalies dans votre commune ou les environs, et récupérez les résultats détaillés des analyses en cas de problème pour identifier la source de l'anomalie.\n",
        "\n",
        "Au fil de votre parcours de la base, vous pouvez avoir envie de créer d'autres fonctions pour afficher d'autres informations. Ne vous privez pas !!!\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eXrwkL0w60By",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Amusez vous !\n",
        "liste_communes(curseur,14)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QCPy6MZK60B1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "X0UDx8ro60CC",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "markdown",
          "checksum": "0ca80b2ebafb9ead8f1e889ed010202f",
          "grade": false,
          "grade_id": "cell-881af0a8c807dd30",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "NvA3d7dY60CG",
        "colab_type": "text"
      },
      "source": [
        "\n",
        "### Pour aller plus loin\n",
        "\n",
        "Vous avez les outils à présent pout construire une application graphique (avec TKInter par exemple) ou bien un site web mettant à disposition ces résultats. Cela peut faire l'objet d'un projet.\n",
        "\n",
        "## Fin du travail\n",
        "\n",
        "On n'oublie pas de fermer l'accès à la base de données :)\n",
        "\n",
        "Vous aurez pu constater au cours de vos requêtes que les réponses de la base de données sont immédiates malgré les millions d'enregistrement que celle-ci contient !!\n",
        "\n",
        "Voici un petit aperçu de l'utilisation d'une base de donnée dans le monde réel."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "deletable": false,
        "editable": false,
        "nbgrader": {
          "cell_type": "code",
          "checksum": "f26a91f25a3a02f157f0a6a0e4c346ab",
          "grade": false,
          "grade_id": "cell-c14ec773daadc881",
          "locked": true,
          "schema_version": 3,
          "solution": false,
          "task": false
        },
        "id": "fnkOiWz860CI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "bdd.close()"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}