Cables per company based on www.submarinecablemap.com
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.

58 lines
1.9 KiB

2 years ago
#!/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()