08b. Get journeytime json for non-London stations multithreaded.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/robinlinacre/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2505: SAWarning: Did not recognize type 'geometry' of column 'geom'\n",
      "  (attype, name))\n"
     ]
    }
   ],
   "source": [
    "# get secret key\n",
    "from mylibrary.secrets import app_id, app_key, orig_name, dest_name, orig_pc, dest_pc\n",
    "from mylibrary.connections import cursor, conn, engine, Automapped_Base, session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# RUNNING THIS WILL DELETE ALL YOUR JOURNEYTIME RESULTS!!!\n",
    "\n",
    "# for name in (orig_name, dest_name):\n",
    "#     sql = \"\"\"\n",
    "#     drop table if exists tt_gh.stations_journeytime_{name};\n",
    "    \n",
    "#     create table tt_gh.stations_journeytime_{name} as \n",
    "#     select nlc, icscode, station_name  from tt_gh.all_stations where london_or_gb = 'gb' and icscode_status= 'ok';\n",
    "#     \"\"\".format(name = name)\n",
    "#     cur = conn.cursor()\n",
    "#     cur.execute(sql)\n",
    "#     conn.commit()\n",
    "\n",
    "#     sql = \"\"\"\n",
    "#     ALTER TABLE tt_gh.stations_journeytime_{name} \n",
    "#     ADD tfl_request text, ADD tfl_response json,\n",
    "#     ADD querydict json,\n",
    "#     ADD journey_time int,\n",
    "#     ADD tfl_message text,\n",
    "#     ADD london_mainline_station_ics text,\n",
    "#     ADD london_mainline_station_text text;\n",
    "#     ALTER TABLE tt_gh.stations_journeytime_{name} ADD PRIMARY KEY (nlc);\n",
    "\n",
    "#     \"\"\".format(name=name)\n",
    "#     cur = conn.cursor()\n",
    "#     cur.execute(sql)\n",
    "#     conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Dump all the json to the database first.  Then process it later\n",
    "# Iterate through adding icscode\n",
    "import requests\n",
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "from mylibrary.tfl_helpers import get_journeyplanner_raw_content, status_of_tfl_response_journey, get_journeyplanner_results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n",
      "20\n",
      "30\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "40\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "50\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "60\n",
      "70\n",
      "80\n",
      "90\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "100\n",
      "110\n",
      "120\n",
      "130\n",
      "140\n",
      "150\n",
      "160\n",
      "170\n",
      "180\n",
      "190\n",
      "200\n",
      "210\n",
      "220\n",
      "230\n",
      "240\n",
      "250\n",
      "260\n",
      "270\n",
      "280\n",
      "290\n",
      "300\n",
      "310\n",
      "320\n",
      "330\n",
      "340\n",
      "350\n",
      "360\n",
      "370\n",
      "380\n",
      "390\n",
      "400\n",
      "410\n",
      "420\n",
      "430\n",
      "440\n",
      "450\n",
      "460\n",
      "470\n",
      "480\n",
      "490\n",
      "500\n",
      "510\n",
      "520\n",
      "530\n",
      "540\n",
      "550\n",
      "560\n",
      "570\n",
      "580\n",
      "590\n",
      "600\n",
      "610\n",
      "620\n",
      "630\n",
      "640\n",
      "650\n",
      "660\n",
      "670\n",
      "680\n",
      "690\n",
      "700\n",
      "710\n",
      "720\n",
      "730\n",
      "740\n",
      "750\n",
      "760\n",
      "770\n",
      "780\n",
      "790\n",
      "800\n",
      "810\n",
      "820\n",
      "830\n",
      "840\n",
      "850\n",
      "860\n",
      "870\n",
      "880\n",
      "890\n",
      "900\n",
      "910\n",
      "920\n",
      "930\n",
      "940\n",
      "950\n",
      "960\n",
      "970\n",
      "980\n",
      "990\n",
      "1000\n",
      "1010\n",
      "1020\n",
      "1030\n",
      "1040\n",
      "1050\n",
      "1060\n",
      "1070\n",
      "1080\n",
      "1090\n",
      "1100\n",
      "1110\n",
      "1120\n",
      "1130\n",
      "1140\n",
      "1150\n",
      "1160\n",
      "1170\n",
      "1180\n",
      "1190\n",
      "1200\n",
      "1210\n",
      "1220\n",
      "1230\n",
      "1240\n",
      "1250\n",
      "1260\n",
      "1270\n",
      "1280\n",
      "1290\n",
      "1300\n",
      "1310\n",
      "1320\n",
      "1330\n",
      "1340\n",
      "1350\n",
      "1360\n",
      "1370\n",
      "1380\n",
      "1390\n",
      "1400\n",
      "1410\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "1420\n",
      "1430\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "1440\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "1450\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"mylibrary/tfl_helpers.py\", line 30, in status_of_tfl_response_journey\n",
      "    parsed_response = json.loads(content)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py\", line 339, in loads\n",
      "    return _default_decoder.decode(s)\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 364, in decode\n",
      "    obj, end = self.raw_decode(s, idx=_w(s, 0).end())\n",
      "  File \"/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py\", line 382, in raw_decode\n",
      "    raise ValueError(\"No JSON object could be decoded\")\n",
      "ValueError: No JSON object could be decoded\n",
      "\n",
      "1460\n",
      "1470\n",
      "1480\n",
      "1490\n",
      "1500\n",
      "1510\n",
      "1520\n",
      "1530\n",
      "1540\n",
      "1550\n",
      "1560\n",
      "1570\n",
      "1580\n",
      "1590\n",
      "1600\n",
      "1610\n",
      "1620\n",
      "1630\n",
      "1640\n",
      "1650\n",
      "1660\n",
      "1670\n",
      "1680\n",
      "1690\n",
      "1700\n",
      "1710\n",
      "1720\n",
      "1730\n",
      "1740\n",
      "1750\n",
      "1760\n",
      "1770\n",
      "1780\n",
      "1790\n",
      "1800\n",
      "1810\n",
      "1820\n",
      "1830\n",
      "1840\n",
      "1850\n",
      "1860\n",
      "1870\n",
      "1880\n",
      "1890\n",
      "1900\n",
      "1910\n",
      "1920\n",
      "1930\n",
      "1940\n",
      "1950\n",
      "1960\n",
      "1970\n",
      "1980\n",
      "1990\n",
      "2000\n",
      "2010\n",
      "2020\n",
      "2030\n",
      "2040\n",
      "2050\n",
      "2060\n",
      "2070\n",
      "2080\n",
      "2090\n",
      "2100\n",
      "2110\n",
      "2120\n",
      "2130\n",
      "2140\n",
      "2150\n",
      "2160\n",
      "2170\n",
      "2180\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/robinlinacre/anaconda/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py:279: UnicodeWarning: Unicode equal comparison failed to convert both arguments to Unicode - interpreting them as being unequal\n",
      "  return x == y\n"
     ]
    }
   ],
   "source": [
    "# Multithread \n",
    "import threading\n",
    "num_threads = 10\n",
    "\n",
    "from sqlalchemy import or_\n",
    "\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('postgresql://postgres:@localhost:5432/postgres') \n",
    "\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session\n",
    "from sqlalchemy import create_engine, MetaData\n",
    "\n",
    "\n",
    "\n",
    "Stations_dest = Automapped_Base.classes.stations_journeytime_camden\n",
    "nlcs  = session.query(Stations_dest.nlc).all()\n",
    "\n",
    "counter = 1\n",
    "\n",
    "def new_thread():\n",
    "    global counter\n",
    "    engine = create_engine('postgresql://postgres:@localhost:5432/postgres') \n",
    "    m = MetaData(schema='tt_gh')\n",
    "    Automapped_Base = automap_base(bind=engine,metadata=m)\n",
    "    Automapped_Base.prepare(engine, reflect=True)\n",
    "    session = Session(engine)\n",
    "\n",
    "    Stations_dest = Automapped_Base.classes.stations_journeytime_camden\n",
    "    \n",
    "    param_dict = {\n",
    "    \"to\": orig_pc.replace(\" \",\"\"),\n",
    "    \"id\": app_id,\n",
    "    \"key\": app_key,\n",
    "    \"time\": \"1000\", \n",
    "    \"date\": \"20160517\",\n",
    "    \"nationalsearch\": True\n",
    "    }\n",
    "\n",
    "    param_dicts = []\n",
    "    param_dicts.append(param_dict)\n",
    "\n",
    "    d1 = param_dict.copy()\n",
    "    d1[\"time\"] = \"1500\"\n",
    "    d1[\"date\"] = \"20160518\"\n",
    "    param_dicts.append(d1)\n",
    "\n",
    "    d2 = param_dict.copy()\n",
    "    d2[\"time\"] = \"1600\"\n",
    "    d2[\"date\"] = \"20160519\"\n",
    "    param_dicts.append(d2)\n",
    "\n",
    "    while len(nlcs) > 0:\n",
    "        nlc = nlcs.pop()\n",
    "        station = session.query(Stations_dest).filter(Stations_dest.nlc == nlc).one()\n",
    "        \n",
    "        for d in param_dicts:\n",
    "            d[\"from\"] = station.icscode\n",
    "            if \"stpauls\" in Stations_dest.__table__.name:\n",
    "                d[\"to\"] = orig_pc.replace(\" \",\"\")\n",
    "            if \"camden\" in Stations_dest.__table__.name:\n",
    "                d[\"to\"] = dest_pc.replace(\" \",\"\")\n",
    "                \n",
    "        journey_object = get_journeyplanner_results(param_dicts)\n",
    "\n",
    "        station.tfl_request = journey_object[\"request_url\"]\n",
    "        station.tfl_response = journey_object[\"tfl_response\"]\n",
    "        station.querydict = journey_object[\"query_dict\"]\n",
    "        station.tfl_message = journey_object[\"tfl_message\"]\n",
    "\n",
    "        session.add(station)\n",
    "        session.commit()\n",
    "        \n",
    "        counter +=1\n",
    "        if counter % 10 == 0:\n",
    "            print counter\n",
    " \n",
    "my_threads = []\n",
    "\n",
    "for i in range(num_threads):\n",
    "    t = threading.Thread(target=new_thread)\n",
    "    my_threads.append(t)\n",
    "    t.start() #start this thread\n",
    "\n",
    "for x in my_threads:\n",
    "    x.join()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}