In [2]:

    

# get secret key
from mylibrary.secrets import app_id, app_key, orig_name, dest_name, orig_pc, dest_pc
from mylibrary.connections import cursor, conn, engine, Automapped_Base, session


    

    




/Users/robinlinacre/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2505: SAWarning: Did not recognize type 'geometry' of column 'geom'
  (attype, name))

In [2]:

    

# RUNNING THIS WILL DELETE ALL YOUR JOURNEYTIME RESULTS!!!

# for name in (orig_name, dest_name):
#     sql = """
#     drop table if exists tt_gh.stations_journeytime_{name};
    
#     create table tt_gh.stations_journeytime_{name} as 
#     select nlc, icscode, station_name  from tt_gh.all_stations where london_or_gb = 'gb' and icscode_status= 'ok';
#     """.format(name = name)
#     cur = conn.cursor()
#     cur.execute(sql)
#     conn.commit()

#     sql = """
#     ALTER TABLE tt_gh.stations_journeytime_{name} 
#     ADD tfl_request text, ADD tfl_response json,
#     ADD querydict json,
#     ADD journey_time int,
#     ADD tfl_message text,
#     ADD london_mainline_station_ics text,
#     ADD london_mainline_station_text text;
#     ALTER TABLE tt_gh.stations_journeytime_{name} ADD PRIMARY KEY (nlc);

#     """.format(name=name)
#     cur = conn.cursor()
#     cur.execute(sql)
#     conn.commit()


    

In [3]:

    

# Dump all the json to the database first.  Then process it later
# Iterate through adding icscode
import requests
import pandas as pd
import json

from mylibrary.tfl_helpers import get_journeyplanner_raw_content, status_of_tfl_response_journey, get_journeyplanner_results


    

In [4]:

    




    

In [ ]:

    

from sqlalchemy import or_
Stations_orig = Automapped_Base.classes.stations_journeytime_stpauls
Stations_dest = Automapped_Base.classes.stations_journeytime_camden

for Stations in (Stations_orig, Stations_dest):
    nlcs  = session.query(Stations.nlc).filter(or_(Stations.tfl_message != 'ok',Stations.tfl_message == None)).all()
#     nlcs  = session.query(Stations.nlc).filter(Stations.tfl_message == None).all()
    for nlc in nlcs:
        station = session.query(Stations).filter(Stations.nlc == nlc).one()
        
        for d in param_dicts:
            d["from"] = station.icscode
            if "stpauls" in Stations.__table__.name:
                d["to"] = orig_pc.replace(" ","")
            if "camden" in Stations.__table__.name:
                d["to"] = dest_pc.replace(" ","")
                
        journey_object = get_journeyplanner_results(param_dicts)

        station.tfl_request = journey_object["request_url"]
        station.tfl_response = journey_object["tfl_response"]
        station.querydict = journey_object["query_dict"]
        station.tfl_message = journey_object["tfl_message"]

        session.add(station)
        session.commit()


    

In [4]:

    

# Multithread 
import threading
num_threads = 10

from sqlalchemy import or_


from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:@localhost:5432/postgres') 

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, MetaData



Stations_dest = Automapped_Base.classes.stations_journeytime_camden
nlcs  = session.query(Stations_dest.nlc).all()

counter = 1

def new_thread():
    global counter
    engine = create_engine('postgresql://postgres:@localhost:5432/postgres') 
    m = MetaData(schema='tt_gh')
    Automapped_Base = automap_base(bind=engine,metadata=m)
    Automapped_Base.prepare(engine, reflect=True)
    session = Session(engine)

    Stations_dest = Automapped_Base.classes.stations_journeytime_camden
    
    param_dict = {
    "to": orig_pc.replace(" ",""),
    "id": app_id,
    "key": app_key,
    "time": "1000", 
    "date": "20160517",
    "nationalsearch": True
    }

    param_dicts = []
    param_dicts.append(param_dict)

    d1 = param_dict.copy()
    d1["time"] = "1500"
    d1["date"] = "20160518"
    param_dicts.append(d1)

    d2 = param_dict.copy()
    d2["time"] = "1600"
    d2["date"] = "20160519"
    param_dicts.append(d2)

    while len(nlcs) > 0:
        nlc = nlcs.pop()
        station = session.query(Stations_dest).filter(Stations_dest.nlc == nlc).one()
        
        for d in param_dicts:
            d["from"] = station.icscode
            if "stpauls" in Stations_dest.__table__.name:
                d["to"] = orig_pc.replace(" ","")
            if "camden" in Stations_dest.__table__.name:
                d["to"] = dest_pc.replace(" ","")
                
        journey_object = get_journeyplanner_results(param_dicts)

        station.tfl_request = journey_object["request_url"]
        station.tfl_response = journey_object["tfl_response"]
        station.querydict = journey_object["query_dict"]
        station.tfl_message = journey_object["tfl_message"]

        session.add(station)
        session.commit()
        
        counter +=1
        if counter % 10 == 0:
            print counter
 
my_threads = []

for i in range(num_threads):
    t = threading.Thread(target=new_thread)
    my_threads.append(t)
    t.start() #start this thread

for x in my_threads:
    x.join()


    

    




10
20
30
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

40
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

50
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

60
70
80
90
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

100
110
120
130
140
150
160
170
180
190
200
210
220
230
240
250
260
270
280
290
300
310
320
330
340
350
360
370
380
390
400
410
420
430
440
450
460
470
480
490
500
510
520
530
540
550
560
570
580
590
600
610
620
630
640
650
660
670
680
690
700
710
720
730
740
750
760
770
780
790
800
810
820
830
840
850
860
870
880
890
900
910
920
930
940
950
960
970
980
990
1000
1010
1020
1030
1040
1050
1060
1070
1080
1090
1100
1110
1120
1130
1140
1150
1160
1170
1180
1190
1200
1210
1220
1230
1240
1250
1260
1270
1280
1290
1300
1310
1320
1330
1340
1350
1360
1370
1380
1390
1400
1410
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

1420
1430
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

1440
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

1450
Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

1460
1470
1480
1490
1500
1510
1520
1530
1540
1550
1560
1570
1580
1590
1600
1610
1620
1630
1640
1650
1660
1670
1680
1690
1700
1710
1720
1730
1740
1750
1760
1770
1780
1790
1800
1810
1820
1830
1840
1850
1860
1870
1880
1890
1900
1910
1920
1930
1940
1950
1960
1970
1980
1990
2000
2010
2020
2030
2040
2050
2060
2070
2080
2090
2100
2110
2120
2130
2140
2150
2160
2170
2180






    




/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
  return x == y

In [ ]: