src/seance6-mongodb.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connexion à MongoDB sous `Python`\n",
    "\n",
    "Nous allons voir dans ce TP comment nous pouvons récupérer des données stockées dans une base [MongoDB](https://www.mongodb.com/), par le biais de la librairie [`pymongo`](https://api.mongodb.com/python/current/). Il faut bien évidemment installer ce module (éventuellement localement). Pour l'utiliser, on l'importer classiquement comme ci-dessous."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymongo\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Utilisation générale\n",
    "\n",
    "La première opération est de créer une connexion entre `python` et *MongoDB* en utilisant la fonction `MongoClient()`. Celle-ci prend en paramètre l'adresse du serveur (*IP* et *port*). La commande ci-après permet donc de se connecter au serveur déjà vu en cours précédemment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = pymongo.MongoClient(\"mongodb://193.51.82.104:2343/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Par le biais de l'objet ainsi créé (`con`), on a accès à la liste des bases de données présentes, avec la fonction `database_name()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['admin',\n",
       " 'config',\n",
       " 'gym',\n",
       " 'horodateurs',\n",
       " 'local',\n",
       " 'medicaments',\n",
       " 'test',\n",
       " 'trafic',\n",
       " 'vlsstat',\n",
       " 'world']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.list_database_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nous allons utiliser la base `gym`. Nous allons créer un connecteur directement à cette base."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = con.gym"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "L'objet `db` est doté de différentes fonctions, et permet une utilisation très proche de celle qu'on a dans le `shell` de *MongoDB*. Pour voir la liste des collections présentes, on utilise la fonction `collection_names()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sportifs', 'Gymnases']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.list_collection_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fonctions de base\n",
    "\n",
    "Ensuite, pour accéder aux collections, et aux fonctions à utiliser dans celle-ci, nous utilisont le même formalisme que dans *MongoDB*.\n",
    "\n",
    "Nous avons le dénombrement des collections comme suit, avec `count()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: DeprecationWarning: count is deprecated. Use estimated_document_count or count_documents instead. Please note that $where must be replaced by $expr, $near must be replaced by $geoWithin with $center, and $nearSphere must be replaced by $geoWithin with $centerSphere\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "150"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Sportifs.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: DeprecationWarning: count is deprecated. Use estimated_document_count or count_documents instead. Please note that $where must be replaced by $expr, $near must be replaced by $geoWithin with $center, and $nearSphere must be replaced by $geoWithin with $centerSphere\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "28"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Gymnases.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pour obtenir le premier document d'une collection, on utilise ici la fonction `find_one()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '566eec5f662b388eba464203',\n",
       " 'IdSportif': 1,\n",
       " 'Nom': 'BOUTAHAR',\n",
       " 'Prenom': 'Abderahim',\n",
       " 'Sexe': 'm',\n",
       " 'Age': 30,\n",
       " 'Sports': {'Jouer': ['Volley ball', 'Tennis', 'Football'],\n",
       "  'Arbitrer': ['Basket ball', 'Volley ball', 'Hockey'],\n",
       "  'Entrainer': ['Basket ball',\n",
       "   'Volley ball',\n",
       "   'Hand ball',\n",
       "   'Hockey',\n",
       "   'Badmington']}}"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Sportifs.find_one()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '566eec69662b388eba464299',\n",
       " 'IdGymnase': 1,\n",
       " 'NomGymnase': 'PAUL ELUARD',\n",
       " 'Adresse': '2 rue des pépines',\n",
       " 'Ville': 'STAINS',\n",
       " 'Surface': 200,\n",
       " 'Seances': [{'IdSportifEntraineur': 149,\n",
       "   'Jour': 'Samedi',\n",
       "   'Horaire': 9.0,\n",
       "   'Duree': 60,\n",
       "   'Libelle': 'Basket ball'},\n",
       "  {'IdSportifEntraineur': 1,\n",
       "   'Jour': 'Lundi',\n",
       "   'Horaire': 9.0,\n",
       "   'Duree': 60,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 1,\n",
       "   'Jour': 'Lundi',\n",
       "   'Horaire': 10.0,\n",
       "   'Duree': 60,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 1,\n",
       "   'Jour': 'Lundi',\n",
       "   'Horaire': 11.3,\n",
       "   'Duree': 60,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 1,\n",
       "   'Jour': 'Lundi',\n",
       "   'Horaire': 14.0,\n",
       "   'Duree': 90,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 1,\n",
       "   'Jour': 'lundi',\n",
       "   'Horaire': 17.3,\n",
       "   'Duree': 120,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 1,\n",
       "   'Jour': 'Lundi',\n",
       "   'Horaire': 19.3,\n",
       "   'Duree': 120,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 2,\n",
       "   'Jour': 'Dimanche',\n",
       "   'Horaire': 17.3,\n",
       "   'Duree': 120,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 2,\n",
       "   'Jour': 'Dimanche',\n",
       "   'Horaire': 19.3,\n",
       "   'Duree': 120,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 2,\n",
       "   'Jour': 'mardi',\n",
       "   'Horaire': 17.3,\n",
       "   'Duree': 120,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 2,\n",
       "   'Jour': 'mercredi',\n",
       "   'Horaire': 17.3,\n",
       "   'Duree': 120,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 2,\n",
       "   'Jour': 'Samedi',\n",
       "   'Horaire': 15.3,\n",
       "   'Duree': 60,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 2,\n",
       "   'Jour': 'Samedi',\n",
       "   'Horaire': 16.3,\n",
       "   'Duree': 60,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 2,\n",
       "   'Jour': 'Samedi',\n",
       "   'Horaire': 17.3,\n",
       "   'Duree': 120,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 3,\n",
       "   'Jour': 'jeudi',\n",
       "   'Horaire': 20.0,\n",
       "   'Duree': 30,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 3,\n",
       "   'Jour': 'lundi',\n",
       "   'Horaire': 14.0,\n",
       "   'Duree': 60,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 3,\n",
       "   'Jour': 'lundi',\n",
       "   'Horaire': 18.0,\n",
       "   'Duree': 30,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 3,\n",
       "   'Jour': 'lundi',\n",
       "   'Horaire': 19.0,\n",
       "   'Duree': 30,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 3,\n",
       "   'Jour': 'lundi',\n",
       "   'Horaire': 20.0,\n",
       "   'Duree': 30,\n",
       "   'Libelle': 'Hand ball'},\n",
       "  {'IdSportifEntraineur': 7,\n",
       "   'Jour': 'mercredi',\n",
       "   'Horaire': 17.0,\n",
       "   'Duree': 90,\n",
       "   'Libelle': 'Hockey'}]}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Gymnases.find_one()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Et on peut lister les valeurs prises par un champs dans une collection par la fonction `ditinct()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['m', 'M', 'F']"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Sportifs.distinct(\"Sexe\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Football',\n",
       " 'Tennis',\n",
       " 'Volley ball',\n",
       " 'Basket ball',\n",
       " 'Ping pong',\n",
       " 'Badmington',\n",
       " 'Hand ball']"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Sportifs.distinct(\"Sports.Jouer\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['STAINS',\n",
       " 'MONTMORENCY',\n",
       " 'PIERREFITTE',\n",
       " 'SARCELLES',\n",
       " 'SAINT DENIS',\n",
       " 'VILLETANEUSE',\n",
       " 'GARGES']"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Gymnases.distinct(\"Ville\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[200, 450, 400, 500, 620, 360, 420, 300, 480, 600, 520, 350]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Gymnases.distinct(\"Surface\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Basket ball', 'Hand ball', 'Hockey', 'Volley ball']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Gymnases.distinct(\"Seances.Libelle\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Dimanche',\n",
       " 'Lundi',\n",
       " 'Samedi',\n",
       " 'jeudi',\n",
       " 'lundi',\n",
       " 'mardi',\n",
       " 'mercredi',\n",
       " 'dimanche',\n",
       " 'Mercredi',\n",
       " 'Vendredi',\n",
       " 'vendredi',\n",
       " 'samedi',\n",
       " 'Mardi',\n",
       " 'Jeudi']"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Gymnases.distinct(\"Seances.Jour\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Affichage des résultats\n",
    "\n",
    "Nous l'avons vu précédemment, l'affichage du résultat de la fonction `find_one()` n'est pas totalement lisible. Il est possible d'utiliser la fonction `pprint()` du module `pprint`, normalement installé dès l'installation de `python`. Celle-ci améliore l'affichage, en ajoutant des indentations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Age': 30,\n",
      " 'IdSportif': 1,\n",
      " 'Nom': 'BOUTAHAR',\n",
      " 'Prenom': 'Abderahim',\n",
      " 'Sexe': 'm',\n",
      " 'Sports': {'Arbitrer': ['Basket ball', 'Volley ball', 'Hockey'],\n",
      "            'Entrainer': ['Basket ball',\n",
      "                          'Volley ball',\n",
      "                          'Hand ball',\n",
      "                          'Hockey',\n",
      "                          'Badmington'],\n",
      "            'Jouer': ['Volley ball', 'Tennis', 'Football']},\n",
      " '_id': '566eec5f662b388eba464203'}\n"
     ]
    }
   ],
   "source": [
    "import pprint\n",
    "pprint.pprint(db.Sportifs.find_one())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Par contre, pour l'utiliser sur le résultat de la fonction `find()` (qui renvoie un curseur, puisqu'il peut y avoir plusieurs documents correspondants), il faut soit faire une boucle sur ce curseur, soit le transformer en `list`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pymongo.cursor.Cursor object at 0x0000029FBEAA5278>\n"
     ]
    }
   ],
   "source": [
    "res = db.Sportifs.find({ \"Nom\": \"KERVADEC\" })\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: DeprecationWarning: count is deprecated. Use Collection.count_documents instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "print(res.count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Voici comment réaliser une boucle sur le résultat pour l'affichage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Age': 28,\n",
      " 'IdSportif': 2,\n",
      " 'IdSportifConseiller': 1,\n",
      " 'Nom': 'KERVADEC',\n",
      " 'Prenom': 'Yann',\n",
      " 'Sexe': 'M',\n",
      " 'Sports': {'Arbitrer': ['Hockey', 'Football'],\n",
      "            'Entrainer': ['Basket ball',\n",
      "                          'Volley ball',\n",
      "                          'Hand ball',\n",
      "                          'Tennis',\n",
      "                          'Hockey',\n",
      "                          'Badmington',\n",
      "                          'Ping pong',\n",
      "                          'Boxe'],\n",
      "            'Jouer': ['Basket ball', 'Volley ball', 'Ping pong', 'Football']},\n",
      " '_id': '566eec5f662b388eba464204'}\n"
     ]
    }
   ],
   "source": [
    "for r in res:\n",
    "    pprint.pprint(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Et voici un exemple de transformation en `list`. Une fois que le curseur est lu, comme ici dans la boucle, il est vide. Il faut donc le recalculer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'Age': 28,\n",
      "  'IdSportif': 2,\n",
      "  'IdSportifConseiller': 1,\n",
      "  'Nom': 'KERVADEC',\n",
      "  'Prenom': 'Yann',\n",
      "  'Sexe': 'M',\n",
      "  'Sports': {'Arbitrer': ['Hockey', 'Football'],\n",
      "             'Entrainer': ['Basket ball',\n",
      "                           'Volley ball',\n",
      "                           'Hand ball',\n",
      "                           'Tennis',\n",
      "                           'Hockey',\n",
      "                           'Badmington',\n",
      "                           'Ping pong',\n",
      "                           'Boxe'],\n",
      "             'Jouer': ['Basket ball', 'Volley ball', 'Ping pong', 'Football']},\n",
      "  '_id': '566eec5f662b388eba464204'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Sportifs.find({ \"Nom\": \"KERVADEC\" })\n",
    "pprint.pprint(list(res))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On créé ici une fonction simple, nommée `affichage()`, qui va prendre en paramètre un curseur renvoyé par la fonction `find()` et qui affichera proprement le résultat via `pprint()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "def affiche(res):\n",
    "    pprint.pprint(list(res))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Et voici comment utiliser cette fonction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'Nom': 'KERVADEC'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Sportifs.find({ \"Nom\": \"KERVADEC\" }, { \"_id\": 0, \"Nom\": 1 })\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Recherche d'informations\n",
    "\n",
    "Comme nous allons le voir par la suite, nous allons utiliser exactement le même formalisme que dans *MongoDB*, avec la fonction `find()`. Ceci est possible avec l'utilisation de dictionnaires pour les paramètres de critères de sélection des documents et pour les choix d’items des documents à afficher. La seule obligation est de mettre les noms des champs entre `\"...\"`, alors qu'il était possible de ne pas le faire dans *MongoDB*.\n",
    "\n",
    "Ici, nous recherchons les sportifs d'au moins 32 ans, en affichant que leur nom et leur âge. On voit ici un effet de `python` sur l'ordre d'affichage des items (ici, dans l'ordre alphabétique - comme toujours dans `python`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'Age': 32, 'Nom': 'DORLEANS'},\n",
      " {'Age': 40, 'Nom': 'RABAHI'},\n",
      " {'Age': 32, 'Nom': 'TIZEGHAT'},\n",
      " {'Age': 32, 'Nom': 'BAZOUD'},\n",
      " {'Age': 39, 'Nom': 'SARRAZIN'},\n",
      " {'Age': 40, 'Nom': 'HOUEL'},\n",
      " {'Age': 36, 'Nom': 'LEROUX'},\n",
      " {'Age': 32, 'Nom': 'BONE'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Sportifs.find({ \"Age\": { \"$gte\": 32 } }, { \"_id\": 0, \"Nom\": 1, \"Age\": 1 }) #gte <=\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On peut aussi stocker les critères et les choix en sortie dans des variables, pour les utiliser dans `find()`. Ici, nous recherchons les sportives jouant au Basket."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'Nom': 'COMES',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball',\n",
      "                       'Volley ball',\n",
      "                       'Badmington',\n",
      "                       'Ping pong']}},\n",
      " {'Nom': 'RETALDI',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball',\n",
      "                       'Volley ball',\n",
      "                       'Hand ball',\n",
      "                       'Ping pong']}},\n",
      " {'Nom': 'CAILLIOT',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball', 'Volley ball', 'Ping pong']}},\n",
      " {'Nom': 'LEJEUNE',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball',\n",
      "                       'Volley ball',\n",
      "                       'Badmington',\n",
      "                       'Ping pong']}},\n",
      " {'Nom': 'HEDDI',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball', 'Badmington', 'Ping pong']}},\n",
      " {'Nom': 'JOUVE',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball', 'Ping pong']}},\n",
      " {'Nom': 'GUERRAOUI',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball', 'Ping pong']}},\n",
      " {'Nom': 'CLERICE',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball', 'Badmington', 'Ping pong']}},\n",
      " {'Nom': 'MARIE',\n",
      "  'Sexe': 'F',\n",
      "  'Sports': {'Jouer': ['Basket ball', 'Hand ball', 'Ping pong']}},\n",
      " {'Nom': 'BELZ', 'Sexe': 'F', 'Sports': {'Jouer': 'Basket ball'}}]\n"
     ]
    }
   ],
   "source": [
    "criteres = { \"Sports.Jouer\" : \"Basket ball\", \"Sexe\" : \"F\" }\n",
    "sortie = { \"_id\": 0, \"Nom\": 1, \"Sexe\": 1, \"Sports.Jouer\": 1}\n",
    "res = db.Sportifs.find(criteres, sortie)\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pour le tri, on utilise la fonction `sort()` sur le résultat. Par contre, nous devons ici mettre une liste de critères de tri. Ceux-ci doivent tous être des tuples à deux valeurs : le champ de tri et l'odre choisi (ascendant ou descendant). Pour le spécifier, nous devons utiliser deux valeurs spécifiques du module : `DESCENDING` (pour descendant) et `ASCENDING` (pour ascendant).\n",
    "\n",
    "Ici, nous reprenons la recherche précédent des sportifs d'au moins 32 ans. Mais le résultat est trié par ordre décroissant sur l'âge, et par ordre alphabétique pour le nom (pour ceux ayant le même âge donc)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'Age': 40, 'Nom': 'HOUEL'},\n",
      " {'Age': 40, 'Nom': 'RABAHI'},\n",
      " {'Age': 39, 'Nom': 'SARRAZIN'},\n",
      " {'Age': 36, 'Nom': 'LEROUX'},\n",
      " {'Age': 32, 'Nom': 'BAZOUD'},\n",
      " {'Age': 32, 'Nom': 'BONE'},\n",
      " {'Age': 32, 'Nom': 'DORLEANS'},\n",
      " {'Age': 32, 'Nom': 'TIZEGHAT'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Sportifs.find({ \"Age\": { \"$gte\": 32} }, { \"_id\": 0, \"Nom\": 1, \"Age\": 1 })\n",
    "res_tri = res.sort([ (\"Age\", pymongo.DESCENDING), (\"Nom\", pymongo.ASCENDING) ])\n",
    "affiche(res_tri)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Agrégats\n",
    "\n",
    "Nous avons aussi la possibilité de calculer des agrégats, avec la fonction `aggregate()`. Celle-ci s'utilise de manière très similaire à celle de *MongoDB*.\n",
    "\n",
    "Ci-dessous, nous calculons le nombre de gymnases et la surface moyenne de celle-ci. Pour ne pas mettre de critère d'agrégations, nous mettons une chaîne de caractère comme identifiant."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'_id': 'Total', 'nb': 28, 'surfmoy': 444.2857142857143}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Gymnases.aggregate([ \n",
    "    { \"$group\": { \"_id\": \"Total\", \"nb\": { \"$sum\": 1 }, \"surfmoy\": { \"$avg\": \"$Surface\" }}}\n",
    "])\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pour faire une agrégation sur un critère, on indique le champs toujours avec le symbole `\"$\"` devant. Nous avons ici, pour chaque ville, le nombre de gymnases et des statistiques simples sur la surface de ceux-ci (totale, moyenne, minimum et maximum)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'_id': 'SAINT DENIS',\n",
      "  'nb': 3,\n",
      "  'surfaceMaximum': 520,\n",
      "  'surfaceMinimum': 450,\n",
      "  'surfaceMoyenne': 490.0,\n",
      "  'surfaceTotale': 1470},\n",
      " {'_id': 'STAINS',\n",
      "  'nb': 6,\n",
      "  'surfaceMaximum': 400,\n",
      "  'surfaceMinimum': 200,\n",
      "  'surfaceMoyenne': 333.3333333333333,\n",
      "  'surfaceTotale': 2000},\n",
      " {'_id': 'MONTMORENCY',\n",
      "  'nb': 5,\n",
      "  'surfaceMaximum': 500,\n",
      "  'surfaceMinimum': 420,\n",
      "  'surfaceMoyenne': 470.0,\n",
      "  'surfaceTotale': 2350},\n",
      " {'_id': 'GARGES',\n",
      "  'nb': 1,\n",
      "  'surfaceMaximum': 400,\n",
      "  'surfaceMinimum': 400,\n",
      "  'surfaceMoyenne': 400.0,\n",
      "  'surfaceTotale': 400},\n",
      " {'_id': 'VILLETANEUSE',\n",
      "  'nb': 3,\n",
      "  'surfaceMaximum': 620,\n",
      "  'surfaceMinimum': 350,\n",
      "  'surfaceMoyenne': 523.3333333333334,\n",
      "  'surfaceTotale': 1570},\n",
      " {'_id': 'PIERREFITTE',\n",
      "  'nb': 5,\n",
      "  'surfaceMaximum': 450,\n",
      "  'surfaceMinimum': 300,\n",
      "  'surfaceMoyenne': 382.0,\n",
      "  'surfaceTotale': 1910},\n",
      " {'_id': 'SARCELLES',\n",
      "  'nb': 5,\n",
      "  'surfaceMaximum': 620,\n",
      "  'surfaceMinimum': 400,\n",
      "  'surfaceMoyenne': 548.0,\n",
      "  'surfaceTotale': 2740}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Gymnases.aggregate([ \n",
    "    { \"$group\": { \n",
    "        \"_id\": \"$Ville\", \n",
    "        \"nb\": { \"$sum\": 1 }, #equivaut à $count\n",
    "        \"surfaceTotale\": { \"$sum\": \"$Surface\" },\n",
    "        \"surfaceMoyenne\": { \"$avg\": \"$Surface\" },\n",
    "        \"surfaceMinimum\": { \"$min\": \"$Surface\" },\n",
    "        \"surfaceMaximum\": { \"$max\": \"$Surface\" }\n",
    "    }}\n",
    "])\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Et comme dans *MongoDB*, on peut utiliser les commandes `unwind`, `project` et `sort` pour réaliser des calculs d'agrégats complexes. Ici, nous cherchons le nombre total de séances par jour, ceux-ci étant triés dans l'ordre décroissant du nombre de séances."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'_id': 'lundi', 'nb': 23},\n",
      " {'_id': 'dimanche', 'nb': 17},\n",
      " {'_id': 'mercredi', 'nb': 13},\n",
      " {'_id': 'samedi', 'nb': 9},\n",
      " {'_id': 'mardi', 'nb': 8},\n",
      " {'_id': 'vendredi', 'nb': 6},\n",
      " {'_id': 'jeudi', 'nb': 6}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Gymnases.aggregate([\n",
    "    { \"$unwind\": \"$Seances\" }, \n",
    "    { \"$project\": { \"Jour\": { \"$toLower\": \"$Seances.Jour\" } }}, #projet permet de réécrire les documents\n",
    "    { \"$group\": { \"_id\": \"$Jour\", \"nb\": { \"$sum\": 1 }} },\n",
    "    { \"$sort\": { \"nb\": -1 }}\n",
    "])\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importation dans un `DataFrame`\n",
    "\n",
    "Pour pouvoir utiliser les données recherchées, nous pouvons les transformer en `DataFrame` (de type `pandas`). Pour cela, nous les transformons en `list`, puis en `DataFrame`, comme dans l'exemple ci-dessous."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "\n",
    "res = db.Sportifs.find()\n",
    "df = pandas.DataFrame(list(res))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Quand on regarde le `DataFrame` obtenu, nous remarquons que pour certains colonnes (`Sports` ici), le contenu est un objet complexe (un dictionnaire en l'occurence ici)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>IdSportif</th>\n",
       "      <th>IdSportifConseiller</th>\n",
       "      <th>Nom</th>\n",
       "      <th>Prenom</th>\n",
       "      <th>Sexe</th>\n",
       "      <th>Sports</th>\n",
       "      <th>_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BOUTAHAR</td>\n",
       "      <td>Abderahim</td>\n",
       "      <td>m</td>\n",
       "      <td>{'Entrainer': ['Basket ball', 'Volley ball', '...</td>\n",
       "      <td>566eec5f662b388eba464203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>KERVADEC</td>\n",
       "      <td>Yann</td>\n",
       "      <td>M</td>\n",
       "      <td>{'Entrainer': ['Basket ball', 'Volley ball', '...</td>\n",
       "      <td>566eec5f662b388eba464204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HUE</td>\n",
       "      <td>Pascale</td>\n",
       "      <td>F</td>\n",
       "      <td>{'Entrainer': ['Basket ball', 'Volley ball', '...</td>\n",
       "      <td>566eec5f662b388eba464205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>DORLEANS</td>\n",
       "      <td>Jean-michel</td>\n",
       "      <td>M</td>\n",
       "      <td>{'Entrainer': ['Basket ball', 'Ping pong', 'Bo...</td>\n",
       "      <td>566eec5f662b388eba464206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>COMES</td>\n",
       "      <td>Sylvie</td>\n",
       "      <td>F</td>\n",
       "      <td>{'Arbitrer': 'Ping pong', 'Jouer': ['Basket ba...</td>\n",
       "      <td>566eec5f662b388eba464207</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Age  IdSportif  IdSportifConseiller       Nom       Prenom Sexe  \\\n",
       "0   30          1                  NaN  BOUTAHAR    Abderahim    m   \n",
       "1   28          2                  1.0  KERVADEC         Yann    M   \n",
       "2   25          3                  1.0       HUE      Pascale    F   \n",
       "3   32          4                  1.0  DORLEANS  Jean-michel    M   \n",
       "4   22          5                  1.0     COMES       Sylvie    F   \n",
       "\n",
       "                                              Sports                       _id  \n",
       "0  {'Entrainer': ['Basket ball', 'Volley ball', '...  566eec5f662b388eba464203  \n",
       "1  {'Entrainer': ['Basket ball', 'Volley ball', '...  566eec5f662b388eba464204  \n",
       "2  {'Entrainer': ['Basket ball', 'Volley ball', '...  566eec5f662b388eba464205  \n",
       "3  {'Entrainer': ['Basket ball', 'Ping pong', 'Bo...  566eec5f662b388eba464206  \n",
       "4  {'Arbitrer': 'Ping pong', 'Jouer': ['Basket ba...  566eec5f662b388eba464207  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nous récupérons ici le premier sportif dans l'objet `df0`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0 = df.loc[0,:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Avec cet objet, nous accédons aux sports qu'il joue/entraîne/arbitre comme suit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Jouer': ['Volley ball', 'Tennis', 'Football'],\n",
       " 'Arbitrer': ['Basket ball', 'Volley ball', 'Hockey'],\n",
       " 'Entrainer': ['Basket ball',\n",
       "  'Volley ball',\n",
       "  'Hand ball',\n",
       "  'Hockey',\n",
       "  'Badmington']}"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df0.Sports"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pour obtenir le tableau des sports joués (par exemple), nous le récupérons avec le code suivant."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Volley ball', 'Tennis', 'Football']"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df0.Sports[\"Jouer\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A faire\n",
    "\n",
    "Répondre aux questions suivantes\n",
    "\n",
    "01. Quels sont les sportifs (identifiant, nom et prénom) qui ont entre 20 et 30 ans ?\n",
    "02. Quels sont les gymnases de \"Villetaneuse\" ou de \"Sarcelles\" qui ont une surface de plus de 400 m2 ?\n",
    "03. Quels sont les sportifs (identifiant et nom) qui pratiquent du hand ball ?\n",
    "04. Dans quels gymnases et quels jours y a t-il des séances de hand ball ?\n",
    "05. Dans quels gymnases peut-on jouer au hockey le mercredi apres 15H ?\n",
    "06. Quels sportifs (identifiant et nom) ne pratiquent aucun sport ?\n",
    "07. Quels gymnases n'ont pas de séances le dimanche ?\n",
    "08. Quels gymnases ne proposent que des séances de basket ball ou de volley ball ?\n",
    "09. Quels sont les entraîneurs qui sont aussi joueurs ?\n",
    "10. Quels sont les sportifs qui sont des conseillers ?\n",
    "11. Pour le sportif \"Kervadec\" quel est le nom de son conseiller ?\n",
    "12. Quels entraîneurs entraînent du hand ball et du basket ball ?\n",
    "13. ~~Quels sont les couples de sportifs (identifiant et nom et prénom de chaque) de même age ?~~\n",
    "14. Quelle est la moyenne d'âge des sportives qui pratiquent du basket ball ?\n",
    "15. Quels sont les sportifs les plus jeunes ?\n",
    "16. Quels sont les gymnases de \"Stains\" ou de \"Montmorency\" qui ont la plus grande surface ?\n",
    "17. Quels entraîneurs n'entraînent que du hand ball ou du basket ball ?\n",
    "18. ~~Quels sont les couples de sportifs (identifiant et nom et prénom de chaque) de même âge avec le même conseiller ?~~\n",
    "19. Quels sportifs n'ont pas de conseillers ?\n",
    "20. Pour chaque sportif donner le nombre de sports qu'il arbitre\n",
    "21. Pour chaque gymnase de Stains donner par jour d'ouverture les horaires des premières et dernières\n",
    "séances\n",
    "22. Pour chaque entraîneurs de hand ball quel est le nombre de séances journalières qu'il assure ?\n",
    "23. Quels sont les gymnases ayant plus de 15 séances le mercredi ?\n",
    "24. Pour chaque gymnase de Montmorency : quel est le nombre de séances journalières de chaque sport\n",
    "propose ?\n",
    "25. Dans quels gymnases et quels jours y a t-il au moins 4 séances de volley ball dans la journée ?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1. Quels sont les sportifs (identifiant, nom et prénom) qui ont entre 20 et 30 ans ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'IdSportif': 1, 'Nom': 'BOUTAHAR'},\n",
      " {'IdSportif': 2, 'Nom': 'KERVADEC'},\n",
      " {'IdSportif': 3, 'Nom': 'HUE'},\n",
      " {'IdSportif': 5, 'Nom': 'COMES'},\n",
      " {'IdSportif': 6, 'Nom': 'RETALDI'},\n",
      " {'IdSportif': 7, 'Nom': 'GOMEZ'},\n",
      " {'IdSportif': 9, 'Nom': 'ANTUNES'},\n",
      " {'IdSportif': 10, 'Nom': 'BLANDET'},\n",
      " {'IdSportif': 11, 'Nom': 'BONNET'},\n",
      " {'IdSportif': 12, 'Nom': 'BORREL'},\n",
      " {'IdSportif': 13, 'Nom': 'BOYON'},\n",
      " {'IdSportif': 14, 'Nom': 'CAILLIOT'},\n",
      " {'IdSportif': 15, 'Nom': 'CHAMPENOIS'},\n",
      " {'IdSportif': 16, 'Nom': 'COLAS'},\n",
      " {'IdSportif': 17, 'Nom': 'COLOMB'},\n",
      " {'IdSportif': 18, 'Nom': 'HOSNI'},\n",
      " {'IdSportif': 19, 'Nom': 'TESTEMONT'},\n",
      " {'IdSportif': 20, 'Nom': 'TIENER'},\n",
      " {'IdSportif': 21, 'Nom': 'LE BOUCHER'},\n",
      " {'IdSportif': 22, 'Nom': 'LEDUFAUD'},\n",
      " {'IdSportif': 23, 'Nom': 'LEGRAND'},\n",
      " {'IdSportif': 24, 'Nom': 'LEJEUNE'},\n",
      " {'IdSportif': 25, 'Nom': 'MARTORA'},\n",
      " {'IdSportif': 26, 'Nom': 'MICHEL'},\n",
      " {'IdSportif': 27, 'Nom': 'NIELLEZ'},\n",
      " {'IdSportif': 29, 'Nom': 'ROUSSEL'},\n",
      " {'IdSportif': 30, 'Nom': 'SCHINK'},\n",
      " {'IdSportif': 31, 'Nom': 'STEMPUT'},\n",
      " {'IdSportif': 32, 'Nom': 'VAN CAUTER'},\n",
      " {'IdSportif': 33, 'Nom': 'RAMPNOUX'},\n",
      " {'IdSportif': 34, 'Nom': 'LHERPINIERE'},\n",
      " {'IdSportif': 35, 'Nom': 'TANQUE'},\n",
      " {'IdSportif': 36, 'Nom': 'DJELOUDANE'},\n",
      " {'IdSportif': 37, 'Nom': 'LAZZARI'},\n",
      " {'IdSportif': 38, 'Nom': 'VASSEMON'},\n",
      " {'IdSportif': 39, 'Nom': 'MOREL'},\n",
      " {'IdSportif': 40, 'Nom': 'VALIN'},\n",
      " {'IdSportif': 41, 'Nom': 'DELOVINA'},\n",
      " {'IdSportif': 42, 'Nom': 'LEHOUX'},\n",
      " {'IdSportif': 43, 'Nom': 'MATHIEU'},\n",
      " {'IdSportif': 44, 'Nom': 'ADIBO'},\n",
      " {'IdSportif': 45, 'Nom': 'CHAVANT'},\n",
      " {'IdSportif': 46, 'Nom': 'DAUXIAN'},\n",
      " {'IdSportif': 47, 'Nom': 'GROEN'},\n",
      " {'IdSportif': 48, 'Nom': 'HEDDI'},\n",
      " {'IdSportif': 49, 'Nom': 'JOUVE'},\n",
      " {'IdSportif': 50, 'Nom': 'KALOMBO'},\n",
      " {'IdSportif': 51, 'Nom': 'LOPEZ'},\n",
      " {'IdSportif': 52, 'Nom': 'DANDOIS'},\n",
      " {'IdSportif': 53, 'Nom': 'DEMMER'},\n",
      " {'IdSportif': 54, 'Nom': 'ELKABBADJ'},\n",
      " {'IdSportif': 55, 'Nom': 'FEROLDI'},\n",
      " {'IdSportif': 56, 'Nom': 'GUERRAOUI'},\n",
      " {'IdSportif': 57, 'Nom': 'BOISSEAU'},\n",
      " {'IdSportif': 58, 'Nom': 'GUIGUI'},\n",
      " {'IdSportif': 59, 'Nom': 'CLERICE'},\n",
      " {'IdSportif': 61, 'Nom': 'LAZARRE'},\n",
      " {'IdSportif': 63, 'Nom': 'AMAND'},\n",
      " {'IdSportif': 64, 'Nom': 'LANOE'},\n",
      " {'IdSportif': 65, 'Nom': 'CHESNIER'},\n",
      " {'IdSportif': 66, 'Nom': 'DURIEU'},\n",
      " {'IdSportif': 67, 'Nom': 'VONTHRON'},\n",
      " {'IdSportif': 68, 'Nom': 'REGNAULD'},\n",
      " {'IdSportif': 69, 'Nom': 'MARIE'},\n",
      " {'IdSportif': 70, 'Nom': 'BELLAMY'},\n",
      " {'IdSportif': 71, 'Nom': 'DELAROCHE'},\n",
      " {'IdSportif': 72, 'Nom': 'MARTEL'},\n",
      " {'IdSportif': 73, 'Nom': 'DALLIER'},\n",
      " {'IdSportif': 74, 'Nom': 'AUVRAY'},\n",
      " {'IdSportif': 76, 'Nom': 'HALGATTE'},\n",
      " {'IdSportif': 77, 'Nom': 'HEON'},\n",
      " {'IdSportif': 78, 'Nom': 'CHAUVIN'},\n",
      " {'IdSportif': 79, 'Nom': 'HENRY'},\n",
      " {'IdSportif': 80, 'Nom': 'HOCHET'},\n",
      " {'IdSportif': 81, 'Nom': 'DROULLON'},\n",
      " {'IdSportif': 83, 'Nom': 'LEROUX'},\n",
      " {'IdSportif': 84, 'Nom': 'SALLAÏ'},\n",
      " {'IdSportif': 85, 'Nom': 'HAMARD'},\n",
      " {'IdSportif': 86, 'Nom': 'GALLOT'},\n",
      " {'IdSportif': 87, 'Nom': 'COUESBOT'},\n",
      " {'IdSportif': 88, 'Nom': 'LEJEUNE'},\n",
      " {'IdSportif': 89, 'Nom': 'RIQUIER'},\n",
      " {'IdSportif': 90, 'Nom': 'DUREL'},\n",
      " {'IdSportif': 91, 'Nom': 'LECHEVALIER'},\n",
      " {'IdSportif': 92, 'Nom': 'HERVIEU'},\n",
      " {'IdSportif': 93, 'Nom': 'CAUCHARD'},\n",
      " {'IdSportif': 95, 'Nom': 'EUSTACHE'},\n",
      " {'IdSportif': 96, 'Nom': 'JANY'},\n",
      " {'IdSportif': 97, 'Nom': 'BONHOMME'},\n",
      " {'IdSportif': 98, 'Nom': 'RICHARD'},\n",
      " {'IdSportif': 100, 'Nom': 'LESOIF'},\n",
      " {'IdSportif': 101, 'Nom': 'SWERTVAEGER'},\n",
      " {'IdSportif': 102, 'Nom': 'DUVAL'},\n",
      " {'IdSportif': 103, 'Nom': 'LEMENOREL'},\n",
      " {'IdSportif': 104, 'Nom': 'SEHIER'},\n",
      " {'IdSportif': 105, 'Nom': 'STILO'},\n",
      " {'IdSportif': 106, 'Nom': 'LE BANNARD'},\n",
      " {'IdSportif': 107, 'Nom': 'BORNE'},\n",
      " {'IdSportif': 108, 'Nom': 'LAURENCE'},\n",
      " {'IdSportif': 109, 'Nom': 'SAUVAGE'},\n",
      " {'IdSportif': 110, 'Nom': 'ROULLAND'},\n",
      " {'IdSportif': 111, 'Nom': 'LESIEUR'},\n",
      " {'IdSportif': 112, 'Nom': 'LUYCKX'},\n",
      " {'IdSportif': 113, 'Nom': 'AVICE'},\n",
      " {'IdSportif': 114, 'Nom': 'MICHEL'},\n",
      " {'IdSportif': 115, 'Nom': 'LEMOUSSU'},\n",
      " {'IdSportif': 116, 'Nom': 'BELUAU'},\n",
      " {'IdSportif': 117, 'Nom': 'FERREIRA'},\n",
      " {'IdSportif': 118, 'Nom': 'FROMENT'},\n",
      " {'IdSportif': 119, 'Nom': 'GUITON'},\n",
      " {'IdSportif': 120, 'Nom': 'LECOMTE'},\n",
      " {'IdSportif': 121, 'Nom': 'HUET'},\n",
      " {'IdSportif': 122, 'Nom': 'BECQUET'},\n",
      " {'IdSportif': 123, 'Nom': 'LETHIMONNIER'},\n",
      " {'IdSportif': 124, 'Nom': 'SWERTVAEGER'},\n",
      " {'IdSportif': 125, 'Nom': 'JALON'},\n",
      " {'IdSportif': 126, 'Nom': 'DEBOUT'},\n",
      " {'IdSportif': 127, 'Nom': 'GASTELLIER'},\n",
      " {'IdSportif': 128, 'Nom': 'GIRONIE'},\n",
      " {'IdSportif': 129, 'Nom': 'DABON'},\n",
      " {'IdSportif': 130, 'Nom': 'LECHAUVE'},\n",
      " {'IdSportif': 131, 'Nom': 'GALLOIS'},\n",
      " {'IdSportif': 132, 'Nom': 'DORON'},\n",
      " {'IdSportif': 133, 'Nom': 'LENEVEU'},\n",
      " {'IdSportif': 134, 'Nom': 'LERICHE'},\n",
      " {'IdSportif': 135, 'Nom': 'MANCEL'},\n",
      " {'IdSportif': 136, 'Nom': 'LABOULAIS'},\n",
      " {'IdSportif': 137, 'Nom': 'DUDOUIT'},\n",
      " {'IdSportif': 138, 'Nom': 'MADELAINE'},\n",
      " {'IdSportif': 139, 'Nom': 'BESNARD'},\n",
      " {'IdSportif': 140, 'Nom': 'BELZ'},\n",
      " {'IdSportif': 141, 'Nom': 'BONNET'},\n",
      " {'IdSportif': 142, 'Nom': 'CORNET'},\n",
      " {'IdSportif': 143, 'Nom': 'BEUZELIN'},\n",
      " {'IdSportif': 144, 'Nom': 'GRANDIDIER'},\n",
      " {'IdSportif': 145, 'Nom': 'LENEVEU'},\n",
      " {'IdSportif': 146, 'Nom': 'DABON'},\n",
      " {'IdSportif': 147, 'Nom': 'CLERICE'},\n",
      " {'IdSportif': 148, 'Nom': 'COMES'},\n",
      " {'IdSportif': 149, 'Nom': 'BELZ'},\n",
      " {'IdSportif': 150, 'Nom': 'BELZ'},\n",
      " {'IdSportif': 151, 'Nom': 'HENRY'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Sportifs.find({\"Age\": { \"$gte\": 20, \"$lte\": 30 }},\n",
    "                      {\"_id\": 0, \"IdSportif\": 1, \"Nom\": 1})\n",
    "\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2. Quels sont les gymnases de \"Villetaneuse\" ou de \"Sarcelles\" qui ont une surface de plus de 400 m2 ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'NomGymnase': 'PAUL ELUARD', 'Surface': 500, 'Ville': 'SARCELLES'},\n",
      " {'NomGymnase': 'BRASSENS', 'Surface': 620, 'Ville': 'SARCELLES'},\n",
      " {'NomGymnase': 'CAMUS', 'Surface': 620, 'Ville': 'SARCELLES'},\n",
      " {'NomGymnase': 'SAMOURAI', 'Surface': 600, 'Ville': 'SARCELLES'},\n",
      " {'NomGymnase': 'DOLTO', 'Surface': 620, 'Ville': 'VILLETANEUSE'},\n",
      " {'NomGymnase': 'MERMOZ', 'Surface': 600, 'Ville': 'VILLETANEUSE'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Gymnases.find(\n",
    "    {\n",
    "        \"Ville\": { \"$in\": [ \"VILLETANEUSE\", \"SARCELLES\"]},\n",
    "        \"Surface\": { \"$gt\": 400 }\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"NomGymnase\": 1,\n",
    "        \"Ville\": 1,\n",
    "        \"Surface\": 1\n",
    "    })\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3. Quels sont les sportifs (identifiant et nom) qui pratiquent du hand ball ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'IdSportif': 6, 'Nom': 'RETALDI'},\n",
      " {'IdSportif': 19, 'Nom': 'TESTEMONT'},\n",
      " {'IdSportif': 20, 'Nom': 'TIENER'},\n",
      " {'IdSportif': 25, 'Nom': 'MARTORA'},\n",
      " {'IdSportif': 26, 'Nom': 'MICHEL'},\n",
      " {'IdSportif': 27, 'Nom': 'NIELLEZ'},\n",
      " {'IdSportif': 28, 'Nom': 'RABAHI'},\n",
      " {'IdSportif': 29, 'Nom': 'ROUSSEL'},\n",
      " {'IdSportif': 30, 'Nom': 'SCHINK'},\n",
      " {'IdSportif': 31, 'Nom': 'STEMPUT'},\n",
      " {'IdSportif': 32, 'Nom': 'VAN CAUTER'},\n",
      " {'IdSportif': 33, 'Nom': 'RAMPNOUX'},\n",
      " {'IdSportif': 34, 'Nom': 'LHERPINIERE'},\n",
      " {'IdSportif': 35, 'Nom': 'TANQUE'},\n",
      " {'IdSportif': 38, 'Nom': 'VASSEMON'},\n",
      " {'IdSportif': 39, 'Nom': 'MOREL'},\n",
      " {'IdSportif': 40, 'Nom': 'VALIN'},\n",
      " {'IdSportif': 43, 'Nom': 'MATHIEU'},\n",
      " {'IdSportif': 51, 'Nom': 'LOPEZ'},\n",
      " {'IdSportif': 60, 'Nom': 'TIZEGHAT'},\n",
      " {'IdSportif': 67, 'Nom': 'VONTHRON'},\n",
      " {'IdSportif': 68, 'Nom': 'REGNAULD'},\n",
      " {'IdSportif': 69, 'Nom': 'MARIE'},\n",
      " {'IdSportif': 72, 'Nom': 'MARTEL'},\n",
      " {'IdSportif': 75, 'Nom': 'SARRAZIN'},\n",
      " {'IdSportif': 83, 'Nom': 'LEROUX'},\n",
      " {'IdSportif': 84, 'Nom': 'SALLAÏ'},\n",
      " {'IdSportif': 89, 'Nom': 'RIQUIER'},\n",
      " {'IdSportif': 94, 'Nom': 'LEROUX'},\n",
      " {'IdSportif': 98, 'Nom': 'RICHARD'},\n",
      " {'IdSportif': 100, 'Nom': 'LESOIF'},\n",
      " {'IdSportif': 101, 'Nom': 'SWERTVAEGER'},\n",
      " {'IdSportif': 104, 'Nom': 'SEHIER'},\n",
      " {'IdSportif': 105, 'Nom': 'STILO'},\n",
      " {'IdSportif': 109, 'Nom': 'SAUVAGE'},\n",
      " {'IdSportif': 110, 'Nom': 'ROULLAND'},\n",
      " {'IdSportif': 111, 'Nom': 'LESIEUR'},\n",
      " {'IdSportif': 112, 'Nom': 'LUYCKX'},\n",
      " {'IdSportif': 114, 'Nom': 'MICHEL'},\n",
      " {'IdSportif': 123, 'Nom': 'LETHIMONNIER'},\n",
      " {'IdSportif': 124, 'Nom': 'SWERTVAEGER'},\n",
      " {'IdSportif': 135, 'Nom': 'MANCEL'},\n",
      " {'IdSportif': 138, 'Nom': 'MADELAINE'},\n",
      " {'IdSportif': 151, 'Nom': 'HENRY'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Sportifs.find({\n",
    "    \"Sports.Jouer\": \"Hand ball\"\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"IdSportif\": 1,\n",
    "        \"Nom\": 1\n",
    "    \n",
    "})\n",
    "\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4. Dans quels gymnases et quels jours y a t-il des séances de hand ball ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'NomGymnase': 'PAUL ELUARD',\n",
      "  'Seances': [{'Jour': 'Samedi', 'Libelle': 'Basket ball'},\n",
      "              {'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Dimanche', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Dimanche', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'mardi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'mercredi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Samedi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Samedi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'Samedi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'jeudi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'STAINS'},\n",
      " {'NomGymnase': 'GARCIA LORCA',\n",
      "  'Seances': [{'Jour': 'samedi', 'Libelle': 'Volley ball'},\n",
      "              {'Jour': 'vendredi', 'Libelle': 'Volley ball'},\n",
      "              {'Jour': 'dimanche', 'Libelle': 'Volley ball'},\n",
      "              {'Jour': 'dimanche', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'mardi', 'Libelle': 'Hand ball'},\n",
      "              {'Jour': 'mardi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'STAINS'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Gymnases.find(\n",
    "    {\n",
    "        \"Seances.Libelle\": \"Hand ball\"\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"NomGymnase\": 1,\n",
    "        \"Ville\": 1,\n",
    "        \"Seances.Jour\": 1,\n",
    "        \"Seances.Libelle\": 1\n",
    "    }\n",
    ")\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'_id': {'Jour': 'lundi', 'Ville': 'STAINS'}, 'nb': 10},\n",
      " {'_id': {'Jour': 'samedi', 'Ville': 'STAINS'}, 'nb': 3},\n",
      " {'_id': {'Jour': 'dimanche', 'Ville': 'STAINS'}, 'nb': 3},\n",
      " {'_id': {'Jour': 'mardi', 'Ville': 'STAINS'}, 'nb': 2},\n",
      " {'_id': {'Jour': 'jeudi', 'Ville': 'STAINS'}, 'nb': 1},\n",
      " {'_id': {'Jour': 'mercredi', 'Ville': 'STAINS'}, 'nb': 1}]\n"
     ]
    }
   ],
   "source": [
    "# **Problème** d'affichage des jours\n",
    "\n",
    "res = db.Gymnases.aggregate([\n",
    "    { \"$unwind\" : \"$Seances\" },\n",
    "    { \"$match\": { \"Seances.Libelle\" : \"Hand ball\" }},\n",
    "    { \"$group\": {\n",
    "        \"_id\": { \n",
    "            \"Nom\": \"$Gymnase\", \n",
    "            \"Ville\": \"$Ville\", \n",
    "            \"Jour\": { \"$toLower\": \"$Seances.Jour\"} \n",
    "        },\n",
    "        \"nb\": { \"$sum\": 1 }\n",
    "    }},\n",
    "    { \"$sort\": {\n",
    "        \"_id.Ville\": 1,\n",
    "        \"_id.Nom\": 1,\n",
    "        \"nb\": -1\n",
    "    }}\n",
    "])\n",
    "\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'Jours': ['mardi', 'dimanche'],\n",
      "  '_id': {'Gymnase': 'GARCIA LORCA', 'Ville': 'STAINS'}},\n",
      " {'Jours': ['jeudi', 'samedi', 'lundi', 'dimanche', 'mercredi', 'mardi'],\n",
      "  '_id': {'Gymnase': 'PAUL ELUARD', 'Ville': 'STAINS'}}]\n"
     ]
    }
   ],
   "source": [
    "# ou \n",
    "res = db.Gymnases.aggregate([\n",
    "    { \"$unwind\": \"$Seances\" },\n",
    "    { \"$match\": { \"Seances.Libelle\": \"Hand ball\" }},\n",
    "    { \"$group\": {\n",
    "        \"_id\": { \"Ville\": \"$Ville\", \"Gymnase\": \"$NomGymnase\" },\n",
    "        \"Jours\": { \"$addToSet\": { \"$toLower\": \"$Seances.Jour\" } }\n",
    "    }}\n",
    "])\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5. Dans quels gymnases peut-on jouer au hockey le mercredi apres 15H ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'NomGymnase': 'PAUL ELUARD',\n",
      "  'Seances': [{'Horaire': 9.0, 'Jour': 'Samedi', 'Libelle': 'Basket ball'},\n",
      "              {'Horaire': 9.0, 'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 10.0, 'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 11.3, 'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 14.0, 'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 17.3, 'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 19.3, 'Jour': 'Lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 17.3, 'Jour': 'Dimanche', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 19.3, 'Jour': 'Dimanche', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 17.3, 'Jour': 'mardi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 17.3, 'Jour': 'mercredi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 15.3, 'Jour': 'Samedi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 16.3, 'Jour': 'Samedi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 17.3, 'Jour': 'Samedi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 20.0, 'Jour': 'jeudi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 14.0, 'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 18.0, 'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 19.0, 'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 20.0, 'Jour': 'lundi', 'Libelle': 'Hand ball'},\n",
      "              {'Horaire': 17.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'STAINS'},\n",
      " {'NomGymnase': 'PAUL ELUARD',\n",
      "  'Seances': [{'Horaire': 10.0, 'Jour': 'Vendredi', 'Libelle': 'Basket ball'},\n",
      "              {'Horaire': 19.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'SARCELLES'},\n",
      " {'NomGymnase': 'RAVEL',\n",
      "  'Seances': [{'Horaire': 19.0, 'Jour': 'dimanche', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 20.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'},\n",
      "              {'Horaire': 17.0, 'Jour': 'lundi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'STAINS'},\n",
      " {'NomGymnase': 'SAMOURAI',\n",
      "  'Seances': [{'Horaire': 16.3, 'Jour': 'lundi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 17.0, 'Jour': 'Lundi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 18.0, 'Jour': 'Lundi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 19.0, 'Jour': 'lundi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 20.0, 'Jour': 'Lundi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 19.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'SARCELLES'},\n",
      " {'NomGymnase': 'PABLO NERUDA',\n",
      "  'Seances': [{'Horaire': 16.3, 'Jour': 'lundi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 19.0, 'Jour': 'mardi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 14.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'},\n",
      "              {'Horaire': 16.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'PIERREFITTE'},\n",
      " {'NomGymnase': 'LUMIERES',\n",
      "  'Seances': [{'Horaire': 19.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'SARCELLES'},\n",
      " {'NomGymnase': 'SIMON',\n",
      "  'Seances': [{'Horaire': 16.3, 'Jour': 'lundi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 19.0, 'Jour': 'mardi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 17.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'STAINS'},\n",
      " {'NomGymnase': 'PASCAL',\n",
      "  'Seances': [{'Horaire': 10.0, 'Jour': 'jeudi', 'Libelle': 'Volley ball'},\n",
      "              {'Horaire': 14.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'},\n",
      "              {'Horaire': 17.0, 'Jour': 'mercredi', 'Libelle': 'Hockey'}],\n",
      "  'Ville': 'VILLETANEUSE'}]\n"
     ]
    }
   ],
   "source": [
    "res = db.Gymnases.find(\n",
    "    {\n",
    "        \"Seances.Libelle\": \"Hockey\",\n",
    "        \"Seances.Jour\" : { \"$in\": [ \"mercredi\", \"Mercredi\" ]},\n",
    "        \"Seances.Horaire\": { \"$gte\" : 15 }\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"NomGymnase\": 1,\n",
    "        \"Ville\": 1,\n",
    "        \"Seances.Jour\": 1,\n",
    "        \"Seances.Libelle\": 1,\n",
    "        \"Seances.Horaire\": 1\n",
    "    }\n",
    ")\n",
    "\n",
    "affiche(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Gymnase': 'PABLO NERUDA', 'Ville': 'PIERREFITTE'},\n",
       " {'Gymnase': 'LUMIERES', 'Ville': 'SARCELLES'},\n",
       " {'Gymnase': 'PAUL ELUARD', 'Ville': 'SARCELLES'},\n",
       " {'Gymnase': 'SAMOURAI', 'Ville': 'SARCELLES'},\n",
       " {'Gymnase': 'PAUL ELUARD', 'Ville': 'STAINS'},\n",
       " {'Gymnase': 'RAVEL', 'Ville': 'STAINS'},\n",
       " {'Gymnase': 'SIMON', 'Ville': 'STAINS'},\n",
       " {'Gymnase': 'PASCAL', 'Ville': 'VILLETANEUSE'}]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# **Problème** de sélection (visible si on fait `$lte`)\n",
    "list(db.Gymnases.aggregate([\n",
    "    { \"$unwind\": \"$Seances\" },\n",
    "    { \"$match\": {\n",
    "        \"Seances.Libelle\" : \"Hockey\",\n",
    "        \"Seances.Jour\": { \"$in\": [ \"mercredi\", \"Mercredi\" ]},\n",
    "        \"Seances.Horaire\": { \"$gte\": 15 }\n",
    "    }},\n",
    "    { \"$project\": {\n",
    "        \"_id\": 0,\n",
    "        \"Gymnase\" : \"$NomGymnase\", \n",
    "        \"Ville\" : \"$Ville\"\n",
    "    }},\n",
    "    { \"$sort\": {\n",
    "        \"Ville\": 1,\n",
    "        \"Gymnase\": 1\n",
    "    }}\n",
    "]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6. Quels sportifs (identifiant et nom) ne pratiquent aucun sport ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'DROULLON'},\n",
       " {'Nom': 'BELUAU'},\n",
       " {'Nom': 'FERREIRA'},\n",
       " {'Nom': 'GALLOIS'},\n",
       " {'Nom': 'LENEVEU'},\n",
       " {'Nom': 'DABON'},\n",
       " {'Nom': 'CLERICE'},\n",
       " {'Nom': 'COMES'},\n",
       " {'Nom': 'BELZ'}]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"Sports\" : { \"$exists\" : False }\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\":  1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**7. Quels gymnases n'ont pas de séances le dimanche ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'NomGymnase': 'SAINT EXUPERY',\n",
       "  'Ville': 'PIERREFITTE',\n",
       "  'Seances': [{'Jour': 'Mercredi'}, {'Jour': 'lundi'}, {'Jour': 'jeudi'}]},\n",
       " {'NomGymnase': 'PAUL ELUARD',\n",
       "  'Ville': 'SARCELLES',\n",
       "  'Seances': [{'Jour': 'Vendredi'}, {'Jour': 'mercredi'}]},\n",
       " {'NomGymnase': 'BRASSENS',\n",
       "  'Ville': 'SARCELLES',\n",
       "  'Seances': [{'Jour': 'lundi'}, {'Jour': 'jeudi'}]},\n",
       " {'NomGymnase': 'VERLAINE',\n",
       "  'Ville': 'STAINS',\n",
       "  'Seances': [{'Jour': 'vendredi'}, {'Jour': 'jeudi'}]},\n",
       " {'NomGymnase': 'JULES FERRY', 'Ville': 'PIERREFITTE'},\n",
       " {'NomGymnase': 'CAMUS',\n",
       "  'Ville': 'SARCELLES',\n",
       "  'Seances': [{'Jour': 'samedi'}]},\n",
       " {'NomGymnase': 'LAMARTINE', 'Ville': 'PIERREFITTE'},\n",
       " {'NomGymnase': 'BREL',\n",
       "  'Ville': 'PIERREFITTE',\n",
       "  'Seances': [{'Jour': 'lundi'}]},\n",
       " {'NomGymnase': 'SAMOURAI',\n",
       "  'Ville': 'SARCELLES',\n",
       "  'Seances': [{'Jour': 'lundi'},\n",
       "   {'Jour': 'Lundi'},\n",
       "   {'Jour': 'Lundi'},\n",
       "   {'Jour': 'lundi'},\n",
       "   {'Jour': 'Lundi'},\n",
       "   {'Jour': 'mercredi'}]},\n",
       " {'NomGymnase': 'PABLO NERUDA',\n",
       "  'Ville': 'PIERREFITTE',\n",
       "  'Seances': [{'Jour': 'lundi'},\n",
       "   {'Jour': 'mardi'},\n",
       "   {'Jour': 'mercredi'},\n",
       "   {'Jour': 'mercredi'}]},\n",
       " {'NomGymnase': 'LUMIERES',\n",
       "  'Ville': 'SARCELLES',\n",
       "  'Seances': [{'Jour': 'mercredi'}]},\n",
       " {'NomGymnase': 'SIMON',\n",
       "  'Ville': 'STAINS',\n",
       "  'Seances': [{'Jour': 'lundi'}, {'Jour': 'mardi'}, {'Jour': 'mercredi'}]},\n",
       " {'NomGymnase': 'BARBARA',\n",
       "  'Ville': 'SAINT DENIS',\n",
       "  'Seances': [{'Jour': 'mardi'}]},\n",
       " {'NomGymnase': 'ARAGON', 'Ville': 'SAINT DENIS'},\n",
       " {'NomGymnase': 'BELFEGOR',\n",
       "  'Ville': 'SAINT DENIS',\n",
       "  'Seances': [{'Jour': 'Jeudi'}, {'Jour': 'mercredi'}]},\n",
       " {'NomGymnase': 'MERMOZ', 'Ville': 'VILLETANEUSE'},\n",
       " {'NomGymnase': 'PASCAL',\n",
       "  'Ville': 'VILLETANEUSE',\n",
       "  'Seances': [{'Jour': 'jeudi'}, {'Jour': 'mercredi'}, {'Jour': 'mercredi'}]}]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Gymnases.find(\n",
    "    {\n",
    "        \"Seances.Jour\" : { \"$nin\" : [ \"dimanche\", \"Dimanche\" ]}\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"NomGymnase\": 1,\n",
    "        \"Ville\": 1,\n",
    "        \"Seances.Jour\": 1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**8. Quels gymnases ne proposent que des séances de basket ball ou de volley ball ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'NomGymnase': 'SAINT EXUPERY',\n",
       "  'Ville': 'PIERREFITTE',\n",
       "  'Seances': [{'Libelle': 'Basket ball'},\n",
       "   {'Libelle': 'Volley ball'},\n",
       "   {'Libelle': 'Volley ball'}]},\n",
       " {'NomGymnase': 'CHOPIN',\n",
       "  'Ville': 'MONTMORENCY',\n",
       "  'Seances': [{'Libelle': 'Basket ball'}, {'Libelle': 'Volley ball'}]},\n",
       " {'NomGymnase': 'BELFEGOR',\n",
       "  'Ville': 'SAINT DENIS',\n",
       "  'Seances': [{'Libelle': 'Basket ball'}, {'Libelle': 'Volley ball'}]}]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Gymnases.find(\n",
    "    {\n",
    "        \"$nor\": [\n",
    "            { \"Seances.Libelle\": { \"$ne\": \"Basket ball\" }},\n",
    "            { \"Seances.Libelle\": { \"$ne\": \"Volley ball\" }}\n",
    "        ]\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"NomGymnase\": 1,\n",
    "        \"Ville\": 1,\n",
    "        \"Seances.Libelle\": 1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**9. Quels sont les entraîneurs qui sont aussi joueurs ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'BOUTAHAR'},\n",
       " {'Nom': 'KERVADEC'},\n",
       " {'Nom': 'HUE'},\n",
       " {'Nom': 'DORLEANS'},\n",
       " {'Nom': 'RETALDI'},\n",
       " {'Nom': 'GOMEZ'},\n",
       " {'Nom': 'ROUSSEL'},\n",
       " {'Nom': 'SCHINK'},\n",
       " {'Nom': 'STEMPUT'},\n",
       " {'Nom': 'VAN CAUTER'},\n",
       " {'Nom': 'TANQUE'},\n",
       " {'Nom': 'DJELOUDANE'},\n",
       " {'Nom': 'VASSEMON'},\n",
       " {'Nom': 'VALIN'},\n",
       " {'Nom': 'HEDDI'},\n",
       " {'Nom': 'KALOMBO'},\n",
       " {'Nom': 'GUERRAOUI'},\n",
       " {'Nom': 'BOISSEAU'},\n",
       " {'Nom': 'GUIGUI'},\n",
       " {'Nom': 'CLERICE'},\n",
       " {'Nom': 'TIZEGHAT'},\n",
       " {'Nom': 'LAZARRE'},\n",
       " {'Nom': 'BELZ'},\n",
       " {'Nom': 'HENRY'}]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"Sports.Jouer\" : { \"$exists\" : True },\n",
    "        \"Sports.Entrainer\" : { \"$exists\" : True }\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\":  1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**10. Quels sont les sportifs qui sont des conseillers ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'BOUTAHAR'},\n",
       " {'Nom': 'KERVADEC'},\n",
       " {'Nom': 'HUE'},\n",
       " {'Nom': 'DORLEANS'},\n",
       " {'Nom': 'COMES'},\n",
       " {'Nom': 'RETALDI'},\n",
       " {'Nom': 'GOMEZ'},\n",
       " {'Nom': 'ANTUNES'},\n",
       " {'Nom': 'CAILLIOT'},\n",
       " {'Nom': 'LE BOUCHER'},\n",
       " {'Nom': 'ADIBO'},\n",
       " {'Nom': 'RICHARD'}]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"IdSportif\": { \"$in\": db.Sportifs.distinct(\"IdSportifConseiller\")}\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\":  1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**11. Pour le sportif \"Kervadec\" quel est le nom de son conseiller ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'IdSportif': 1,\n",
       "  'Nom': 'BOUTAHAR',\n",
       "  'Prenom': 'Abderahim',\n",
       "  'Sexe': 'm',\n",
       "  'Age': 30}]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"IdSportif\": list(db.Sportifs.find({ \"Nom\": \"KERVADEC\" }))[0][\"IdSportifConseiller\"]\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Sports\": 0\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**12. Quels entraîneurs entraînent du hand ball et du basket ball ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'BOUTAHAR',\n",
       "  'Sports': {'Entrainer': ['Basket ball',\n",
       "    'Volley ball',\n",
       "    'Hand ball',\n",
       "    'Hockey',\n",
       "    'Badmington']}},\n",
       " {'Nom': 'KERVADEC',\n",
       "  'Sports': {'Entrainer': ['Basket ball',\n",
       "    'Volley ball',\n",
       "    'Hand ball',\n",
       "    'Tennis',\n",
       "    'Hockey',\n",
       "    'Badmington',\n",
       "    'Ping pong',\n",
       "    'Boxe']}},\n",
       " {'Nom': 'HUE',\n",
       "  'Sports': {'Entrainer': ['Basket ball',\n",
       "    'Volley ball',\n",
       "    'Hand ball',\n",
       "    'Badmington']}},\n",
       " {'Nom': 'DORLEANS',\n",
       "  'Sports': {'Entrainer': ['Basket ball', 'Ping pong', 'Boxe']}},\n",
       " {'Nom': 'BELZ', 'Sports': {'Entrainer': 'Basket ball'}},\n",
       " {'Nom': 'HENRY', 'Sports': {'Entrainer': ['Basket ball', 'Hand ball']}}]"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"Sports.Entrainer\": \"Hand ball\", \n",
    "        \"Sports.Entrainer\": \"Basket ball\" \n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\": 1,\n",
    "        \"Sports.Entrainer\": 1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'BOUTAHAR',\n",
       "  'Sports': {'Entrainer': ['Basket ball',\n",
       "    'Volley ball',\n",
       "    'Hand ball',\n",
       "    'Hockey',\n",
       "    'Badmington']}},\n",
       " {'Nom': 'KERVADEC',\n",
       "  'Sports': {'Entrainer': ['Basket ball',\n",
       "    'Volley ball',\n",
       "    'Hand ball',\n",
       "    'Tennis',\n",
       "    'Hockey',\n",
       "    'Badmington',\n",
       "    'Ping pong',\n",
       "    'Boxe']}},\n",
       " {'Nom': 'HUE',\n",
       "  'Sports': {'Entrainer': ['Basket ball',\n",
       "    'Volley ball',\n",
       "    'Hand ball',\n",
       "    'Badmington']}},\n",
       " {'Nom': 'HENRY', 'Sports': {'Entrainer': ['Basket ball', 'Hand ball']}}]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"$and\": [\n",
    "            { \"Sports.Entrainer\": \"Hand ball\" }, \n",
    "            { \"Sports.Entrainer\": \"Basket ball\" }\n",
    "        ]        \n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\": 1,\n",
    "        \"Sports.Entrainer\": 1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**13. ~~Quels sont les couples de sportifs (identifiant et nom et prénom de chaque) de même age ?~~**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**14. Quelle est la moyenne d'âge des sportives qui pratiquent du basket ball ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': None, 'AgeMoyen': 24.6}]"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.aggregate([\n",
    "    { \"$match\": { \"Sports.Jouer\": \"Basket ball\", \"Sexe\": { \"$in\": [ \"f\", \"F\" ]} }},\n",
    "    { \"$group\": { \"_id\": None, \"AgeMoyen\": { \"$avg\": \"$Age\" }}}\n",
    "]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**15. Quels sont les sportifs les plus jeunes ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': None, 'agemin': 22}"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On stocke le résultat de l'aggrégation dans une variable.\n",
    "agemin = list(db.Sportifs.aggregate([ \n",
    "    { \"$group\": { \"_id\": None, \"agemin\": { \"$min\": \"$Age\" } } } \n",
    "]))[0]\n",
    "agemin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'COMES', 'Age': 22},\n",
       " {'Nom': 'BOYON', 'Age': 22},\n",
       " {'Nom': 'COLOMB', 'Age': 22},\n",
       " {'Nom': 'LE BOUCHER', 'Age': 22},\n",
       " {'Nom': 'ROUSSEL', 'Age': 22},\n",
       " {'Nom': 'STEMPUT', 'Age': 22},\n",
       " {'Nom': 'DELOVINA', 'Age': 22},\n",
       " {'Nom': 'KALOMBO', 'Age': 22},\n",
       " {'Nom': 'DANDOIS', 'Age': 22},\n",
       " {'Nom': 'DEMMER', 'Age': 22},\n",
       " {'Nom': 'MARTEL', 'Age': 22},\n",
       " {'Nom': 'HERVIEU', 'Age': 22},\n",
       " {'Nom': 'MICHEL', 'Age': 22}]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On cherche les sportifs avec `Age = agemin.agemin`.\n",
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"Age\": agemin[\"agemin\"]\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\": 1,\n",
    "        \"Age\": 1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**16. Quels sont les gymnases de \"Stains\" ou de \"Montmorency\" qui ont la plus grande surface ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': 'SAINT DENIS', 'surfmax': 520},\n",
       " {'_id': 'STAINS', 'surfmax': 400},\n",
       " {'_id': 'MONTMORENCY', 'surfmax': 500},\n",
       " {'_id': 'GARGES', 'surfmax': 400},\n",
       " {'_id': 'VILLETANEUSE', 'surfmax': 620},\n",
       " {'_id': 'PIERREFITTE', 'surfmax': 450},\n",
       " {'_id': 'SARCELLES', 'surfmax': 620}]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On peut chercher les plus grands gymnases de chaque ville. \n",
    "grand = list(db.Gymnases.aggregate([\n",
    "    { \"$group\": { \"_id\": \"$Ville\", \"surfmax\": { \"$max\": \"$Surface\" }} }\n",
    "]))\n",
    "grand"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**17. Quels entraîneurs n'entraînent que du hand ball ou du basket ball ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Badmington',\n",
       " 'Basket ball',\n",
       " 'Hand ball',\n",
       " 'Hockey',\n",
       " 'Volley ball',\n",
       " 'Boxe',\n",
       " 'Ping pong',\n",
       " 'Tennis']"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On récupère la liste des sports entraînés.\n",
    "sports = db.Sportifs.distinct(\"Sports.Entrainer\")\n",
    "sports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Badmington', 'Hockey', 'Volley ball', 'Boxe', 'Ping pong', 'Tennis']"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On fait un filtre pour supprimer le hand et le basket.\n",
    "autres = list(filter(lambda s: (s != \"Hand ball\") & (s != \"Basket ball\"), sports))\n",
    "autres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'BELZ', 'Sports': {'Entrainer': 'Basket ball'}},\n",
       " {'Nom': 'HENRY', 'Sports': {'Entrainer': ['Basket ball', 'Hand ball']}}]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On cherche ceux qui entraîne l'un ou l'autre, mais pas d'autres sports.\n",
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"$and\": [\n",
    "            { \"$or\": [\n",
    "                { \"Sports.Entrainer\": \"Hand ball\" },\n",
    "                { \"Sports.Entrainer\": \"Basket ball\" }\n",
    "            ] },\n",
    "            { \"Sports.Entrainer\" : { \"$nin\" : autres }}\n",
    "        ]\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\": 1,\n",
    "        \"Sports.Entrainer\": 1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**18. ~~Quels sont les couples de sportifs (identifiant et nom et prénom de chaque) de même âge avec le même conseiller ?~~**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**19. Quels sportifs n'ont pas de conseillers ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Nom': 'BOUTAHAR'},\n",
       " {'Nom': 'CHAVANT'},\n",
       " {'Nom': 'CLERICE'},\n",
       " {'Nom': 'CHESNIER'},\n",
       " {'Nom': 'CHAUVIN'},\n",
       " {'Nom': 'RIQUIER'},\n",
       " {'Nom': 'BONHOMME'},\n",
       " {'Nom': 'BONE'}]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.find(\n",
    "    {\n",
    "        \"IdSportifConseiller\": { \"$exists\": False }\n",
    "    },\n",
    "    {\n",
    "        \"_id\": 0,\n",
    "        \"Nom\": 1\n",
    "    }\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**20. Pour chaque sportif donner le nombre de sports qu'il arbitre**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': 'STILO', 'nbArbitrer': 1},\n",
       " {'_id': 'RICHARD', 'nbArbitrer': 1},\n",
       " {'_id': 'LEROUX', 'nbArbitrer': 1},\n",
       " {'_id': 'TIZEGHAT', 'nbArbitrer': 1},\n",
       " {'_id': 'CLERICE', 'nbArbitrer': 1},\n",
       " {'_id': 'TANQUE', 'nbArbitrer': 1},\n",
       " {'_id': 'DORLEANS', 'nbArbitrer': 4},\n",
       " {'_id': 'ROUSSEL', 'nbArbitrer': 1},\n",
       " {'_id': 'BELZ', 'nbArbitrer': 1},\n",
       " {'_id': 'COMES', 'nbArbitrer': 1},\n",
       " {'_id': 'HUE', 'nbArbitrer': 3},\n",
       " {'_id': 'RETALDI', 'nbArbitrer': 3},\n",
       " {'_id': 'KERVADEC', 'nbArbitrer': 2},\n",
       " {'_id': 'GOMEZ', 'nbArbitrer': 3},\n",
       " {'_id': 'VAN CAUTER', 'nbArbitrer': 1},\n",
       " {'_id': 'BOUTAHAR', 'nbArbitrer': 3},\n",
       " {'_id': 'TESTEMONT', 'nbArbitrer': 1},\n",
       " {'_id': 'HENRY', 'nbArbitrer': 2},\n",
       " {'_id': 'TIENER', 'nbArbitrer': 1}]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Sportifs.aggregate([\n",
    "    { \"$match\": { \"Sports.Arbitrer\": { \"$exists\": True }}},\n",
    "    { \"$unwind\": \"$Sports.Arbitrer\"},\n",
    "    { \"$group\": { \"_id\": \"$Nom\", \"nbArbitrer\": { \"$sum\": 1 }}}\n",
    "]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**21. Pour chaque gymnase de Stains donner par jour d'ouverture les horaires des premières et dernières séances**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': {'nom': 'SIMON', 'jour': 'mardi'}, 'debut': 19.0, 'fin': 19.0},\n",
       " {'_id': {'nom': 'SIMON', 'jour': 'lundi'}, 'debut': 16.3, 'fin': 16.3},\n",
       " {'_id': {'nom': 'SIMON', 'jour': 'mercredi'}, 'debut': 17.0, 'fin': 17.0},\n",
       " {'_id': {'nom': 'GARCIA LORCA', 'jour': 'mardi'}, 'debut': 17.0, 'fin': 20.0},\n",
       " {'_id': {'nom': 'GARCIA LORCA', 'jour': 'dimanche'},\n",
       "  'debut': 17.0,\n",
       "  'fin': 18.0},\n",
       " {'_id': {'nom': 'GARCIA LORCA', 'jour': 'samedi'},\n",
       "  'debut': 17.3,\n",
       "  'fin': 17.3},\n",
       " {'_id': {'nom': 'RAVEL', 'jour': 'lundi'}, 'debut': 17.0, 'fin': 17.0},\n",
       " {'_id': {'nom': 'RAVEL', 'jour': 'dimanche'}, 'debut': 19.0, 'fin': 19.0},\n",
       " {'_id': {'nom': 'PAUL ELUARD', 'jour': 'dimanche'},\n",
       "  'debut': 17.3,\n",
       "  'fin': 19.3},\n",
       " {'_id': {'nom': 'PAUL ELUARD', 'jour': 'jeudi'}, 'debut': 20.0, 'fin': 20.0},\n",
       " {'_id': {'nom': 'PAUL ELUARD', 'jour': 'mardi'}, 'debut': 17.3, 'fin': 17.3},\n",
       " {'_id': {'nom': 'PAUL ELUARD', 'jour': 'lundi'}, 'debut': 9.0, 'fin': 20.0},\n",
       " {'_id': {'nom': 'PAUL ELUARD', 'jour': 'mercredi'},\n",
       "  'debut': 17.0,\n",
       "  'fin': 17.3},\n",
       " {'_id': {'nom': 'VERLAINE', 'jour': 'vendredi'}, 'debut': 19.0, 'fin': 19.0},\n",
       " {'_id': {'nom': 'VERLAINE', 'jour': 'jeudi'}, 'debut': 17.0, 'fin': 17.0},\n",
       " {'_id': {'nom': 'RIMBAUD', 'jour': 'samedi'}, 'debut': 19.0, 'fin': 19.0},\n",
       " {'_id': {'nom': 'GARCIA LORCA', 'jour': 'vendredi'},\n",
       "  'debut': 17.3,\n",
       "  'fin': 17.3},\n",
       " {'_id': {'nom': 'RAVEL', 'jour': 'mercredi'}, 'debut': 20.0, 'fin': 20.0},\n",
       " {'_id': {'nom': 'PAUL ELUARD', 'jour': 'samedi'}, 'debut': 9.0, 'fin': 17.3},\n",
       " {'_id': {'nom': 'RIMBAUD', 'jour': 'dimanche'}, 'debut': 17.0, 'fin': 19.0}]"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(db.Gymnases.aggregate([\n",
    "    { \"$match\": { \"Ville\": \"STAINS\" } },\n",
    "    { \"$unwind\": \"$Seances\" },\n",
    "    { \"$project\": { \"nom\": \"$NomGymnase\", \"jour\": { \"$toLower\": \"$Seances.Jour\" }, \"h\": \"$Seances.Horaire\"}},\n",
    "    { \"$group\": { \"_id\": { \"nom\": \"$nom\", \"jour\": \"$jour\" }, \"debut\": { \"$min\": \"$h\"}, \"fin\": { \"$max\": \"$h\" }}}\n",
    "]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**22. Pour chaque entraîneurs de hand ball quel est le nombre de séances journalières qu'il assure ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 2, 3, 7, 151]"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On récupère la liste des identifiants des entraîneurs de Hand.\n",
    "entraineursHand = list(db.Sportifs.find(\n",
    "    { \"Sports.Entrainer\" : \"Hand ball\" }, \n",
    "    { \"_id\": 0, \"IdSportif\": 1 }\n",
    "))\n",
    "[e[\"IdSportif\"] for e in entraineursHand]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': {'entraineur': 7, 'jour': 'mardi'}, 'nbSeances': 2},\n",
       " {'_id': {'entraineur': 3, 'jour': 'mardi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 3, 'jour': 'dimanche'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 3, 'jour': 'vendredi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 3, 'jour': 'samedi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 7, 'jour': 'lundi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 2, 'jour': 'mercredi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 7, 'jour': 'jeudi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 2, 'jour': 'dimanche'}, 'nbSeances': 2},\n",
       " {'_id': {'entraineur': 3, 'jour': 'lundi'}, 'nbSeances': 4},\n",
       " {'_id': {'entraineur': 1, 'jour': 'lundi'}, 'nbSeances': 6},\n",
       " {'_id': {'entraineur': 2, 'jour': 'samedi'}, 'nbSeances': 3},\n",
       " {'_id': {'entraineur': 7, 'jour': 'dimanche'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 7, 'jour': 'vendredi'}, 'nbSeances': 2},\n",
       " {'_id': {'entraineur': 2, 'jour': 'mardi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 3, 'jour': 'jeudi'}, 'nbSeances': 1},\n",
       " {'_id': {'entraineur': 7, 'jour': 'mercredi'}, 'nbSeances': 6},\n",
       " {'_id': {'entraineur': 7, 'jour': 'samedi'}, 'nbSeances': 2}]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# On les utilise pour faire la restriction (avec le `$match`) dans l'aggrégation.\n",
    "list(db.Gymnases.aggregate([\n",
    "    { \"$unwind\": \"$Seances\" },\n",
    "    { \"$match\": { \"Seances.IdSportifEntraineur\": { \"$in\": [e[\"IdSportif\"] for e in entraineursHand] }}},\n",
    "    { \"$project\" : { \"ent\": \"$Seances.IdSportifEntraineur\", \"jour\": { \"$toLower\": \"$Seances.Jour\"}}},\n",
    "    { \"$group\": { \"_id\": { \"entraineur\": \"$ent\", \"jour\": \"$jour\"}, \"nbSeances\": { \"$sum\": 1}}}\n",
    "]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**23. Quels sont les gymnases ayant plus de 15 séances le mercredi ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A priori, il n'y en a aucun.\n",
    "list(db.Gymnases.aggregate([\n",
    "    { \"$unwind\": \"$Seances\" },\n",
    "    { \"$match\": { \"Seances.Jour\": { \"$in\": [ \"mercredi\", \"Mercredi\" ] }}},\n",
    "    { \"$group\": { \"_id\": { \"nom\": \"$NomGymnase\", \"ville\": \"$Ville\" }, \"nbMercredi\": { \"$sum\": 1 }}},\n",
    "    { \"$match\": { \"nbMercredi\": { \"$gte\": 15 }}}\n",
    "]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**24. Pour chaque gymnase de Montmorency : quel est le nombre de séances journalières de chaque sport propose ?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**25. Dans quels gymnases et quels jours y a t-il au moins 4 séances de volley ball dans la journée ?**"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}