58 lines
1.9 KiB
Python
Executable File
58 lines
1.9 KiB
Python
Executable File
#!/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()
|