SQLite_db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7032e2b8",
   "metadata": {},
   "source": [
    "<b>Only run once to initialize database.</b>\n",
    "<br>\n",
    "<br>\n",
    "Running multiple times will create duplicates (redundancies).\n",
    "<br>\n",
    "<br>\n",
    "IF script is run more than once, delete database ('sqlite:///project_2.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cdcf3f6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Column, Integer,Float, String, create_engine\n",
    "from sqlalchemy.orm import Session\n",
    "\n",
    "\n",
    "Base = declarative_base()\n",
    "engine = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bb3bb305",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import os\n",
    "from pprint import pprint\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "data_url = \"https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2019-01-01&endtime=2019-02-01&eventtype=earthquake\"\n",
    "# data_url1 = \"https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2014-02-01&endtime=2014-03-01&eventtype=earthquake\"\n",
    "output_path = os.path.join(\"/images/\")\n",
    "response = requests.get(data_url)\n",
    "data = response.json()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# pprint(data)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# create list variables to contain data\n",
    "coords = []\n",
    "alert = []\n",
    "cdi = []\n",
    "code = []\n",
    "detail = []\n",
    "dmin = []\n",
    "mag = []\n",
    "magType = []\n",
    "ids = []\n",
    "felt = []\n",
    "gap = []\n",
    "mmi = []\n",
    "net = []\n",
    "nst = []\n",
    "place = []\n",
    "rms = []\n",
    "sig = []\n",
    "sources = []\n",
    "status = []\n",
    "sources = []\n",
    "Time = []\n",
    "title = []\n",
    "tsunami = []\n",
    "types = []\n",
    "tz = []\n",
    "updated = []\n",
    "url = []\n",
    "Lat = []\n",
    "Lon = []\n",
    "\n",
    "# append all lists\n",
    "for x in data[\"features\"]:\n",
    "    alert.append(x[\"properties\"][\"alert\"])\n",
    "\n",
    "    cdi.append(x[\"properties\"][\"cdi\"])\n",
    "\n",
    "    code.append(x[\"properties\"][\"code\"])\n",
    "\n",
    "    detail.append(x[\"properties\"][\"detail\"])\n",
    "\n",
    "    dmin.append(x[\"properties\"][\"dmin\"])\n",
    "\n",
    "    mag.append(x[\"properties\"][\"mag\"])\n",
    "\n",
    "    magType.append(x[\"properties\"][\"magType\"])\n",
    "\n",
    "    ids.append(x[\"properties\"][\"ids\"])\n",
    "\n",
    "    felt.append(x[\"properties\"][\"felt\"])\n",
    "\n",
    "    gap.append(x[\"properties\"][\"gap\"])\n",
    "\n",
    "    mmi.append(x[\"properties\"][\"mmi\"])\n",
    "\n",
    "    net.append(x[\"properties\"][\"net\"])\n",
    "\n",
    "    nst.append(x[\"properties\"][\"nst\"])\n",
    "\n",
    "    place.append(x[\"properties\"][\"place\"])\n",
    "\n",
    "    rms.append(x[\"properties\"][\"rms\"])\n",
    "\n",
    "    sig.append(x[\"properties\"][\"sig\"])\n",
    "\n",
    "    sources.append(x[\"properties\"][\"sources\"])\n",
    "\n",
    "    status.append(x[\"properties\"][\"status\"])\n",
    "\n",
    "    Time.append(x[\"properties\"][\"time\"])\n",
    "\n",
    "    title.append(x[\"properties\"][\"title\"])\n",
    "\n",
    "    tsunami.append(x[\"properties\"][\"tsunami\"])\n",
    "\n",
    "    types.append(x[\"properties\"][\"type\"])\n",
    "\n",
    "    tz.append(x[\"properties\"][\"tz\"])\n",
    "\n",
    "    updated.append(x[\"properties\"][\"updated\"])\n",
    "\n",
    "    url.append(x[\"properties\"][\"url\"])\n",
    "\n",
    "    value = (x[\"geometry\"][\"coordinates\"])\n",
    "    # sep = value.split(\",\")\n",
    "    Lon.append(value[0])\n",
    "    Lat.append(value[1])\n",
    "\n",
    "\n",
    "# construct dataframes for each list in Pandas\n",
    "\n",
    "alert_df = pd.DataFrame({\"Alert\": alert})\n",
    "cdi_df = pd.DataFrame({\"CDI\": cdi})\n",
    "code_df = pd.DataFrame({\"Code\": code})\n",
    "detail_df = pd.DataFrame({\"Detail\": detail})\n",
    "dmin_df = pd.DataFrame({\"Depth\": dmin})\n",
    "mag_df = pd.DataFrame({\"Magnitude\":mag})\n",
    "magType_df = pd.DataFrame({\"Waveform\":magType})\n",
    "ids_df = pd.DataFrame({\"ID\":ids})\n",
    "felt_df = pd.DataFrame({\"Felt\":felt})\n",
    "gap_df = pd.DataFrame({\"Gap\":gap})\n",
    "mmi_df = pd.DataFrame({\"MMI\":mmi})\n",
    "net_df = pd.DataFrame({\"Net\":net})\n",
    "nst_df = pd.DataFrame({\"NST\":nst})\n",
    "place_df = pd.DataFrame({\"Place\":place})\n",
    "rms_df = pd.DataFrame({\"RMS\":rms})\n",
    "sig_df = pd.DataFrame({\"Sig\":sig})\n",
    "sources_df = pd.DataFrame({\"Sources\":sources})\n",
    "status_df = pd.DataFrame({\"Status\":status})\n",
    "time_df = pd.DataFrame({\"Time\":Time})\n",
    "title_df = pd.DataFrame({\"Title\":title})\n",
    "tsunami_df = pd.DataFrame({\"Tsunami\":tsunami})\n",
    "types_df = pd.DataFrame({\"Type\":types})\n",
    "tz_df = pd.DataFrame({\"TZ\":tz})\n",
    "updated_df = pd.DataFrame({\"Updated\":updated})\n",
    "# url_df = pd.DataFrame({\"URL\":url})\n",
    "# coords_Df = pd.DataFrame({\"Coordinates\":coords})\n",
    "Lat_Df = pd.DataFrame({\"Latitude\":Lat})\n",
    "Lon_Df = pd.DataFrame({\"Longitude\":Lon})\n",
    "\n",
    "# merges dataframes made above\n",
    "\n",
    "ses_df = pd.DataFrame(Lat_Df)\n",
    "# ses_df = ses_df.merge(Lat_Df, \"inner\", right_index=True, left_index=True)\n",
    "ses_df = ses_df.merge(Lon_Df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(cdi_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(code_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(detail_df, \"inner\", right_index=True, left_index=True)\n",
    "ses_df = ses_df.merge(dmin_df, \"inner\", right_index=True, left_index=True)\n",
    "ses_df = ses_df.merge(mag_df, \"inner\", right_index=True, left_index=True)\n",
    "ses_df = ses_df.merge(magType_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(ids_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(felt_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(gap_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(mmi_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(net_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(nst_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(rms_df, \"inner\", right_index=True, left_index=True)\n",
    "ses_df = ses_df.merge(place_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(sig_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(sources_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(status_df, \"inner\", right_index=True, left_index=True)\n",
    "ses_df = ses_df.merge(time_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(title_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(tsunami_df, \"inner\", right_index=True, left_index=True)\n",
    "ses_df = ses_df.merge(types_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(tz_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(updated_df, \"inner\", right_index=True, left_index=True)\n",
    "# ses_df = ses_df.merge(url_df, \"inner\", right_index=True, left_index=True)\n",
    "\n",
    "#print(ses_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "06b4cb9b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                               Country Total Disaster Relief($)\n",
      "0    Democratic Republic of the Congo                 397960925\n",
      "1                Republic of the Sudan                380660961\n",
      "2                             Ethiopia                309516428\n",
      "3                              Somalia                300513523\n",
      "4                              Yemen                  271975141\n",
      "..                                 ...                      ...\n",
      "97                              Mexico                  1693550\n",
      "98                              Brazil                  1502535\n",
      "99                    Marshall Islands                  1000000\n",
      "100                         Cape Verde                   474338\n",
      "101                            Armenia                   299787\n",
      "\n",
      "[102 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "uncerf_df = pd.read_csv('static/csv/UNCERF.csv')\n",
    "uncerf_df['Total Disaster Relief($)'] = uncerf_df['Total Disaster Relief($)'].replace(',','', regex=True)\n",
    "print(uncerf_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1eda1a3e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   country  magnitude  pop_mil  aid_usd_mil  \\\n",
      "0                  DRCongo        6.6     92.0    397000000   \n",
      "1                    Sudan        6.2     45.0    380000000   \n",
      "2                 Ethiopia        6.5    114.0    309000000   \n",
      "3                  Somalia        5.6     15.4    300000000   \n",
      "4                    Yemen        6.6     31.0    271000000   \n",
      "5                     Chad        5.3     16.2    212000000   \n",
      "6                 Pakistan        8.1    225.0    211000000   \n",
      "7              South Sudan        7.2     11.4    190000000   \n",
      "8                    Niger        5.3     25.0    178000000   \n",
      "9                    Kenya        6.9     55.0    176000000   \n",
      "10            Afghanistan         7.6     39.8    147000000   \n",
      "11                 Uganda         6.5     47.0    143000000   \n",
      "12             North Korea        7.3     25.9    140000000   \n",
      "13                   Haiti        8.1     12.0    137000000   \n",
      "14  CentralAfricanRepublic        4.7      4.9    128000000   \n",
      "15                   Syria        7.1     18.3    122000000   \n",
      "16                 Myanmar        6.9     54.8    122000000   \n",
      "17                    Iraq        7.3     41.1    115000000   \n",
      "18                Zimbabwe        5.8     15.0    109000000   \n",
      "19               Sri Lanka        9.1     22.2    104000000   \n",
      "20                Cameroon        4.9     27.0     95000000   \n",
      "21             Philippines        7.9    111.0     91000000   \n",
      "22                    Mali        4.2     21.0     88000000   \n",
      "23                 Nigeria        4.5    211.4     87000000   \n",
      "24              Bangladesh        5.8    166.3     79000000   \n",
      "\n",
      "    earthqks#_year_prev  \n",
      "0                     0  \n",
      "1                     0  \n",
      "2                     0  \n",
      "3                     0  \n",
      "4                     0  \n",
      "5                     0  \n",
      "6                     4  \n",
      "7                     1  \n",
      "8                     0  \n",
      "9                     0  \n",
      "10                   20  \n",
      "11                    0  \n",
      "12                    3  \n",
      "13                   12  \n",
      "14                    0  \n",
      "15                    2  \n",
      "16                    3  \n",
      "17                   19  \n",
      "18                    0  \n",
      "19                    0  \n",
      "20                    1  \n",
      "21                   52  \n",
      "22                    0  \n",
      "23                    0  \n",
      "24                    6  \n"
     ]
    }
   ],
   "source": [
    "quaker_df = pd.read_csv('static/csv/quakers2shakers.csv')\n",
    "print(quaker_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c2c5008e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the uncerf and earthquake class\n",
    "class earthquake(Base):\n",
    "    __tablename__ = 'seismic_data'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    Latitude = Column(String(255))\n",
    "    Longitude = Column(String(255))\n",
    "    Depth = Column(Float)\n",
    "    Magnitude = Column(Float)\n",
    "    Waveform = Column(String(255))\n",
    "    Place = Column(String(255))\n",
    "    Time = Column(Float)\n",
    "    Type = Column(String(255))\n",
    "\n",
    "class uncerf(Base):\n",
    "    __tablename__ = 'relief_expenses'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    Country = Column(String(255))\n",
    "    Total_relief = Column(Integer)\n",
    "\n",
    "class quake(Base):\n",
    "    __tablename__ = 'quakers2shakers'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    country = Column(String(255))\n",
    "    magnitude = Column(Float)\n",
    "    pop_mil = Column(Float)\n",
    "    aid_usd_mil = Column(Float)\n",
    "    earthqks_year_prev = Column(Integer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "08ff8aa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "dbPath = 'sqlite:///project_2.sqlite'\n",
    "engine = create_engine(dbPath)\n",
    "Base.metadata.create_all(engine)\n",
    "s = Session(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "50b55785",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "relief_objects = []\n",
    "for index, row in uncerf_df.iterrows():\n",
    "    data_row = uncerf(Country = row[0] , Total_relief = row[1])\n",
    "    relief_objects.append(data_row)\n",
    "\n",
    "s.bulk_save_objects(relief_objects)\n",
    "s.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e0a0acd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "quaker_objects = []\n",
    "for index, row in quaker_df.iterrows():\n",
    "    data_row = quake(country=row[0], magnitude = row[1], pop_mil = row[2], aid_usd_mil = row[3], earthqks_year_prev = row[4])\n",
    "    quaker_objects.append(data_row)\n",
    "\n",
    "s.bulk_save_objects(quaker_objects)\n",
    "s.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1bafd15b",
   "metadata": {},
   "outputs": [],
   "source": [
    "earthquake_objects = []\n",
    "for index, row in ses_df.iterrows():\n",
    "    data_row = earthquake(Latitude=row[0], Longitude=row[1], Depth=row[2], Magnitude=row[3],\n",
    "                     Waveform=row[4], Place=row[5], Time=row[6], Type=row[7])\n",
    "    earthquake_objects.append(data_row)\n",
    "\n",
    "s.bulk_save_objects(earthquake_objects)\n",
    "s.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c90d2e8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "s.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}