Download and parse to PostgreSQL Dublin Hoods

gistfile1.txt

import zipfile, os, subprocess, psycopg2, codecs, json
from urllib.request import Request, urlopen
import shutil

dir_path = os.path.dirname(os.path.realpath(__file__))

DB = "PG:dbname=postgis host=localhost port=5432 user=postgres password=postgres"
conn = psycopg2.connect(host='localhost', dbname='postgis', user='postgres', password='postgres')

print(DB)

TABLE = 'all_dub'
# Currently uses the hard-coded schema: dublin_hoods

conn.autocommit = True
cur = conn.cursor()

drop_table = False
if drop_table:
    cur.execute('drop table if exists dublin_hoods.' + TABLE)
    cur.execute('create table dublin_hoods.' + TABLE +
                '(id varchar, name varchar, geom geometry(Polygon, 4326));')
    cur.execute('create unique index idx_' + TABLE + '_id on dublin_hoods.' + TABLE +
                ' (id);')

#last run:
#83
#Entries 22

run_dl = True

empty_count = 0

if run_dl:
    hdr = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11',
           'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
           'Accept-Charset': 'ISO-8859-1,utf-8;q=0.7,*;q=0.3',
           'Accept-Encoding': 'none',
           'Accept-Language': 'en-US,en;q=0.8',
           'Connection': 'keep-alive'}

    for i in range(82, 100):
        request = Request('https://neighbourhoods.dublininquirer.com/hoods.json?p={}'.format(i), headers=hdr)
        with urlopen(request) as f:
            json_f = json.load(f)
            print(i)
            json_len = len(json_f['hoods'])
            print('Entries', json_len)
            if json_len == 0:
                empty_count += 1
                print('empty')
            if empty_count > 3:
                break
            if json_len > 0:
                for j in json_f['hoods']:
                    id = j['id']
                    name = j['name']
                    geojson = str(j['geojson']['geometry'])
                    #print(geojson)
                    cur.execute(
                        'insert into dublin_hoods.' + TABLE + ' VALUES (%s, %s, st_setsrid(ST_GeomFromGeoJSON(%s),4326))'
                                                              ' ON CONFLICT DO NOTHING;',
                        (id, name, geojson))


conn.close()