database/db_creation_sqlite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ff4fcbe1",
   "metadata": {},
   "source": [
    "# SQLite Database\n",
    "\n",
    "This notebook is for creating an sqlite database from the exported csv files in the data\\transformed_data folder. Unlike postGreSQL databases, SQLite databases do not require the use of additional software. The database is stored in a file in the database folder and can be easily accessed using sqlalchemy."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35e42668",
   "metadata": {},
   "source": [
    "## Database Creation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e356f1c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load dependencies\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine, MetaData, inspect, Table, Column, Integer, String, text\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4c9acba9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create sqlite database\n",
    "engine = create_engine('sqlite:///us_hate_crimes_sqlite.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b50e7e70",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table created: bias\n",
      "Table created: census_data\n",
      "Table created: incident\n",
      "Table created: incident_bias\n",
      "Table created: incident_location\n",
      "Table created: incident_offense\n",
      "Table created: incident_victim_type\n",
      "Table created: jurisdiction\n",
      "Table created: location\n",
      "Table created: offense\n",
      "Table created: race\n",
      "Table created: state\n",
      "Table created: victim_type\n"
     ]
    }
   ],
   "source": [
    "# Store filenames in a list\n",
    "table_names = ['bias', 'census_data', 'incident', 'incident_bias',\n",
    "               'incident_location', 'incident_offense', 'incident_victim_type',\n",
    "               'jurisdiction', 'location', 'offense', 'race', 'state', 'victim_type']\n",
    "\n",
    "# Loop through list, read each file into a dataframe, and then export to a table in the database\n",
    "# Notes:\n",
    "#  - Pandas to_sql cannot define a primary key nor can you set a primary key after creation in an sqlite database\n",
    "#  - SQLalchemy can not send multiple commands to an SQLite database, hence the SQL schema file cannot be used\n",
    "#  - The best method would be to define/create each table and append the corresponding dataframe\n",
    "for table in table_names:\n",
    "    df = pd.read_csv('../data/transformed_data/' + table + '.csv')\n",
    "    df.to_sql(table, engine, if_exists='replace', index=False)\n",
    "    print(f'Table created: {table}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9d31a42c",
   "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>state_abbr</th>\n",
       "      <th>state</th>\n",
       "      <th>division</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>East South Central</td>\n",
       "      <td>South</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>West South Central</td>\n",
       "      <td>South</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>California</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>West</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_abbr       state            division region\n",
       "0         AK      Alaska             Pacific   West\n",
       "1         AL     Alabama  East South Central  South\n",
       "2         AR    Arkansas  West South Central  South\n",
       "3         AZ     Arizona            Mountain   West\n",
       "4         CA  California             Pacific   West"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display a table to verify creation\n",
    "state_df = pd.read_sql('select * from state', engine)\n",
    "state_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b6ea02e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create year view in database\n",
    "\n",
    "# Query originally developed in postgreSQL database\n",
    "query = \"\"\"\n",
    "CREATE VIEW year_view AS\n",
    "SELECT ROW_NUMBER() OVER (ORDER BY 1) AS id, i.incident_year as year\n",
    "FROM incident as i\n",
    "GROUP BY i.incident_year\n",
    "ORDER BY i.incident_year;\n",
    "\"\"\"\n",
    "\n",
    "# Execute query to create view\n",
    "with engine.connect() as con:\n",
    "    con.execute(text(query))\n",
    "    con.commit()\n",
    "\n",
    "# Close database connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "54793b11",
   "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>id</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  year\n",
       "0    1  2009\n",
       "1    2  2010\n",
       "2    3  2011\n",
       "3    4  2012\n",
       "4    5  2013\n",
       "5    6  2014\n",
       "6    7  2015\n",
       "7    8  2016\n",
       "8    9  2017\n",
       "9   10  2018\n",
       "10  11  2019\n",
       "11  12  2020"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in view and display to verify creation\n",
    "pd.read_sql(\"select * from year_view\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "891eee86",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create population view in database\n",
    "\n",
    "# Query originally developed in postgreSQL database\n",
    "query = \"\"\"\n",
    "CREATE VIEW population_view AS\n",
    "SELECT ROW_NUMBER() OVER (ORDER BY 1) AS id, c.year, s.state, sum(c.population) AS population, 'All' AS _all\n",
    "FROM census_data as c\n",
    "LEFT JOIN (\n",
    "SELECT s.state_abbr, s.state, s.region, s.division\n",
    "FROM state AS s\n",
    ") s\n",
    "ON s.state_abbr = c.state_abbr\n",
    "GROUP BY c.year, s.state\n",
    "ORDER BY c.year, s.state\n",
    "\"\"\"\n",
    "\n",
    "# Execute query to create view\n",
    "with engine.connect() as con:\n",
    "    con.execute(text(query))\n",
    "    con.commit()\n",
    "\n",
    "# Close database connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3139424c",
   "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>id</th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>population</th>\n",
       "      <th>_all</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2009</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4595018</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2009</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>671529</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2009</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>5747084</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2009</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2771008</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2009</td>\n",
       "      <td>California</td>\n",
       "      <td>30669293</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>659</td>\n",
       "      <td>2021</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>8317118</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>659</th>\n",
       "      <td>660</td>\n",
       "      <td>2021</td>\n",
       "      <td>Washington</td>\n",
       "      <td>7238408</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>660</th>\n",
       "      <td>661</td>\n",
       "      <td>2021</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>1791487</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>661</th>\n",
       "      <td>662</td>\n",
       "      <td>2021</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>5741298</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662</th>\n",
       "      <td>663</td>\n",
       "      <td>2021</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>563324</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>663 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  year          state  population _all\n",
       "0      1  2009        Alabama     4595018  All\n",
       "1      2  2009         Alaska      671529  All\n",
       "2      3  2009        Arizona     5747084  All\n",
       "3      4  2009       Arkansas     2771008  All\n",
       "4      5  2009     California    30669293  All\n",
       "..   ...   ...            ...         ...  ...\n",
       "658  659  2021       Virginia     8317118  All\n",
       "659  660  2021     Washington     7238408  All\n",
       "660  661  2021  West Virginia     1791487  All\n",
       "661  662  2021      Wisconsin     5741298  All\n",
       "662  663  2021        Wyoming      563324  All\n",
       "\n",
       "[663 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in view and display to verify creation\n",
    "pd.read_sql('select * from population_view', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "60cd6e3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create incident view in database\n",
    "\n",
    "# Query originally developed in postgreSQL database\n",
    "query = \"\"\"\n",
    "CREATE VIEW incident_view AS\n",
    "SELECT ROW_NUMBER() OVER (ORDER BY 1) AS id, i.incident_id, i.incident_year, s.state_abbr, s.state, s.region, s.division, ib.bias_category, io.offense, 'All' as _all\n",
    "FROM incident AS i\n",
    "LEFT JOIN (\n",
    "SELECT ib.incident_id, b.bias_category AS bias_category\n",
    "FROM incident_bias AS ib\n",
    "LEFT JOIN (\n",
    "SELECT b.bias_id, b.bias_category\n",
    "FROM bias AS b\n",
    ") b\n",
    "ON ib.bias_id = b.bias_id\n",
    ") ib\n",
    "ON i.incident_id = ib.incident_id\n",
    "LEFT JOIN (\n",
    "SELECT io.incident_id, o.offense AS offense\n",
    "FROM incident_offense AS io\n",
    "LEFT JOIN (\n",
    "SELECT o.offense_id, o.offense\n",
    "FROM offense AS o\n",
    ") o\n",
    "ON io.offense_id = o.offense_id\n",
    ") io\n",
    "ON i.incident_id = io.incident_id\n",
    "LEFT JOIN (\n",
    "SELECT s.state_abbr, s.state, s.region, s.division\n",
    "FROM state AS s\n",
    ") s\n",
    "ON i.state_abbr = s.state_abbr\n",
    "ORDER BY i.incident_id\n",
    "\"\"\"\n",
    "\n",
    "# Execute query to create view\n",
    "with engine.connect() as con:\n",
    "    con.execute(text(query))\n",
    "    con.commit()\n",
    "\n",
    "# Close database connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a08afb83",
   "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>id</th>\n",
       "      <th>incident_id</th>\n",
       "      <th>incident_year</th>\n",
       "      <th>state_abbr</th>\n",
       "      <th>state</th>\n",
       "      <th>region</th>\n",
       "      <th>division</th>\n",
       "      <th>bias_category</th>\n",
       "      <th>offense</th>\n",
       "      <th>_all</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>136965</td>\n",
       "      <td>2009</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>South</td>\n",
       "      <td>East South Central</td>\n",
       "      <td>Race, Ethnicity or Ancestry</td>\n",
       "      <td>Intimidation</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>136966</td>\n",
       "      <td>2009</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>South</td>\n",
       "      <td>East South Central</td>\n",
       "      <td>Race, Ethnicity or Ancestry</td>\n",
       "      <td>Destruction/Damage/Vandalism of Property</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>136967</td>\n",
       "      <td>2009</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>South</td>\n",
       "      <td>East South Central</td>\n",
       "      <td>Sexual Orientation</td>\n",
       "      <td>Destruction/Damage/Vandalism of Property</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>136968</td>\n",
       "      <td>2009</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>South</td>\n",
       "      <td>East South Central</td>\n",
       "      <td>Race, Ethnicity or Ancestry</td>\n",
       "      <td>Intimidation</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>136969</td>\n",
       "      <td>2009</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>South</td>\n",
       "      <td>East South Central</td>\n",
       "      <td>Religion</td>\n",
       "      <td>Destruction/Damage/Vandalism of Property</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85751</th>\n",
       "      <td>77398</td>\n",
       "      <td>1449971</td>\n",
       "      <td>2020</td>\n",
       "      <td>CO</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>West</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>Race, Ethnicity or Ancestry</td>\n",
       "      <td>Aggravated Assault</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85752</th>\n",
       "      <td>77399</td>\n",
       "      <td>1449977</td>\n",
       "      <td>2020</td>\n",
       "      <td>CO</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>West</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>Race, Ethnicity or Ancestry</td>\n",
       "      <td>Intimidation</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85753</th>\n",
       "      <td>77400</td>\n",
       "      <td>1449982</td>\n",
       "      <td>2020</td>\n",
       "      <td>CO</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>West</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>Sexual Orientation</td>\n",
       "      <td>Simple Assault</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85754</th>\n",
       "      <td>77401</td>\n",
       "      <td>1449982</td>\n",
       "      <td>2020</td>\n",
       "      <td>CO</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>West</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>Sexual Orientation</td>\n",
       "      <td>Theft From Building</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85755</th>\n",
       "      <td>77979</td>\n",
       "      <td>1450095</td>\n",
       "      <td>2020</td>\n",
       "      <td>GA</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>South</td>\n",
       "      <td>South Atlantic</td>\n",
       "      <td>Race, Ethnicity or Ancestry</td>\n",
       "      <td>Intimidation</td>\n",
       "      <td>All</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>85756 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id  incident_id  incident_year state_abbr     state region  \\\n",
       "0         10       136965           2009         AL   Alabama  South   \n",
       "1         11       136966           2009         AL   Alabama  South   \n",
       "2         12       136967           2009         AL   Alabama  South   \n",
       "3         13       136968           2009         AL   Alabama  South   \n",
       "4         14       136969           2009         AL   Alabama  South   \n",
       "...      ...          ...            ...        ...       ...    ...   \n",
       "85751  77398      1449971           2020         CO  Colorado   West   \n",
       "85752  77399      1449977           2020         CO  Colorado   West   \n",
       "85753  77400      1449982           2020         CO  Colorado   West   \n",
       "85754  77401      1449982           2020         CO  Colorado   West   \n",
       "85755  77979      1450095           2020         GA   Georgia  South   \n",
       "\n",
       "                 division                bias_category  \\\n",
       "0      East South Central  Race, Ethnicity or Ancestry   \n",
       "1      East South Central  Race, Ethnicity or Ancestry   \n",
       "2      East South Central           Sexual Orientation   \n",
       "3      East South Central  Race, Ethnicity or Ancestry   \n",
       "4      East South Central                     Religion   \n",
       "...                   ...                          ...   \n",
       "85751            Mountain  Race, Ethnicity or Ancestry   \n",
       "85752            Mountain  Race, Ethnicity or Ancestry   \n",
       "85753            Mountain           Sexual Orientation   \n",
       "85754            Mountain           Sexual Orientation   \n",
       "85755      South Atlantic  Race, Ethnicity or Ancestry   \n",
       "\n",
       "                                        offense _all  \n",
       "0                                  Intimidation  All  \n",
       "1      Destruction/Damage/Vandalism of Property  All  \n",
       "2      Destruction/Damage/Vandalism of Property  All  \n",
       "3                                  Intimidation  All  \n",
       "4      Destruction/Damage/Vandalism of Property  All  \n",
       "...                                         ...  ...  \n",
       "85751                        Aggravated Assault  All  \n",
       "85752                              Intimidation  All  \n",
       "85753                            Simple Assault  All  \n",
       "85754                       Theft From Building  All  \n",
       "85755                              Intimidation  All  \n",
       "\n",
       "[85756 rows x 10 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in view and display to verify creation\n",
    "pd.read_sql('select * from incident_view', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34c74f03",
   "metadata": {},
   "source": [
    "## Database Testing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0e4f6d06",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create database engine\n",
    "engine = create_engine('sqlite:///us_hate_crimes_sqlite.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "87dbb17f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables: ['bias', 'census_data', 'incident', 'incident_bias', 'incident_location', 'incident_offense', 'incident_victim_type', 'jurisdiction', 'location', 'offense', 'race', 'state', 'victim_type']\n",
      "Views: ['incident_view', 'population_view', 'year_view']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[{'name': 'bias_id',\n",
       "  'type': BIGINT(),\n",
       "  'nullable': True,\n",
       "  'default': None,\n",
       "  'primary_key': 0},\n",
       " {'name': 'bias',\n",
       "  'type': TEXT(),\n",
       "  'nullable': True,\n",
       "  'default': None,\n",
       "  'primary_key': 0},\n",
       " {'name': 'bias_category',\n",
       "  'type': TEXT(),\n",
       "  'nullable': True,\n",
       "  'default': None,\n",
       "  'primary_key': 0}]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create inspector for testing\n",
    "inspector = inspect(engine)\n",
    "\n",
    "# Display tables and views in database\n",
    "print(f'Tables: {inspector.get_table_names()}')\n",
    "print(f'Views: {inspector.get_view_names()}')\n",
    "\n",
    "# Display columns in bias table\n",
    "display(inspector.get_columns('bias'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a7f31cd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reflect database and tables\n",
    "metadata = MetaData()\n",
    "metadata.reflect(engine)\n",
    "\n",
    "# Define primary keys for all tables - this is needed to reflect them\n",
    "Table(\n",
    "    'bias',\n",
    "    metadata,\n",
    "    Column('bias_id', String, primary_key=True, nullable=False),\n",
    "    autoload_with=engine,\n",
    "    extend_existing=True\n",
    ")\n",
    "\n",
    "Table(\n",
    "    'state',\n",
    "    metadata,\n",
    "    Column('state_abbr', String, primary_key=True, nullable=False),\n",
    "    autoload_with=engine,\n",
    "    extend_existing=True\n",
    ")\n",
    "\n",
    "Table(\n",
    "    'population_view',\n",
    "    metadata,\n",
    "    Column('id', String, primary_key=True, nullable=False),\n",
    "    autoload_with=engine\n",
    ")\n",
    "\n",
    "Table(\n",
    "    'year_view',\n",
    "    metadata,\n",
    "    Column('id', String, primary_key=True, nullable=False),\n",
    "    autoload_with=engine\n",
    ")\n",
    "\n",
    "Table(\n",
    "    'incident_view',\n",
    "    metadata,\n",
    "    Column('id', String, primary_key=True, nullable=False),\n",
    "    autoload_with=engine\n",
    ")\n",
    "\n",
    "\n",
    "Base = automap_base(metadata=metadata)\n",
    "Base.prepare(autoload_with=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ff4a4d08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define variables for tables and views\n",
    "B = Base.classes.bias\n",
    "S = Base.classes.state\n",
    "Y = Base.classes.year_view\n",
    "I = Base.classes.incident_view\n",
    "P = Base.classes.population_view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "863bd369",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2009,)\n",
      "(2010,)\n",
      "(2011,)\n",
      "(2012,)\n",
      "(2013,)\n",
      "(2014,)\n",
      "(2015,)\n",
      "(2016,)\n",
      "(2017,)\n",
      "(2018,)\n",
      "(2019,)\n",
      "(2020,)\n"
     ]
    }
   ],
   "source": [
    "# Start session and query year view\n",
    "session = Session(engine)\n",
    "results = session.query(Y).with_entities(*[Y.year]).all()\n",
    "session.close()\n",
    "\n",
    "# Display query results\n",
    "for row in results:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddabefbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete views - used before recreating views\n",
    "with engine.connect() as con:\n",
    "    con.execute(text('DROP VIEW year_view'))\n",
    "    con.execute(text('DROP VIEW population_view'))\n",
    "    con.execute(text('DROP VIEW incident_view'))\n",
    "    con.commit()\n",
    "\n",
    "# Close database connection\n",
    "con.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}