js_sql_export.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine, inspect, Column, Integer, String, Float, DateTime\n",
    "from sqlalchemy.orm import Session\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.ext.declarative import declarative_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sets an object to utilize the default declarative base in SQL Alchemy\n",
    "Base = declarative_base()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates Classes which will serve as the anchor points for our Tables\n",
    "class Shooting(Base):\n",
    "    __tablename__ = 'shooting_data'\n",
    "    index = Column(Integer, primary_key = True)\n",
    "    incident_id = Column(Integer)\n",
    "    incident_date = Column(String(255))\n",
    "    state = Column(String(255))\n",
    "    city_county = Column(String(255))\n",
    "    address = Column(String(255))\n",
    "    number_killed = Column(Integer)\n",
    "    number_injured = Column(Integer)\n",
    "    latitude = Column(Float)\n",
    "    longitude = Column(Float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates Classes which will serve as the anchor points for our Tables\n",
    "class Guns(Base):\n",
    "    __tablename__ = 'ownership_data'\n",
    "    index = Column(Integer, primary_key = True)\n",
    "    state = Column(String(255))\n",
    "    number_per_capita = Column(Integer)\n",
    "    number_registered = Column(Integer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates Classes which will serve as the anchor points for our Tables\n",
    "class Poverty(Base):\n",
    "    __tablename__ = 'poverty_data'\n",
    "    rank = Column(Integer)\n",
    "    state = Column(String(255), primary_key = True)\n",
    "    poverty = Column(Float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates Classes which will serve as the anchor points for our Tables\n",
    "class Election(Base):\n",
    "    __tablename__ = 'election_data'\n",
    "    state = Column(String(255), primary_key = True)\n",
    "    result = Column(String(255))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creates Classes which will serve as the anchor points for our Tables\n",
    "class State(Base):\n",
    "    __tablename__ = 'state_data'\n",
    "    state = Column(String(255), primary_key = True)\n",
    "    latitude = Column(Float)\n",
    "    longitude = Column(Float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:38:56,417 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2021-05-07 21:38:56,427 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:38:56,430 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2021-05-07 21:38:56,432 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "# Creates a connection to our DB\n",
    "engine = create_engine('sqlite:///Data/shootings.sqlite', echo=True)\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:38:59,665 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"election_data\")\n",
      "2021-05-07 21:38:59,666 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:38:59,669 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"state_data\")\n",
      "2021-05-07 21:38:59,671 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "# Create (if not already in existence) the tables associated with our classes.\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Session is a temporary binding to our DB\n",
    "session = Session(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store filepath in a variable\n",
    "file_one = \"data/final_msa.csv\"\n",
    "file_two = \"data/gun_ownership_clean.csv\"\n",
    "file_three = \"data/poverty_data_clean.csv\"\n",
    "file_four = \"data/election_data_clean.csv\"\n",
    "file_five = \"data/state_clean.csv\"\n",
    "\n",
    "# Read in csv files\n",
    "df = pd.read_csv(file_one)\n",
    "df2 = pd.read_csv(file_two)\n",
    "df3 = pd.read_csv(file_three)\n",
    "df4 = pd.read_csv(file_four)\n",
    "df5 = pd.read_csv(file_five)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df.iterrows():\n",
    "    new_row = Shooting(incident_id = df.loc[index,'incident_id'],incident_date = df.loc[index,'incident_date'],\n",
    "                       state = df.loc[index, 'state'], city_county = df.loc[index, 'city_county'],\n",
    "                       address = df.loc[index, 'address'], number_killed = int(df.loc[index, 'number_killed']),\n",
    "                      number_injured = int(df.loc[index, 'number_injured']), latitude = df.loc[index, 'latitude'],\n",
    "                       longitude = df.loc[index, 'longitude'])    \n",
    "    \n",
    "    # Add these objects to the session\n",
    "    session.add(new_row)\n",
    "    \n",
    "    # Commit the objects to the database\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df2.iterrows():\n",
    "    \n",
    "    new_gun = Guns(state = df2.loc[index, 'state'],\n",
    "                           number_per_capita = df2.loc[index, 'number_per_capita'],\n",
    "                      number_registered = df2.loc[index, 'number_registered'])   \n",
    "    \n",
    "    # Add these objects to the session\n",
    "    session.add(new_gun)\n",
    "    \n",
    "    # Commit the objects to the database\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df3.iterrows():\n",
    "    \n",
    "    new_poverty = Poverty(state = df3.loc[index, 'state'],\n",
    "                          poverty = df3.loc[index, 'poverty'])\n",
    "      \n",
    "    # Add these objects to the session\n",
    "    session.add(new_poverty)\n",
    "    \n",
    "    # Commit the objects to the database\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:39:13,976 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:13,978 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:13,980 INFO sqlalchemy.engine.base.Engine ('Alabama', 'blue')\n",
      "2021-05-07 21:39:14,014 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,073 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,075 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,076 INFO sqlalchemy.engine.base.Engine ('Alaska', 'red')\n",
      "2021-05-07 21:39:14,080 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,119 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,121 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,122 INFO sqlalchemy.engine.base.Engine ('Arizona', 'blue')\n",
      "2021-05-07 21:39:14,125 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,165 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,166 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,167 INFO sqlalchemy.engine.base.Engine ('Arkansas', 'red')\n",
      "2021-05-07 21:39:14,171 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,209 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,211 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,212 INFO sqlalchemy.engine.base.Engine ('California', 'red')\n",
      "2021-05-07 21:39:14,215 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,256 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,257 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,258 INFO sqlalchemy.engine.base.Engine ('Colorado', 'blue')\n",
      "2021-05-07 21:39:14,262 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,301 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,302 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,303 INFO sqlalchemy.engine.base.Engine ('Connecticut', 'red')\n",
      "2021-05-07 21:39:14,306 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,436 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,438 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,438 INFO sqlalchemy.engine.base.Engine ('Delaware', 'blue')\n",
      "2021-05-07 21:39:14,442 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,482 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,483 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,484 INFO sqlalchemy.engine.base.Engine ('District of Columbia', 'blue')\n",
      "2021-05-07 21:39:14,487 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,529 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,531 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,531 INFO sqlalchemy.engine.base.Engine ('Florida', 'red')\n",
      "2021-05-07 21:39:14,534 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,574 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,576 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,577 INFO sqlalchemy.engine.base.Engine ('Georgia', 'blue')\n",
      "2021-05-07 21:39:14,580 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,620 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,621 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,623 INFO sqlalchemy.engine.base.Engine ('Hawaii', 'blue')\n",
      "2021-05-07 21:39:14,626 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,665 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,667 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,668 INFO sqlalchemy.engine.base.Engine ('Idaho', 'red')\n",
      "2021-05-07 21:39:14,673 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,711 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,713 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,714 INFO sqlalchemy.engine.base.Engine ('Illinois', 'blue')\n",
      "2021-05-07 21:39:14,717 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,757 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,759 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,759 INFO sqlalchemy.engine.base.Engine ('Indiana', 'red')\n",
      "2021-05-07 21:39:14,762 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,802 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,804 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,805 INFO sqlalchemy.engine.base.Engine ('Iowa', 'red')\n",
      "2021-05-07 21:39:14,808 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,847 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,849 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,850 INFO sqlalchemy.engine.base.Engine ('Kansas', 'red')\n",
      "2021-05-07 21:39:14,854 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,893 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,895 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,896 INFO sqlalchemy.engine.base.Engine ('Kentucky', 'blue')\n",
      "2021-05-07 21:39:14,899 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,938 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,940 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,942 INFO sqlalchemy.engine.base.Engine ('Louisiana', 'blue')\n",
      "2021-05-07 21:39:14,946 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:14,985 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:14,987 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:14,988 INFO sqlalchemy.engine.base.Engine ('Maine', 'blue')\n",
      "2021-05-07 21:39:14,992 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,033 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,035 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,037 INFO sqlalchemy.engine.base.Engine ('Maryland', 'red')\n",
      "2021-05-07 21:39:15,042 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,087 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,089 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,090 INFO sqlalchemy.engine.base.Engine ('Massachusetts', 'blue')\n",
      "2021-05-07 21:39:15,094 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,134 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,135 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,137 INFO sqlalchemy.engine.base.Engine ('Michigan', 'blue')\n",
      "2021-05-07 21:39:15,141 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,179 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,181 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,182 INFO sqlalchemy.engine.base.Engine ('Minnesota', 'blue')\n",
      "2021-05-07 21:39:15,185 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,224 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,227 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:39:15,227 INFO sqlalchemy.engine.base.Engine ('Mississippi', 'red')\n",
      "2021-05-07 21:39:15,231 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,270 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,272 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,273 INFO sqlalchemy.engine.base.Engine ('Missouri', 'red')\n",
      "2021-05-07 21:39:15,277 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,315 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,317 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,318 INFO sqlalchemy.engine.base.Engine ('Montana', 'red')\n",
      "2021-05-07 21:39:15,322 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,361 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,363 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,364 INFO sqlalchemy.engine.base.Engine ('Nebraska', 'red')\n",
      "2021-05-07 21:39:15,367 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,408 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,410 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,411 INFO sqlalchemy.engine.base.Engine ('Nevada', 'blue')\n",
      "2021-05-07 21:39:15,414 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,454 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,455 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,456 INFO sqlalchemy.engine.base.Engine ('New Hampshire', 'blue')\n",
      "2021-05-07 21:39:15,460 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,500 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,502 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,502 INFO sqlalchemy.engine.base.Engine ('New Jersey', 'blue')\n",
      "2021-05-07 21:39:15,505 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,546 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,549 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,550 INFO sqlalchemy.engine.base.Engine ('New Mexico', 'blue')\n",
      "2021-05-07 21:39:15,554 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,591 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,593 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,594 INFO sqlalchemy.engine.base.Engine ('New York', 'blue')\n",
      "2021-05-07 21:39:15,598 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,637 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,641 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,644 INFO sqlalchemy.engine.base.Engine ('North Carolina', 'red')\n",
      "2021-05-07 21:39:15,648 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,682 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,684 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,685 INFO sqlalchemy.engine.base.Engine ('North Dakota', 'red')\n",
      "2021-05-07 21:39:15,688 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,726 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,728 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,731 INFO sqlalchemy.engine.base.Engine ('Ohio', 'red')\n",
      "2021-05-07 21:39:15,734 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,772 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,775 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,775 INFO sqlalchemy.engine.base.Engine ('Oklahoma', 'blue')\n",
      "2021-05-07 21:39:15,778 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,818 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,820 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,821 INFO sqlalchemy.engine.base.Engine ('Oregon', 'red')\n",
      "2021-05-07 21:39:15,825 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,865 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,867 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,868 INFO sqlalchemy.engine.base.Engine ('Pennsylvania', 'blue')\n",
      "2021-05-07 21:39:15,872 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,911 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,912 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,913 INFO sqlalchemy.engine.base.Engine ('Rhode Island', 'blue')\n",
      "2021-05-07 21:39:15,918 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:15,956 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:15,958 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:15,959 INFO sqlalchemy.engine.base.Engine ('South Carolina', 'red')\n",
      "2021-05-07 21:39:15,963 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,002 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,003 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,005 INFO sqlalchemy.engine.base.Engine ('South Dakota', 'red')\n",
      "2021-05-07 21:39:16,008 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,047 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,049 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,050 INFO sqlalchemy.engine.base.Engine ('Tennessee', 'red')\n",
      "2021-05-07 21:39:16,053 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,093 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,095 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,096 INFO sqlalchemy.engine.base.Engine ('Texas', 'red')\n",
      "2021-05-07 21:39:16,100 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,151 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,152 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,153 INFO sqlalchemy.engine.base.Engine ('Utah', 'red')\n",
      "2021-05-07 21:39:16,156 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,197 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,199 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,200 INFO sqlalchemy.engine.base.Engine ('Vermont', 'blue')\n",
      "2021-05-07 21:39:16,203 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,243 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,244 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,246 INFO sqlalchemy.engine.base.Engine ('Virginia', 'blue')\n",
      "2021-05-07 21:39:16,249 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,288 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,290 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,291 INFO sqlalchemy.engine.base.Engine ('Washington', 'blue')\n",
      "2021-05-07 21:39:16,295 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,335 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,337 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,338 INFO sqlalchemy.engine.base.Engine ('West Virginia', 'red')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:39:16,341 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,469 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,470 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,471 INFO sqlalchemy.engine.base.Engine ('Wisconsin', 'blue')\n",
      "2021-05-07 21:39:16,474 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:39:16,514 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:39:16,516 INFO sqlalchemy.engine.base.Engine INSERT INTO election_data (state, result) VALUES (?, ?)\n",
      "2021-05-07 21:39:16,517 INFO sqlalchemy.engine.base.Engine ('Wyoming', 'blue')\n",
      "2021-05-07 21:39:16,520 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "for index, row in df4.iterrows():\n",
    "    \n",
    "    new_election = Election(state = df4.loc[index, 'state'],\n",
    "                          result = df4.loc[index, 'result'])\n",
    "      \n",
    "    # Add these objects to the session\n",
    "    session.add(new_election)\n",
    "    \n",
    "    # Commit the objects to the database\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:41:55,269 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,273 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,274 INFO sqlalchemy.engine.base.Engine ('Alaska', 63.588753, -154.493062)\n",
      "2021-05-07 21:41:55,314 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,375 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,377 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,378 INFO sqlalchemy.engine.base.Engine ('Alabama', 32.318231, -86.902298)\n",
      "2021-05-07 21:41:55,382 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,420 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,422 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,423 INFO sqlalchemy.engine.base.Engine ('Arkansas', 35.20105, -91.831833)\n",
      "2021-05-07 21:41:55,426 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,465 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,467 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,467 INFO sqlalchemy.engine.base.Engine ('Arizona', 34.048928000000004, -111.093731)\n",
      "2021-05-07 21:41:55,471 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,511 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,513 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,514 INFO sqlalchemy.engine.base.Engine ('California', 36.778261, -119.417932)\n",
      "2021-05-07 21:41:55,517 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,556 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,558 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,559 INFO sqlalchemy.engine.base.Engine ('Colorado', 39.550051, -105.782067)\n",
      "2021-05-07 21:41:55,563 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,602 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,604 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,605 INFO sqlalchemy.engine.base.Engine ('Connecticut', 41.60322100000001, -73.087749)\n",
      "2021-05-07 21:41:55,608 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,647 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,649 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,650 INFO sqlalchemy.engine.base.Engine ('District of Columbia', 38.905985, -77.03341800000003)\n",
      "2021-05-07 21:41:55,653 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,694 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,695 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,696 INFO sqlalchemy.engine.base.Engine ('Delaware', 38.910832, -75.52767)\n",
      "2021-05-07 21:41:55,699 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,739 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,741 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,742 INFO sqlalchemy.engine.base.Engine ('Florida', 27.664827, -81.515754)\n",
      "2021-05-07 21:41:55,745 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,785 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,787 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,788 INFO sqlalchemy.engine.base.Engine ('Georgia', 32.157435, -82.907123)\n",
      "2021-05-07 21:41:55,791 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,832 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,834 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,834 INFO sqlalchemy.engine.base.Engine ('Hawaii', 19.898682, -155.66585700000005)\n",
      "2021-05-07 21:41:55,837 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,877 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,879 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,881 INFO sqlalchemy.engine.base.Engine ('Iowa', 41.878003, -93.097702)\n",
      "2021-05-07 21:41:55,885 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,923 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,924 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,925 INFO sqlalchemy.engine.base.Engine ('Idaho', 44.068202, -114.742041)\n",
      "2021-05-07 21:41:55,928 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:55,968 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:55,970 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:55,971 INFO sqlalchemy.engine.base.Engine ('Illinois', 40.633125, -89.398528)\n",
      "2021-05-07 21:41:55,974 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,014 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,015 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,016 INFO sqlalchemy.engine.base.Engine ('Indiana', 40.551217, -85.602364)\n",
      "2021-05-07 21:41:56,020 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,059 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,062 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,063 INFO sqlalchemy.engine.base.Engine ('Kansas', 39.011902, -98.484246)\n",
      "2021-05-07 21:41:56,066 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,105 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,107 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,108 INFO sqlalchemy.engine.base.Engine ('Kentucky', 37.839333, -84.27001800000002)\n",
      "2021-05-07 21:41:56,112 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,151 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,152 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,153 INFO sqlalchemy.engine.base.Engine ('Louisiana', 31.244823, -92.145024)\n",
      "2021-05-07 21:41:56,157 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,195 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,198 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,199 INFO sqlalchemy.engine.base.Engine ('Massachusetts', 42.407211, -71.382437)\n",
      "2021-05-07 21:41:56,202 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,241 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,243 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,244 INFO sqlalchemy.engine.base.Engine ('Maryland', 39.045755, -76.641271)\n",
      "2021-05-07 21:41:56,248 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,289 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,292 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,294 INFO sqlalchemy.engine.base.Engine ('Maine', 45.253783, -69.44546899999999)\n",
      "2021-05-07 21:41:56,298 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,335 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,337 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:41:56,338 INFO sqlalchemy.engine.base.Engine ('Michigan', 44.314844, -85.602364)\n",
      "2021-05-07 21:41:56,343 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,381 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,383 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,384 INFO sqlalchemy.engine.base.Engine ('Minnesota', 46.729553, -94.6859)\n",
      "2021-05-07 21:41:56,389 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,438 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,440 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,441 INFO sqlalchemy.engine.base.Engine ('Missouri', 37.964253, -91.831833)\n",
      "2021-05-07 21:41:56,444 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,483 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,485 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,486 INFO sqlalchemy.engine.base.Engine ('Mississippi', 32.354668, -89.398528)\n",
      "2021-05-07 21:41:56,489 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,528 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,530 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,531 INFO sqlalchemy.engine.base.Engine ('Montana', 46.879682, -110.362566)\n",
      "2021-05-07 21:41:56,536 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,574 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,576 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,577 INFO sqlalchemy.engine.base.Engine ('North Carolina', 35.759573, -79.0193)\n",
      "2021-05-07 21:41:56,580 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,619 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,621 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,622 INFO sqlalchemy.engine.base.Engine ('North Dakota', 47.551493, -101.002012)\n",
      "2021-05-07 21:41:56,625 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,665 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,667 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,668 INFO sqlalchemy.engine.base.Engine ('Nebraska', 41.492537, -99.901813)\n",
      "2021-05-07 21:41:56,672 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,713 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,715 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,715 INFO sqlalchemy.engine.base.Engine ('New Hampshire', 43.193852, -71.572395)\n",
      "2021-05-07 21:41:56,718 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,757 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,760 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,762 INFO sqlalchemy.engine.base.Engine ('New Jersey', 40.058324, -74.405661)\n",
      "2021-05-07 21:41:56,764 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,803 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,805 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,806 INFO sqlalchemy.engine.base.Engine ('New Mexico', 34.97273, -105.032363)\n",
      "2021-05-07 21:41:56,810 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,848 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,850 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,852 INFO sqlalchemy.engine.base.Engine ('Nevada', 38.80261, -116.419389)\n",
      "2021-05-07 21:41:56,857 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,894 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,896 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,897 INFO sqlalchemy.engine.base.Engine ('New York', 43.299428000000006, -74.217933)\n",
      "2021-05-07 21:41:56,900 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,941 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,942 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,943 INFO sqlalchemy.engine.base.Engine ('Ohio', 40.417287, -82.907123)\n",
      "2021-05-07 21:41:56,946 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:56,996 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:56,998 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:56,998 INFO sqlalchemy.engine.base.Engine ('Oklahoma', 35.007752, -97.092877)\n",
      "2021-05-07 21:41:57,002 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,041 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,043 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,044 INFO sqlalchemy.engine.base.Engine ('Oregon', 43.804133, -120.554201)\n",
      "2021-05-07 21:41:57,048 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,176 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,178 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,179 INFO sqlalchemy.engine.base.Engine ('Pennsylvania', 41.203322, -77.194525)\n",
      "2021-05-07 21:41:57,182 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,221 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,223 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,224 INFO sqlalchemy.engine.base.Engine ('Puerto Rico', 18.220833, -66.590149)\n",
      "2021-05-07 21:41:57,227 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,269 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,271 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,272 INFO sqlalchemy.engine.base.Engine ('Rhode Island', 41.580095, -71.477429)\n",
      "2021-05-07 21:41:57,275 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,315 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,317 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,318 INFO sqlalchemy.engine.base.Engine ('South Carolina', 33.836081, -81.163725)\n",
      "2021-05-07 21:41:57,322 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,362 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,364 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,365 INFO sqlalchemy.engine.base.Engine ('South Dakota', 43.969515, -99.901813)\n",
      "2021-05-07 21:41:57,369 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,406 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,407 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,408 INFO sqlalchemy.engine.base.Engine ('Tennessee', 35.517491, -86.58044699999998)\n",
      "2021-05-07 21:41:57,413 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,452 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,454 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,456 INFO sqlalchemy.engine.base.Engine ('Texas', 31.968599, -99.901813)\n",
      "2021-05-07 21:41:57,460 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:41:57,496 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,498 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,499 INFO sqlalchemy.engine.base.Engine ('Utah', 39.32098, -111.093731)\n",
      "2021-05-07 21:41:57,502 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,542 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,544 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,544 INFO sqlalchemy.engine.base.Engine ('Virginia', 37.431573, -78.656894)\n",
      "2021-05-07 21:41:57,547 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,588 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,590 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,591 INFO sqlalchemy.engine.base.Engine ('Vermont', 44.55880300000001, -72.577841)\n",
      "2021-05-07 21:41:57,595 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,634 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,636 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,637 INFO sqlalchemy.engine.base.Engine ('Washington', 47.751074, -120.740139)\n",
      "2021-05-07 21:41:57,641 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,679 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,680 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,681 INFO sqlalchemy.engine.base.Engine ('Wisconsin', 43.78444, -88.787868)\n",
      "2021-05-07 21:41:57,684 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,726 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,728 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,729 INFO sqlalchemy.engine.base.Engine ('West Virginia', 38.597626, -80.454903)\n",
      "2021-05-07 21:41:57,733 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-05-07 21:41:57,772 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:41:57,774 INFO sqlalchemy.engine.base.Engine INSERT INTO state_data (state, latitude, longitude) VALUES (?, ?, ?)\n",
      "2021-05-07 21:41:57,775 INFO sqlalchemy.engine.base.Engine ('Wyoming', 43.075968, -107.290284)\n",
      "2021-05-07 21:41:57,778 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "for index, row in df5.iterrows():\n",
    "    \n",
    "    new_state = State(state = df5.loc[index, 'state'],\n",
    "                          latitude = df5.loc[index, 'latitude'],\n",
    "                          longitude = df5.loc[index, 'longitude'])\n",
    "      \n",
    "    # Add these objects to the session\n",
    "    session.add(new_state)\n",
    "    \n",
    "    # Commit the objects to the database\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import the SQLite Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:42:02,975 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2021-05-07 21:42:02,976 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:02,978 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2021-05-07 21:42:02,979 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "engine = create_engine('sqlite:///data/shootings.sqlite', echo=True)\n",
    "session = Session(bind=engine)\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:42:04,605 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name\n",
      "2021-05-07 21:42:04,606 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['election_data',\n",
       " 'ownership_data',\n",
       " 'poverty_data',\n",
       " 'shooting_data',\n",
       " 'state_data']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Collect the names of tables within the database\n",
    "inspector.get_table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Declare a Base using `automap_base()`\n",
    "Base = automap_base()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:42:09,299 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name\n",
      "2021-05-07 21:42:09,301 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,304 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"election_data\")\n",
      "2021-05-07 21:42:09,306 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,308 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'election_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,309 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,311 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"election_data\")\n",
      "2021-05-07 21:42:09,312 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,314 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"election_data\")\n",
      "2021-05-07 21:42:09,314 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,316 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'election_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,317 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,320 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"election_data\")\n",
      "2021-05-07 21:42:09,321 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,324 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"election_data\")\n",
      "2021-05-07 21:42:09,325 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,327 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"sqlite_autoindex_election_data_1\")\n",
      "2021-05-07 21:42:09,327 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,329 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'election_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,330 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,335 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"ownership_data\")\n",
      "2021-05-07 21:42:09,336 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,339 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'ownership_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,340 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,343 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"ownership_data\")\n",
      "2021-05-07 21:42:09,344 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,345 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"ownership_data\")\n",
      "2021-05-07 21:42:09,346 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,347 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'ownership_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,348 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,349 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"ownership_data\")\n",
      "2021-05-07 21:42:09,350 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,351 INFO sqlalchemy.engine.base.Engine PRAGMA temp.index_list(\"ownership_data\")\n",
      "2021-05-07 21:42:09,352 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,353 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"ownership_data\")\n",
      "2021-05-07 21:42:09,354 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,355 INFO sqlalchemy.engine.base.Engine PRAGMA temp.index_list(\"ownership_data\")\n",
      "2021-05-07 21:42:09,356 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,358 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'ownership_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,359 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,362 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"poverty_data\")\n",
      "2021-05-07 21:42:09,363 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,365 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'poverty_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,366 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,368 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"poverty_data\")\n",
      "2021-05-07 21:42:09,369 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,371 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"poverty_data\")\n",
      "2021-05-07 21:42:09,373 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,375 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'poverty_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,377 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,378 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"poverty_data\")\n",
      "2021-05-07 21:42:09,379 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,381 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"poverty_data\")\n",
      "2021-05-07 21:42:09,381 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,383 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"sqlite_autoindex_poverty_data_1\")\n",
      "2021-05-07 21:42:09,384 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,385 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'poverty_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,386 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,389 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"shooting_data\")\n",
      "2021-05-07 21:42:09,389 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,394 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'shooting_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,396 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,398 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"shooting_data\")\n",
      "2021-05-07 21:42:09,399 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,400 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"shooting_data\")\n",
      "2021-05-07 21:42:09,401 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,402 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'shooting_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,403 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,404 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"shooting_data\")\n",
      "2021-05-07 21:42:09,405 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,407 INFO sqlalchemy.engine.base.Engine PRAGMA temp.index_list(\"shooting_data\")\n",
      "2021-05-07 21:42:09,408 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,410 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"shooting_data\")\n",
      "2021-05-07 21:42:09,411 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,413 INFO sqlalchemy.engine.base.Engine PRAGMA temp.index_list(\"shooting_data\")\n",
      "2021-05-07 21:42:09,413 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,415 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'shooting_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,415 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,417 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"state_data\")\n",
      "2021-05-07 21:42:09,418 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,420 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'state_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,421 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,424 INFO sqlalchemy.engine.base.Engine PRAGMA main.foreign_key_list(\"state_data\")\n",
      "2021-05-07 21:42:09,425 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,427 INFO sqlalchemy.engine.base.Engine PRAGMA temp.foreign_key_list(\"state_data\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:42:09,428 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,429 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'state_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,430 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,432 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"state_data\")\n",
      "2021-05-07 21:42:09,433 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,434 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_list(\"state_data\")\n",
      "2021-05-07 21:42:09,435 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,437 INFO sqlalchemy.engine.base.Engine PRAGMA main.index_info(\"sqlite_autoindex_state_data_1\")\n",
      "2021-05-07 21:42:09,438 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:42:09,443 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'state_data' AND type = 'table'\n",
      "2021-05-07 21:42:09,444 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "# Use the Base class to reflect the database tables\n",
    "Base.prepare(engine, reflect=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['election_data',\n",
       " 'ownership_data',\n",
       " 'poverty_data',\n",
       " 'shooting_data',\n",
       " 'state_data']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print all of the classes mapped to the Base\n",
    "Base.classes.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = Base.classes.shooting_data\n",
    "guns = Base.classes.ownership_data\n",
    "poverty = Base.classes.poverty_data\n",
    "election = Base.classes.election_data\n",
    "state = Base.classes.state_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lng = session.query(data.longitude).all()\n",
    "lat = session.query(data.latitude).all()\n",
    "killed = session.query(data.number_killed).all()\n",
    "injured = session.query(data.number_injured).all()\n",
    "date = session.query(data.incident_date).all()\n",
    "state = session.query(data.state).all()\n",
    "locale = session.query(data.city_county).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gun_states = session.query(guns.state).all()\n",
    "gun_capita = session.query(guns.number_per_capita).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "poverty_states = session.query(poverty.state).all()\n",
    "poverty_rates = session.query(poverty.poverty_rate).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:48:05,968 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2021-05-07 21:48:05,971 INFO sqlalchemy.engine.base.Engine SELECT election_data.state AS election_data_state \n",
      "FROM election_data\n",
      "2021-05-07 21:48:05,972 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:48:05,975 INFO sqlalchemy.engine.base.Engine SELECT election_data.result AS election_data_result \n",
      "FROM election_data\n",
      "2021-05-07 21:48:05,977 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "state_name = session.query(election.state).all()\n",
    "state_result = session.query(election.result).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-05-07 21:49:23,497 INFO sqlalchemy.engine.base.Engine SELECT state_data.state AS state_data_state \n",
      "FROM state_data\n",
      "2021-05-07 21:49:23,498 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:49:23,502 INFO sqlalchemy.engine.base.Engine SELECT state_data.latitude AS state_data_latitude \n",
      "FROM state_data\n",
      "2021-05-07 21:49:23,503 INFO sqlalchemy.engine.base.Engine ()\n",
      "2021-05-07 21:49:23,506 INFO sqlalchemy.engine.base.Engine SELECT state_data.longitude AS state_data_longitude \n",
      "FROM state_data\n",
      "2021-05-07 21:49:23,508 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "state_name = session.query(state.state).all()\n",
    "state_lat = session.query(state.latitude).all()\n",
    "state_long = session.query(state.longitude).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "incident_list = {'date': date,\n",
    "                'state': state,\n",
    "                'city_county': locale,\n",
    "                'killed': killed,\n",
    "                'injured': injured,\n",
    "                'latitude': lat,\n",
    "                'longitude': lng}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gun_list = {'state': gun_states,\n",
    "                'num_guns': gun_capita}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "poverty_list = {'state' : poverty_states,\n",
    "               'poverty_rates': poverty_rates}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "election_list = {'state':state_name,\n",
    "                 'result':state_result}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_list = {'state':state_name,\n",
    "             'latitude':state_lat,\n",
    "             'longitude':state_long}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:PythonData] *",
   "language": "python",
   "name": "conda-env-PythonData-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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}