W2Kr7Skd

1import sqlite3
2import requests
3from datetime import datetime
4
5DB_FILE = "koleo_database.db"
6
7
8def prepare_columns(json_data):
9    columns = []
10
11    for data in json_data:
12        column = list(data.keys())
13        for col in column:
14            if col not in columns:
15                columns.append(col)
16    return columns
17
18
19def prepare_data(columns, json_data):
20    value = []
21    values = []
22
23    for data in json_data:
24        for i in columns:
25            value.append(str(dict(data).get(i)))
26        values.append(list(value))
27        value.clear()
28
29    return values
30
31
32def insert_data(create_query, insert_query, data):
33    db_connection = sqlite3.connect(DB_FILE)
34    print("insert has started at " + str(datetime.now()))
35    cursor = db_connection.cursor()
36    cursor.execute(create_query)
37    cursor.executemany(insert_query, data)
38    db_connection.commit()
39    cursor.close()
40    print("insert has completed at " + str(datetime.now()))
41
42
43def make_insert_data(create_query, insert_query, json):
44    insert_data(create_query, insert_query, prepare_data(prepare_columns(json), json))
45
46
47def make_json_request(url):
48    header = {'X-KOLEO-Version': '1', 'Content-Type': 'application/json'}
49    request = requests.get(url, headers=header)
50    return request.json()
51
52
53def load_brands():
54    brand_request = make_json_request("https://koleo.pl/api/v2/main/brands")
55
56    create_query = "CREATE TABLE IF NOT EXISTS `brand` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, `display_name` " \
57                   "TEXT NOT NULL, `logo_text` TEXT NOT NULL, `color` TEXT NOT NULL, `carrier_id` INTEGER NOT NULL, " \
58                   "PRIMARY KEY(`id`)) "
59    insert_query = "INSERT OR REPLACE INTO brand (id, name, display_name, logo_text, color, carrier_id) values(?,?,?," \
60                   "?,?,?) "
61
62    make_insert_data(create_query, insert_query, brand_request)
63
64
65def load_stations():
66    stations_request = make_json_request("https://koleo.pl/api/v2/main/stations")
67
68    create_query = "CREATE TABLE IF NOT EXISTS `station` (`id` INTEGER NOT NULL, `name` TEXT, `name_slug` TEXT, " \
69                   "`latitude` REAL, `longitude` REAL, `hits` INTEGER, `ibnr` INTEGER, `city` TEXT, `region` TEXT, " \
70                   "`country` TEXT, `localised_name` TEXT, PRIMARY KEY(`id`)) "
71    insert_query = "INSERT OR REPLACE INTO station (id, name, name_slug, latitude, longitude, hits, ibnr, city, " \
72                   "region, country, localised_name) values (?,?,?,?,?,?,?,?,?,?,?) "
73
74    make_insert_data(create_query, insert_query, stations_request)
75
76
77def load_carriers():
78    carriers_request = make_json_request("https://koleo.pl/api/v2/main/carriers")
79
80    create_query = "CREATE TABLE IF NOT EXISTS `carrier` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, " \
81                   "`short_name` TEXT NOT NULL, `slug` TEXT NOT NULL, `legal_name` TEXT NOT NULL, PRIMARY KEY(`id`)) "
82
83    insert_query = "INSERT OR REPLACE INTO carrier (id, name, short_name, slug, legal_name) values (?,?,?,?,?)"
84
85    make_insert_data(create_query, insert_query, carriers_request)
86
87
88def load_discount():
89    discount_request = make_json_request("https://koleo.pl/api/v2/main/discounts")
90
91    create_query = "CREATE TABLE IF NOT EXISTS `discount` (`id` INTEGER NOT NULL, `passenger_percentage` INTEGER NOT " \
92                   "NULL, `display_passenger_percentage` INTEGER NOT NULL, `flyer_second_class_percentage` INTEGER " \
93                   "NOT NULL, `flyer_first_class_percentage` INTEGER NOT NULL, `express_second_class_percentage` " \
94                   "INTEGER NOT NULL, `express_first_class_percentage` INTEGER NOT NULL, `dependent_on_ids` TEXT NOT " \
95                   "NULL, `name` TEXT NOT NULL, `season_passenger_percentage` INTEGER NOT NULL, `displayable` INTEGER " \
96                   "NOT NULL, `is_company` INTEGER NOT NULL, `rank` INTEGER NOT NULL, PRIMARY KEY(`id`)) "
97
98    insert_query = "INSERT OR REPLACE INTO discount (id,passenger_percentage, display_passenger_percentage," \
99                   "flyer_second_class_percentage, flyer_first_class_percentage, express_second_class_percentage," \
100                   "express_first_class_percentage, dependent_on_ids, name, season_passenger_percentage, displayable, " \
101                   "is_company, rank) values (?,?,?,?,?,?,?,?,?,?,?,?,?)"
102
103    make_insert_data(create_query, insert_query, discount_request)
104
105
106def load_documents():
107    documents_request = make_json_request("https://koleo.pl/api/v2/main/identity_document_types")
108
109    create_query = "CREATE TABLE IF NOT EXISTS `document` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, PRIMARY KEY(" \
110                   "`id`)) "
111    insert_query = "INSERT OR REPLACE INTO document (id, name) values (?,?)"
112
113    make_insert_data(create_query, insert_query, documents_request)
114
115
116def load_seat_type():
117    seat_type_request = make_json_request("https://koleo.pl/api/v2/main/seat_types")
118
119    create_query = "CREATE TABLE IF NOT EXISTS `seat_type` (`id` INTEGER NOT NULL, `key` TEXT NOT NULL, PRIMARY KEY(" \
120                   "`id`)) "
121    insert_query = "INSERT OR REPLACE INTO seat_type (id,key) values (?,?)"
122
123    make_insert_data(create_query, insert_query, seat_type_request)
124
125
126def load_station_keyword():
127    station_keyword_request = make_json_request("https://koleo.pl/api/v2/main/station_keywords")
128
129    create_query = "CREATE TABLE IF NOT EXISTS `station_keyword` (`id` INTEGER NOT NULL, `keyword` TEXT NOT NULL, " \
130                   "`station_id` INTEGER NOT NULL, PRIMARY KEY(`id`)) "
131    insert_query = "INSERT OR REPLACE INTO station_keyword (id, keyword, station_id) values (?,?,?)"
132
133    make_insert_data(create_query, insert_query, station_keyword_request)
134
135
136def load_train_attributes():
137    train_attributes_request = make_json_request("https://koleo.pl/api/v2/main/train_attributes")
138
139    create_query = "CREATE TABLE IF NOT EXISTS `train_attribute` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, " \
140                   "`short_name` TEXT NOT NULL, `rank` INTEGER NOT NULL, `warning` INTEGER NOT NULL, PRIMARY KEY(" \
141                   "`id`)) "
142    insert_query = "INSERT OR REPLACE INTO train_attribute (id, name, short_name, rank, warning) values (?,?,?,?,?)"
143
144    make_insert_data(create_query, insert_query, train_attributes_request)
145
146
147load_stations()
148load_brands()
149load_carriers()
150load_discount()
151load_documents()
152load_seat_type()
153load_station_keyword()
154load_train_attributes()