main.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1: Extract, Transform, Load (ETL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1 Extract Data\n",
    "The dataset contains a list of top-level domain names, their types, and their sponsoring organization. For more information on the dataset, see https://datahub.io/core/top-level-domain-names\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_data(url, file_name): \n",
    "    # Send a GET request to the URL\n",
    "    response = requests.get(url)\n",
    "\n",
    "    # Check first if the request was successful \n",
    "    # Write the content to a file if successful\n",
    "    if response.status_code == 200:\n",
    "        with open(file_name, \"wb\") as file:\n",
    "            file.write(response.content)\n",
    "        print(\"Success! File downloaded\")\n",
    "    else:\n",
    "        print(\"Failed to download file:\", response.status_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success! File downloaded\n"
     ]
    }
   ],
   "source": [
    "url = \"https://datahub.io/core/top-level-domain-names/r/top-level-domain-names.csv.csv\"\n",
    "extract_data(url, 'ETL_data.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 Transform Data\n",
    "We desire a dataset that contains a list of **generic** top-level domains. In the following code blocks, we will be transforming the extracted data such that the dataframe only contains rows of 'generic' type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Domain</th>\n",
       "      <th>Type</th>\n",
       "      <th>Sponsoring Organisation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.abbott</td>\n",
       "      <td>generic</td>\n",
       "      <td>Abbott Laboratories, Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.abogado</td>\n",
       "      <td>generic</td>\n",
       "      <td>Top Level Domain Holdings Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.ac</td>\n",
       "      <td>country-code</td>\n",
       "      <td>Network Information Center (AC Domain Registry...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.academy</td>\n",
       "      <td>generic</td>\n",
       "      <td>Half Oaks, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>.accountant</td>\n",
       "      <td>generic</td>\n",
       "      <td>dot Accountant Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914</th>\n",
       "      <td>.zip</td>\n",
       "      <td>generic</td>\n",
       "      <td>Charleston Road Registry Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>915</th>\n",
       "      <td>.zm</td>\n",
       "      <td>country-code</td>\n",
       "      <td>Zambia Information and Communications Technolo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>916</th>\n",
       "      <td>.zone</td>\n",
       "      <td>generic</td>\n",
       "      <td>Outer Falls, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917</th>\n",
       "      <td>.zuerich</td>\n",
       "      <td>generic</td>\n",
       "      <td>Kanton Zürich (Canton of Zurich)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918</th>\n",
       "      <td>.zw</td>\n",
       "      <td>country-code</td>\n",
       "      <td>Postal and Telecommunications Regulatory Autho...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>919 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Domain          Type  \\\n",
       "0        .abbott       generic   \n",
       "1       .abogado       generic   \n",
       "2            .ac  country-code   \n",
       "3       .academy       generic   \n",
       "4    .accountant       generic   \n",
       "..           ...           ...   \n",
       "914         .zip       generic   \n",
       "915          .zm  country-code   \n",
       "916        .zone       generic   \n",
       "917     .zuerich       generic   \n",
       "918          .zw  country-code   \n",
       "\n",
       "                               Sponsoring Organisation  \n",
       "0                            Abbott Laboratories, Inc.  \n",
       "1                    Top Level Domain Holdings Limited  \n",
       "2    Network Information Center (AC Domain Registry...  \n",
       "3                                       Half Oaks, LLC  \n",
       "4                               dot Accountant Limited  \n",
       "..                                                 ...  \n",
       "914                      Charleston Road Registry Inc.  \n",
       "915  Zambia Information and Communications Technolo...  \n",
       "916                                   Outer Falls, LLC  \n",
       "917                   Kanton Zürich (Canton of Zurich)  \n",
       "918  Postal and Telecommunications Regulatory Autho...  \n",
       "\n",
       "[919 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read extracted data in a dataframe\n",
    "df = pd.read_csv('ETL_data.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Domain</th>\n",
       "      <th>Type</th>\n",
       "      <th>Sponsoring Organisation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.abbott</td>\n",
       "      <td>generic</td>\n",
       "      <td>Abbott Laboratories, Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.abogado</td>\n",
       "      <td>generic</td>\n",
       "      <td>Top Level Domain Holdings Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.academy</td>\n",
       "      <td>generic</td>\n",
       "      <td>Half Oaks, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>.accountant</td>\n",
       "      <td>generic</td>\n",
       "      <td>dot Accountant Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>.accountants</td>\n",
       "      <td>generic</td>\n",
       "      <td>Knob Town, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910</th>\n",
       "      <td>.yokohama</td>\n",
       "      <td>generic</td>\n",
       "      <td>GMO Registry, Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>911</th>\n",
       "      <td>.youtube</td>\n",
       "      <td>generic</td>\n",
       "      <td>Charleston Road Registry Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914</th>\n",
       "      <td>.zip</td>\n",
       "      <td>generic</td>\n",
       "      <td>Charleston Road Registry Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>916</th>\n",
       "      <td>.zone</td>\n",
       "      <td>generic</td>\n",
       "      <td>Outer Falls, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917</th>\n",
       "      <td>.zuerich</td>\n",
       "      <td>generic</td>\n",
       "      <td>Kanton Zürich (Canton of Zurich)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>590 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Domain     Type            Sponsoring Organisation\n",
       "0         .abbott  generic          Abbott Laboratories, Inc.\n",
       "1        .abogado  generic  Top Level Domain Holdings Limited\n",
       "3        .academy  generic                     Half Oaks, LLC\n",
       "4     .accountant  generic             dot Accountant Limited\n",
       "5    .accountants  generic                     Knob Town, LLC\n",
       "..            ...      ...                                ...\n",
       "910     .yokohama  generic                 GMO Registry, Inc.\n",
       "911      .youtube  generic      Charleston Road Registry Inc.\n",
       "914          .zip  generic      Charleston Road Registry Inc.\n",
       "916         .zone  generic                   Outer Falls, LLC\n",
       "917      .zuerich  generic   Kanton Zürich (Canton of Zurich)\n",
       "\n",
       "[590 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter dataframe \n",
    "generic_df = df[df['Type'] == 'generic']\n",
    "generic_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime, date\n",
    "\n",
    "today = date.today()\n",
    "\n",
    "# Insert a new column named 'Date', so that other users would know when this data was extracted\n",
    "generic_df['Date'] = today.strftime('%Y-%m-%d') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Domain</th>\n",
       "      <th>Type</th>\n",
       "      <th>Sponsoring Organisation</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.abbott</td>\n",
       "      <td>generic</td>\n",
       "      <td>Abbott Laboratories, Inc.</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.abogado</td>\n",
       "      <td>generic</td>\n",
       "      <td>Top Level Domain Holdings Limited</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.academy</td>\n",
       "      <td>generic</td>\n",
       "      <td>Half Oaks, LLC</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>.accountant</td>\n",
       "      <td>generic</td>\n",
       "      <td>dot Accountant Limited</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>.accountants</td>\n",
       "      <td>generic</td>\n",
       "      <td>Knob Town, LLC</td>\n",
       "      <td>2024-02-22</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>910</th>\n",
       "      <td>.yokohama</td>\n",
       "      <td>generic</td>\n",
       "      <td>GMO Registry, Inc.</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>911</th>\n",
       "      <td>.youtube</td>\n",
       "      <td>generic</td>\n",
       "      <td>Charleston Road Registry Inc.</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914</th>\n",
       "      <td>.zip</td>\n",
       "      <td>generic</td>\n",
       "      <td>Charleston Road Registry Inc.</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>916</th>\n",
       "      <td>.zone</td>\n",
       "      <td>generic</td>\n",
       "      <td>Outer Falls, LLC</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917</th>\n",
       "      <td>.zuerich</td>\n",
       "      <td>generic</td>\n",
       "      <td>Kanton Zürich (Canton of Zurich)</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>590 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Domain     Type            Sponsoring Organisation        Date\n",
       "0         .abbott  generic          Abbott Laboratories, Inc.  2024-02-22\n",
       "1        .abogado  generic  Top Level Domain Holdings Limited  2024-02-22\n",
       "3        .academy  generic                     Half Oaks, LLC  2024-02-22\n",
       "4     .accountant  generic             dot Accountant Limited  2024-02-22\n",
       "5    .accountants  generic                     Knob Town, LLC  2024-02-22\n",
       "..            ...      ...                                ...         ...\n",
       "910     .yokohama  generic                 GMO Registry, Inc.  2024-02-22\n",
       "911      .youtube  generic      Charleston Road Registry Inc.  2024-02-22\n",
       "914          .zip  generic      Charleston Road Registry Inc.  2024-02-22\n",
       "916         .zone  generic                   Outer Falls, LLC  2024-02-22\n",
       "917      .zuerich  generic   Kanton Zürich (Canton of Zurich)  2024-02-22\n",
       "\n",
       "[590 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "generic_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "generic_df.to_csv('ELT_transformed_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 Load Data\n",
    "We will store the transformed CSV file into a SQLite database. Using the `sqlalchemy` library, we can connect and write to a SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('ELT_transformed_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "590"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Create a database engine instance\n",
    "engine = create_engine('sqlite:///database.db')\n",
    "\n",
    "# Write the dataframe to a database table named 'domain_data'\n",
    "df.to_sql('domain_data', engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2: Extract, Load, and Transform (ELT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Extract Data\n",
    "Call the defined function in Part 1 to extract data from the specified URL. For more information on the dataset, see https://datahub.io/core/world-cities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success! File downloaded\n"
     ]
    }
   ],
   "source": [
    "url = 'https://pkgstore.datahub.io/core/world-cities/world-cities_csv/data/6cc66692f0e82b18216a48443b6b95da/world-cities_csv.csv'\n",
    "extract_data(url, 'ELT_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>name</th>\n",
       "      <th>country</th>\n",
       "      <th>subcountry</th>\n",
       "      <th>geonameid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>les Escaldes</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Escaldes-Engordany</td>\n",
       "      <td>3040051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andorra la Vella</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Andorra la Vella</td>\n",
       "      <td>3041563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Umm al Qaywayn</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Umm al Qaywayn</td>\n",
       "      <td>290594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ras al-Khaimah</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Raʼs al Khaymah</td>\n",
       "      <td>291074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Khawr Fakkān</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Ash Shāriqah</td>\n",
       "      <td>291696</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>23013</th>\n",
       "      <td>Bulawayo</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Bulawayo</td>\n",
       "      <td>894701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23014</th>\n",
       "      <td>Bindura</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Mashonaland Central</td>\n",
       "      <td>895061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23015</th>\n",
       "      <td>Beitbridge</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Matabeleland South</td>\n",
       "      <td>895269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23016</th>\n",
       "      <td>Epworth</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Harare</td>\n",
       "      <td>1085510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23017</th>\n",
       "      <td>Chitungwiza</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Harare</td>\n",
       "      <td>1106542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23018 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name               country           subcountry  geonameid\n",
       "0          les Escaldes               Andorra   Escaldes-Engordany    3040051\n",
       "1      Andorra la Vella               Andorra     Andorra la Vella    3041563\n",
       "2        Umm al Qaywayn  United Arab Emirates       Umm al Qaywayn     290594\n",
       "3        Ras al-Khaimah  United Arab Emirates      Raʼs al Khaymah     291074\n",
       "4          Khawr Fakkān  United Arab Emirates         Ash Shāriqah     291696\n",
       "...                 ...                   ...                  ...        ...\n",
       "23013          Bulawayo              Zimbabwe             Bulawayo     894701\n",
       "23014           Bindura              Zimbabwe  Mashonaland Central     895061\n",
       "23015        Beitbridge              Zimbabwe   Matabeleland South     895269\n",
       "23016           Epworth              Zimbabwe               Harare    1085510\n",
       "23017       Chitungwiza              Zimbabwe               Harare    1106542\n",
       "\n",
       "[23018 rows x 4 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('ELT_data.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23018"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write the dataframe to a database table named 'major_cities_data'\n",
    "df.to_sql('major_cities_data', engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 Transform Data\n",
    "For this particular data, we want to filter the table such that it retains only the cities (rows) in which the country names start with letters A to M."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Connect to the SQLite database specfied\n",
    "conn = sqlite3.connect('database.db')\n",
    "\n",
    "# Create a cursor object; Cursors are used to execute SQL queries and fetch results from the database.\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the SQL query\n",
    "query = \"\"\"\n",
    "        SELECT *\n",
    "        FROM major_cities_data\n",
    "        WHERE country BETWEEN 'A' AND 'M';\n",
    "        \"\"\"\n",
    "\n",
    "# Execute the query\n",
    "cursor.execute(query)\n",
    "\n",
    "# Save changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the cursor and connection\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}