scrape_yearly.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e4f3fc69",
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "import pandas as pd\n",
    "import glob \n",
    "import os\n",
    "import requests\n",
    "from bs4 import BeautifulSoup as bs\n",
    "import requests\n",
    "import urllib.parse\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f997d115",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calendar_scrape(year):\n",
    "    site = 'https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682'\n",
    "    year = year\n",
    "\n",
    "    safe_site = urllib.parse.quote_plus(site)\n",
    "\n",
    "    url = f'https://web.archive.org/__wb/calendarcaptures/2?url={safe_site}&date={year}&groupby=day'\n",
    "\n",
    "    resp = requests.get(url).json()\n",
    "\n",
    "    complete = []\n",
    "    complete_df = pd.DataFrame()\n",
    "    for item in resp['items']:\n",
    "        if len(str(item[0])) == 4:\n",
    "            day = str(item[0])[-2:]\n",
    "            month = str(item[0])[:2]\n",
    "        else:\n",
    "            day = str(item[0])[-2:]\n",
    "            month = str(item[0])[:1].zfill(2)\n",
    "\n",
    "        day_url = f'https://web.archive.org/__wb/calendarcaptures/2?url={safe_site}&date={year}{month}{day}'\n",
    "\n",
    "        time_data = requests.get(day_url).json()\n",
    "        #print(f'Finished {day_url}')\n",
    "\n",
    "        for t in time_data['items']:\n",
    "            time_str = str(t[0]).zfill(6)\n",
    "\n",
    "            final_url = f'https://web.archive.org/web/{year}{month}{day}{time_str}/{site}'\n",
    "\n",
    "            complete.append(final_url)\n",
    "            complete_df = complete_df.append(complete, ignore_index=True)\n",
    "    complete_df = complete_df.rename(columns={0:'Link'})\n",
    "    \n",
    "    return complete_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "db277518",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-2-1525279ed13a>:32: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  complete_df = complete_df.append(complete, ignore_index=True)\n"
     ]
    }
   ],
   "source": [
    "scrape_2019 = calendar_scrape(2019)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "fc1b8b68",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-2-1525279ed13a>:32: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  complete_df = complete_df.append(complete, ignore_index=True)\n"
     ]
    }
   ],
   "source": [
    "scrape_2020 = calendar_scrape(2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7d5d9fba",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-2-1525279ed13a>:32: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  complete_df = complete_df.append(complete, ignore_index=True)\n"
     ]
    }
   ],
   "source": [
    "scrape_2021 = calendar_scrape(2021)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "04825154",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-2-1525279ed13a>:32: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  complete_df = complete_df.append(complete, ignore_index=True)\n"
     ]
    }
   ],
   "source": [
    "scrape_2022 = calendar_scrape(2022)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "0266c921",
   "metadata": {},
   "outputs": [],
   "source": [
    "def annual_to_csv(df, year):\n",
    "    def df_expand(df):\n",
    "        data = df.copy()\n",
    "        new = data[\"Link\"].str.split(\"/\", expand = True)\n",
    "        new_df = new[[4,9,10,11]]\n",
    "        new_df = pd.concat([new_df,data['Link']],axis=1)\n",
    "        return new_df\n",
    "\n",
    "    test_df = df_expand(df)\n",
    "\n",
    "    def add_columns(df):\n",
    "        data = df.copy()\n",
    "        df['Year'] = data[4].str[:4]\n",
    "        df['Month'] = data[4].str[4:6]\n",
    "        df['Day'] = data[4].str[6:8]\n",
    "        df['Category'] = data[9]\n",
    "        df['Category_id'] = data[10]\n",
    "        df['bn_id'] = data[11]\n",
    "        return df[['Link','Year','Month','Day', 'Category', 'Category_id', 'bn_id']]\n",
    "\n",
    "    refined_df = add_columns(test_df)\n",
    "\n",
    "    def drop_dupes(df):\n",
    "        df_ = df.drop_duplicates(subset=['Year','Month','Day'],keep='first')\n",
    "        return df_\n",
    "\n",
    "    final = drop_dupes(refined_df)\n",
    "\n",
    "    def df_to_csv(df):\n",
    "        df.to_csv(f'{year}.csv')\n",
    "    printed = df_to_csv(final)\n",
    "    return final\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "335b8afa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div id=4b557e19-fa39-451f-b5e6-e39861e12b6d style=\"display:none; background-color:#9D6CFF; color:white; width:200px; height:30px; padding-left:5px; border-radius:4px; flex-direction:row; justify-content:space-around; align-items:center;\" onmouseover=\"this.style.backgroundColor='#BA9BF8'\" onmouseout=\"this.style.backgroundColor='#9D6CFF'\" onclick=\"window.commands?.execute('create-mitosheet-from-dataframe-output');\">See Full Dataframe in Mito</div> <script> if (window.commands.hasCommand('create-mitosheet-from-dataframe-output')) document.getElementById('4b557e19-fa39-451f-b5e6-e39861e12b6d').style.display = 'flex' </script> <table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Link</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Category</th>\n",
       "      <th>Category_id</th>\n",
       "      <th>bn_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://web.archive.org/web/20191105160139/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>05</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://web.archive.org/web/20191106171518/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>06</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>https://web.archive.org/web/20191108003406/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>08</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>https://web.archive.org/web/20191109005941/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>09</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>https://web.archive.org/web/20191110012724/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table></div>"
      ],
      "text/plain": [
       "                                                  Link  Year Month Day  \\\n",
       "0    https://web.archive.org/web/20191105160139/htt...  2019    11  05   \n",
       "9    https://web.archive.org/web/20191106171518/htt...  2019    11  06   \n",
       "20   https://web.archive.org/web/20191108003406/htt...  2019    11  08   \n",
       "77   https://web.archive.org/web/20191109005941/htt...  2019    11  09   \n",
       "119  https://web.archive.org/web/20191110012724/htt...  2019    11  10   \n",
       "\n",
       "                          Category Category_id      bn_id  \n",
       "0    Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "9    Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "20   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "77   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "119  Apple-Cell-Phones-Smartphones        9355  bn_319682  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2019 = annual_to_csv(scrape_2019, 2019)\n",
    "df_2019.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "139bf861",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div id=14b28d70-1e21-4436-bd39-01f7e632d590 style=\"display:none; background-color:#9D6CFF; color:white; width:200px; height:30px; padding-left:5px; border-radius:4px; flex-direction:row; justify-content:space-around; align-items:center;\" onmouseover=\"this.style.backgroundColor='#BA9BF8'\" onmouseout=\"this.style.backgroundColor='#9D6CFF'\" onclick=\"window.commands?.execute('create-mitosheet-from-dataframe-output');\">See Full Dataframe in Mito</div> <script> if (window.commands.hasCommand('create-mitosheet-from-dataframe-output')) document.getElementById('14b28d70-1e21-4436-bd39-01f7e632d590').style.display = 'flex' </script> <table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Link</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Category</th>\n",
       "      <th>Category_id</th>\n",
       "      <th>bn_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://web.archive.org/web/20200101003040/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2020</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>https://web.archive.org/web/20200102012246/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2020</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>https://web.archive.org/web/20200103050156/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2020</td>\n",
       "      <td>01</td>\n",
       "      <td>03</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>https://web.archive.org/web/20200104054644/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2020</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275</th>\n",
       "      <td>https://web.archive.org/web/20200105084849/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2020</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table></div>"
      ],
      "text/plain": [
       "                                                  Link  Year Month Day  \\\n",
       "0    https://web.archive.org/web/20200101003040/htt...  2020    01  01   \n",
       "20   https://web.archive.org/web/20200102012246/htt...  2020    01  02   \n",
       "65   https://web.archive.org/web/20200103050156/htt...  2020    01  03   \n",
       "119  https://web.archive.org/web/20200104054644/htt...  2020    01  04   \n",
       "275  https://web.archive.org/web/20200105084849/htt...  2020    01  05   \n",
       "\n",
       "                          Category Category_id      bn_id  \n",
       "0    Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "20   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "65   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "119  Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "275  Apple-Cell-Phones-Smartphones        9355  bn_319682  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2020 = annual_to_csv(scrape_2020, 2020)\n",
    "df_2020.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "4710df62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div id=f3695116-e39c-4973-8454-d7ca6f0581ca style=\"display:none; background-color:#9D6CFF; color:white; width:200px; height:30px; padding-left:5px; border-radius:4px; flex-direction:row; justify-content:space-around; align-items:center;\" onmouseover=\"this.style.backgroundColor='#BA9BF8'\" onmouseout=\"this.style.backgroundColor='#9D6CFF'\" onclick=\"window.commands?.execute('create-mitosheet-from-dataframe-output');\">See Full Dataframe in Mito</div> <script> if (window.commands.hasCommand('create-mitosheet-from-dataframe-output')) document.getElementById('f3695116-e39c-4973-8454-d7ca6f0581ca').style.display = 'flex' </script> <table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Link</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Category</th>\n",
       "      <th>Category_id</th>\n",
       "      <th>bn_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://web.archive.org/web/20210101021535/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2021</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>https://web.archive.org/web/20210102023131/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2021</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>https://web.archive.org/web/20210103041206/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2021</td>\n",
       "      <td>01</td>\n",
       "      <td>03</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>https://web.archive.org/web/20210104051152/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2021</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>https://web.archive.org/web/20210108225635/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2021</td>\n",
       "      <td>01</td>\n",
       "      <td>08</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table></div>"
      ],
      "text/plain": [
       "                                                  Link  Year Month Day  \\\n",
       "0    https://web.archive.org/web/20210101021535/htt...  2021    01  01   \n",
       "20   https://web.archive.org/web/20210102023131/htt...  2021    01  02   \n",
       "90   https://web.archive.org/web/20210103041206/htt...  2021    01  03   \n",
       "170  https://web.archive.org/web/20210104051152/htt...  2021    01  04   \n",
       "252  https://web.archive.org/web/20210108225635/htt...  2021    01  08   \n",
       "\n",
       "                          Category Category_id      bn_id  \n",
       "0    Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "20   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "90   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "170  Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "252  Apple-Cell-Phones-Smartphones        9355  bn_319682  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2021 = annual_to_csv(scrape_2021, 2021)\n",
    "df_2021.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "7493ecb4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div id=200ac441-d932-4e4c-bf75-ed01557fd124 style=\"display:none; background-color:#9D6CFF; color:white; width:200px; height:30px; padding-left:5px; border-radius:4px; flex-direction:row; justify-content:space-around; align-items:center;\" onmouseover=\"this.style.backgroundColor='#BA9BF8'\" onmouseout=\"this.style.backgroundColor='#9D6CFF'\" onclick=\"window.commands?.execute('create-mitosheet-from-dataframe-output');\">See Full Dataframe in Mito</div> <script> if (window.commands.hasCommand('create-mitosheet-from-dataframe-output')) document.getElementById('200ac441-d932-4e4c-bf75-ed01557fd124').style.display = 'flex' </script> <table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Link</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Category</th>\n",
       "      <th>Category_id</th>\n",
       "      <th>bn_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://web.archive.org/web/20220101154045/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2022</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://web.archive.org/web/20220102163445/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2022</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>https://web.archive.org/web/20220103044117/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2022</td>\n",
       "      <td>01</td>\n",
       "      <td>03</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>https://web.archive.org/web/20220104003523/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2022</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>https://web.archive.org/web/20220105011801/https://www.ebay.com/b/Apple-Cell-Phones-Smartphones/9355/bn_319682</td>\n",
       "      <td>2022</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>Apple-Cell-Phones-Smartphones</td>\n",
       "      <td>9355</td>\n",
       "      <td>bn_319682</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table></div>"
      ],
      "text/plain": [
       "                                                  Link  Year Month Day  \\\n",
       "0    https://web.archive.org/web/20220101154045/htt...  2022    01  01   \n",
       "9    https://web.archive.org/web/20220102163445/htt...  2022    01  02   \n",
       "27   https://web.archive.org/web/20220103044117/htt...  2022    01  03   \n",
       "65   https://web.archive.org/web/20220104003523/htt...  2022    01  04   \n",
       "230  https://web.archive.org/web/20220105011801/htt...  2022    01  05   \n",
       "\n",
       "                          Category Category_id      bn_id  \n",
       "0    Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "9    Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "27   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "65   Apple-Cell-Phones-Smartphones        9355  bn_319682  \n",
       "230  Apple-Cell-Phones-Smartphones        9355  bn_319682  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2022 = annual_to_csv(scrape_2022, 2022)\n",
    "df_2022.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f69a6fc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "a5c73ae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "def df_to_SQL(df, year):\n",
    "    engine =create_engine(\"mysql+pymysql://{user}:{pw}@localhost/{db}\".format(user='root',pw=\"Boston1429$$\",\n",
    "                                                                              db='EBAY'))\n",
    "    df.to_sql(f'iPhones_{year}', con=engine, if_exists='append',chunksize=1000,index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "30c0860a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_to_SQL(df_2019, 2019)\n",
    "df_to_SQL(df_2020, 2020)\n",
    "df_to_SQL(df_2021, 2021)\n",
    "df_to_SQL(df_2022, 2022)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2db554c",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}