partie_4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PARTIE 4 : Mise à disposition des données"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import librairies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import pandas as pd\n",
    "import boto3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datalake : AWS S3 bucket\n",
    "\n",
    "#### Création d'une instance et connexion au compte AWS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Les clés secrètes ci-dessous ont été cachées\n",
    "# Pour pouvoir exécuter ce code vous devrez renseigner vos clés AWS\n",
    "\n",
    "ACCESS_KEY_ID = '---------------------'\n",
    "SECRET_ACCESS_KEY = '------------------------------------------'\n",
    "\n",
    "session = boto3.Session(aws_access_key_id=ACCESS_KEY_ID, \n",
    "                        aws_secret_access_key=SECRET_ACCESS_KEY,\n",
    "                        region_name = \"eu-west-3\"\n",
    "                        )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Création d'une ressource S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [],
   "source": [
    "s3 = session.resource('s3')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Création d'un bucket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [],
   "source": [
    "bucket_name = 'bucket-for-kayak-project'\n",
    "bucket = s3.create_bucket(Bucket = bucket_name, CreateBucketConfiguration = {'LocationConstraint': 'eu-west-3'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "s3.Bucket(name='bucket-for-kayak-project')\n",
      "<class 'boto3.resources.factory.s3.Bucket'>\n"
     ]
    }
   ],
   "source": [
    "print(bucket)\n",
    "print(type(bucket))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Envoi du fichier csv vers le bucket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [],
   "source": [
    "bucket.upload_file('./src/hotels.csv', 'top_hotels.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Vérification des éléments présents dans le bucket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "top_hotels.csv\n"
     ]
    }
   ],
   "source": [
    "for item in bucket.objects.all():\n",
    "    print(item.key)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lecture du fichier csv depuis le bucket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "\n",
    "s3_client = session.client('s3')\n",
    "s3_object = s3_client.get_object(Bucket=bucket_name, Key='top_hotels.csv')\n",
    "s3_data = s3_object['Body'].read().decode('utf-8')\n",
    "data = pd.read_csv(StringIO(s3_data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "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>city_id</th>\n",
       "      <th>hotel_name</th>\n",
       "      <th>description</th>\n",
       "      <th>score</th>\n",
       "      <th>price</th>\n",
       "      <th>url</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>Hotel des Vosges</td>\n",
       "      <td>L'Hotel des Vosges vous accueille dans le XXe ...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>102</td>\n",
       "      <td>https://www.booking.com/hotel/fr/des-vosges-pa...</td>\n",
       "      <td>48.867778</td>\n",
       "      <td>2.382908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Le Héron</td>\n",
       "      <td>Offrant une vue sur la rue calme, Le Héron est...</td>\n",
       "      <td>8.5</td>\n",
       "      <td>137</td>\n",
       "      <td>https://www.booking.com/hotel/fr/le-heron-huis...</td>\n",
       "      <td>48.610084</td>\n",
       "      <td>-1.456738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Les Terrasses Poulard</td>\n",
       "      <td>Occupant 2 bâtiments différents au cœur du Mon...</td>\n",
       "      <td>7.4</td>\n",
       "      <td>242</td>\n",
       "      <td>https://www.booking.com/hotel/fr/les-terrasses...</td>\n",
       "      <td>48.635349</td>\n",
       "      <td>-1.510379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Les Initiés</td>\n",
       "      <td>Installé au cœur du centre historique de Rouen...</td>\n",
       "      <td>7.9</td>\n",
       "      <td>81</td>\n",
       "      <td>https://www.booking.com/hotel/fr/les-inities-r...</td>\n",
       "      <td>49.441976</td>\n",
       "      <td>1.092439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Ideal Hotel</td>\n",
       "      <td>Situé à Paris, l'Ideal Hotel propose des héber...</td>\n",
       "      <td>5.3</td>\n",
       "      <td>75</td>\n",
       "      <td>https://www.booking.com/hotel/fr/ideal-paris.f...</td>\n",
       "      <td>48.867610</td>\n",
       "      <td>2.375461</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   city_id             hotel_name  \\\n",
       "0        6       Hotel des Vosges   \n",
       "1        1               Le Héron   \n",
       "2        1  Les Terrasses Poulard   \n",
       "3        5            Les Initiés   \n",
       "4        6            Ideal Hotel   \n",
       "\n",
       "                                         description  score  price  \\\n",
       "0  L'Hotel des Vosges vous accueille dans le XXe ...    8.0    102   \n",
       "1  Offrant une vue sur la rue calme, Le Héron est...    8.5    137   \n",
       "2  Occupant 2 bâtiments différents au cœur du Mon...    7.4    242   \n",
       "3  Installé au cœur du centre historique de Rouen...    7.9     81   \n",
       "4  Situé à Paris, l'Ideal Hotel propose des héber...    5.3     75   \n",
       "\n",
       "                                                 url        lat       lon  \n",
       "0  https://www.booking.com/hotel/fr/des-vosges-pa...  48.867778  2.382908  \n",
       "1  https://www.booking.com/hotel/fr/le-heron-huis...  48.610084 -1.456738  \n",
       "2  https://www.booking.com/hotel/fr/les-terrasses...  48.635349 -1.510379  \n",
       "3  https://www.booking.com/hotel/fr/les-inities-r...  49.441976  1.092439  \n",
       "4  https://www.booking.com/hotel/fr/ideal-paris.f...  48.867610  2.375461  "
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698, 8)"
      ]
     },
     "execution_count": 220,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datawarehouse : AWS RDS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import librairies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import Table, Column, Integer, String, MetaData, Float, Text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Création d'une base de données SQL\n",
    "\n",
    "Nous utilisons l'interface AWS RDS pour créer une base de données.\n",
    "\n",
    "Nous définissons et récupérons les informations de connexion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Les identifiants ci-dessous ont été cachés\n",
    "# Pour pouvoir exécuter ce code vous devrez renseigner vos identifiants\n",
    "\n",
    "YOUR_HOSTNAME = \"---------------------------------------\"\n",
    "YOUR_USERNAME = \"postgres\"\n",
    "YOUR_PASSWORD = \"password\"\n",
    "PORT = \"5432\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Connexion à la base de données"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(f\"postgresql+psycopg2://{YOUR_USERNAME}:{YOUR_PASSWORD}@{YOUR_HOSTNAME}/postgres\", echo=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(postgresql+psycopg2://postgres:***@kayak-db.ca16dzxuqxwp.eu-west-3.rds.amazonaws.com/postgres)"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-03-12 23:45:38,387 INFO sqlalchemy.engine.Engine select pg_catalog.version()\n",
      "2024-03-12 23:45:38,388 INFO sqlalchemy.engine.Engine [raw sql] {}\n",
      "2024-03-12 23:45:38,411 INFO sqlalchemy.engine.Engine select current_schema()\n",
      "2024-03-12 23:45:38,412 INFO sqlalchemy.engine.Engine [raw sql] {}\n",
      "2024-03-12 23:45:38,434 INFO sqlalchemy.engine.Engine show standard_conforming_strings\n",
      "2024-03-12 23:45:38,436 INFO sqlalchemy.engine.Engine [raw sql] {}\n"
     ]
    }
   ],
   "source": [
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Création des tables\n",
    "\n",
    "Notre base de données contiendra 2 tables : \n",
    "\n",
    "- Table city : elle regroupe les informations des 35 villes et les données météorologiques ;\n",
    "\n",
    "- Table hotel : elle regroupe les informations de tous les hôtels ;\n",
    "\n",
    "- Ces tables seront reliées par la clé *city_id*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(35, 11)\n",
      "(698, 8)\n"
     ]
    }
   ],
   "source": [
    "cities = pd.read_csv('./src/top_35_cities_lite.csv')\n",
    "hotels = pd.read_csv('./src/hotels.csv')\n",
    "\n",
    "print(cities.shape)\n",
    "print(hotels.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ajout d'une colonne id pour les hôtels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [],
   "source": [
    "hotel_id = [i for i in range(1, 699)] \n",
    "hotels.insert(loc = 0,\n",
    "            column = 'id',\n",
    "            value = hotel_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities.rename(columns={'city_id': 'id', 'city': 'name', 'lat_city': 'lat', 'lon_city': 'lon', \n",
    "                       'avg_weather': 'weather', 'avg_temp': 'temperature', 'avg_clouds': 'clouds',\n",
    "                       'avg_wind': 'wind', 'avg_humidity': 'humidity', 'avg_rain': 'rain', 'avg_pop': 'pop'}, inplace=True)\n",
    "\n",
    "hotels.rename(columns={'hotel_name': 'name'},  inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Ajout des données dans les tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-03-12 23:46:44,817 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-03-12 23:46:44,833 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname \n",
      "FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace \n",
      "WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s\n",
      "2024-03-12 23:46:44,834 INFO sqlalchemy.engine.Engine [generated in 0.00111s] {'table_name': 'city', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}\n",
      "2024-03-12 23:46:44,860 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE city (\n",
      "\tindex BIGINT, \n",
      "\tid BIGINT, \n",
      "\tname TEXT, \n",
      "\tlat FLOAT(53), \n",
      "\tlon FLOAT(53), \n",
      "\tweather TEXT, \n",
      "\ttemperature FLOAT(53), \n",
      "\tclouds FLOAT(53), \n",
      "\twind FLOAT(53), \n",
      "\thumidity FLOAT(53), \n",
      "\train FLOAT(53), \n",
      "\tpop FLOAT(53)\n",
      ")\n",
      "\n",
      "\n",
      "2024-03-12 23:46:44,860 INFO sqlalchemy.engine.Engine [no key 0.00070s] {}\n",
      "2024-03-12 23:46:44,891 INFO sqlalchemy.engine.Engine CREATE INDEX ix_city_index ON city (index)\n",
      "2024-03-12 23:46:44,892 INFO sqlalchemy.engine.Engine [no key 0.00106s] {}\n",
      "2024-03-12 23:46:44,907 INFO sqlalchemy.engine.Engine INSERT INTO city (index, id, name, lat, lon, weather, temperature, clouds, wind, humidity, rain, pop) VALUES (%(index__0)s, %(id__0)s, %(name__0)s, %(lat__0)s, %(lon__0)s, %(weather__0)s, %(temperature__0)s, %(clouds__0)s, %(wind__0)s, %(humidity__0) ... 6007 characters truncated ... 4)s, %(temperature__34)s, %(clouds__34)s, %(wind__34)s, %(humidity__34)s, %(rain__34)s, %(pop__34)s)\n",
      "2024-03-12 23:46:44,908 INFO sqlalchemy.engine.Engine [generated in 0.00264s (insertmanyvalues) 1/1 (unordered)] {'humidity__0': 87.0, 'pop__0': 0.528, 'id__0': 1, 'name__0': 'Mont Saint Michel', 'lon__0': -1.511459954959514, 'temperature__0': 5.668, 'clouds__0': 82.6, 'rain__0': 0.446, 'lat__0': 48.6359541, 'index__0': 0, 'wind__0': 7.708000000000001, 'weather__0': 'Rain', 'humidity__1': 84.0, 'pop__1': 0.546, 'id__1': 2, 'name__1': 'St Malo', 'lon__1': -2.0260409, 'temperature__1': 6.324, 'clouds__1': 84.0, 'rain__1': 0.524, 'lat__1': 48.649518, 'index__1': 1, 'wind__1': 8.852, 'weather__1': 'Rain', 'humidity__2': 88.8, 'pop__2': 0.616, 'id__2': 3, 'name__2': 'Bayeux', 'lon__2': -0.7024738, 'temperature__2': 5.518, 'clouds__2': 71.2, 'rain__2': 0.8320000000000001, 'lat__2': 49.2764624, 'index__2': 2, 'wind__2': 6.991999999999999, 'weather__2': 'Rain', 'humidity__3': 82.0, 'pop__3': 0.6459999999999999, 'id__3': 4, 'name__3': 'Le Havre', 'lon__3': 0.1079732, 'temperature__3': 7.252000000000001, 'clouds__3': 74.2, 'rain__3': 0.806, 'lat__3': 49.4938975, 'index__3': 3, 'wind__3': 9.022000000000002, 'weather__3': 'Rain', 'humidity__4': 87.6, 'pop__4': 0.5280000000000001 ... 320 parameters truncated ... 'wind__30': 5.646, 'weather__30': 'Rain', 'humidity__31': 89.4, 'pop__31': 0.834, 'id__31': 32, 'name__31': 'Montauban', 'lon__31': 1.3549991, 'temperature__31': 6.368, 'clouds__31': 99.6, 'rain__31': 1.6939999999999995, 'lat__31': 44.0175835, 'index__31': 31, 'wind__31': 5.739999999999999, 'weather__31': 'Rain', 'humidity__32': 76.4, 'pop__32': 0.828, 'id__32': 33, 'name__32': 'Biarritz', 'lon__32': -1.5592776, 'temperature__32': 9.294, 'clouds__32': 99.8, 'rain__32': 2.754, 'lat__32': 43.4832523, 'index__32': 32, 'wind__32': 7.971999999999999, 'weather__32': 'Rain', 'humidity__33': 78.0, 'pop__33': 0.834, 'id__33': 34, 'name__33': 'Bayonne', 'lon__33': -1.4736657, 'temperature__33': 8.984, 'clouds__33': 99.6, 'rain__33': 2.904, 'lat__33': 43.4945144, 'index__33': 33, 'wind__33': 6.962000000000001, 'weather__33': 'Rain', 'humidity__34': 75.6, 'pop__34': 0.674, 'id__34': 35, 'name__34': 'La Rochelle', 'lon__34': -1.1520434, 'temperature__34': 8.214, 'clouds__34': 75.2, 'rain__34': 0.78, 'lat__34': 46.1591126, 'index__34': 34, 'wind__34': 10.422, 'weather__34': 'Rain'}\n",
      "2024-03-12 23:46:44,919 INFO sqlalchemy.engine.Engine COMMIT\n",
      "2024-03-12 23:46:44,934 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-03-12 23:46:44,937 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname \n",
      "FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace \n",
      "WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s\n",
      "2024-03-12 23:46:44,938 INFO sqlalchemy.engine.Engine [cached since 0.1045s ago] {'table_name': 'hotel', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}\n",
      "2024-03-12 23:46:44,959 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE hotel (\n",
      "\tindex BIGINT, \n",
      "\tid BIGINT, \n",
      "\tcity_id BIGINT, \n",
      "\tname TEXT, \n",
      "\tdescription TEXT, \n",
      "\tscore FLOAT(53), \n",
      "\tprice BIGINT, \n",
      "\turl TEXT, \n",
      "\tlat FLOAT(53), \n",
      "\tlon FLOAT(53)\n",
      ")\n",
      "\n",
      "\n",
      "2024-03-12 23:46:44,960 INFO sqlalchemy.engine.Engine [no key 0.00164s] {}\n",
      "2024-03-12 23:46:44,971 INFO sqlalchemy.engine.Engine CREATE INDEX ix_hotel_index ON hotel (index)\n",
      "2024-03-12 23:46:44,971 INFO sqlalchemy.engine.Engine [no key 0.00074s] {}\n",
      "2024-03-12 23:46:44,999 INFO sqlalchemy.engine.Engine INSERT INTO hotel (index, id, city_id, name, description, score, price, url, lat, lon) VALUES (%(index__0)s, %(id__0)s, %(city_id__0)s, %(name__0)s, %(description__0)s, %(score__0)s, %(price__0)s, %(url__0)s, %(lat__0)s, %(lon__0)s), (%(index__1)s, % ... 110322 characters truncated ... 7)s, %(description__697)s, %(score__697)s, %(price__697)s, %(url__697)s, %(lat__697)s, %(lon__697)s)\n",
      "2024-03-12 23:46:45,001 INFO sqlalchemy.engine.Engine [generated in 0.00689s (insertmanyvalues) 1/1 (unordered)] {'id__0': 1, 'city_id__0': 6, 'price__0': 102, 'name__0': 'Hotel des Vosges', 'score__0': 8.0, 'lon__0': 2.38290846, 'url__0': 'https://www.booking.com/hotel/fr/des-vosges-paris.fr.html', 'lat__0': 48.86777797, 'index__0': 0, 'description__0': \"L'Hotel des Vosges vous accueille dans le XXe arrondissement de Paris, à seulement 700 mètres du cimetière du Père Lachaise. Il\\xa0vous propose\\xa0un ... (675 characters truncated) ... a gare du Nord est située sur la même ligne de métro et la gare de l'Est se trouve à 2,5 km. Vous bénéficierez de prix réduits pour le stationnement.\", 'id__1': 2, 'city_id__1': 1, 'price__1': 137, 'name__1': 'Le Héron', 'score__1': 8.5, 'lon__1': -1.4567376, 'url__1': 'https://www.booking.com/hotel/fr/le-heron-huisnes-sur-mer.fr.html', 'lat__1': 48.6100841, 'index__1': 1, 'description__1': \"Offrant une vue sur la rue calme, Le Héron est un hébergement situé à Huisnes-sur-Mer, à 12 km du Mont-Saint-Michel et à 19 km du Scriptorial d'Avran ... (529 characters truncated) ... n\\nVous séjournerez à 44 km de la gare de Granville et à 45 km du zoo de Champrepus. L'aéroport de Rennes-Saint-Jacques, le plus proche, est à 92 km.\", 'id__2': 3, 'city_id__2': 1, 'price__2': 242, 'name__2': 'Les Terrasses Poulard', 'score__2': 7.4, 'lon__2': -1.51037872, 'url__2': 'https://www.booking.com/hotel/fr/les-terrasses-poulard.fr.html', 'lat__2': 48.63534943, 'index__2': 2, 'description__2': 'Occupant 2\\xa0bâtiments différents au cœur du Mont-Saint-Michel, l’établissement historique Les Terrasses Poulard propose des hébergements avec une v ... (653 characters truncated) ... aisser vos bagages à la réception.\\n\\nLa région constitue un cadre idéal pour diverses activités telles que la pêche, le vélo et la randonnée à pied.', 'id__3': 4, 'city_id__3': 5, 'price__3': 81, 'name__3': 'Les Initiés', 'score__3': 7.9, 'lon__3': 1.09243892, 'url__3': 'https://www.booking.com/hotel/fr/les-inities-rouen.fr.html', 'lat__3': 49.44197641, 'index__3': 3, 'description__3': \"Installé au cœur du centre historique de Rouen, l'établissement Les Initiés est situé à seulement 10 minutes à pied de la gare de Rouen-Rive-Droite e ... (505 characters truncated) ... la cathédrale Notre-Dame se trouve à 5 minutes à pied. L'emplacement de l'établissement est idéal pour découvrir la capitale historique de Normandie.\", 'id__4': 5, 'city_id__4': 6, 'price__4': 75, 'name__4': 'Ideal Hotel', 'score__4': 5.3, 'lon__4': 2.3754613, 'url__4': 'https://www.booking.com/hotel/fr/ideal-paris.fr.html', 'lat__4': 48.8676104, 'index__4': 4, 'description__4': \"Situé à Paris, l'Ideal Hotel propose des hébergements simples équipés d'une télévision par câble à écran plat. Une connexion Wi-Fi est accessible gra ... (355 characters truncated) ... taurants.\\n\\nL'Ideal Hotel se situe à 2,5 km de la gare de Lyon et à 6 km de la tour Eiffel. L'aéroport de Paris-Charles-de-Gaulle se trouve à 20 km.\" ... 6880 parameters truncated ... 'id__693': 694, 'city_id__693': 30, 'price__693': 91, 'name__693': 'Hôtel Terranostra', 'score__693': 8.5, 'lon__693': 1.6056028, 'url__693': 'https://www.booking.com/hotel/fr/terranostra.fr.html', 'lat__693': 42.84718593, 'index__693': 693, 'description__693': \"Doté d'un restaurant et d'un bar, l'Hotel Terranostra vous accueille à Tarascon-sur-Ariège, au cœur des sites historiques de l'Ariège. Une connexion  ... (514 characters truncated) ... uit à proximité, et la gare la plus proche se situe à 5 minutes à pied. Vous rejoindrez la station de ski d'Ax Les 3 Domaines en 40 minutes de route.\", 'id__694': 695, 'city_id__694': 30, 'price__694': 64, 'name__694': \"The Originals Access, Hôtel Foix (P'tit Dej-Hotel)\", 'score__694': 8.0, 'lon__694': 1.61583245, 'url__694': 'https://www.booking.com/hotel/fr/campanilefoix.fr.html', 'lat__694': 42.9921119, 'index__694': 694, 'description__694': \"Situé au sein du parc naturel régional des Pyrénées ariégeoises, à 75\\xa0km de Toulouse, l’établissement The Originals Access, Hôtel Foix (P'tit Dej- ... (405 characters truncated) ... nals Access, Hôtel Foix, et un parking public gratuit se trouve à proximité. Ce logement se trouve à 80\\xa0km d’Andorre et de la frontière espagnole.\", 'id__695': 696, 'city_id__695': 30, 'price__695': 215, 'name__695': 'La Grange - 10 couchages', 'score__695': 9.3, 'lon__695': 1.7654221, 'url__695': 'https://www.booking.com/hotel/fr/la-grange-carla-de-roquefort.fr.html', 'lat__695': 42.9751989, 'index__695': 695, 'description__695': \"Situé à Carla-de-Roquefort, à 20 km du château de Foix et de la fontaine Fontestorbes, l'établissement La Grange - 10 couchages dispose d'un jardin e ... (607 characters truncated) ...  séjournerez à 21 km du musée de Montségur et à 25 km de la rivière souterraine de Labouiche. L'aéroport de Carcassonne, le plus proche, est à 64 km.\", 'id__696': 697, 'city_id__696': 30, 'price__696': 130, 'name__696': 'Chalet bois au milieu des Pyrénées', 'score__696': 9.0, 'lon__696': 1.79836833, 'url__696': 'https://www.booking.com/hotel/fr/chalet-bois-au-milieu-des-pyrenees.fr.html', 'lat__696': 42.58990385, 'index__696': 696, 'description__696': \"Doté d'une terrasse et d'une réception ouverte 24h/24, le Chalet bois au milieu des Pyrénées bénéficie d’un emplacement idéal à L'Hospitalet-près-l'A ... (588 characters truncated) ... de Font-Romeu à 38\\xa0km du chalet. L'aéroport d'Andorre-La Seu d'Urgell, le plus proche, est situé à 62\\xa0km du Chalet bois au milieu des Pyrénées.\", 'id__697': 698, 'city_id__697': 30, 'price__697': 212, 'name__697': \"Demeure de la Carmine, sejour bien-etre d'exception, piscine, hammam, champagne\", 'score__697': 9.5, 'lon__697': 1.87135531, 'url__697': 'https://www.booking.com/hotel/fr/demeure-de-la-carmine.fr.html', 'lat__697': 43.08754039, 'index__697': 697, 'description__697': \"Implantée à Mirepoix, en Midi-Pyrénées, la Demeure de la Carmine, sejour bien-etre d'exception, piscine, hammam, champagne se situe à 41 km de Carcas ... (661 characters truncated) ... et de boutiques sur place.\\n\\nVous séjournerez à 26 km de Foix et à 41 km d'Ax-les-Thermes. L'aéroport de Blagnac, le plus proche, se trouve à 73 km.\"}\n",
      "2024-03-12 23:46:45,186 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "698"
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.to_sql('city', engine)\n",
    "hotels.to_sql('hotel', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Requêtes SQL\n",
    "\n",
    "Nous effectuons quelques requêtes SQL pour vérifier si les données sont bien enregistrées dans la base de données."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-03-13 00:13:33,125 INFO sqlalchemy.engine.Engine SELECT C.name, H.name, score, price FROM hotel H INNER JOIN city C ON H.city_id = C.id WHERE C.id = 6 ORDER BY score DESC\n",
      "2024-03-13 00:13:33,127 INFO sqlalchemy.engine.Engine [generated in 0.00128s] {}\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Paris', 'Motel One Paris-Porte Dorée', 8.7, 139),\n",
       " ('Paris', \"Sonder L'Edmond Parc Monceau\", 8.5, 349),\n",
       " ('Paris', 'Hotel Cluny Square', 8.4, 156),\n",
       " ('Paris', 'Virgina', 8.3, 131),\n",
       " ('Paris', 'Hotel du Pré', 8.3, 159),\n",
       " ('Paris', 'Hotel Albe Bastille', 8.2, 150),\n",
       " ('Paris', 'Hotel des Vosges', 8.0, 102),\n",
       " ('Paris', 'Hotel Luna Park', 7.9, 95),\n",
       " ('Paris', 'Hôtel Avenir Jonquière', 7.9, 84),\n",
       " ('Paris', 'Hotel Studia', 7.9, 121),\n",
       " ('Paris', 'Hotel Esmeralda', 7.7, 182),\n",
       " ('Paris', 'The Originals Boutique, Hôtel Maison Montmartre Paris Les Puces', 7.6, 129),\n",
       " ('Paris', 'Grand Hôtel du Bel Air', 7.4, 112),\n",
       " ('Paris', 'Hotel des Belges', 7.3, 96),\n",
       " ('Paris', \"Hotel Le Clos d'Alésia\", 7.2, 108),\n",
       " ('Paris', 'Grand Hôtel De Paris', 7.2, 116),\n",
       " ('Paris', 'Résidence de Bourgogne', 6.7, 103),\n",
       " ('Paris', 'Hotel De La Poste', 6.0, 66),\n",
       " ('Paris', 'Printania Porte de Versailles', 5.7, 87),\n",
       " ('Paris', 'Ideal Hotel', 5.3, 75)]"
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy.sql import text\n",
    "\n",
    "# Afficher les meilleurs hôtels et leurs prix pour la ville de Paris\n",
    "\n",
    "stmt = text(\"SELECT C.name, H.name, score, price \"\n",
    "            \"FROM hotel H \"\n",
    "            \"INNER JOIN city C \"\n",
    "            \"ON H.city_id = C.id \"\n",
    "            \"WHERE C.id = 6 \"\n",
    "            \"ORDER BY score DESC\")\n",
    "\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-03-13 00:08:32,808 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-03-13 00:08:32,809 INFO sqlalchemy.engine.Engine SELECT name, weather, temperature FROM city ORDER BY temperature DESC LIMIT 10\n",
      "2024-03-13 00:08:32,810 INFO sqlalchemy.engine.Engine [cached since 408s ago] {}\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Marseille', 'Clouds', 11.24),\n",
       " ('Cassis', 'Rain', 11.072),\n",
       " ('Saintes Maries de la mer', 'Clouds', 10.652),\n",
       " ('Bormes les Mimosas', 'Rain', 10.28),\n",
       " ('Aigues Mortes', 'Clouds', 10.157999999999998),\n",
       " ('Biarritz', 'Rain', 9.294),\n",
       " ('Bayonne', 'Rain', 8.984),\n",
       " ('Nimes', 'Clouds', 8.45),\n",
       " ('Avignon', 'Clouds', 8.43),\n",
       " ('Collioure', 'Clouds', 8.358)]"
      ]
     },
     "execution_count": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Afficher les 10 villes où il fait le plus chaud\n",
    "\n",
    "stmt = text(\"SELECT name, weather, temperature \"\n",
    "            \"FROM city \"\n",
    "            \"ORDER BY temperature DESC \"\n",
    "            \"LIMIT 10\")\n",
    "\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-03-13 00:15:56,333 INFO sqlalchemy.engine.Engine SELECT round(avg(price)) FROM hotel \n",
      "2024-03-13 00:15:56,333 INFO sqlalchemy.engine.Engine [generated in 0.00093s] {}\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(Decimal('124'),)]"
      ]
     },
     "execution_count": 283,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Afficher le prix moyen des hôtels\n",
    "\n",
    "stmt = text(\"SELECT round(avg(price)) \"\n",
    "            \"FROM hotel \")\n",
    "\n",
    "result = conn.execute(stmt)\n",
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note : cette commande est utile en cas d'erreur dans la requête SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "etl",
   "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}