Creating SQL Tables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import os\n",
    "\n",
    "username = os.environ['PG_USER']\n",
    "password = os.environ['PG_PASSWORD']\n",
    "\n",
    "conn_args = {\n",
    "        'user': username,\n",
    "        'password': password,\n",
    "        'database': 'nhis'\n",
    "       }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "DATA_RANGE = [2007, 2008, 2009, 2011, 2012, 2013]\n",
    "DISAB_RANGE = [2012, 2013]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "CREATE_VIEW = '''CREATE VIEW {name} AS ({select_query})'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Person File for Each Year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "PERSON_QUERY = '''\n",
    "SELECT\n",
    "    fpx,\n",
    "    hhx || '.' || fmx || '.' || fpx as person_id,\n",
    "    hhx || '.' || fmx as family_id,\n",
    "\n",
    "    -- Basic Info\n",
    "    (sex = 1)::integer as male,\n",
    "    srvy_yr,\n",
    "    intv_qrt,\n",
    "    (srvy_yr::text || '.' || intv_qrt::text) as qrt_yr,\n",
    "  \n",
    "    -- Government Assistance\n",
    "    (ptanf = 1)::integer as welfare,\n",
    "    (powben = 1)::integer as other_welfare,\n",
    "    (pwic = '1')::integer as wic,\n",
    "  \n",
    "    /* Race Recode\n",
    "       ---------------------\n",
    "      Original Definition:\n",
    "      1 Hispanic\n",
    "      2 Non-Hispanic White\n",
    "      3 Non-Hispanic Black\n",
    "      4 Non-Hispanic Asian\n",
    "      5 Non-Hispanic All other race groups\n",
    "    */\n",
    "  \n",
    "    hiscodi3 as race,\n",
    "    (hiscodi3 = 1)::integer as hispanic,\n",
    "    (hiscodi3 = 2)::integer as white,\n",
    "    (hiscodi3 = 3)::integer as black,\n",
    "    (hiscodi3 = 4)::integer as asian,\n",
    "    (hiscodi3 = 5)::integer as other_race,\n",
    "  \n",
    "    wtfa as weight\n",
    "    -- intv_mon\n",
    "    FROM nhis_person_{year}\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with psycopg2.connect(**conn_args) as conn:\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "    for year in DATA_RANGE:\n",
    "        select_query = PERSON_QUERY.format(year=year)\n",
    "        \n",
    "        cur.execute(CREATE_VIEW.format(\n",
    "            name = \"person_{}\".format(year),\n",
    "            select_query = select_query\n",
    "        ))"
   ]
  }
 ],
 "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}