database/db_creation_postgresql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ff4fcbe1",
   "metadata": {},
   "source": [
    "# PostgreSQL Database\n",
    "\n",
    "This notebook is for creating a postgreSQL database from the csv files in the data\\transformed_data folder. Please be sure to verify your database information in the config.py file in the app folder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e356f1c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load dependencies\n",
    "import sys\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\n",
    "from sqlalchemy_utils import database_exists, create_database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "930446cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Append path to root directory to access app folder\n",
    "sys.path.append('../')\n",
    "\n",
    "# Import database information\n",
    "from app.config import db_username, db_password, db_host, db_port, db_name, db_options"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35e42668",
   "metadata": {},
   "source": [
    "## Create Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a5e81d74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database name: us_hate_crimes\n",
      "Database host: localhost\n",
      "Database user: postgres\n"
     ]
    }
   ],
   "source": [
    "# Display name of database to be created or updated\n",
    "print(f'Database name: {db_name}')\n",
    "print(f'Database host: {db_host}')\n",
    "print(f'Database user: {db_username}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ed278b2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create SQLAlchemy database engine \n",
    "db_url = f'postgresql://{db_username}:{db_password}@{db_host}/{db_name}{db_options}'\n",
    "engine = create_engine(db_url)\n",
    "\n",
    "# Create database if it doesn't exist\n",
    "if not database_exists(engine.url): create_database(engine.url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a7d5aa8",
   "metadata": {},
   "source": [
    "## Create Database Tables and Views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c4fa4ccd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create inspector to verify creation tables and views\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3afc9a66",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables in Database: ['jurisdiction', 'incident', 'state', 'race', 'incident_bias', 'bias', 'incident_offense', 'offense', 'incident_victim_type', 'victim_type', 'incident_location', 'location', 'census_data']\n"
     ]
    }
   ],
   "source": [
    "# Execute SQL code to create all database tables\n",
    "with engine.connect() as con:\n",
    "    # Read and execute sql file\n",
    "    with open('schema_erd/db_schema_tables.sql') as file:\n",
    "        query = text(file.read())\n",
    "        con.execute(query)\n",
    "        con.commit()\n",
    "\n",
    "# Close database connection\n",
    "con.close()\n",
    "        \n",
    "# Display tables in database\n",
    "print(f'Tables in Database: {inspector.get_table_names()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "64a036f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Views in Database: ['year_view', 'population_view', 'incident_view']\n"
     ]
    }
   ],
   "source": [
    "# Execute SQL code to create all database views\n",
    "with engine.connect() as con:\n",
    "    # Read and execute sql file\n",
    "    with open('schema_erd/db_schema_views.sql') as file:\n",
    "        query = text(file.read())\n",
    "        con.execute(query)\n",
    "        con.commit()\n",
    "\n",
    "# Close database connection\n",
    "con.close()\n",
    "        \n",
    "# Display views in database\n",
    "print(f'Views in Database: {inspector.get_view_names()}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4525759",
   "metadata": {},
   "source": [
    "## Load Data into Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0424ca2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store filenames in a list - the order ensures no foreign key constraint errors\n",
    "tables = ['jurisdiction', 'state', 'race', 'incident', 'bias', 'incident_bias',\n",
    "          'offense', 'incident_offense', 'victim_type', 'incident_victim_type',\n",
    "          'location', 'incident_location','census_data'\n",
    "         ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a29acdab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table jurisdiction is empty\n",
      "Table state is empty\n",
      "Table race is empty\n",
      "Table incident is empty\n",
      "Table bias is empty\n",
      "Table incident_bias is empty\n",
      "Table offense is empty\n",
      "Table incident_offense is empty\n",
      "Table victim_type is empty\n",
      "Table incident_victim_type is empty\n",
      "Table location is empty\n",
      "Table incident_location is empty\n",
      "Table census_data is empty\n"
     ]
    }
   ],
   "source": [
    "# Check if any tables contain data\n",
    "for table in tables:\n",
    "    df = pd.read_sql(f'select * from {table}', engine)\n",
    "    rows = df.shape[0]\n",
    "    if not rows:\n",
    "        print(f'Table {table} is empty')\n",
    "    else:\n",
    "        print(f'Error: Table {table} contains data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b50e7e70",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table created: jurisdiction\n",
      "Table created: state\n",
      "Table created: race\n",
      "Table created: incident\n",
      "Table created: bias\n",
      "Table created: incident_bias\n",
      "Table created: offense\n",
      "Table created: incident_offense\n",
      "Table created: victim_type\n",
      "Table created: incident_victim_type\n",
      "Table created: location\n",
      "Table created: incident_location\n",
      "Table created: census_data\n"
     ]
    }
   ],
   "source": [
    "# Loop through list, read each file into a dataframe, then append to empty table in the database\n",
    "for table in tables:\n",
    "    df = pd.read_csv('../data/transformed_data/' + table + '.csv')\n",
    "    df.to_sql(table, engine, if_exists='append', index=False)\n",
    "    print(f'Table created: {table}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "60b0bc1c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows in table jurisdiction: 8\n",
      "Rows in table state: 51\n",
      "Rows in table race: 7\n",
      "Rows in table incident: 81666\n",
      "Rows in table bias: 34\n",
      "Rows in table incident_bias: 82716\n",
      "Rows in table offense: 49\n",
      "Rows in table incident_offense: 84555\n",
      "Rows in table victim_type: 9\n",
      "Rows in table incident_victim_type: 83043\n",
      "Rows in table location: 46\n",
      "Rows in table incident_location: 81790\n",
      "Rows in table census_data: 3978\n"
     ]
    }
   ],
   "source": [
    "# Display # of rows of data in each table\n",
    "for table in tables:\n",
    "    df = pd.read_sql(f'select * from {table}', engine)\n",
    "    print(f'Rows in table {table}: {df.shape[0]}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>jurisdiction_id</th>\n",
       "      <th>jurisdiction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>State Police</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Other State Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Federal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>University or College</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Tribal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   jurisdiction_id           jurisdiction\n",
       "0                0                   City\n",
       "1                1                 County\n",
       "2                2           State Police\n",
       "3                3     Other State Agency\n",
       "4                4                Federal\n",
       "5                5  University or College\n",
       "6                6                 Tribal\n",
       "7                7                  Other"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display a table to verify data\n",
    "pd.read_sql('select * from jurisdiction', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b689ea7",
   "metadata": {},
   "source": [
    "## Delete Database Data\n",
    "Use this code to reset the database. Note this does not delete the tables or views. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "549e67ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create MetaData object and reflect existing database\n",
    "metadata = MetaData()\n",
    "metadata.reflect(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a44e90d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store filenames in a list - the order ensures no foreign key constraint errors\n",
    "tables = ['census_data', 'incident_location', 'location', 'incident_victim_type', 'victim_type',\n",
    "          'incident_offense', 'offense', 'incident_bias', 'bias', 'incident', 'race', 'state',\n",
    "          'jurisdiction']\n",
    "\n",
    "# Open connection to database\n",
    "with engine.connect() as con:\n",
    "    # Loop through tables and delete all data\n",
    "    for table in tables:\n",
    "        table_obj = Table(table, metadata, autoload_with=engine)\n",
    "        con.execute(table_obj.delete())\n",
    "        con.commit()\n",
    "        \n",
    "# Close database connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "881a1e44",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify tables are empty\n",
    "for table in tables:\n",
    "    df = pd.read_sql(f'select * from {table}', engine)\n",
    "    print(f'{table} table contains {df.shape[0]} rows')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34c74f03",
   "metadata": {},
   "source": [
    "## Database Testing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0e4f6d06",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create database engine\n",
    "db_url = f'postgresql://{db_username}:{db_password}@{db_host}:{db_port}/{db_name}'\n",
    "        \n",
    "engine = create_engine(db_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "959f5ee5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create inspector for testing\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "87dbb17f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables: ['jurisdiction', 'incident', 'state', 'race', 'incident_bias', 'bias', 'incident_offense', 'offense', 'incident_victim_type', 'victim_type', 'incident_location', 'location', 'census_data']\n",
      "Views: ['year_view', 'population_view', 'incident_view']\n"
     ]
    }
   ],
   "source": [
    "# Display tables and views in database\n",
    "print(f'Tables: {inspector.get_table_names()}')\n",
    "print(f'Views: {inspector.get_view_names()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4edf1309",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'bias_id',\n",
       "  'type': INTEGER(),\n",
       "  'nullable': False,\n",
       "  'default': None,\n",
       "  'autoincrement': False,\n",
       "  'comment': None},\n",
       " {'name': 'bias',\n",
       "  'type': VARCHAR(length=60),\n",
       "  'nullable': False,\n",
       "  'default': None,\n",
       "  'autoincrement': False,\n",
       "  'comment': None},\n",
       " {'name': 'bias_category',\n",
       "  'type': VARCHAR(length=30),\n",
       "  'nullable': False,\n",
       "  'default': None,\n",
       "  'autoincrement': False,\n",
       "  'comment': None}]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Display columns in one table\n",
    "display(inspector.get_columns('bias'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "13780c94",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create MetaData object and reflect existing database\n",
    "metadata = MetaData()\n",
    "metadata.reflect(engine)\n",
    "\n",
    "# Create mappings\n",
    "Base = automap_base(metadata=metadata)\n",
    "Base.prepare(autoload_with=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ff4a4d08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define variable for state table\n",
    "S = Base.classes.state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "863bd369",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Alaska', 'AK')\n",
      "('Alabama', 'AL')\n",
      "('Arkansas', 'AR')\n",
      "('Arizona', 'AZ')\n",
      "('California', 'CA')\n",
      "('Colorado', 'CO')\n",
      "('Connecticut', 'CT')\n",
      "('District of Columbia', 'DC')\n",
      "('Delaware', 'DE')\n",
      "('Florida', 'FL')\n",
      "('Georgia', 'GA')\n",
      "('Hawaii', 'HI')\n",
      "('Iowa', 'IA')\n",
      "('Idaho', 'ID')\n",
      "('Illinois', 'IL')\n",
      "('Indiana', 'IN')\n",
      "('Kansas', 'KS')\n",
      "('Kentucky', 'KY')\n",
      "('Louisiana', 'LA')\n",
      "('Massachusetts', 'MA')\n",
      "('Maryland', 'MD')\n",
      "('Maine', 'ME')\n",
      "('Michigan', 'MI')\n",
      "('Minnesota', 'MN')\n",
      "('Missouri', 'MO')\n",
      "('Mississippi', 'MS')\n",
      "('Montana', 'MT')\n",
      "('Nebraska', 'NB')\n",
      "('North Carolina', 'NC')\n",
      "('North Dakota', 'ND')\n",
      "('New Hampshire', 'NH')\n",
      "('New Jersey', 'NJ')\n",
      "('New Mexico', 'NM')\n",
      "('Nevada', 'NV')\n",
      "('New York', 'NY')\n",
      "('Ohio', 'OH')\n",
      "('Oklahoma', 'OK')\n",
      "('Oregon', 'OR')\n",
      "('Pennsylvania', 'PA')\n",
      "('Rhode Island', 'RI')\n",
      "('South Carolina', 'SC')\n",
      "('South Dakota', 'SD')\n",
      "('Tennessee', 'TN')\n",
      "('Texas', 'TX')\n",
      "('Utah', 'UT')\n",
      "('Virginia', 'VA')\n",
      "('Vermont', 'VT')\n",
      "('Washington', 'WA')\n",
      "('Wisconsin', 'WI')\n",
      "('West Virginia', 'WV')\n",
      "('Wyoming', 'WY')\n"
     ]
    }
   ],
   "source": [
    "# Display data in state table\n",
    "try:\n",
    "    # Open session and query all table\n",
    "    session = Session(engine)\n",
    "    results = session.query(S).with_entities(*[S.state, S.state_abbr]).all()\n",
    "    session.close()\n",
    "    \n",
    "    # Print each row in table\n",
    "    for row in results:\n",
    "        print(row)\n",
    "    \n",
    "except Exception as e:\n",
    "    print('Error accessing table: ', str(e))"
   ]
  }
 ],
 "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
}