lodes2_drop_db_tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Human Mobility in San Diego County\n",
    "Jessica Embury\n",
    "\n",
    "### Drop tables from the database as needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import needed libraries\n",
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import os\n",
    "import glob\n",
    "import gzip\n",
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#USER ENTERED VARIABLES\n",
    "\n",
    "#directory where this file is located and where data files will be stored\n",
    "abs_path = ''\n",
    "\n",
    "#for database \n",
    "host = ''\n",
    "db = ''\n",
    "user = ''\n",
    "password = ''\n",
    "\n",
    "schema_name = 'lodes'\n",
    "\n",
    "#For fresh start (get all files for all states), use state_num = 0 and file_num = 0\n",
    "#Change state_num and file_num if data retrieval needs to be resumed (after SSL Error, etc.)\n",
    "\n",
    "#Enter CURRENT state number\n",
    "#'az' = 0, 'ca' = 1, 'co' = 2, 'dc' = 3, 'fl' = 4, 'il' = 5, 'ma' = 6, 'mi'  = 7, \n",
    "#'mn' = 8, 'nv' = 9, 'ny' = 10, 'tx' = 11, 'wa' = 12\n",
    "state_num = 0\n",
    "\n",
    "#Enter last SUCCESSFUL file number for the current state (For example, in 'az', 71 out of 136, first file will be 72)\n",
    "file_num = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#list object containing states to get lodes data from\n",
    "#states = ['az', 'ca', 'co', 'dc', 'fl', 'il', 'ma', 'mi', 'mn', 'nv', 'ny', 'tx', 'wa']\n",
    "states = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#FUNCTIONS\n",
    "\n",
    "#Function to get all file names from the associated url \n",
    "#reference: https://stackoverflow.com/questions/11023530/python-to-list-http-files-and-directories\n",
    "def listFD(url, ext=''):\n",
    "    page = requests.get(url).text\n",
    "    #print(page)\n",
    "    soup = BeautifulSoup(page, 'html.parser')\n",
    "    return [url + '/' + node.get('href') for node in soup.find_all('a') if node.get('href').endswith(ext)]\n",
    "\n",
    "#function for psycopg2 to connect to the PostgreSQL database server\n",
    "#reference: https://github.com/NaysanSaran/pandas2postgresql/blob/master/notebooks/CompleteExample.ipynb\n",
    "def connect(params_dic):\n",
    "    \"\"\" Connect to the PostgreSQL database server \"\"\"\n",
    "    conn = None\n",
    "    try:\n",
    "        # connect to the PostgreSQL server\n",
    "        print('Connecting to the PostgreSQL database...')\n",
    "        conn = psycopg2.connect(**params_dic)\n",
    "\n",
    "    except (Exception, psycopg2.DatabaseError) as error:\n",
    "        print(error)\n",
    "        sys.exit(1) \n",
    "    return conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting to the PostgreSQL database...\n"
     ]
    }
   ],
   "source": [
    "#CONNECT TO DATABASE\n",
    "\n",
    "#specify the psql connection parameters\n",
    "param_dic = {\n",
    "    \"host\"      : host,\n",
    "    \"database\"  : db,\n",
    "    \"user\"      : user,\n",
    "    \"password\"  : password,\n",
    "}\n",
    "\n",
    "#connect to the database server\n",
    "conn = connect(param_dic)\n",
    "\n",
    "#define the cursor\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "State: dc, Number of files for https://lehd.ces.census.gov/data/lodes/LODES7/dc/od/ = 88\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "State: co, Number of files for https://lehd.ces.census.gov/data/lodes/LODES7/co/od/ = 152\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n",
      "Table dropped if exists.\n"
     ]
    }
   ],
   "source": [
    "#delete all tables from database for states in list\n",
    "for y in range(len(states) - state_num):\n",
    "    \n",
    "    #adjust to current state\n",
    "    i = y + state_num\n",
    "    \n",
    "    #list object to contain names of all files to be downloaded from url - for current state\n",
    "    filenames = []\n",
    "    \n",
    "    #variable for url to the current state's lodes files\n",
    "    url = 'https://lehd.ces.census.gov/data/lodes/LODES7/' + states[i] + '/od/'\n",
    "    \n",
    "    #file extension of lodes data\n",
    "    ext = 'csv.gz'\n",
    "\n",
    "    #get all file names for the current state\n",
    "    for file in listFD(url, ext):\n",
    "        #print(file)\n",
    "        filenames.append(os.path.basename(file))\n",
    "    \n",
    "    print('State: ' +states[i] + ', Number of files for ' + url +' = ' + str(len(filenames)))\n",
    "    \n",
    "        \n",
    "    #for each file for the current state:\n",
    "    for z in range(len(filenames) - file_num):\n",
    "            \n",
    "        #adjust to next file\n",
    "        file = z + file_num\n",
    "      \n",
    "        #DATABASE SET UP\n",
    "        \n",
    "        #database table name - same as filename, but remove extensions (.csv.gz)\n",
    "        table_name = schema_name + '.' + filenames[file][:-7]\n",
    "        \n",
    "        #drop table if exists\n",
    "        drop_table = ('DROP TABLE IF EXISTS {};'.format(table_name))\n",
    "\n",
    "        try:\n",
    "            cur.execute(drop_table)\n",
    "            print('Table dropped if exists.')\n",
    "        except:\n",
    "            print(\"Error. Table (if exists) not dropped.\")\n",
    "            \n",
    "    #commit database changes for each state\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#COMMIT AND CLOSE DATABASE\n",
    "#commit changes to the database\n",
    "conn.commit()\n",
    "\n",
    "#close the database connection\n",
    "conn.close()\n",
    "\n",
    "#close the cursor\n",
    "cur.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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}