You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
57 lines
1.9 KiB
57 lines
1.9 KiB
#!/usr/bin/env python |
|
|
|
import json |
|
import os |
|
import sqlite3 |
|
|
|
json_path = 'www.submarinecablemap.com/public/api/v2/cable/' |
|
json_files = [pos_json for pos_json in os.listdir(json_path) if |
|
pos_json.endswith('.json')] |
|
json_files.remove('all.json') |
|
json_files.remove('cable-geo.json') |
|
|
|
sqlite3_conn = sqlite3.connect('cables.db') |
|
sqlite3_cur = sqlite3_conn.cursor() |
|
sqlite3_cur.execute('''DROP TABLE IF EXISTS cables''') |
|
sqlite3_cur.execute('''CREATE TABLE cables (company varchar unique, count |
|
integer, total_length float, cables varchar)''') |
|
|
|
for i in json_files: |
|
with open(json_path + i, 'r') as json_file: |
|
json_text = json.load(json_file) |
|
cable_name = json_text['name'] |
|
cable_owners = json_text['owners'] |
|
cable_owners = [x.strip() for x in cable_owners.split(',')] |
|
|
|
if json_text['length'] == 'n.a.': |
|
cable_length = float(0) |
|
else: |
|
cable_length = float(json_text['length'].replace(' km', |
|
'').replace(',', '')) |
|
|
|
print('{} ({} km): {}'.format(cable_name, cable_length, |
|
sorted(cable_owners))) |
|
|
|
for owner in cable_owners: |
|
sqlite3_cur.execute('''SELECT count FROM cables WHERE company = |
|
?''', (owner,)) |
|
count = sqlite3_cur.fetchone() |
|
if count is None: |
|
sqlite3_cur.execute('''INSERT INTO cables VALUES (?, ?, ?, |
|
?)''', (owner, 1, cable_length, cable_name)) |
|
else: |
|
cable_name_comma = ', ' + cable_name |
|
sqlite3_cur.execute(''' |
|
UPDATE cables |
|
SET |
|
count = count + 1, |
|
total_length = total_length + ?, |
|
cables = cables || ? |
|
WHERE company = ?''', |
|
(cable_length,cable_name_comma,owner) |
|
) |
|
|
|
json_file.close() |
|
|
|
sqlite3_conn.commit() |
|
sqlite3_conn.close()
|
|
|