Web Scraper.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import json\n",
    "import re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean(string):\n",
    "    \"\"\"\n",
    "    Clean the script tag contents for easier retrieval of data.\n",
    "    \n",
    "    paramters:\n",
    "        string: str.\n",
    "        The contents of the script tag.\n",
    "        \n",
    "    returns:\n",
    "        string: str.\n",
    "        The cleaned contents of the script tag.\n",
    "    \"\"\"\n",
    "    \n",
    "    string = re.sub(\"[\\n \\\\\\']\",'',str(string))\n",
    "    string = string.replace(\" \",'')\n",
    "    string = re.sub('[{}\\[\\]():]',' ',string)\n",
    "    string = re.sub('[\\\"\\\" /*]','',string)\n",
    "    \n",
    "    return string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "def retrieve_dates(string):\n",
    "    \"\"\"\n",
    "    Retrieve dates from the cleaned script tag contents.\n",
    "    \n",
    "    parameters:\n",
    "        string: str.\n",
    "        The cleaned contents of the script tag.\n",
    "        \n",
    "    returns:\n",
    "        dates: list.\n",
    "        A list of the dates.\n",
    "    \"\"\"\n",
    "    \n",
    "    start_string = 'categories'\n",
    "    end_string = ',yAxis'\n",
    "    \n",
    "    start_index = string.find(start_string) + len(start_string)\n",
    "    end_index = string.find(end_string)\n",
    "    \n",
    "    dates = string[start_index:end_index].strip().split(\",\")\n",
    "    \n",
    "    return dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "def retrieve_daily_stats(string):\n",
    "    \"\"\"\n",
    "    Retrieves daily statistics from the cleaned script tag contents.\n",
    "    \n",
    "    parameters:\n",
    "        string: str.\n",
    "        Cleaned contents of the script tag.\n",
    "                \n",
    "    returns:\n",
    "        values: list.\n",
    "        A list of daily statistics ordered by date.\n",
    "    \"\"\"\n",
    "    \n",
    "    start_string = 'data'\n",
    "    end_string = ',name'\n",
    "    \n",
    "    start_index = string.find(start_string) + len(start_string)\n",
    "    end_index = string.find(end_string)\n",
    "    \n",
    "    values = string[start_index:end_index].strip().split(\",\")\n",
    "    \n",
    "    return values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "def retrieve_overall_stats(string):\n",
    "    \"\"\"\n",
    "    Retrieves overall statistics from the cleaned script tag contents.\n",
    "    \n",
    "    parameters:\n",
    "        string: str.\n",
    "        Cleaned contents of the script tag.\n",
    "        \n",
    "    returns:\n",
    "        values: list.\n",
    "        A list of daily statistics ordered by date.\n",
    "    \"\"\"\n",
    "    \n",
    "    start_string = 'data'\n",
    "    end_string = ',resp'\n",
    "    \n",
    "    start_index = string.find(start_string) + len(start_string)\n",
    "    end_index = string.find(end_string)\n",
    "    \n",
    "    values = string[start_index:end_index].strip().split(\",\")\n",
    "    \n",
    "    return values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = [\n",
    "    'us','brazil','russia','spain',\n",
    "    'italy','france','germany',\n",
    "    'turkey','india','iran','peru',\n",
    "    'canada','chile','china','mexico',\n",
    "    'saudi-arabia','pakistan','belgium',\n",
    "    'qatar', 'bangladesh',\n",
    "    'belarus', 'ecuador', 'sweden'\n",
    "]\n",
    "\n",
    "\n",
    "data_indexes = {\n",
    "                'total_cases':0,\n",
    "                'daily_cases':1,\n",
    "                'active_cases':2,\n",
    "                'total_deaths':3,\n",
    "                'daily_deaths':4\n",
    "               }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "def page_contents(url):\n",
    "    \"\"\"\n",
    "    Retrieves contents of the web page from the specified url and the specific div tag class - col-md-12\n",
    "    \n",
    "    paramters:\n",
    "        url: str.\n",
    "        The url to the web page to be scrapped.\n",
    "        \n",
    "    returns:\n",
    "        result: str.\n",
    "        HTML parsed web page content as string.\n",
    "    \"\"\"\n",
    "    \n",
    "    page = requests.get(url)\n",
    "    soup = BeautifulSoup(page.content, 'html.parser')\n",
    "    \n",
    "    result = soup.find_all('div', class_= 'col-md-12')\n",
    "    \n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "def script_tag_contents(page_content, stat):\n",
    "    \"\"\"\n",
    "    Retrieves the script tag contents from the web page contents.\n",
    "    \n",
    "    paramters:\n",
    "        page_content: str.\n",
    "        HTML parsed web page contents\n",
    "        \n",
    "        stat: str.\n",
    "        String specifying the kind of statistic from the data_indexes.\n",
    "        \n",
    "    returns:\n",
    "        script_content: str.\n",
    "        Script tag contents as string.\n",
    "    \"\"\"\n",
    "    \n",
    "    stat_data = page_content[data_indexes[stat]]\n",
    "    script_content = stat_data.find('script').contents[0]\n",
    "    \n",
    "    return script_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_dataframe(values,stat_name,dataframe=None,date=None):\n",
    "    \"\"\"\n",
    "    Build a DataFrame from the dates and the values scraped.\n",
    "    \n",
    "    parameters:\n",
    "        dataframe: DataFrame.\n",
    "        A DataFrame containing dates and/or statistics.\n",
    "        \n",
    "        date: list.\n",
    "        List of dates for the statistics in string format.\n",
    "        \n",
    "        values: list:\n",
    "        List of values (data) for the statistics in string format.\n",
    "        \n",
    "        stat_name: str.\n",
    "        Name of the statistic, for which the list of values are passed.\n",
    "        \n",
    "        returns:\n",
    "            dataframe: DataFrame.\n",
    "            DataFrame containing dates and passed statistic values.\n",
    "    \"\"\"\n",
    "    \n",
    "    if dataframe is None and date is not None:\n",
    "        \n",
    "        dataframe = pd.DataFrame({'date':date, stat_name:values})\n",
    "        \n",
    "    else:\n",
    "        \n",
    "        dataframe[stat_name] = values\n",
    "        \n",
    "    return dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_date(dataframe, date_col):\n",
    "    \"\"\"\n",
    "    Clean the date column in the dataframe to standard date representation - YYYY-MM-DD\n",
    "    \n",
    "    parameters:\n",
    "        dataframe: DataFrame.\n",
    "            DataFrame whose dates are to be cleaned.\n",
    "            \n",
    "        date_col: str.\n",
    "        Name of the date column in the DataFrame.\n",
    "        \n",
    "    returns:\n",
    "        dataframe: DataFrame.\n",
    "        Cleaned DataFrame.\n",
    "    \"\"\"\n",
    "    \n",
    "    dataframe[date_col] = dataframe[date_col].apply(\n",
    "        lambda date: date[:3]+\" \"+date[3:]+\" 2020\"\n",
    "    )\n",
    "    dataframe[date_col] = dataframe[date_col].apply(\n",
    "        lambda date: datetime.datetime.strptime(date,'%b %d %Y').date()\n",
    "    )\n",
    "    \n",
    "    return dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "def scrape_data():\n",
    "    \"\"\"\n",
    "    Scrape the web page for date, total cases, daily cases, total active cases, total_deaths, daily deaths\n",
    "    daily recoveries per country. Creates a folder in local directory containing csv files per country with \n",
    "    the respective data. Website - worldometers.info\n",
    "    \n",
    "    parameters: None\n",
    "    \n",
    "    returns: bool.\n",
    "    boolean.\n",
    "    \"\"\"\n",
    "    \n",
    "    has_been_run_once = False\n",
    "        \n",
    "    for country in countries:\n",
    "                \n",
    "        url = \"https://www.worldometers.info/coronavirus/country/\"+country+\"/\"\n",
    "        content = page_contents(url)\n",
    "        \n",
    "        for stat in data_indexes:\n",
    "                        \n",
    "            script_contents = script_tag_contents(content, stat)\n",
    "            script_contents = clean(script_contents)\n",
    "            \n",
    "            if 'daily' in stat:\n",
    "                \n",
    "                data = retrieve_daily_stats(script_contents)\n",
    "            \n",
    "            else:\n",
    "                \n",
    "                data = retrieve_overall_stats(script_contents)\n",
    "                \n",
    "            if not has_been_run_once:\n",
    "                \n",
    "                date = retrieve_dates(script_contents)\n",
    "                dataframe = build_dataframe(data, stat, date= date)\n",
    "                has_been_run_once = True\n",
    "                \n",
    "            else:\n",
    "                \n",
    "                dataframe = build_dataframe(data, stat, dataframe= dataframe)\n",
    "            \n",
    "        dataframe = clean_date(dataframe, date_col='date')\n",
    "        dataframe.to_csv('./Data/covid19_'+country+'_stats.csv',index=False)\n",
    "        has_been_run_once = False\n",
    "        print(\"Scraped successfully: \",country)\n",
    "    \n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Scraped successfully:  us\n",
      "Scraped successfully:  brazil\n",
      "Scraped successfully:  russia\n",
      "Scraped successfully:  spain\n",
      "Scraped successfully:  italy\n",
      "Scraped successfully:  france\n",
      "Scraped successfully:  germany\n",
      "Scraped successfully:  turkey\n",
      "Scraped successfully:  india\n",
      "Scraped successfully:  iran\n",
      "Scraped successfully:  peru\n",
      "Scraped successfully:  canada\n",
      "Scraped successfully:  chile\n",
      "Scraped successfully:  china\n",
      "Scraped successfully:  mexico\n",
      "Scraped successfully:  saudi-arabia\n",
      "Scraped successfully:  pakistan\n",
      "Scraped successfully:  belgium\n",
      "Scraped successfully:  qatar\n",
      "Scraped successfully:  bangladesh\n",
      "Scraped successfully:  belarus\n",
      "Scraped successfully:  ecuador\n",
      "Scraped successfully:  sweden\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 287,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scrape_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>date</th>\n",
       "      <th>total_cases</th>\n",
       "      <th>daily_cases</th>\n",
       "      <th>active_cases</th>\n",
       "      <th>total_deaths</th>\n",
       "      <th>daily_deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02-16</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-02-18</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>2020-05-25</td>\n",
       "      <td>376669</td>\n",
       "      <td>13051.0</td>\n",
       "      <td>199314</td>\n",
       "      <td>23522</td>\n",
       "      <td>806.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>392360</td>\n",
       "      <td>15691.0</td>\n",
       "      <td>209218</td>\n",
       "      <td>24549</td>\n",
       "      <td>1027.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2020-05-27</td>\n",
       "      <td>414661</td>\n",
       "      <td>22301.0</td>\n",
       "      <td>222317</td>\n",
       "      <td>25697</td>\n",
       "      <td>1148.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2020-05-28</td>\n",
       "      <td>438812</td>\n",
       "      <td>24151.0</td>\n",
       "      <td>218867</td>\n",
       "      <td>26764</td>\n",
       "      <td>1067.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>468338</td>\n",
       "      <td>29526.0</td>\n",
       "      <td>247213</td>\n",
       "      <td>27944</td>\n",
       "      <td>1180.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date  total_cases  daily_cases  active_cases  total_deaths  \\\n",
       "0    2020-02-15            0          NaN             0             0   \n",
       "1    2020-02-16            0          NaN             0             0   \n",
       "2    2020-02-17            0          NaN             0             0   \n",
       "3    2020-02-18            0          NaN             0             0   \n",
       "4    2020-02-19            0          NaN             0             0   \n",
       "..          ...          ...          ...           ...           ...   \n",
       "100  2020-05-25       376669      13051.0        199314         23522   \n",
       "101  2020-05-26       392360      15691.0        209218         24549   \n",
       "102  2020-05-27       414661      22301.0        222317         25697   \n",
       "103  2020-05-28       438812      24151.0        218867         26764   \n",
       "104  2020-05-29       468338      29526.0        247213         27944   \n",
       "\n",
       "     daily_deaths  \n",
       "0             NaN  \n",
       "1             NaN  \n",
       "2             NaN  \n",
       "3             NaN  \n",
       "4             NaN  \n",
       "..            ...  \n",
       "100         806.0  \n",
       "101        1027.0  \n",
       "102        1148.0  \n",
       "103        1067.0  \n",
       "104        1180.0  \n",
       "\n",
       "[105 rows x 6 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./Data/covid19_brazil_stats.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "def table_contents(url):\n",
    "    \"\"\"\n",
    "    Retrieve contents of the table on the url.\n",
    "    \n",
    "    parameters:\n",
    "        url: str.\n",
    "        URL of the webpage. The table contents are scraped from this.\n",
    "        \n",
    "    returns:\n",
    "        table_data: str.\n",
    "        HTML parsed table data in string format.\n",
    "    \"\"\"\n",
    "    \n",
    "    page_content = requests.get(url)\n",
    "    soup = BeautifulSoup(page_content.content, 'html.parser')\n",
    "    table_data = soup.find('table', id='thetable')\n",
    "    \n",
    "    return table_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [],
   "source": [
    "def country_names(table_data):\n",
    "    \"\"\"\n",
    "    Scrape country names from the table contents scraped from the Wikipedia url.\n",
    "    \n",
    "    parameters:\n",
    "        table_data: str.\n",
    "        Scraped table data from webpage in string format.\n",
    "        \n",
    "    returns:\n",
    "        countries: list.\n",
    "        A list of country names from the table on webpage.\n",
    "    \"\"\"\n",
    "    \n",
    "    countries= []\n",
    "    table_head_data = table_data.find_all('th', scope= 'row')\n",
    "    \n",
    "    for data in table_head_data:\n",
    "        \n",
    "        anchor_data = data.find('a')\n",
    "        \n",
    "        if anchor_data is not None:\n",
    "            \n",
    "            if anchor_data.find('i'):\n",
    "                \n",
    "                countries.append(anchor_data.i.contents[0])\n",
    "                continue\n",
    "            \n",
    "            countries.append(anchor_data.contents[0])\n",
    "    \n",
    "    return countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "def chunks(lst, size):\n",
    "    \"\"\"\n",
    "    Segments the input list into equal chunks of specified size.\n",
    "    \n",
    "    parameters:\n",
    "        lst: list.\n",
    "        List to be segmented.\n",
    "        \n",
    "        size: int.\n",
    "        Size of the chunks.\n",
    "    \"\"\"\n",
    "    \n",
    "    for i in range(0, len(lst), size):\n",
    "        \n",
    "        yield lst[i:i+size]\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_stats(stats):\n",
    "    \"\"\"\n",
    "    Clean the statistics scraped from the webpage. Remove ',' and '\\n'.\n",
    "    \n",
    "    parameters:\n",
    "        stats: list.\n",
    "        A list of statistics.\n",
    "        \n",
    "    returns:\n",
    "        cleaned_stats: list.\n",
    "        A cleaned list of statistics.\n",
    "    \"\"\"\n",
    "    \n",
    "    cleaned_stats = []\n",
    "    \n",
    "    for stat in stats:\n",
    "        \n",
    "        stat = re.sub(',','',stat)\n",
    "        stat = stat.rstrip()\n",
    "        cleaned_stats.append(stat)\n",
    "        \n",
    "    cleaned_stats.pop()\n",
    "    \n",
    "    return cleaned_stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def table_statistics(table_data):\n",
    "    \"\"\"\n",
    "    Scrape overall statistics on total cases, total deaths and total recoveries from \n",
    "    the table contents on the webpage, for each country.\n",
    "    \n",
    "    parameters:\n",
    "        table_data: str.\n",
    "        Scraped table data in string format.\n",
    "        \n",
    "    returns:\n",
    "        stats: list.\n",
    "        A list of lists giving country wise statistics.\n",
    "    \"\"\"\n",
    "    \n",
    "    stats = []\n",
    "    row_data = table_data.find_all('td')\n",
    "    \n",
    "    for data in row_data:\n",
    "        \n",
    "        if data.find('sup'):\n",
    "            \n",
    "            continue\n",
    "            \n",
    "        if data.find('span'):\n",
    "            \n",
    "            stats.append('null')\n",
    "            continue\n",
    "        \n",
    "        stats.append(data.contents[0])\n",
    "        \n",
    "    stats = clean_stats(stats)    \n",
    "    stats = list(chunks(stats,3))\n",
    "        \n",
    "    return stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "def scrape_overall_data():\n",
    "    \"\"\"\n",
    "    Scrape overall statistics country wise from the Wikipedia page on COVID-19 pandemic into a DataFrame.\n",
    "    Writes the DataFrame to a csv file.\n",
    "    \n",
    "    returns: bool.\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    url = 'https://en.wikipedia.org/wiki/COVID-19_pandemic_by_country_and_territory'\n",
    "    table_data = table_contents(url)\n",
    "    \n",
    "    countries = country_names(table_data)\n",
    "    statistics = table_statistics(table_data)\n",
    "    \n",
    "    statistics_dict = {}\n",
    "    \n",
    "    for country, statistic in zip(countries, statistics):\n",
    "        \n",
    "        statistics_dict[country] = statistic\n",
    "        \n",
    "    dataframe = pd.DataFrame.from_dict(statistics_dict, orient= 'index', columns= [\n",
    "        'total_cases',\n",
    "        'total_deaths',\n",
    "        'total_recoveries'\n",
    "    ]).reset_index()\n",
    "    \n",
    "    dataframe.rename(columns= {'index':'country'}, inplace= True)\n",
    "    dataframe.to_csv('./Data/covid19_overall_stat.csv', index= False)\n",
    "    \n",
    "    print(\"Successfully scraped table\")\n",
    "    \n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully scraped table\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 288,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scrape_overall_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "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>country</th>\n",
       "      <th>total_cases</th>\n",
       "      <th>total_deaths</th>\n",
       "      <th>total_recoveries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>1786171</td>\n",
       "      <td>104235</td>\n",
       "      <td>385125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>469510</td>\n",
       "      <td>28015</td>\n",
       "      <td>189476.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Russia</td>\n",
       "      <td>396575</td>\n",
       "      <td>4555</td>\n",
       "      <td>167469.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>272826</td>\n",
       "      <td>38376</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Spain</td>\n",
       "      <td>238564</td>\n",
       "      <td>27125</td>\n",
       "      <td>150376.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>Saba</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>Bonaire</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>Lesotho</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>Sint Eustatius</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>Saint Pierre &amp; Miquelon</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     country  total_cases  total_deaths  total_recoveries\n",
       "0              United States      1786171        104235          385125.0\n",
       "1                     Brazil       469510         28015          189476.0\n",
       "2                     Russia       396575          4555          167469.0\n",
       "3             United Kingdom       272826         38376               NaN\n",
       "4                      Spain       238564         27125          150376.0\n",
       "..                       ...          ...           ...               ...\n",
       "223                     Saba            3             0               3.0\n",
       "224                  Bonaire            2             0               2.0\n",
       "225                  Lesotho            2             0               1.0\n",
       "226           Sint Eustatius            2             0               2.0\n",
       "227  Saint Pierre & Miquelon            1             0               1.0\n",
       "\n",
       "[228 rows x 4 columns]"
      ]
     },
     "execution_count": 289,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('./Data/covid19_overall_stat.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_check(page_content):\n",
    "    \"\"\"\n",
    "    Checks the date for the most recent update for statistics on the webpage.\n",
    "    \n",
    "    parameters:\n",
    "        page_content: str.\n",
    "        HTML parsed contents of the page.\n",
    "        \n",
    "    returns:\n",
    "        result: bool.\n",
    "        Boolean.\n",
    "    \"\"\"\n",
    "    \n",
    "    page_date = page_content.find('div', class_= 'news_date').h4.contents[0]\n",
    "    page_date = re.sub('\\(.*\\)','',page_date)\n",
    "    date = datetime.datetime.strptime(page_date + \"2020\", '%b %d %Y').date()\n",
    "    \n",
    "    if date == datetime.datetime.today().date():\n",
    "        \n",
    "        return False\n",
    "    \n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "def updated_stats(url):\n",
    "    \"\"\"\n",
    "    Gathers updated data on the number of cases and deaths in a day.\n",
    "    \n",
    "    paramters:\n",
    "        url: str.\n",
    "        URL from where the updates are scraped.\n",
    "        \n",
    "    returns:\n",
    "        result: tuple.\n",
    "        A tuple with total cases and total deaths of the day.\n",
    "    \"\"\"\n",
    "    \n",
    "    page = requests.get(url)\n",
    "    soup = BeautifulSoup(page.content, 'html.parser')\n",
    "    \n",
    "    if date_check(soup):\n",
    "        \n",
    "        return ()\n",
    "    \n",
    "    updated_list = soup.find('li', class_= 'news_li')\n",
    "    updates = updated_list.find_all('strong')\n",
    "    \n",
    "    if 'new' in updates[0].contents[0]:\n",
    "        \n",
    "        daily_cases = updates[0].contents[0]\n",
    "        daily_cases = re.sub('[, new cases]','',daily_cases)\n",
    "        daily_cases = int(daily_cases)\n",
    "        \n",
    "    else:\n",
    "        \n",
    "        daily_cases = np.NaN\n",
    "        \n",
    "    if 'new' in updates[1].contents[0]:\n",
    "        \n",
    "        daily_deaths = updates[1].contents[0]\n",
    "        daily_deaths = re.sub('[, new deaths]','',daily_deaths)\n",
    "        daily_deaths = int(daily_deaths)\n",
    "    \n",
    "    else:\n",
    "        \n",
    "        daily_deaths = np.NaN\n",
    "    \n",
    "    \n",
    "    result = (daily_cases, daily_deaths)\n",
    "    \n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_mapping = {\n",
    "    'us':'United States',\n",
    "    'brazil':'Brazil',\n",
    "    'russia':'Russia',\n",
    "    'spain':'Spain',\n",
    "    'italy':'Italy',\n",
    "    'france':'France',\n",
    "    'germany':'Germany',\n",
    "    'turkey':'Turkey',\n",
    "    'india':'India',\n",
    "    'iran':'Iran',\n",
    "    'peru':'Peru',\n",
    "    'canada':'Canada',\n",
    "    'chile':'Chile',\n",
    "    'china':'China',\n",
    "    'mexico':'Mexico',\n",
    "    'saudi-arabia':'Saudi Arabia',\n",
    "    'pakistan':'Pakistan',\n",
    "    'belgium':'Belgium',\n",
    "    'qatar':'Qatar',\n",
    "    'bangladesh':'Bangladesh',\n",
    "    'belarus':'Belarus',\n",
    "    'ecuador':'Ecuador',\n",
    "    'sweden':'Sweden'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "def daily_updates():\n",
    "    \"\"\"\n",
    "    Scrape daily updates on covid-19 statistics and update data files.\n",
    "    \n",
    "    returns: bool.\n",
    "    Boolean.\n",
    "    \"\"\"\n",
    "    \n",
    "    for country in country_mapping.keys():\n",
    "        \n",
    "        url = \"https://www.worldometers.info/coronavirus/country/\"+country+\"/\"\n",
    "        updates = updated_stats(url)\n",
    "        \n",
    "        country_df = pd.read_csv('./Data/covid19_'+country+'_stats.csv')\n",
    "        overall_data = pd.read_csv('./Data/covid19_overall_stat.csv')\n",
    "        \n",
    "        overall_county_data = overall_data[overall_data.country == country_mapping[country]]\n",
    "        \n",
    "        last_update = country_df.iloc[-1]\n",
    "        new_update = last_update\n",
    "        \n",
    "        new_update.date = datetime.datetime.today().date()\n",
    "        \n",
    "        if updates:\n",
    "            \n",
    "            new_update.total_cases = last_update.total_cases + updates[0]\n",
    "            new_update.daily_cases = updates[0]\n",
    "            \n",
    "            if not np.isnan(updates[1]):\n",
    "                \n",
    "                new_update.total_deaths = last_update.total_deaths + updates[1]\n",
    "                new_update.daily_deaths = updates[1]\n",
    "                \n",
    "            else:\n",
    "                \n",
    "                new_update.daily_deaths = np.NaN\n",
    "                \n",
    "            new_update.active_cases = new_update.total_cases - (\n",
    "                overall_county_data.total_recoveries.iloc[0] + overall_county_data.total_deaths.iloc[0]\n",
    "            )\n",
    "        \n",
    "            country_df = country_df.append(new_update).reset_index(drop= True)\n",
    "            country_df.to_csv('./Data/covid19_'+country+'_stats.csv', index=False)\n",
    "            print('Successfully updated: ',country)\n",
    "            \n",
    "        else:\n",
    "            \n",
    "            print(\"No updates: \", country)\n",
    "        \n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/hp/anaconda3/envs/r_env/lib/python3.7/site-packages/pandas/core/generic.py:5303: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self[name] = value\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No updates:  us\n",
      "Successfully updated:  brazil\n",
      "No updates:  russia\n",
      "No updates:  spain\n",
      "No updates:  italy\n",
      "No updates:  france\n",
      "No updates:  germany\n",
      "No updates:  turkey\n",
      "No updates:  india\n",
      "No updates:  iran\n",
      "No updates:  peru\n",
      "No updates:  canada\n",
      "No updates:  chile\n",
      "Successfully updated:  china\n",
      "Successfully updated:  mexico\n",
      "No updates:  saudi-arabia\n",
      "Successfully updated:  pakistan\n",
      "No updates:  belgium\n",
      "No updates:  qatar\n",
      "No updates:  bangladesh\n",
      "No updates:  belarus\n",
      "No updates:  ecuador\n",
      "No updates:  sweden\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_updates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "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>date</th>\n",
       "      <th>total_cases</th>\n",
       "      <th>daily_cases</th>\n",
       "      <th>active_cases</th>\n",
       "      <th>total_deaths</th>\n",
       "      <th>daily_deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02-16</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-02-18</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>150793</td>\n",
       "      <td>5843.0</td>\n",
       "      <td>82172.0</td>\n",
       "      <td>4344</td>\n",
       "      <td>172.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2020-05-27</td>\n",
       "      <td>158086</td>\n",
       "      <td>7293.0</td>\n",
       "      <td>85803.0</td>\n",
       "      <td>4534</td>\n",
       "      <td>190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2020-05-28</td>\n",
       "      <td>165386</td>\n",
       "      <td>7300.0</td>\n",
       "      <td>89755.0</td>\n",
       "      <td>4711</td>\n",
       "      <td>177.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>173491</td>\n",
       "      <td>8105.0</td>\n",
       "      <td>85884.0</td>\n",
       "      <td>4980</td>\n",
       "      <td>269.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2020-05-30</td>\n",
       "      <td>181796</td>\n",
       "      <td>8305.0</td>\n",
       "      <td>94456.0</td>\n",
       "      <td>5185</td>\n",
       "      <td>205.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>106 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date  total_cases  daily_cases  active_cases  total_deaths  \\\n",
       "0    2020-02-15            3          NaN           0.0             0   \n",
       "1    2020-02-16            3          0.0           0.0             0   \n",
       "2    2020-02-17            3          0.0           0.0             0   \n",
       "3    2020-02-18            3          0.0           0.0             0   \n",
       "4    2020-02-19            3          0.0           0.0             0   \n",
       "..          ...          ...          ...           ...           ...   \n",
       "101  2020-05-26       150793       5843.0       82172.0          4344   \n",
       "102  2020-05-27       158086       7293.0       85803.0          4534   \n",
       "103  2020-05-28       165386       7300.0       89755.0          4711   \n",
       "104  2020-05-29       173491       8105.0       85884.0          4980   \n",
       "105  2020-05-30       181796       8305.0       94456.0          5185   \n",
       "\n",
       "     daily_deaths  \n",
       "0             NaN  \n",
       "1             NaN  \n",
       "2             NaN  \n",
       "3             NaN  \n",
       "4             NaN  \n",
       "..            ...  \n",
       "101         172.0  \n",
       "102         190.0  \n",
       "103         177.0  \n",
       "104         269.0  \n",
       "105         205.0  \n",
       "\n",
       "[106 rows x 6 columns]"
      ]
     },
     "execution_count": 291,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('./Data/covid19_india_stats.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "from psycopg2 import connect\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"dbname=covid19_stats user=hp password=redblue22 host=127.0.0.1\")\n",
    "conn.autocommit = True\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT date FROM india_stats ORDER BY date DESC LIMIT 1\"\"\"\n",
    "\n",
    "cursor.execute(query)\n",
    "cursor.fetchone()[0] == datetime.datetime.today().date()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date            2020-02-16\n",
       "total_cases              0\n",
       "daily_cases              0\n",
       "active_cases             0\n",
       "total_deaths             0\n",
       "daily_deaths             0\n",
       "Name: 1, dtype: object"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "row = df.iloc[1].fillna(0)\n",
    "query = \"\"\"\n",
    "            INSERT INTO \"\"\"+'india'+\"\"\"_stats\n",
    "            VALUES (\n",
    "            '\"\"\"+row.date+\"\"\"',\n",
    "            \"\"\"+str(row.total_cases)+\"\"\",\n",
    "            \"\"\"+str(row.daily_cases)+\"\"\",\n",
    "            \"\"\"+str(row.active_cases)+\"\"\",\n",
    "            \"\"\"+str(row.total_deaths)+\"\"\",\n",
    "            \"\"\"+str(row.daily_deaths)+\"\"\"\n",
    "            );\n",
    "            \"\"\"\n",
    "\n",
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<connection object at 0x7fec19556b90; dsn: 'user=hp password=xxx dbname=covid19_stats host=127.0.0.1', closed: 0>"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "metadata": {},
   "outputs": [],
   "source": [
    "def connect_database(dbname, user='hp', password='test1234', host='127.0.0.1', autocommit=False):\n",
    "    \"\"\"\n",
    "    Connect to the PostgreSQL database based on the parameters.\n",
    "    \n",
    "    parameters:\n",
    "        dbname: str.\n",
    "        Name of the database.\n",
    "        \n",
    "        user: str.\n",
    "        Name of the user. Default hp.\n",
    "        \n",
    "        password: str.\n",
    "        Password for the user to connect.\n",
    "        \n",
    "        host: str.\n",
    "        The IP address of the hosted database. Default 127.0.0.1 (localhost).\n",
    "        \n",
    "        autocommit: Boolean.\n",
    "        Set autocommit to True or False. Default False\n",
    "        \n",
    "    returns:\n",
    "        conn: connection object.\n",
    "        A connection object to the database.\n",
    "    \"\"\"\n",
    "    \n",
    "    conn = connect(\"dbname=\"+dbname+\" user=\"+user+\" password=\"+password+\" host=\"+host)\n",
    "    conn.autocommit = True\n",
    "    \n",
    "    return conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<connection object at 0x7fec19f6fcd0; dsn: 'user=hp password=xxx dbname=covid19_stats host=127.0.0.1', closed: 0>"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connect_database('covid19_stats',password='redblue22',autocommit= True)c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_country_relations(conn):\n",
    "    \"\"\"\n",
    "    Create relations for every country in the database, for covid-19 statistics.\n",
    "    \n",
    "    parameters:\n",
    "        conn: connection object.\n",
    "        A connection object to the database.\n",
    "        \n",
    "    returns: bool.\n",
    "    Boolean.\n",
    "    \"\"\"\n",
    "    \n",
    "    cursor = conn.cursor()\n",
    "    \n",
    "    for country in countries:\n",
    "        \n",
    "        data = pd.read_csv('./Data/covid19_'+country+'_stats.csv')\n",
    "        country = re.sub('-','',country)\n",
    "        \n",
    "        query = \"DROP TABLE IF EXISTS \"+country+\"_stats;\"\n",
    "        cursor.execute(query)\n",
    "        \n",
    "        query = \"\"\"\n",
    "        CREATE TABLE \"\"\"+country+\"\"\"_stats (\n",
    "            date DATE PRIMARY KEY, \n",
    "            total_cases INT,\n",
    "            daily_cases INT,\n",
    "            active_cases INT,\n",
    "            total_deaths INT,\n",
    "            daily_deaths INT\n",
    "        );\n",
    "        \"\"\"\n",
    "        \n",
    "        cursor.execute(query)\n",
    "        \n",
    "        for ind,row in data.iterrows():\n",
    "            \n",
    "            row = row.fillna(0)\n",
    "            \n",
    "            query = \"\"\"\n",
    "            INSERT INTO \"\"\"+country+\"\"\"_stats\n",
    "            VALUES (\n",
    "            '\"\"\"+row.date+\"\"\"',\n",
    "            \"\"\"+str(row.total_cases)+\"\"\",\n",
    "            \"\"\"+str(row.daily_cases)+\"\"\",\n",
    "            \"\"\"+str(row.active_cases)+\"\"\",\n",
    "            \"\"\"+str(row.total_deaths)+\"\"\",\n",
    "            \"\"\"+str(row.daily_deaths)+\"\"\"\n",
    "            );\"\"\"\n",
    "            \n",
    "            cursor.execute(query)\n",
    "            \n",
    "        print('Table successfully created: ',country+'_stats')\n",
    "        \n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table successfully created:  us_stats\n",
      "Table successfully created:  brazil_stats\n",
      "Table successfully created:  russia_stats\n",
      "Table successfully created:  spain_stats\n",
      "Table successfully created:  italy_stats\n",
      "Table successfully created:  france_stats\n",
      "Table successfully created:  germany_stats\n",
      "Table successfully created:  turkey_stats\n",
      "Table successfully created:  india_stats\n",
      "Table successfully created:  iran_stats\n",
      "Table successfully created:  peru_stats\n",
      "Table successfully created:  canada_stats\n",
      "Table successfully created:  chile_stats\n",
      "Table successfully created:  china_stats\n",
      "Table successfully created:  mexico_stats\n",
      "Table successfully created:  saudiarabia_stats\n",
      "Table successfully created:  pakistan_stats\n",
      "Table successfully created:  belgium_stats\n",
      "Table successfully created:  qatar_stats\n",
      "Table successfully created:  bangladesh_stats\n",
      "Table successfully created:  belarus_stats\n",
      "Table successfully created:  ecuador_stats\n",
      "Table successfully created:  sweden_stats\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 292,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_country_relations(connect_database('covid19_stats',password='redblue22',autocommit= True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_overall_relation(conn):\n",
    "    \"\"\"\n",
    "    Create the relation containing overall statistics for all the countries.\n",
    "    \n",
    "    parameters:\n",
    "        conn: connection object.\n",
    "        A connection object to the database.\n",
    "        \n",
    "    returns: bool.\n",
    "    Boolean.\n",
    "    \"\"\"\n",
    "    \n",
    "    cursor = conn.cursor()\n",
    "    \n",
    "    data = pd.read_csv('./Data/covid19_overall_stat.csv')\n",
    "    \n",
    "    query = \"DROP TABLE IF EXISTS overall_stats;\"\n",
    "    cursor.execute(query)\n",
    "        \n",
    "    query = \"\"\"\n",
    "        CREATE TABLE overall_stats (\n",
    "            country VARCHAR(25), \n",
    "            total_cases BIGINT,\n",
    "            total_deaths BIGINT,\n",
    "            total_recoveries BIGINT\n",
    "        );\n",
    "        \"\"\"\n",
    "    cursor.execute(query)\n",
    "    \n",
    "    for ind,row in data.iterrows():\n",
    "        \n",
    "        row = row.fillna(0)\n",
    "        row.country = row.country.replace(' ','')\n",
    "        \n",
    "        query = \"\"\"\n",
    "            INSERT INTO overall_stats VALUES (\n",
    "            '\"\"\"+row.country+\"\"\"',\n",
    "            \"\"\"+str(row.total_cases)+\"\"\",\n",
    "            \"\"\"+str(row.total_deaths)+\"\"\",\n",
    "            \"\"\"+str(row.total_recoveries)+\"\"\"\n",
    "            );\"\"\"\n",
    "        \n",
    "        cursor.execute(query)\n",
    "        \n",
    "    print(\"Table successfully created\")\n",
    "    \n",
    "    return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table successfully created\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 293,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_overall_relation(connect_database('covid19_stats',password='redblue22',autocommit= True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_database(conn):\n",
    "    \"\"\"\n",
    "    Updates the database with the daily updates on statistics for countries.\n",
    "    \n",
    "    parameters:\n",
    "        conn: connection object.\n",
    "        A connection object to the database.\n",
    "        \n",
    "    returns: bool.\n",
    "    Boolean.\n",
    "    \"\"\"\n",
    "    \n",
    "    cursor = conn.cursor()\n",
    "    \n",
    "    for country in countries:\n",
    "        \n",
    "        query = \"SELECT date FROM \"+country.replace('-','')+\"_stats ORDER BY date DESC LIMIT 1;\"\n",
    "        cursor.execute(query)\n",
    "        last_date = cursor.fetchone()[0]\n",
    "        \n",
    "        if (pd.to_datetime(last_date).date() != datetime.datetime.today().date()):\n",
    "            \n",
    "            data = pd.read_csv('./Data/covid19_'+country+'_stats.csv')\n",
    "            update = data.iloc[-1].fillna(0)\n",
    "            \n",
    "            if pd.to_datetime(update.date).date() != datetime.datetime.today().date():\n",
    "                \n",
    "                continue\n",
    "            \n",
    "            query = \"\"\"\n",
    "            INSERT INTO \"\"\"+country.replace('-','')+\"\"\"_stats\n",
    "            VALUES (\n",
    "            '\"\"\"+update.date+\"\"\"',\n",
    "            \"\"\"+str(update.total_cases)+\"\"\",\n",
    "            \"\"\"+str(update.daily_cases)+\"\"\",\n",
    "            \"\"\"+str(update.active_cases)+\"\"\",\n",
    "            \"\"\"+str(update.total_deaths)+\"\"\",\n",
    "            \"\"\"+str(update.daily_deaths)+\"\"\"\n",
    "            );\n",
    "            \"\"\"\n",
    "            \n",
    "            cursor.execute(query)\n",
    "            \n",
    "            print(\"Table\",country+\"_stats updated\")\n",
    "            \n",
    "    \n",
    "    return True\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 312,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "update_database(connect_database('covid19_stats',password='test1234',autocommit= True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<connection object at 0x7fec11123050; dsn: 'user=hp password=xxx dbname=covid19_stats host=127.0.0.1', closed: 0>"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connect_database('covid19_stats',password='test1234',autocommit=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}