_doc/notebooks/2a/seance_5_intro_et_json.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# 2A.i - Donn\u00e9es non structur\u00e9es, programmation fonctionnelle\n",
        "\n",
        "Une table dans une base de donn\u00e9es est d\u00e9j\u00e0 le r\u00e9sultat d'une r\u00e9flexion sur la fa\u00e7on de les repr\u00e9senter."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div id=\"my_id_menu_nb\">run previous cell, wait for 2 seconds</div>\n",
              "<script>\n",
              "function repeat_indent_string(n){\n",
              "    var a = \"\" ;\n",
              "    for ( ; n > 0 ; --n)\n",
              "        a += \"    \";\n",
              "    return a;\n",
              "}\n",
              "// look up into all sections and builds an automated menu //\n",
              "var update_menu_string = function(begin, lfirst, llast, sformat, send, keep_item, begin_format, end_format) {\n",
              "    var anchors = document.getElementsByClassName(\"section\");\n",
              "    if (anchors.length == 0) {\n",
              "        anchors = document.getElementsByClassName(\"text_cell_render rendered_html\");\n",
              "    }\n",
              "    var i,t;\n",
              "    var text_menu = begin;\n",
              "    var text_memo = \"<pre>\\nlength:\" + anchors.length + \"\\n\";\n",
              "    var ind = \"\";\n",
              "    var memo_level = 1;\n",
              "    var href;\n",
              "    var tags = [];\n",
              "    var main_item = 0;\n",
              "    var format_open = 0;\n",
              "    for (i = 0; i <= llast; i++)\n",
              "        tags.push(\"h\" + i);\n",
              "\n",
              "    for (i = 0; i < anchors.length; i++) {\n",
              "        text_memo += \"**\" + anchors[i].id + \"--\\n\";\n",
              "\n",
              "        var child = null;\n",
              "        for(t = 0; t < tags.length; t++) {\n",
              "            var r = anchors[i].getElementsByTagName(tags[t]);\n",
              "            if (r.length > 0) {\n",
              "child = r[0];\n",
              "break;\n",
              "            }\n",
              "        }\n",
              "        if (child == null) {\n",
              "            text_memo += \"null\\n\";\n",
              "            continue;\n",
              "        }\n",
              "        if (anchors[i].hasAttribute(\"id\")) {\n",
              "            // when converted in RST\n",
              "            href = anchors[i].id;\n",
              "            text_memo += \"#1-\" + href;\n",
              "            // passer \u00e0 child suivant (le chercher)\n",
              "        }\n",
              "        else if (child.hasAttribute(\"id\")) {\n",
              "            // in a notebook\n",
              "            href = child.id;\n",
              "            text_memo += \"#2-\" + href;\n",
              "        }\n",
              "        else {\n",
              "            text_memo += \"#3-\" + \"*\" + \"\\n\";\n",
              "            continue;\n",
              "        }\n",
              "        var title = child.textContent;\n",
              "        var level = parseInt(child.tagName.substring(1,2));\n",
              "\n",
              "        text_memo += \"--\" + level + \"?\" + lfirst + \"--\" + title + \"\\n\";\n",
              "\n",
              "        if ((level < lfirst) || (level > llast)) {\n",
              "            continue ;\n",
              "        }\n",
              "        if (title.endsWith('\u00b6')) {\n",
              "            title = title.substring(0,title.length-1).replace(\"<\", \"&lt;\")\n",
              "         .replace(\">\", \"&gt;\").replace(\"&\", \"&amp;\");\n",
              "        }\n",
              "        if (title.length == 0) {\n",
              "            continue;\n",
              "        }\n",
              "\n",
              "        while (level < memo_level) {\n",
              "            text_menu += end_format + \"</ul>\\n\";\n",
              "            format_open -= 1;\n",
              "            memo_level -= 1;\n",
              "        }\n",
              "        if (level == lfirst) {\n",
              "            main_item += 1;\n",
              "        }\n",
              "        if (keep_item != -1 && main_item != keep_item + 1) {\n",
              "            // alert(main_item + \" - \" + level + \" - \" + keep_item);\n",
              "            continue;\n",
              "        }\n",
              "        while (level > memo_level) {\n",
              "            text_menu += \"<ul>\\n\";\n",
              "            memo_level += 1;\n",
              "        }\n",
              "        text_menu += repeat_indent_string(level-2);\n",
              "        text_menu += begin_format + sformat.replace(\"__HREF__\", href).replace(\"__TITLE__\", title);\n",
              "        format_open += 1;\n",
              "    }\n",
              "    while (1 < memo_level) {\n",
              "        text_menu += end_format + \"</ul>\\n\";\n",
              "        memo_level -= 1;\n",
              "        format_open -= 1;\n",
              "    }\n",
              "    text_menu += send;\n",
              "    //text_menu += \"\\n\" + text_memo;\n",
              "\n",
              "    while (format_open > 0) {\n",
              "        text_menu += end_format;\n",
              "        format_open -= 1;\n",
              "    }\n",
              "    return text_menu;\n",
              "};\n",
              "var update_menu = function() {\n",
              "    var sbegin = \"\";\n",
              "    var sformat = '<a href=\"#__HREF__\">__TITLE__</a>';\n",
              "    var send = \"\";\n",
              "    var begin_format = '<li>';\n",
              "    var end_format = '</li>';\n",
              "    var keep_item = -1;\n",
              "    var text_menu = update_menu_string(sbegin, 2, 4, sformat, send, keep_item,\n",
              "       begin_format, end_format);\n",
              "    var menu = document.getElementById(\"my_id_menu_nb\");\n",
              "    menu.innerHTML=text_menu;\n",
              "};\n",
              "window.setTimeout(update_menu,2000);\n",
              "            </script>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "execution_count": 2,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "from jyquickhelper import add_notebook_menu\n",
        "add_notebook_menu()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Avant-propos : programmation fonctionnelle ou numpy ?\n",
        "\n",
        "* [toolz](https://github.com/pytoolz/toolz/)/[cytoolz](https://github.com/pytoolz/cytoolz) : [programmation fonctionnelle](https://fr.wikipedia.org/wiki/Programmation_fonctionnelle)\n",
        "* [numpy](http://www.numpy.org/) : [calcul matriciel](https://fr.wikipedia.org/wiki/Matrice_(math%C3%A9matiques))\n",
        "\n",
        "Donn\u00e9es : [twitter_for_network_100000.db.zip](https://drive.google.com/open?id=0B6jkqYitZ0uTQ3k1NDZmLUJBZVk) or [twitter_for_network_100000.db.zip](http://www.xavierdupre.fr/enseignement/complements/twitter_for_network_100000.db.zip) (xavierdupre.fr)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/plain": [
              "['.\\\\twitter_for_network_100000.db']"
            ]
          },
          "execution_count": 3,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "import pyensae\n",
        "pyensae.download_data(\"twitter_for_network_100000.db.zip\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "User defined method or cross-method\n"
          ]
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "c:\\python372_x64\\lib\\site-packages\\ipykernel_launcher.py:6: RuntimeWarning: overflow encountered in long_scalars\n",
            "  \n"
          ]
        },
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "21.3 ms \u00b1 548 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 10 loops each)\n"
          ]
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "c:\\python372_x64\\lib\\site-packages\\ipykernel_launcher.py:1: RuntimeWarning: overflow encountered in long_scalars\n",
            "  \"\"\"Entry point for launching an IPython kernel.\n"
          ]
        },
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "17.1 ms \u00b1 611 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 100 loops each)\n",
            "6.24 ms \u00b1 274 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 100 loops each)\n",
            "4.95 ms \u00b1 658 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 100 loops each)\n",
            "Builtin function\n",
            "1.54 ms \u00b1 80.8 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 1000 loops each)\n",
            "Numpy function\n",
            "49.6 \u00b5s \u00b1 366 ns per loop (mean \u00b1 std. dev. of 7 runs, 10000 loops each)\n",
            "50.4 \u00b5s \u00b1 4.17 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 10000 loops each)\n"
          ]
        }
      ],
      "source": [
        "import numpy as np\n",
        "\n",
        "def my_sum(l):\n",
        "    res = 0\n",
        "    for it in l:\n",
        "        res += it\n",
        "    return res\n",
        "l = list(range(100000))\n",
        "a = np.arange(100000)\n",
        "\n",
        "print(\"User defined method or cross-method\")\n",
        "%timeit my_sum(a) # user defined with numpy array\n",
        "%timeit sum(a)    # built-in with numpy array\n",
        "%timeit np.sum(l) # numpy function with list\n",
        "%timeit my_sum(l) # user definedwith list\n",
        "print(\"Builtin function\")\n",
        "%timeit sum(l)    # built-in with list\n",
        "print(\"Numpy function\")\n",
        "%timeit np.sum(a) # numpy function\n",
        "%timeit a.sum()   # numpy method"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Il y a un rapport de 10 dans le temps d'ex\u00e9cution entre la m\u00e9thode **\"user defined\"** et la m\u00e9thode **\"builtin\"**.\n",
        "On retrouve ce m\u00eame rapport entre la m\u00e9thode \"builtin\" et les m\u00e9thodes numpy.\n",
        "On peut noter que m\u00e9langer les objets numpy et non-numpy donne de tr\u00e8s mauvais r\u00e9sultats.\n",
        "\n",
        "**Dans le cas de la programmation fonctionnelle, nous nous situerons plut\u00f4t dans le cas \"builtin\"** :"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<table><thead><tr><th>Project</th><th>Computation</th><th>Data Structures</th></tr></thead><tbody><tr><td>Code de l'utilisateur</td><td>Python</td><td>Python</td></tr><tr><td>CyToolz</td><td>C</td><td>Python</td></tr><tr><td>Pandas/NumPy</td><td>C</td><td>C</td></tr></tbody></table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Dans le cas de manipulation de donn\u00e9e structur\u00e9e de taille \"raisonnable\", [pandas](http://pandas.pydata.org/) et [numpy](http://www.numpy.org/) restent plus performants. Ils peuvent toutefois \u00eatre limit\u00e9s par plusieurs points :\n",
        "\n",
        "- manipulation de donn\u00e9es plus complexes avec des sous-listes, des champs manquants\n",
        "- ils sont construits sur le principe de chargement en m\u00e9moire des donn\u00e9es "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {},
      "outputs": [],
      "source": [
        "import os, psutil, gc, sys\n",
        "if not sys.platform.startswith(\"win\"):\n",
        "    import resource\n",
        "\n",
        "def memory_usage_psutil():\n",
        "    gc.collect()\n",
        "    process = psutil.Process(os.getpid())\n",
        "    mem = process.memory_info()[0] / float(2 ** 20)\n",
        "\n",
        "    print( \"Memory used : %i MB\" % mem )\n",
        "    if not sys.platform.startswith(\"win\"):\n",
        "        print( \"Max memory usage : %i MB\" % (resource.getrusage(resource.RUSAGE_SELF).ru_maxrss//1024) )"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Memory used : 114 MB\n"
          ]
        }
      ],
      "source": [
        "memory_usage_psutil()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {},
      "outputs": [],
      "source": [
        "import cytoolz as ct # import groupby, valmap, compose\n",
        "import cytoolz.curried as ctc ## pipe, map, filter, get\n",
        "import sqlite3\n",
        "import pprint\n",
        "try:\n",
        "    import ujson as json\n",
        "except:\n",
        "    print(\"ujson not available\")\n",
        "    import json\n",
        "\n",
        "conn_sqlite = sqlite3.connect(\"twitter_for_network_100000.db\")\n",
        "cursor_sqlite = conn_sqlite.cursor()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Le code suivant va lire plusieurs gigaoctets de donn\u00e9es, et la consommation maximale de m\u00e9moire du process ne va augmenter que de quelques Mo. De plus ce code manipule des dictionnaires qu'il serait compliqu\u00e9 de faire rentrer dans un [DataFrame pandas](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html). "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "108086205\n"
          ]
        }
      ],
      "source": [
        "cursor_sqlite.execute('SELECT content FROM tw_users' )\n",
        "object_to_sum = ctc.pluck( \"followers_count\", ctc.map( json.loads, ctc.pluck( 0, cursor_sqlite ) ) )\n",
        "print(sum(object_to_sum))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Memory used : 120 MB\n"
          ]
        }
      ],
      "source": [
        "memory_usage_psutil()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Dans le cadre du TP d'aujourd'hui, les donn\u00e9es que nous allons utiliser peuvent largement tenir en m\u00e9moire, et de fa\u00e7on g\u00e9n\u00e9rale, lorsqu'on d\u00e9veloppe des codes pour g\u00e9rer des gros volumes de donn\u00e9es, on les teste sur des volumes de donn\u00e9es qui tiennent en m\u00e9moire.\n",
        "\n",
        "Dans le cadre de la gestion de volume important de donn\u00e9es, on ne pourra donc pas stocker des r\u00e9sultats interm\u00e9diaire, on va donc composer des fonctions pour qu'elles produisent directement le r\u00e9sultat final.\n",
        "\n",
        "Cas classique :\n",
        "\n",
        "```\n",
        "resultat_intermediaire_1 = f( donnees )\n",
        "resultat_intermediaire_2 = g( resultat_intermediaire_1 )\n",
        "resultat_final = h( resultat_intermediaire_2 )\n",
        "```\n",
        "\n",
        "Programmation fonctionnelle :\n",
        "\n",
        "```\n",
        "resultat_final = h( g( f( donnees ) ) )\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Donn\u00e9es structur\u00e9es SQL et NOSQL"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "[SQL](https://fr.wikipedia.org/wiki/Structured_Query_Language) signifie *Structured Query Language*, les tables ont un nombre de colonnes fixes, et chaque colonne poss\u00e8de un type particulier.\n",
        "Comment g\u00e8re-t-on un nombre d'objets variables ? La plupart du temps avec une table secondaire qui contiendra une ligne par \u00e9l\u00e9ment de la liste.\n",
        "\n",
        "Par exemple, si l'on veut stocker la liste des films poss\u00e9d\u00e9s par une personne."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<table><tbody><tr><td><h5>Table person</h5></td><td><table><thead><tr><th>Id</th><th>Name</th></tr></thead><tbody><tr><td>1</td><td>Jean</td></tr><tr><td>2</td><td>Paul</td></tr><tr><td>3</td><td>Jacques</td></tr></tbody></table></td><td><h5>Table related_items</h5></td><td><table><thead><tr><th>Id</th><th>Person_id</th><th>Value</th></tr></thead><tbody><tr><td>1</td><td>1</td><td>Star wars</td></tr><tr><td>2</td><td>1</td><td>Cyrano</td></tr><tr><td>3</td><td>1</td><td>Lord of the rings</td></tr><tr><td>4</td><td>2</td><td>Mad max</td></tr><tr><td>5</td><td>2</td><td>Dr Horrible</td></tr></tbody></table></td></tbody></table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Ce syst\u00e8me est tr\u00e8s \"structur\u00e9\" (comme son nom l'indique) et peut s'av\u00e9rer assez lourd si l'on a affaire \u00e0 des donn\u00e9es moins bien structur\u00e9es, avec beaucoup de listes, des donn\u00e9es pr\u00e9sentes ou non.\n",
        "\n",
        "On voit donc se d\u00e9velopper de plus en plus des syst\u00e8mes alternatifs, dit [NoSQL](https://fr.wikipedia.org/wiki/NoSQL) (pour Not Only Sql).\n",
        "\n",
        "Nous allons en voir trois :\n",
        "\n",
        "  - [Sqlite3](https://docs.python.org/3/library/sqlite3.html) support for [Json](https://fr.wikipedia.org/wiki/JavaScript_Object_Notation)\n",
        "  - [mongodb](https://www.mongodb.org/)\n",
        "  - [PostGreSql](http://www.postgresql.org/)\n",
        "  \n",
        "Ils sont analogues sur la nature des donn\u00e9es stock\u00e9es, elles le sont au format [Json](https://fr.wikipedia.org/wiki/JavaScript_Object_Notation)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Json ? Qu'est ce que c'est ?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Il s'agit du format majoritaire pour les API informatiques internet.\n",
        "\n",
        "Par exemple les donn\u00e9es renvoy\u00e9es par twitter (sur lesquelles nous travaillerons aujourd'hui) sont sous ce format.\n",
        "\n",
        "Il signifie : [JavaScript Object Notation](https://fr.wikipedia.org/wiki/JavaScript_Object_Notation).\n",
        "Il se base essentiellement sur des dictionnaires (association cl\u00e9/valeur) et des listes.\n",
        "Il est tr\u00e8s proche des objets python (modulo les false/False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "scrolled": false
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "############### user raw json ###############\n",
            "{\"utc_offset\": 7200, \"friends_count\": 454, \"entities\": {\"description\": {\"urls\": []}, \"url\": {\"urls\": [{\"expanded_url\": \"http://www.havas.com\", \"display_url\": \"havas.com\", \"indices\": [0, 22], \"url\": \"http://t.co/8GcZtydjWh\"}]}}, \"description\": \"Havas Group CEO\", \"id\": 1103159180, \"contributors_enabled\": false, \"geo_enabled\": false, \"name\": \"Yannick Bollor\\u00e9\", \"favourites_count\": 873, \"verified\": true, \"protected\": false, \"created_at\": \"Sat Jan 19 08:23:33 +0000 2013\", \"statuses_count\": 654, \"lang\": \"en\", \"time_zone\": \"Ljubljana\", \"screen_name\": \"YannickBollore\", \"location\": \"\", \"id_str\": \"1103159180\", \"url\": \"http://t.co/8GcZtydjWh\", \"followers_count\": 7345, \"listed_count\": 118, \"has_extended_profile\": false}\n",
            "############### user as python dict ###############\n",
            "{'contributors_enabled': False,\n",
            " 'created_at': 'Sat Jan 19 08:23:33 +0000 2013',\n",
            " 'description': 'Havas Group CEO',\n",
            " 'entities': {'description': {'urls': []},\n",
            "              'url': {'urls': [{'display_url': 'havas.com',\n",
            "                                'expanded_url': 'http://www.havas.com',\n",
            "                                'indices': [0, 22],\n",
            "                                'url': 'http://t.co/8GcZtydjWh'}]}},\n",
            " 'favourites_count': 873,\n",
            " 'followers_count': 7345,\n",
            " 'friends_count': 454,\n",
            " 'geo_enabled': False,\n",
            " 'has_extended_profile': False,\n",
            " 'id': 1103159180,\n",
            " 'id_str': '1103159180',\n",
            " 'lang': 'en',\n",
            " 'listed_count': 118,\n",
            " 'location': '',\n",
            " 'name': 'Yannick Bollor\u00e9',\n",
            " 'protected': False,\n",
            " 'screen_name': 'YannickBollore',\n",
            " 'statuses_count': 654,\n",
            " 'time_zone': 'Ljubljana',\n",
            " 'url': 'http://t.co/8GcZtydjWh',\n",
            " 'utc_offset': 7200,\n",
            " 'verified': True}\n",
            "############### status as python dict ###############\n",
            "{'contributors': None,\n",
            " 'coordinates': None,\n",
            " 'created_at': 'Wed Jul 22 17:14:47 +0000 2015',\n",
            " 'entities': {'hashtags': [{'indices': [16, 28], 'text': 'Agriculture'}],\n",
            "              'symbols': [],\n",
            "              'urls': [{'display_url': 'blog-fillon.com/2015/07/plan-d\u2026',\n",
            "                        'expanded_url': 'http://www.blog-fillon.com/2015/07/plan-de-soutien-du-gouvernement-a-l-agriculture-decevant.html',\n",
            "                        'indices': [139, 140],\n",
            "                        'url': 'http://t.co/wZ3HkhHvuM'}],\n",
            "              'user_mentions': [{'id': 34598169,\n",
            "                                 'id_str': '34598169',\n",
            "                                 'indices': [3, 14],\n",
            "                                 'name': 'Fix RICHARD',\n",
            "                                 'screen_name': 'FixRichard'},\n",
            "                                {'id': 551669623,\n",
            "                                 'id_str': '551669623',\n",
            "                                 'indices': [113, 128],\n",
            "                                 'name': 'Fran\u00e7ois Fillon',\n",
            "                                 'screen_name': 'FrancoisFillon'}]},\n",
            " 'favorite_count': 0,\n",
            " 'favorited': False,\n",
            " 'geo': None,\n",
            " 'id': 623904030251708416,\n",
            " 'id_str': '623904030251708416',\n",
            " 'in_reply_to_screen_name': None,\n",
            " 'in_reply_to_status_id': None,\n",
            " 'in_reply_to_status_id_str': None,\n",
            " 'in_reply_to_user_id': None,\n",
            " 'in_reply_to_user_id_str': None,\n",
            " 'is_quote_status': False,\n",
            " 'lang': 'fr',\n",
            " 'place': None,\n",
            " 'possibly_sensitive': False,\n",
            " 'retweet_count': 23,\n",
            " 'retweeted': False,\n",
            " 'retweeted_status': {'contributors': None,\n",
            "                      'coordinates': None,\n",
            "                      'created_at': 'Wed Jul 22 17:09:33 +0000 2015',\n",
            "                      'entities': {'hashtags': [{'indices': [0, 12],\n",
            "                                                 'text': 'Agriculture'}],\n",
            "                                   'symbols': [],\n",
            "                                   'urls': [{'display_url': 'blog-fillon.com/2015/07/plan-d\u2026',\n",
            "                                             'expanded_url': 'http://www.blog-fillon.com/2015/07/plan-de-soutien-du-gouvernement-a-l-agriculture-decevant.html',\n",
            "                                             'indices': [113, 135],\n",
            "                                             'url': 'http://t.co/wZ3HkhHvuM'}],\n",
            "                                   'user_mentions': [{'id': 551669623,\n",
            "                                                      'id_str': '551669623',\n",
            "                                                      'indices': [97, 112],\n",
            "                                                      'name': 'Fran\u00e7ois Fillon',\n",
            "                                                      'screen_name': 'FrancoisFillon'}]},\n",
            "                      'favorite_count': 4,\n",
            "                      'favorited': False,\n",
            "                      'geo': None,\n",
            "                      'id': 623902715584888832,\n",
            "                      'id_str': '623902715584888832',\n",
            "                      'in_reply_to_screen_name': None,\n",
            "                      'in_reply_to_status_id': None,\n",
            "                      'in_reply_to_status_id_str': None,\n",
            "                      'in_reply_to_user_id': None,\n",
            "                      'in_reply_to_user_id_str': None,\n",
            "                      'is_quote_status': False,\n",
            "                      'lang': 'fr',\n",
            "                      'place': None,\n",
            "                      'possibly_sensitive': False,\n",
            "                      'retweet_count': 23,\n",
            "                      'retweeted': False,\n",
            "                      'source': '<a href=\"http://www.apple.com\" '\n",
            "                                'rel=\"nofollow\">iOS</a>',\n",
            "                      'text': '#Agriculture : \"Le plan du Gouvernement '\n",
            "                              \"n'apporte aucune solution durable aux fili\u00e8res \"\n",
            "                              'en crise\" @FrancoisFillon '\n",
            "                              'http://t.co/wZ3HkhHvuM',\n",
            "                      'truncated': False,\n",
            "                      'user': {'contributors_enabled': False,\n",
            "                               'created_at': 'Thu Apr 23 12:27:59 +0000 2009',\n",
            "                               'default_profile': False,\n",
            "                               'default_profile_image': False,\n",
            "                               'description': '#CM #ComPol #SocialMedia '\n",
            "                                              '#Politique \u2022 #PDL2015 '\n",
            "                                              '#AvecRetailleau \u2022 #Angers '\n",
            "                                              '#Paris #Nantes',\n",
            "                               'entities': {'description': {'urls': []},\n",
            "                                            'url': {'urls': [{'display_url': 'about.me/fixrichard',\n",
            "                                                              'expanded_url': 'http://about.me/fixrichard',\n",
            "                                                              'indices': [0,\n",
            "                                                                          22],\n",
            "                                                              'url': 'http://t.co/apaFxTrJMQ'}]}},\n",
            "                               'favourites_count': 10728,\n",
            "                               'follow_request_sent': False,\n",
            "                               'followers_count': 2488,\n",
            "                               'following': False,\n",
            "                               'friends_count': 865,\n",
            "                               'geo_enabled': True,\n",
            "                               'has_extended_profile': False,\n",
            "                               'id': 34598169,\n",
            "                               'id_str': '34598169',\n",
            "                               'is_translation_enabled': False,\n",
            "                               'is_translator': False,\n",
            "                               'lang': 'fr',\n",
            "                               'listed_count': 178,\n",
            "                               'location': 'Angers, Pays de la Loire',\n",
            "                               'name': 'Fix RICHARD',\n",
            "                               'notifications': False,\n",
            "                               'profile_background_color': 'C71E4E',\n",
            "                               'profile_background_image_url': 'http://abs.twimg.com/images/themes/theme14/bg.gif',\n",
            "                               'profile_background_image_url_https': 'https://abs.twimg.com/images/themes/theme14/bg.gif',\n",
            "                               'profile_background_tile': True,\n",
            "                               'profile_banner_url': 'https://pbs.twimg.com/profile_banners/34598169/1436884606',\n",
            "                               'profile_image_url': 'http://pbs.twimg.com/profile_images/621396058682343424/IMzOsat1_normal.jpg',\n",
            "                               'profile_image_url_https': 'https://pbs.twimg.com/profile_images/621396058682343424/IMzOsat1_normal.jpg',\n",
            "                               'profile_link_color': '0C4499',\n",
            "                               'profile_sidebar_border_color': 'FFFFFF',\n",
            "                               'profile_sidebar_fill_color': 'C0DFEC',\n",
            "                               'profile_text_color': '333333',\n",
            "                               'profile_use_background_image': True,\n",
            "                               'protected': False,\n",
            "                               'screen_name': 'FixRichard',\n",
            "                               'statuses_count': 20446,\n",
            "                               'time_zone': 'Paris',\n",
            "                               'url': 'http://t.co/apaFxTrJMQ',\n",
            "                               'utc_offset': 7200,\n",
            "                               'verified': False}},\n",
            " 'source': '<a href=\"http://twitter.com/download/iphone\" '\n",
            "           'rel=\"nofollow\">Twitter for iPhone</a>',\n",
            " 'text': 'RT @FixRichard: #Agriculture : \"Le plan du Gouvernement n\\'apporte '\n",
            "         'aucune solution durable aux fili\u00e8res en crise\" @FrancoisFillon '\n",
            "         'http://t.c\u2026',\n",
            " 'truncated': False,\n",
            " 'user': {'contributors_enabled': False,\n",
            "          'created_at': 'Thu Apr 12 08:09:49 +0000 2012',\n",
            "          'default_profile': False,\n",
            "          'default_profile_image': False,\n",
            "          'description': '',\n",
            "          'entities': {'description': {'urls': []},\n",
            "                       'url': {'urls': [{'display_url': 'blog-fillon.com',\n",
            "                                         'expanded_url': 'http://www.blog-fillon.com',\n",
            "                                         'indices': [0, 22],\n",
            "                                         'url': 'http://t.co/kqslZ0a4nj'}]}},\n",
            "          'favourites_count': 17,\n",
            "          'follow_request_sent': False,\n",
            "          'followers_count': 256321,\n",
            "          'following': False,\n",
            "          'friends_count': 1425,\n",
            "          'geo_enabled': True,\n",
            "          'has_extended_profile': False,\n",
            "          'id': 551669623,\n",
            "          'id_str': '551669623',\n",
            "          'is_translation_enabled': False,\n",
            "          'is_translator': False,\n",
            "          'lang': 'fr',\n",
            "          'listed_count': 1455,\n",
            "          'location': '',\n",
            "          'name': 'Fran\u00e7ois Fillon',\n",
            "          'notifications': False,\n",
            "          'profile_background_color': '0D37E0',\n",
            "          'profile_background_image_url': 'http://pbs.twimg.com/profile_background_images/598167675/40rdxs0i9ay7rkh2a4no.jpeg',\n",
            "          'profile_background_image_url_https': 'https://pbs.twimg.com/profile_background_images/598167675/40rdxs0i9ay7rkh2a4no.jpeg',\n",
            "          'profile_background_tile': False,\n",
            "          'profile_banner_url': 'https://pbs.twimg.com/profile_banners/551669623/1440524758',\n",
            "          'profile_image_url': 'http://pbs.twimg.com/profile_images/555688874446319616/0k5Rrgra_normal.jpeg',\n",
            "          'profile_image_url_https': 'https://pbs.twimg.com/profile_images/555688874446319616/0k5Rrgra_normal.jpeg',\n",
            "          'profile_link_color': '3B3B42',\n",
            "          'profile_sidebar_border_color': 'FFFFFF',\n",
            "          'profile_sidebar_fill_color': 'EFEFEF',\n",
            "          'profile_text_color': '333333',\n",
            "          'profile_use_background_image': False,\n",
            "          'protected': False,\n",
            "          'screen_name': 'FrancoisFillon',\n",
            "          'statuses_count': 7725,\n",
            "          'time_zone': 'Athens',\n",
            "          'url': 'http://t.co/kqslZ0a4nj',\n",
            "          'utc_offset': 10800,\n",
            "          'verified': True}}\n"
          ]
        }
      ],
      "source": [
        "import pprint\n",
        "\n",
        "cursor_sqlite.execute('SELECT content FROM tw_users LIMIT 1')\n",
        "user = cursor_sqlite.fetchone()[0]\n",
        "print(\"#\"*15 + \" user raw json \" + \"#\"*15)\n",
        "print( user )\n",
        "print(\"#\"*15 + \" user as python dict \" + \"#\"*15)\n",
        "pprint.pprint( json.loads( user ) )\n",
        "cursor_sqlite.execute('SELECT content FROM tw_status LIMIT 1')\n",
        "print(\"#\"*15 + \" status as python dict \" + \"#\"*15)\n",
        "pprint.pprint( json.loads( cursor_sqlite.fetchone()[0] ) )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Le NoSql / json permet donc une alternative au sch\u00e9ma classique suivant : "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<table><tbody><tr><td><h5>Table person</h5></td><td><table><thead><tr><th>Id</th><th>Name</th></tr></thead><tbody><tr><td>1</td><td>Jean</td></tr><tr><td>2</td><td>Paul</td></tr><tr><td>3</td><td>Jacques</td></tr></tbody></table></td><td><h5>Table related_items</h5></td><td><table><thead><tr><th>Id</th><th>Person_id</th><th>Value</th></tr></thead><tbody><tr><td>1</td><td>1</td><td>Star wars</td></tr><tr><td>2</td><td>1</td><td>Cyrano</td></tr><tr><td>3</td><td>1</td><td>Lord of the rings</td></tr><tr><td>4</td><td>2</td><td>Mad max</td></tr><tr><td>5</td><td>2</td><td>Dr Horrible</td></tr></tbody></table></td></tbody></table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Qui serait :"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<table><tbody><tr><td><h5>Table person_with_items</h5></td><td><table><thead><tr><th>Id</th><th>Name</th><th>Item_list</th></tr></thead><tbody><tr><td>1</td><td>Jean</td><td>['Star wars', 'Cyrano', 'Lord of the rings']</td></tr><tr><td>2</td><td>Paul</td><td>['Mad max', 'Dr Horrible']</td></tr><tr><td>3</td><td>Jacques</td><td></td></tr></tbody></table></td></tr></tbody></table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Cette derni\u00e8re structure serait vraiment un exemple de nosql dans son sens de Not Only Sql, il y a un mixe de donn\u00e9es structur\u00e9es et non structur\u00e9es.\n",
        "Il y a \u00e9galement certaines base de donn\u00e9es o\u00f9 il n'y a plus du tout de structure, comme mongodb, qui est qualifi\u00e9e de document-oriented."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<table><tbody><tr><td><h5>Table person_with_items</h5></td><td><table><tbody><tr><td>{'Id': 1, 'Name': 'Jean', 'Item_list' : ['Star wars', 'Cyrano', 'Lord of the rings']}</td></tr><tr><td>{'Id': 2, 'Name': 'Paul', 'Item_list' : ['Mad max', 'Dr Horrible']}</td></tr><tr><td>{'Id': 3, 'Name': 'Jacques', 'Item_list' : []}</td></tr></tbody></table></td></tr></tbody></table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Il faut toutefois remarquer que cette derni\u00e8re structure au moins deux inconv\u00e9nients par rapport \u00e0 une structure Sql avec une sous-table :\n",
        "\n",
        "  - vous ne pouvez pas acc\u00e9der directement aux objets de 'Item_list' sans passer par la table person\n",
        "  - les informations de Item_list ne peuvent \u00eatre partag\u00e9es entre plusieurs objets, on peut donc avoir \u00e0 restocker les informations\n",
        "  \n",
        "Voir module [psycopg](https://pypi.python.org/pypi/psycopg2)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {},
      "outputs": [],
      "source": [
        "try:\n",
        "    import psycopg2\n",
        "    from psycopg2.extras import Json\n",
        "    postgre_ok = True\n",
        "except ImportError:\n",
        "    postgre_ok = False\n",
        "\n",
        "if postgre_ok:\n",
        "    db_name = 'cours_ensae'\n",
        "    conn_string = \"host='localhost' dbname='{0}' user='python' password='kyojin'\".format( db_name )\n",
        "    try:\n",
        "        conn_psql = psycopg2.connect(conn_string)\n",
        "        cursor_psql = conn_psql.cursor()\n",
        "        postgre_ok = True\n",
        "    except psycopg2.OperationalError:\n",
        "        postgre_ok = False    "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {},
      "outputs": [],
      "source": [
        "if postgre_ok:\n",
        "    conn_psql.server_version"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {},
      "outputs": [],
      "source": [
        "if postgre_ok:\n",
        "    conn_psql.rollback()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {},
      "outputs": [],
      "source": [
        "if postgre_ok:\n",
        "    def get_data_sql(doc_id):\n",
        "        cursor_psql.execute(\"SELECT id, company FROM document WHERE id = %s\", (doc_id,))\n",
        "        res_1 = cursor_psql.fetchone()\n",
        "        cursor_psql.execute(\"SELECT id FROM ticket WHERE document_id = %s ORDER BY id\", (doc_id,))\n",
        "        res_2 = cursor_psql.fetchall()\n",
        "        tickets_id = [it[0] for it in res_2 ]\n",
        "        cursor_psql.execute(\"SELECT id FROM coupon WHERE ticket_id = ANY( %s ) ORDER BY id\", (tickets_id,))\n",
        "        res_3 = cursor_psql.fetchall()\n",
        "        return res_1 + (res_2,) + (res_3,)\n",
        "\n",
        "    %timeit get_data_sql(10000) \n",
        "    get_data_sql(10000)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {},
      "outputs": [],
      "source": [
        "if postgre_ok:\n",
        "    def get_data_sql_join(doc_id):\n",
        "        cursor_psql.execute(\"SELECT d.id, d.company, t.id, c.id FROM document as d \\\n",
        "                                                                JOIN ticket as t on d.id = t.document_id \\\n",
        "                                                                JOIN coupon as c on t.id = c.ticket_id \\\n",
        "                             WHERE d.id = %s\", (doc_id,))\n",
        "        return cursor_psql.fetchall()\n",
        "\n",
        "    %timeit get_data_sql_join(10000) \n",
        "    get_data_sql_join(10000)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {},
      "outputs": [],
      "source": [
        "if postgre_ok:\n",
        "    def get_data_nosql(doc_id):\n",
        "        cursor_psql.execute(\"SELECT id, company, content FROM document_nosql WHERE id = %s\", (doc_id,))\n",
        "        return cursor_psql.fetchone()\n",
        "\n",
        "    %timeit get_data_nosql(10000)\n",
        "    get_data_nosql(10000)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "[mongodb](https://www.mongodb.org/) ([pymongo](https://api.mongodb.org/python/current/)) lui ,ne connait pas de colonnes, que des documents, dont le format est analogue \u00e0 un objet json.  \n",
        "Cela se traduit par une tr\u00e8s grande simplicit\u00e9, pas besoin de d\u00e9clarer les tables, ni les bases de donn\u00e9es ..."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {},
      "outputs": [],
      "source": [
        "mongo = False\n",
        "if mongo:\n",
        "    import pymongo\n",
        "\n",
        "    mongo_client = pymongo.MongoClient( 'localhost', 27017 )\n",
        "    mongo_db = mongo_client.ensae_db\n",
        "\n",
        "    mongo_db.table_for_ensae.delete_many( {} )\n",
        "    mongo_db.table_for_ensae.insert_one( {'nom' : 'Martin', 'prenom' : 'Nicolas', 'grades': [20,18,7,12]} )\n",
        "    mongo_db.table_for_ensae.insert_one( {'nom' : 'Dupont', 'prenom' : 'Jean', 'grades': [11,5,7,12]} )\n",
        "    mongo_db.table_for_ensae.insert_one( {'nom' : 'Martin', 'prenom' : 'Gilles', 'grades': [10,10,10,10]} )\n",
        "\n",
        "    user = mongo_db.table_for_ensae.find_one( {'nom' : 'Dupont'} )\n",
        "    user_list = mongo_db.table_for_ensae.find( {} )\n",
        "    _ = list(map( pprint.pprint, user_list ))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Par contre certaines syntaxes usuelles en sql, ici le groupby, ont une \u00e9criture nettement plus complexes en mongodb."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {},
      "outputs": [],
      "source": [
        "if mongo:\n",
        "    result = mongo_db.table_for_ensae.group(['nom'], \n",
        "                                        None,\n",
        "                                        {'list': []}, # initial\n",
        "                                        'function(obj, prev) {prev.list.push(obj)}')\n",
        "    pprint.pprint( result )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Mon retour :\n",
        "\n",
        "- [mongodb](https://www.mongodb.org/) malgr\u00e9 sa simplicit\u00e9 d'utilisation peut \u00eatre tr\u00e8s gourmand en ressources (consommation d'espace disque et/ou m\u00e9moire 15 fois sup\u00e9rieure \u00e0 [PostGreSql](http://www.postgresql.org/) pour les m\u00eames donn\u00e9es). Je la d\u00e9conseille pour une application personnelle.\n",
        "- [Sqlite3](https://docs.python.org/3/library/sqlite3.html) est plus archa\u00efque, mais le fait d'avoir une base de donn\u00e9e contenue dans un fichier est tr\u00e8s pratique pour certains usages (d\u00e9ploiement chez un client ou pour des \u00e9l\u00e8ves)\n",
        "- [PostGreSql](http://www.postgresql.org/) me semble le plus robuste pour un usage en serveur personnel."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Et les fichiers plats ?"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Vous pouvez tout \u00e0 fait utiliser des fichiers plats.  \n",
        "Ils offrent beaucoup de simplicit\u00e9s d'utilisation.  \n",
        "Ils auront des performances potentiellement tr\u00e8s proches pour une lecture compl\u00e8te. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "4284281\n"
          ]
        }
      ],
      "source": [
        "cursor_sqlite.execute(\"SELECT content FROM tw_users LIMIT 10000\" )\n",
        "\n",
        "with open(\"tw_users.json\", 'w') as f:\n",
        "    for it_user in cursor_sqlite:\n",
        "        f.write(it_user[0])\n",
        "        f.write(\"\\n\")\n",
        "        \n",
        "with open(\"tw_users.json\", 'r') as f:\n",
        "    nb_total_followers = 0\n",
        "    for it_user in f:\n",
        "        nb_total_followers += json.loads( it_user )[\"followers_count\"]\n",
        "\n",
        "print( nb_total_followers )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Et pandas ?\n",
        "\n",
        "Pandas attend lui des donn\u00e9es plus ou moins structur\u00e9es. Vous pouvez charger une base sous pandans avec la syntaxe suivante :"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "           id     screen_name\n",
            "0  1103159180  YannickBollore\n",
            "1  2865692548     yveslemasne\n",
            "2    24732180     harlemdesir\n",
            "3   359979086      jpraffarin\n",
            "4   273341346  gilles_schnepp\n",
            "(100071, 2)\n"
          ]
        }
      ],
      "source": [
        "import pandas as pd\n",
        "\n",
        "df = pd.read_sql(  \"SELECT id, screen_name from tw_users\", conn_sqlite )\n",
        "print( df.head() )\n",
        "print( df.shape )"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "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.7.2"
    },
    "widgets": {
      "state": {},
      "version": "1.1.2"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}