database_engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 2 - Database Engineering\n",
    "***\n",
    "Taking the cleaned data from ***Step 1*** and creating data tables and a data model SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependency setup\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "import sqlalchemy as sa\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import Session\n",
    "# Import and establish Base for which classes will be constructed \n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "Base = declarative_base()\n",
    "\n",
    "# Import modules to declare columns and column data types\n",
    "from sqlalchemy import Column, Integer, String, Float, Date, ForeignKey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removes any existing instance of the database that this notebook creates\n",
    "db_instance=\"hawaii.sqlite\"\n",
    "\n",
    "## if file exists, delete it ##\n",
    "if os.path.isfile(db_instance):\n",
    "    os.remove(db_instance)\n",
    "else:\n",
    "    print(\"Hawaii database not created, continue to connection engine\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Defining the path for the pandas CSV imports\n",
    "clean_meas_csv = \"data_files/CLEAN_hawaii_measurements.csv\"\n",
    "clean_stat_csv = \"data_files/CLEAN_hawaii_stations.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reading csv data into pandas dataframe\n",
    "measurements_df = pd.read_csv(clean_meas_csv, header=\"infer\",dtype={\"station\":\"str\",\"date\":\"str\",\"prcp\":\"float\",\"tobs\":\"float\"})\n",
    "measurements_df[\"date\"] = pd.to_datetime(measurements_df[\"date\"],format=\"%Y-%m-%d\", errors=\"coerce\")\n",
    "stations_df = pd.read_csv(clean_stat_csv, header=\"infer\", dtype={\"station\":\"str\",\"name\":\"str\",\"latitude\":\"float\",\"longitude\":\"float\",\"elevation\":\"float\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating class for the \"stations\" table\n",
    "class Stations(Base):\n",
    "    __tablename__=\"stations\"\n",
    "    station = Column(String(50), primary_key = True, unique = True, index= True)\n",
    "    name = Column(String(255))\n",
    "    latitude = Column(Float)\n",
    "    longitude = Column(Float)\n",
    "    elevation = Column(Float)\n",
    "    \n",
    "# Creating dtype dictionary for Stations data\n",
    "dict_stations = {\"station\":sa.String(50),\n",
    "                 \"name\": sa.String(255),\n",
    "                 \"latitude\":sa.Float(),\n",
    "                 \"longitude\":sa.Float(),\n",
    "                 \"elevation\":sa.Float(),\n",
    "                }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating class for the \"measurements\" table\n",
    "class Measurements(Base):\n",
    "    __tablename__=\"measurements\"\n",
    "    id = Column(Integer, primary_key = True, unique = True, index = True)\n",
    "    station = Column(String(50), ForeignKey(\"stations.station\"))\n",
    "    date = Column(Date)\n",
    "    prcp = Column(Float)\n",
    "    tobs = Column(Float)\n",
    "    \n",
    "# Creating dtype dictionary Measurements Data\n",
    "dict_measurements = {\"id\":sa.Integer(),\n",
    "                     \"station\":sa.String(50),\n",
    "                     \"date\":sa.Date(),\n",
    "                     \"prcp\":sa.Float(),\n",
    "                     \"tobs\":sa.Float(),\n",
    "                    }\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a connection to a SQLite database\n",
    "engine = create_engine(\"sqlite:///hawaii.sqlite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating the SQLITE data tables from the previously created engine\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establishing pathway for dataframe to be inserted into SQLITE database\n",
    "from sqlalchemy.orm import Session\n",
    "session = Session(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exporting pandas measurement dataframe to hawaii.sqlite database by iterating through Measurements class\n",
    "for i in range(len(measurements_df)):\n",
    "    session.add(\n",
    "        Measurements(\n",
    "            station=measurements_df.station[i], \n",
    "            date=measurements_df.date[i], \n",
    "            prcp=measurements_df.prcp[i], \n",
    "            tobs=measurements_df.tobs[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Commiting records to the \"measurements\" table within the database\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exporting pandas station dataframe to hawaii.sqlite database by iterating through Stations class\n",
    "for i in range(len(stations_df)):\n",
    "    session.add(\n",
    "        Stations(station=stations_df.station[i], \n",
    "                 name=stations_df.name[i], \n",
    "                 latitude=stations_df.latitude[i], \n",
    "                 longitude=stations_df.longitude[i], \n",
    "                 elevation=stations_df.elevation[i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Commiting records to the \"stations\" table within the database\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(19550,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query to confirm measurement data was added to hawaii.sqlite\n",
    "engine.execute(\"SELECT COUNT(station) FROM measurements\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(9,)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query to confirm station data was added to hawaii.sqlite\n",
    "engine.execute(\"SELECT COUNT(station) FROM stations\").fetchall()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}