Collect_data_and_save_to_MySql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8f2cf1b3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# !pip install mysql\n",
    "# !pip install mysql-connector\n",
    "# !pip install pymysql\n",
    "#!pip install uszipcode\n",
    "#!pip install geopy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "015d070e",
   "metadata": {},
   "source": [
    "## 1. Import Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "8daf36f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import re\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "import sys\n",
    "import mysql.connector\n",
    "from mysql.connector import Error\n",
    "import mysql\n",
    "from sqlalchemy import create_engine\n",
    "import pymysql\n",
    "import geopy\n",
    "import os\n",
    "import csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33260613",
   "metadata": {},
   "source": [
    "## 2. Load hypter link and datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b630295",
   "metadata": {},
   "outputs": [],
   "source": [
    "url  = \"https://www.zillow.com/research/data/\"\n",
    "        response = requests.get(url)\n",
    "        soup = BeautifulSoup(response.content,features=\"lxml\")\n",
    "        href = soup.find('a', attrs={'id':'home-values-forecasts-download-link'}).attrs['href']\n",
    "        print('Getting data from the following link:\\n',href)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "c8f2a290",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_zillow_data():\n",
    "    try:\n",
    "        url  = \"https://www.zillow.com/research/data/\"\n",
    "        response = requests.get(url)\n",
    "        soup = BeautifulSoup(response.content,features=\"lxml\")\n",
    "        href = soup.find('a', attrs={'id':'home-values-forecasts-download-link'}).attrs['href']\n",
    "        print('Getting data from the following link:\\n',href)\n",
    "    except:\n",
    "        return print('Load zillow data failed')\n",
    "    return pd.read_csv(href)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "f5b913a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_airbnb_data():\n",
    "    try:\n",
    "        url  = \"http://insideairbnb.com/get-the-data.html\"\n",
    "        response = requests.get(url)\n",
    "        soup = BeautifulSoup(response.content,features=\"lxml\")\n",
    "        href = soup.find('a', attrs={'href':'http://data.insideairbnb.com/united-states/ca/san-diego/2021-12-20/visualisations/listings.csv'}).attrs['href']\n",
    "        print('Getting data from the following link:\\n',href)\n",
    "    except:\n",
    "        return print('Load airbnb data failed')\n",
    "    return pd.read_csv(href)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "d5e4a742",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting data from the following link:\n",
      " https://files.zillowstatic.com/research/public_csvs/zhvf_growth/Metro_zhvf_growth_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv?t=1647494303\n"
     ]
    }
   ],
   "source": [
    "df_zillow = get_zillow_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "ba8d932b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RegionID        int64\n",
       "SizeRank        int64\n",
       "RegionName     object\n",
       "RegionType     object\n",
       "StateName      object\n",
       "BaseDate       object\n",
       "2022-03-31    float64\n",
       "2022-05-31    float64\n",
       "2023-02-28    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_zillow.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "f0c18507",
   "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>RegionID</th>\n",
       "      <th>SizeRank</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>RegionType</th>\n",
       "      <th>StateName</th>\n",
       "      <th>BaseDate</th>\n",
       "      <th>2022-03-31</th>\n",
       "      <th>2022-05-31</th>\n",
       "      <th>2023-02-28</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>102001</td>\n",
       "      <td>0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Country</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-02-28</td>\n",
       "      <td>1.8</td>\n",
       "      <td>5.9</td>\n",
       "      <td>17.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>394913</td>\n",
       "      <td>1</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>Msa</td>\n",
       "      <td>NY</td>\n",
       "      <td>2022-02-28</td>\n",
       "      <td>1.2</td>\n",
       "      <td>4.3</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>753899</td>\n",
       "      <td>2</td>\n",
       "      <td>Los Angeles-Long Beach-Anaheim, CA</td>\n",
       "      <td>Msa</td>\n",
       "      <td>CA</td>\n",
       "      <td>2022-02-28</td>\n",
       "      <td>1.6</td>\n",
       "      <td>5.6</td>\n",
       "      <td>17.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>394463</td>\n",
       "      <td>3</td>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>Msa</td>\n",
       "      <td>IL</td>\n",
       "      <td>2022-02-28</td>\n",
       "      <td>1.1</td>\n",
       "      <td>4.2</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>394514</td>\n",
       "      <td>4</td>\n",
       "      <td>Dallas-Fort Worth, TX</td>\n",
       "      <td>Msa</td>\n",
       "      <td>TX</td>\n",
       "      <td>2022-02-28</td>\n",
       "      <td>2.7</td>\n",
       "      <td>8.4</td>\n",
       "      <td>23.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  RegionID SizeRank                          RegionName RegionType StateName  \\\n",
       "0   102001        0                       United States    Country      None   \n",
       "1   394913        1                        New York, NY        Msa        NY   \n",
       "2   753899        2  Los Angeles-Long Beach-Anaheim, CA        Msa        CA   \n",
       "3   394463        3                         Chicago, IL        Msa        IL   \n",
       "4   394514        4               Dallas-Fort Worth, TX        Msa        TX   \n",
       "\n",
       "     BaseDate 2022-03-31 2022-05-31 2023-02-28  \n",
       "0  2022-02-28        1.8        5.9       17.8  \n",
       "1  2022-02-28        1.2        4.3       13.5  \n",
       "2  2022-02-28        1.6        5.6       17.2  \n",
       "3  2022-02-28        1.1        4.2       13.4  \n",
       "4  2022-02-28        2.7        8.4       23.3  "
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_zillow = df_zillow.where(pd.notnull(df_zillow), None)\n",
    "df_zillow = df_zillow.astype(str) \n",
    "df_zillow.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "4041e9bc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting data from the following link:\n",
      " http://data.insideairbnb.com/united-states/ca/san-diego/2021-12-20/visualisations/listings.csv\n"
     ]
    }
   ],
   "source": [
    "df_airbnb = get_airbnb_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "8e01da75",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#df_airbnb.drop(columns=['name'],inplace = True)\n",
    "df_airbnb = df_airbnb[['id','neighbourhood','latitude','longitude','room_type','price','minimum_nights','reviews_per_month']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "bab2d408",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check if df_coor_zip.csv extist\n",
    "if os.path.exists('data/df_coor_zip.csv') == False:\n",
    "    \n",
    "    ## Transfer latitude and longitude to zipcode with geopy.Nominatim\n",
    "    geolocator = geopy.Nominatim(user_agent='user_agent')\n",
    "    zipcodes = []\n",
    "    for i in range(len(df_airbnb)):\n",
    "        info= geolocator.reverse((df_airbnb.iloc[i].latitude, df_airbnb.iloc[i].longitude))\n",
    "        if 'postcode' in info.raw['address'].keys():\n",
    "            zipcodes.append(info.raw['address']['postcode'])\n",
    "        else:\n",
    "            zipcodes.append('Unknown')\n",
    "            \n",
    "            \n",
    "    df_airbnb['zipcode'] = [x.split('-')[0] for x in zipcodes]\n",
    "    df_coor_zip = df_airbnb.loc[:,['id','latitude','longitude','zipcode']]\n",
    "    df_coor_zip.to_csv('df_coor_zip.csv')\n",
    "    \n",
    "else: \n",
    "    df_coor_zip = pd.read_csv('data/df_coor_zip.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "d443bf95",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   object\n",
       "neighbourhood        object\n",
       "latitude             object\n",
       "longitude            object\n",
       "room_type            object\n",
       "price                object\n",
       "minimum_nights       object\n",
       "reviews_per_month    object\n",
       "zipcode              object\n",
       "dtype: object"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_airbnb['zipcode'] = df_coor_zip['zipcode']\n",
    "df_airbnb = df_airbnb.where(pd.notnull(df_airbnb), None)\n",
    "df_airbnb = df_airbnb.astype(str) \n",
    "df_airbnb.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "790e5368",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MLS                          object\n",
       "Status                       object\n",
       "Rental Residential Styles    object\n",
       "Address Number               object\n",
       "Address Street               object\n",
       "Zip                          object\n",
       "Bedrooms Total               object\n",
       "Baths Full                   object\n",
       "Baths Half                   object\n",
       "Estimated Square Feet        object\n",
       "Levels                       object\n",
       "Year Built                   object\n",
       "Listing Type                 object\n",
       "Price Reflects               object\n",
       "DOMLS                        object\n",
       "List Price                   object\n",
       "Original Price               object\n",
       "List Date Received           object\n",
       "Rented Price                 object\n",
       "Close of Escrow Date         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load rental sample data\n",
    "df_rental_sample = pd.read_csv('data/rental.csv',nrows =10)\n",
    "df_rental_sample = df_rental_sample.astype(str)\n",
    "df_rental_sample.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "b803a787",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MLS                      object\n",
       "Status                   object\n",
       "Residential Styles       object\n",
       "Address Number           object\n",
       "Address Street           object\n",
       "Zip                      object\n",
       "Map Coordinates          object\n",
       "Community                object\n",
       "Bedrooms Total           object\n",
       "Baths Full               object\n",
       "Baths Half               object\n",
       "Estimated Square Feet    object\n",
       "Year Built               object\n",
       "DOMLS                    object\n",
       "List Price               object\n",
       "Original Price           object\n",
       "List Date Received       object\n",
       "Sold Price               object\n",
       "Close of Escrow Date     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load selling sampe data\n",
    "df_selling_sample = pd.read_csv('data/selling.csv',nrows = 10)\n",
    "df_selling_sample = df_selling_sample.astype(str)\n",
    "df_selling_sample.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74ce4e99",
   "metadata": {},
   "source": [
    "## 3. Create table statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "92edc5e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# reference https://github.com/kevinchiv/Predicting-Kickstarter-Success/blob/master/00%20-%20PostgreSQL%20Through%20Jupyter.ipynb\n",
    "\n",
    "def create_table_schema(dataframe, table_name):\n",
    "    \n",
    "    col_names = dataframe.dtypes.index.values\n",
    "   \n",
    "    dtypes = dataframe.dtypes.values\n",
    "   \n",
    "    #every name in psql is lowercase by default\n",
    "    table_name = table_name.lower()\n",
    "\n",
    "    #statement for creating the table in psql\n",
    "    create_table = \"CREATE TABLE IF NOT EXISTS %s (\\n\" %table_name  \n",
    "\n",
    "    for i, col_name in enumerate(col_names):\n",
    "\n",
    "        dtype = dtypes[i]\n",
    "        col_name = col_name.lower().replace(' ', '_').replace('-', '_')\n",
    "        create_table += \"\\t\"\n",
    "\n",
    "        #assign column types and default values of null\n",
    "        if dtype == 'int64':\n",
    "            create_table = create_table + col_name + \" INT DEFAULT NULL\"\n",
    "\n",
    "        elif dtype == 'object':\n",
    "            create_table = create_table + col_name + \" VARCHAR(255) DEFAULT NULL\"\n",
    "\n",
    "        elif dtype == 'float64':\n",
    "            create_table = create_table + col_name + \" DECIMAL(10,2) DEFAULT NULL\"\n",
    "\n",
    "        if i != len(col_names) - 1:\n",
    "            create_table += \", \\n\"\n",
    "\n",
    "    #newline for cleaner print format and ; for execution\n",
    "    create_table += \"\\n);\"\n",
    "\n",
    "    print(create_table)\n",
    "    \n",
    "    return create_table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "fd030fb6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE IF NOT EXISTS zillow_tb (\n",
      "\tregionid VARCHAR(255) DEFAULT NULL, \n",
      "\tsizerank VARCHAR(255) DEFAULT NULL, \n",
      "\tregionname VARCHAR(255) DEFAULT NULL, \n",
      "\tregiontype VARCHAR(255) DEFAULT NULL, \n",
      "\tstatename VARCHAR(255) DEFAULT NULL, \n",
      "\tbasedate VARCHAR(255) DEFAULT NULL, \n",
      "\t2022_03_31 VARCHAR(255) DEFAULT NULL, \n",
      "\t2022_05_31 VARCHAR(255) DEFAULT NULL, \n",
      "\t2023_02_28 VARCHAR(255) DEFAULT NULL\n",
      ");\n",
      "CREATE TABLE IF NOT EXISTS airbnb_tb (\n",
      "\tid VARCHAR(255) DEFAULT NULL, \n",
      "\tneighbourhood VARCHAR(255) DEFAULT NULL, \n",
      "\tlatitude VARCHAR(255) DEFAULT NULL, \n",
      "\tlongitude VARCHAR(255) DEFAULT NULL, \n",
      "\troom_type VARCHAR(255) DEFAULT NULL, \n",
      "\tprice VARCHAR(255) DEFAULT NULL, \n",
      "\tminimum_nights VARCHAR(255) DEFAULT NULL, \n",
      "\treviews_per_month VARCHAR(255) DEFAULT NULL, \n",
      "\tzipcode VARCHAR(255) DEFAULT NULL\n",
      ");\n",
      "CREATE TABLE IF NOT EXISTS rental_tb (\n",
      "\tmls VARCHAR(255) DEFAULT NULL, \n",
      "\tstatus VARCHAR(255) DEFAULT NULL, \n",
      "\trental_residential_styles VARCHAR(255) DEFAULT NULL, \n",
      "\taddress_number VARCHAR(255) DEFAULT NULL, \n",
      "\taddress_street VARCHAR(255) DEFAULT NULL, \n",
      "\tzip VARCHAR(255) DEFAULT NULL, \n",
      "\tbedrooms_total VARCHAR(255) DEFAULT NULL, \n",
      "\tbaths_full VARCHAR(255) DEFAULT NULL, \n",
      "\tbaths_half VARCHAR(255) DEFAULT NULL, \n",
      "\testimated_square_feet VARCHAR(255) DEFAULT NULL, \n",
      "\tlevels VARCHAR(255) DEFAULT NULL, \n",
      "\tyear_built VARCHAR(255) DEFAULT NULL, \n",
      "\tlisting_type VARCHAR(255) DEFAULT NULL, \n",
      "\tprice_reflects VARCHAR(255) DEFAULT NULL, \n",
      "\tdomls VARCHAR(255) DEFAULT NULL, \n",
      "\tlist_price VARCHAR(255) DEFAULT NULL, \n",
      "\toriginal_price VARCHAR(255) DEFAULT NULL, \n",
      "\tlist_date_received VARCHAR(255) DEFAULT NULL, \n",
      "\trented_price VARCHAR(255) DEFAULT NULL, \n",
      "\tclose_of_escrow_date VARCHAR(255) DEFAULT NULL\n",
      ");\n",
      "CREATE TABLE IF NOT EXISTS selling_tb (\n",
      "\tmls VARCHAR(255) DEFAULT NULL, \n",
      "\tstatus VARCHAR(255) DEFAULT NULL, \n",
      "\tresidential_styles VARCHAR(255) DEFAULT NULL, \n",
      "\taddress_number VARCHAR(255) DEFAULT NULL, \n",
      "\taddress_street VARCHAR(255) DEFAULT NULL, \n",
      "\tzip VARCHAR(255) DEFAULT NULL, \n",
      "\tmap_coordinates VARCHAR(255) DEFAULT NULL, \n",
      "\tcommunity VARCHAR(255) DEFAULT NULL, \n",
      "\tbedrooms_total VARCHAR(255) DEFAULT NULL, \n",
      "\tbaths_full VARCHAR(255) DEFAULT NULL, \n",
      "\tbaths_half VARCHAR(255) DEFAULT NULL, \n",
      "\testimated_square_feet VARCHAR(255) DEFAULT NULL, \n",
      "\tyear_built VARCHAR(255) DEFAULT NULL, \n",
      "\tdomls VARCHAR(255) DEFAULT NULL, \n",
      "\tlist_price VARCHAR(255) DEFAULT NULL, \n",
      "\toriginal_price VARCHAR(255) DEFAULT NULL, \n",
      "\tlist_date_received VARCHAR(255) DEFAULT NULL, \n",
      "\tsold_price VARCHAR(255) DEFAULT NULL, \n",
      "\tclose_of_escrow_date VARCHAR(255) DEFAULT NULL\n",
      ");\n"
     ]
    }
   ],
   "source": [
    "TABLES = {}\n",
    "TABLES['zillow_tb']= create_table_schema(df_zillow,'zillow_tb')\n",
    "TABLES['airbnb_tb'] = create_table_schema(df_airbnb,'airbnb_tb')\n",
    "TABLES['rental_tb'] = create_table_schema(df_rental_sample,'rental_tb')\n",
    "TABLES['selling_tb'] = create_table_schema(df_selling_sample,'selling_tb')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "497a6ed4",
   "metadata": {},
   "source": [
    "## 4. Create database SDRE_data and tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "8d0f671f",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Save data into database \n",
    "# Create database\n",
    "def set_database(db_name):\n",
    "    try:\n",
    "        db_con = mysql.connector.connect(host='localhost', user='root',password='root1234')\n",
    "        if db_con.is_connected():\n",
    "            cursor = db_con.cursor()\n",
    "#             cursor.execute(\"SHOW DATABASES\")\n",
    "#             print([x for x in cursor])\n",
    "            cursor.execute(f\"CREATE DATABASE IF NOT EXISTS {db_name}\")\n",
    "            print(\"Database %s is created\" %db_name)\n",
    "        db_con.close()   \n",
    "    except Error as e:\n",
    "        print(\"Error while connecting to MySQL\", e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "4f743c0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_tables(tables):\n",
    "    db_con = mysql.connector.connect(host='localhost', user='root',password='root1234',database = 'SDRE_data')\n",
    "    if db_con.is_connected():\n",
    "        cursor = db_con.cursor() \n",
    "        for table in TABLES:\n",
    "            cursor.execute(f'DROP TABLE IF EXISTS {table};')\n",
    "            #data.to_sql(tb_name, db_con, if_exists='fail');\n",
    "            cursor.execute(TABLES[table])\n",
    "            print(f'TABLE {table} is created...\\n')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "b3542e84",
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_table_statement(df, table_name):\n",
    "    \n",
    "    col_names = df.columns\n",
    "    col_n = len(col_names)\n",
    "    \n",
    "    #statement for inserting values into table\n",
    "    insert_statement = \"INSERT INTO %s (\" %table_name\n",
    "    values = \" VALUES (\"\n",
    "    insert_statement\n",
    "\n",
    "    for i, col_name in enumerate(col_names):\n",
    "\n",
    "        col_name = col_name.lower().replace(' ', '_').replace('-', '_')\n",
    "        \n",
    "        insert_statement = insert_statement + col_name \n",
    "        if i != col_n-1:\n",
    "            insert_statement+= \",\"\n",
    "        \n",
    "\n",
    "    insert_statement += \") VALUES (\" + \"%s,\"*(col_n-1) + \"%s\" +\")\"\n",
    "    \n",
    "    return insert_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "503f07ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_dataframe_to_table(data,tb_name):\n",
    "    db_con = mysql.connector.connect(host='localhost', user='root',password='root1234',database = 'SDRE_data')\n",
    "\n",
    "    if db_con.is_connected():\n",
    "        cursor = db_con.cursor() \n",
    "\n",
    "        insert_statement = insert_table_statement(data,tb_name)\n",
    "        for i in range(len(data)):\n",
    "            cursor.execute(insert_statement,tuple(data.iloc[i].to_list()))\n",
    "        db_con.commit()\n",
    "\n",
    "        db_con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "d4c74ceb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert csv file into table\n",
    "\n",
    "def insert_csv_into_table(df_sample,file, tb_name):\n",
    "    db_con = mysql.connector.connect(host='localhost', user='root',password='root1234',database = 'SDRE_data')\n",
    "    num_correct = 0\n",
    "    if db_con.is_connected():\n",
    "        cursor = db_con.cursor() \n",
    "        insert_statement = insert_table_statement(df_sample,tb_name)\n",
    "        with open(file,'r') as csv_file:\n",
    "            reader = csv.reader(csv_file,delimiter = ',')\n",
    "            next(reader)\n",
    "            for index, row in enumerate(reader):\n",
    "                try:\n",
    "                    cursor.execute(insert_statement, tuple(row))\n",
    "                    num_correct += 1\n",
    "                except Exception as error:\n",
    "                    with open(f'data/{tb_name}_error.csv', mode = 'a') as error_file:\n",
    "                        error_file.write(str(index+1))            \n",
    "            \n",
    "        db_con.commit()\n",
    "\n",
    "        db_con.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "9a0a11c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database SDRE_data is created\n",
      "TABLE zillow_tb is created...\n",
      "\n",
      "TABLE airbnb_tb is created...\n",
      "\n",
      "TABLE rental_tb is created...\n",
      "\n",
      "TABLE selling_tb is created...\n",
      "\n"
     ]
    }
   ],
   "source": [
    "set_database(\"SDRE_data\")\n",
    "create_tables(TABLES)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "3ddf4ae0",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_dataframe_to_table(df_zillow,tb_name = 'zillow_tb')\n",
    "insert_dataframe_to_table(df_airbnb,tb_name = 'airbnb_tb')\n",
    "insert_csv_into_table(df_selling_sample,'data/selling.csv','selling_tb' )\n",
    "insert_csv_into_table(df_selling_sample,'data/rental.csv','rental_tb' )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a63be9b",
   "metadata": {},
   "source": [
    "## Querying data from mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2b70493",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "2011f762",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "db_con = mysql.connector.connect(host='localhost', user='root',password='root1234',database = 'SDRE_data')\n",
    "\n",
    "if db_con.is_connected():\n",
    "    cursor = db_con.cursor() \n",
    "    query = (\"SELECT * from zillow_tb where CountyName = 'San Diego County' \")\n",
    "\n",
    "    cursor.execute(query)\n",
    "    results = list(cursor.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "eb22c812",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Zip', '91901', 'CA', 'San Diego County', 'Alpine', '2023-01-31', '28.1'), ('Zip', '91902', 'CA', 'San Diego County', 'Bonita', '2023-01-31', '28.4'), ('Zip', '91905', 'CA', 'San Diego County', 'Boulevard', '2023-01-31', '30.2'), ('Zip', '91906', 'CA', 'San Diego County', 'Campo', '2023-01-31', '27.9'), ('Zip', '91910', 'CA', 'San Diego County', 'Chula Vista', '2023-01-31', '28.0'), ('Zip', '91911', 'CA', 'San Diego County', 'Chula Vista', '2023-01-31', '27.0'), ('Zip', '91913', 'CA', 'San Diego County', 'Chula Vista', '2023-01-31', '28.5'), ('Zip', '91914', 'CA', 'San Diego County', 'Chula Vista', '2023-01-31', '28.8'), ('Zip', '91915', 'CA', 'San Diego County', 'Chula Vista', '2023-01-31', '28.3'), ('Zip', '91916', 'CA', 'San Diego County', 'Descanso', '2023-01-31', '24.8'), ('Zip', '91917', 'CA', 'San Diego County', 'Dulzura', '2023-01-31', '26.2'), ('Zip', '91931', 'CA', 'San Diego County', 'Descanso', '2023-01-31', '24.8'), ('Zip', '91932', 'CA', 'San Diego County', 'Imperial Beach', '2023-01-31', '27.7'), ('Zip', '91934', 'CA', 'San Diego County', 'Jacumba', '2023-01-31', '26.4'), ('Zip', '91935', 'CA', 'San Diego County', 'Jamul', '2023-01-31', '27.6'), ('Zip', '91941', 'CA', 'San Diego County', 'La Mesa', '2023-01-31', '29.1'), ('Zip', '91942', 'CA', 'San Diego County', 'La Mesa', '2023-01-31', '29.3'), ('Zip', '91945', 'CA', 'San Diego County', 'Lemon Grove', '2023-01-31', '28.1'), ('Zip', '91948', 'CA', 'San Diego County', 'Mount Laguna', '2023-01-31', '16.0'), ('Zip', '91950', 'CA', 'San Diego County', 'National City', '2023-01-31', '27.1'), ('Zip', '91962', 'CA', 'San Diego County', 'Descanso', '2023-01-31', '27.0'), ('Zip', '91963', 'CA', 'San Diego County', 'Potrero', '2023-01-31', '24.0'), ('Zip', '91977', 'CA', 'San Diego County', 'Spring Valley', '2023-01-31', '27.7'), ('Zip', '91978', 'CA', 'San Diego County', 'Spring Valley', '2023-01-31', '27.9'), ('Zip', '91980', 'CA', 'San Diego County', 'Tecate', '2023-01-31', '31.4'), ('Zip', '92003', 'CA', 'San Diego County', 'Bonsall', '2023-01-31', '28.1'), ('Zip', '92004', 'CA', 'San Diego County', 'Borrego Springs', '2023-01-31', '12.4'), ('Zip', '92007', 'CA', 'San Diego County', 'Encinitas', '2023-01-31', '29.3'), ('Zip', '92008', 'CA', 'San Diego County', 'Carlsbad', '2023-01-31', '30.4'), ('Zip', '92009', 'CA', 'San Diego County', 'Carlsbad', '2023-01-31', '30.5'), ('Zip', '92010', 'CA', 'San Diego County', 'Carlsbad', '2023-01-31', '29.5'), ('Zip', '92011', 'CA', 'San Diego County', 'Carlsbad', '2023-01-31', '30.7'), ('Zip', '92014', 'CA', 'San Diego County', 'Del Mar', '2023-01-31', '30.7'), ('Zip', '92019', 'CA', 'San Diego County', 'El Cajon', '2023-01-31', '28.3'), ('Zip', '92020', 'CA', 'San Diego County', 'El Cajon', '2023-01-31', '28.4'), ('Zip', '92021', 'CA', 'San Diego County', 'El Cajon', '2023-01-31', '27.8'), ('Zip', '92024', 'CA', 'San Diego County', 'Encinitas', '2023-01-31', '30.0'), ('Zip', '92025', 'CA', 'San Diego County', 'Escondido', '2023-01-31', '28.5'), ('Zip', '92026', 'CA', 'San Diego County', 'Escondido', '2023-01-31', '28.2'), ('Zip', '92027', 'CA', 'San Diego County', 'Escondido', '2023-01-31', '28.4'), ('Zip', '92028', 'CA', 'San Diego County', 'Fallbrook', '2023-01-31', '27.4'), ('Zip', '92029', 'CA', 'San Diego County', 'Escondido', '2023-01-31', '29.2'), ('Zip', '92036', 'CA', 'San Diego County', 'Julian', '2023-01-31', '27.3'), ('Zip', '92037', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.1'), ('Zip', '92040', 'CA', 'San Diego County', 'Lakeside', '2023-01-31', '28.1'), ('Zip', '92054', 'CA', 'San Diego County', 'Oceanside', '2023-01-31', '28.9'), ('Zip', '92056', 'CA', 'San Diego County', 'Oceanside', '2023-01-31', '28.4'), ('Zip', '92057', 'CA', 'San Diego County', 'Oceanside', '2023-01-31', '28.0'), ('Zip', '92058', 'CA', 'San Diego County', 'Oceanside', '2023-01-31', '28.7'), ('Zip', '92059', 'CA', 'San Diego County', 'Pala', '2023-01-31', '27.5'), ('Zip', '92060', 'CA', 'San Diego County', 'Palomar Mountain', '2023-01-31', '29.6'), ('Zip', '92061', 'CA', 'San Diego County', 'Pauma Valley', '2023-01-31', '27.2'), ('Zip', '92064', 'CA', 'San Diego County', 'Poway', '2023-01-31', '30.0'), ('Zip', '92065', 'CA', 'San Diego County', 'Ramona', '2023-01-31', '27.6'), ('Zip', '92066', 'CA', 'San Diego County', 'Ranchita', '2023-01-31', '26.5'), ('Zip', '92067', 'CA', 'San Diego County', 'Rancho Santa Fe', '2023-01-31', '29.3'), ('Zip', '92069', 'CA', 'San Diego County', 'San Marcos', '2023-01-31', '28.4'), ('Zip', '92070', 'CA', 'San Diego County', 'Santa Ysabel', '2023-01-31', '28.6'), ('Zip', '92071', 'CA', 'San Diego County', 'Santee', '2023-01-31', '28.3'), ('Zip', '92075', 'CA', 'San Diego County', 'Solana Beach', '2023-01-31', '29.9'), ('Zip', '92078', 'CA', 'San Diego County', 'San Marcos', '2023-01-31', '29.3'), ('Zip', '92081', 'CA', 'San Diego County', 'Vista', '2023-01-31', '28.7'), ('Zip', '92082', 'CA', 'San Diego County', 'Valley Center', '2023-01-31', '27.2'), ('Zip', '92083', 'CA', 'San Diego County', 'Vista', '2023-01-31', '27.7'), ('Zip', '92084', 'CA', 'San Diego County', 'Vista', '2023-01-31', '28.4'), ('Zip', '92086', 'CA', 'San Diego County', 'Warner Springs', '2023-01-31', '27.5'), ('Zip', '92091', 'CA', 'San Diego County', 'Rancho Santa Fe', '2023-01-31', '28.8'), ('Zip', '92101', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '25.0'), ('Zip', '92102', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '27.9'), ('Zip', '92103', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '27.1'), ('Zip', '92104', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.7'), ('Zip', '92105', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.9'), ('Zip', '92106', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.0'), ('Zip', '92107', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.6'), ('Zip', '92108', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '27.1'), ('Zip', '92109', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.4'), ('Zip', '92110', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '27.8'), ('Zip', '92111', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.5'), ('Zip', '92113', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.1'), ('Zip', '92114', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '27.2'), ('Zip', '92115', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.7'), ('Zip', '92116', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.7'), ('Zip', '92117', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.8'), ('Zip', '92118', 'CA', 'San Diego County', 'Coronado', '2023-01-31', '27.2'), ('Zip', '92119', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.8'), ('Zip', '92120', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.1'), ('Zip', '92121', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.3'), ('Zip', '92122', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.1'), ('Zip', '92123', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.2'), ('Zip', '92124', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.0'), ('Zip', '92126', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.3'), ('Zip', '92127', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '30.9'), ('Zip', '92128', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.0'), ('Zip', '92129', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '31.2'), ('Zip', '92130', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '30.2'), ('Zip', '92131', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '29.9'), ('Zip', '92139', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '28.3'), ('Zip', '92154', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '27.4'), ('Zip', '92173', 'CA', 'San Diego County', 'San Diego', '2023-01-31', '27.1')]\n"
     ]
    }
   ],
   "source": [
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "0f2669e0",
   "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>Region</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>StateName</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>CityName</th>\n",
       "      <th>ForecastedDate</th>\n",
       "      <th>ForecastYoYPctChange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21087</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92037</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21111</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92101</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21112</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92102</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>27.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21113</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92103</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>27.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21114</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92104</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21115</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92105</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21116</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92106</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21117</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92107</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21118</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92108</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>27.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21119</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92109</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21120</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92110</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>27.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21121</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92111</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21122</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92113</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21123</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92114</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>27.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21124</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92115</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21125</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92116</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21126</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92117</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21128</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92119</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21129</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92120</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21130</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92121</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21131</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92122</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21132</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92123</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21133</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92124</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21134</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92126</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21135</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92127</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>30.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21136</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92128</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21137</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92129</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>31.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21138</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92130</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>30.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21139</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92131</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>29.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21140</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92139</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>28.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21141</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92154</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>27.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21142</th>\n",
       "      <td>Zip</td>\n",
       "      <td>92173</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego County</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>2023-01-31</td>\n",
       "      <td>27.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Region RegionName StateName        CountyName   CityName ForecastedDate  \\\n",
       "21087    Zip      92037        CA  San Diego County  San Diego     2023-01-31   \n",
       "21111    Zip      92101        CA  San Diego County  San Diego     2023-01-31   \n",
       "21112    Zip      92102        CA  San Diego County  San Diego     2023-01-31   \n",
       "21113    Zip      92103        CA  San Diego County  San Diego     2023-01-31   \n",
       "21114    Zip      92104        CA  San Diego County  San Diego     2023-01-31   \n",
       "21115    Zip      92105        CA  San Diego County  San Diego     2023-01-31   \n",
       "21116    Zip      92106        CA  San Diego County  San Diego     2023-01-31   \n",
       "21117    Zip      92107        CA  San Diego County  San Diego     2023-01-31   \n",
       "21118    Zip      92108        CA  San Diego County  San Diego     2023-01-31   \n",
       "21119    Zip      92109        CA  San Diego County  San Diego     2023-01-31   \n",
       "21120    Zip      92110        CA  San Diego County  San Diego     2023-01-31   \n",
       "21121    Zip      92111        CA  San Diego County  San Diego     2023-01-31   \n",
       "21122    Zip      92113        CA  San Diego County  San Diego     2023-01-31   \n",
       "21123    Zip      92114        CA  San Diego County  San Diego     2023-01-31   \n",
       "21124    Zip      92115        CA  San Diego County  San Diego     2023-01-31   \n",
       "21125    Zip      92116        CA  San Diego County  San Diego     2023-01-31   \n",
       "21126    Zip      92117        CA  San Diego County  San Diego     2023-01-31   \n",
       "21128    Zip      92119        CA  San Diego County  San Diego     2023-01-31   \n",
       "21129    Zip      92120        CA  San Diego County  San Diego     2023-01-31   \n",
       "21130    Zip      92121        CA  San Diego County  San Diego     2023-01-31   \n",
       "21131    Zip      92122        CA  San Diego County  San Diego     2023-01-31   \n",
       "21132    Zip      92123        CA  San Diego County  San Diego     2023-01-31   \n",
       "21133    Zip      92124        CA  San Diego County  San Diego     2023-01-31   \n",
       "21134    Zip      92126        CA  San Diego County  San Diego     2023-01-31   \n",
       "21135    Zip      92127        CA  San Diego County  San Diego     2023-01-31   \n",
       "21136    Zip      92128        CA  San Diego County  San Diego     2023-01-31   \n",
       "21137    Zip      92129        CA  San Diego County  San Diego     2023-01-31   \n",
       "21138    Zip      92130        CA  San Diego County  San Diego     2023-01-31   \n",
       "21139    Zip      92131        CA  San Diego County  San Diego     2023-01-31   \n",
       "21140    Zip      92139        CA  San Diego County  San Diego     2023-01-31   \n",
       "21141    Zip      92154        CA  San Diego County  San Diego     2023-01-31   \n",
       "21142    Zip      92173        CA  San Diego County  San Diego     2023-01-31   \n",
       "\n",
       "      ForecastYoYPctChange  \n",
       "21087                 29.1  \n",
       "21111                 25.0  \n",
       "21112                 27.9  \n",
       "21113                 27.1  \n",
       "21114                 28.7  \n",
       "21115                 28.9  \n",
       "21116                 29.0  \n",
       "21117                 28.6  \n",
       "21118                 27.1  \n",
       "21119                 29.4  \n",
       "21120                 27.8  \n",
       "21121                 28.5  \n",
       "21122                 28.1  \n",
       "21123                 27.2  \n",
       "21124                 28.7  \n",
       "21125                 28.7  \n",
       "21126                 28.8  \n",
       "21128                 29.8  \n",
       "21129                 29.1  \n",
       "21130                 28.3  \n",
       "21131                 28.1  \n",
       "21132                 29.2  \n",
       "21133                 29.0  \n",
       "21134                 29.3  \n",
       "21135                 30.9  \n",
       "21136                 29.0  \n",
       "21137                 31.2  \n",
       "21138                 30.2  \n",
       "21139                 29.9  \n",
       "21140                 28.3  \n",
       "21141                 27.4  \n",
       "21142                 27.1  "
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_zillow[df_zillow.CityName == 'San Diego']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "182b6dcd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3054c761",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80a5f6f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlEngine       = create_engine('mysql://root:root1234@localhost/SDRE_data', pool_recycle=3600)\n",
    "\n",
    "dbConnection    = sqlEngine.connect()\n",
    "\n",
    "test_df = pd.read_sql(\"select * from SDRE_data.df_zillow\", dbConnection);\n",
    "\n",
    " \n",
    "\n",
    "pd.set_option('display.expand_frame_repr', False)\n",
    "dbConnection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "368a9a85",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3c1b440",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30ee189e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# try:\n",
    "#     conn = mysql.connect(host='localhost', database='employee', user='root', password='root@123')\n",
    "#     if conn.is_connected():\n",
    "#         cursor = conn.cursor()\n",
    "#         cursor.execute(\"select database();\")\n",
    "#         record = cursor.fetchone()\n",
    "#         print(\"You're connected to database: \", record)\n",
    "#         cursor.execute('DROP TABLE IF EXISTS employee_data;')\n",
    "#         print('Creating table....')\n",
    "# # in the below line please pass the create table statement which you want #to create\n",
    "#         cursor.execute(\"CREATE TABLE employee_data(first_name varchar(255),last_name varchar(255),company_name varchar(255),address varchar(255),city varchar(255),county varchar(255),state varchar(255),zip int,phone1 varchar(255),phone2 varchar(255),email varchar(255),web varchar(255))\")\n",
    "#         print(\"Table is created....\")\n",
    "#         #loop through the data frame\n",
    "#         for i,row in empdata.iterrows():\n",
    "#             #here %S means string values \n",
    "#             sql = \"INSERT INTO employee.employee_data VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)\"\n",
    "#             cursor.execute(sql, tuple(row))\n",
    "#             print(\"Record inserted\")\n",
    "#             # the connection is not auto committed by default, so we must commit to save our changes\n",
    "#             conn.commit()\n",
    "# except Error as e:\n",
    "#             print(\"Error while connecting to MySQL\", e)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "792751e3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1981999",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c62db78d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b109552f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fa62dcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_zillow(href,con):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0626af9c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "821ccee7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89a52803",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ac20023",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44b99a7e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f646306",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:metis] *",
   "language": "python",
   "name": "conda-env-metis-py"
  },
  "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}