def db_update_airports():
"""This function creates a table (airports) in the database flights_departures"""
airports = get_airports()
db, cur = db_create_cursor()
# airports.drop(columns=['name'], inplace=True)
for index, airport in airports.iterrows():
# solve problem with ("), replace by (')
airport['name'] = airport['name'].replace('\"', '\'')
query = f"""UPDATE airports
SET
name ="{airport['name']}",
elevation_ft='{int(airport['elevation_ft'])}',
continent="{airport['continent']}",
iso_country='{airport['iso_country']}',
iso_region='{airport['iso_region']}',
municipality="{airport['municipality']}",
gps_code='{airport['gps_code']}',
iata_code='{airport['iata_code']}',
local_code='{airport['local_code']}',
longitude='{airport['longitude']}',
latitude='{airport['latitude']}'
WHERE
iata_code='{airport['iata_code']}';"""
# # catch error if there are duplicates in the data set
try:
cur.execute(query)
except mysql.connector.errors.IntegrityError as err:
print("Error caught while updating airport table: {}".format(err))
db.commit()
def db_insert_events(events_data):
"""This function takes the a event data dictionary as an input and feeds it into the database table events.
:param events_data: (flight_id, event_date, event_time, event_type)"""
# change type of data from bs4.elemnt.NavigableString to string
events_data = tuple([str(e) for e in events_data])
length = len(events_data)
event_date = events_data[CFG.second_elem]
flight_id = events_data[CFG.first_elem]
db, cur = db_create_cursor()
table = 'events'
is_observation = db_select_event(flight_id, event_date)
if is_observation:
pass
else:
placeholder = ", ".join(["%s"] * length)
smt = "INSERT INTO {table} ({columns}) values ({values});".format(
table=table,
columns=f'{CFG.KEY_flight_id},'
f'{CFG.KEY_event_date},'
f'{CFG.KEY_event_time},'
f'{CFG.KEY_even_type}',
values=placeholder)
try:
cur.execute(smt, events_data)
except mysql.connector.errors.IntegrityError as err:
print("Error while inserting events data: {}".format(err))
db.commit()
def db_insert_airports():
"""This function creates a table (airports) in the database flights_departures"""
airports = get_airports()
db, cur = db_create_cursor()
# airports.drop(columns=['name'], inplace=True)
for index, airport in airports.iterrows():
# if elevation is empty fill in None value
if airport[CFG.elevation] == '':
airport[CFG.elevation] = None
airport.fillna(0, inplace=True)
data = tuple(airport)[CFG.second_elem:]
print(data)
airport['name'] = airport['name'].replace('\"', '\'')
query = f"""INSERT INTO airports (airport_type, name, elevation_ft, continent, iso_country, iso_region,
municipality, gps_code, iata_code, local_code, longitude, latitude)
VALUES ("{airport['type']}", "{airport['name']}", '{int(airport['elevation_ft'])}',
"{airport['continent']}", '{airport['iso_country']}', '{airport['iso_region']}',
"{airport['municipality']}", '{airport['gps_code']}', '{airport['iata_code']}',
'{airport['local_code']}', '{airport['longitude']}','{airport['latitude']}');"""
# catch error if there are duplicates in the data set
try:
cur.execute(query)
except mysql.connector.errors.IntegrityError as err:
print("Error caught while updating airport table: {}".format(err))
# except mysql.connector.errors.DatabaseError as err:
# print("Error caught while updating airport table: {}".format(err))
db.commit()
def db_feed_covid19_region(df):
"""
This function feeds the table covid19_region with the data scraped
:param df: dataframe created with an API request
:return: None
"""
db, cur = db_create_cursor()
query = """INSERT INTO covid19_region (iso_region, latitude, longitude, confirmed, dead, recovered, updated)
VALUES (%s, %s, %s, %s, %s, %s, %s)"""
for index, data in df.iterrows():
# todo: get NAN values into database as for now nan values are represented by 0
data.fillna(0, inplace=True)
food = tuple(data)
# catch error if there are duplicates in the data set
try:
cur.execute(query, food)
except mysql.connector.errors.IntegrityError as err:
print(food)
print(
"Error caught while updating covid19_region table: {}".format(
err))
db.commit()
def create_tables_flights():
"""this function create the tables in the database flight_departures"""
db, cur = db_create_cursor()
# airports table
cur.execute("""CREATE TABLE IF NOT EXISTS airport(
type VARCHAR(255)
, name VARCHAR(255)
, elevation_ft INTEGER
, continent VARCHAR(255)
, iso_country VARCHAR(5), FOREIGN KEY (iso_country) REFERENCES country_airports(iso_country)
, iso_region VARCHAR(255), FOREIGN KEY (iso_region) REFERENCES region_airports(iso_region)
, municipality VARCHAR(255), FOREIGN KEY (municipality) REFERENCES city_airports(municipality)
, gps_code VARCHAR(255)
, iata_code VARCHAR(5) PRIMARY KEY
, local_code VARCHAR(255)
, longitude FLOAT
, latitude FLOAT);""")
try:
cur.execute("CREATE UNIQUE INDEX idx_iata ON airports(iata_code)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
# departures table
cur.execute("""CREATE TABLE IF NOT EXISTS departures(
flight_id VARCHAR(255) PRIMARY KEY
, departure_airport VARCHAR(10), FOREIGN KEY (departure_airport) REFERENCES airports(iata_code)
, flight_number INTEGER
, flight_status VARCHAR(255)
, arrival_airport VARCHAR(10)
, departure_date DATE
, arrival_date DATE
, operating_airline VARCHAR(255));""")
try:
cur.execute(
"CREATE UNIQUE INDEX idx_flight_id ON departures(flight_id)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
# events table
cur.execute("""CREATE TABLE IF NOT EXISTS events(
id INTEGER(255) PRIMARY KEY AUTO_INCREMENT
, flight_id VARCHAR(255), FOREIGN KEY (flight_id) REFERENCES departures(flight_id)
, event_time TIME
, event_date DATE
, event_type VARCHAR(255));""")
try:
cur.execute("CREATE INDEX idx_flight_id_event ON events(flight_id)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
db.commit()
def create_tables_covid19():
"""this function creates the tables containing the numbers of the COVID-19 pandemic"""
db, cur = db_create_cursor()
# corona per country table
cur.execute("""CREATE TABLE IF NOT EXISTS covid19_country(
id INTEGER PRIMARY KEY AUTO_INCREMENT
, iso_country VARCHAR(10), FOREIGN KEY (iso_country) REFERENCES country_airports(iso_country)
, confirmed INTEGER
, dead INTEGER
, recovered INTEGER
, updated TIMESTAMP
, longitude FLOAT
, latitude FLOAT
, travel_restrictions VARCHAR(2000))""")
try:
cur.execute(
"CREATE INDEX idx_country_ ON covid19_country(iso_country)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
# corona per region table
cur.execute("""CREATE TABLE IF NOT EXISTS covid19_region(
id INTEGER PRIMARY KEY AUTO_INCREMENT
, iso_region VARCHAR(50), FOREIGN KEY (iso_region) REFERENCES region_airports(iso_region)
, confirmed INTEGER
, dead INTEGER
, recovered INTEGER
, updated TIMESTAMP
, longitude FLOAT
, latitude FLOAT)""")
try:
cur.execute("CREATE INDEX idx_region_ ON covid19_region(iso_region)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
# corona per city table
cur.execute("""CREATE TABLE IF NOT EXISTS covid19_city(
id INTEGER PRIMARY KEY AUTO_INCREMENT
, location VARCHAR(200), FOREIGN KEY (location) REFERENCES city_airports(municipality)
, confirmed INTEGER
, dead INTEGER
, recovered INTEGER
, updated TIMESTAMP
, longitude FLOAT
, latitude FLOAT)""")
try:
cur.execute("CREATE INDEX idx_location_ ON covid19_city(location)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
db.commit()
def db_insert_flights(flight_data):
"""This function takes the a flight data dictionary as an input and feeds it into the database table departures.
:param flight_data: e.g.
{'flight_number': '425',
'flight_status': 'On time | Scheduled',
'departure_airport': 'TLV',
'arrival_airport': 'ETM',
'departure_date': '12-Apr-2020',
'arrival_date': '12-Apr-2020',
'operating_airline': '(6H) Israir Airlines'}"""
table = 'departures'
dep_airport = flight_data[CFG.KEY_dep_airport]
flight_id = flight_data[CFG.KEY_flight_id]
flight_status = flight_data[CFG.KEY_flight_status]
arrival_airport = flight_data[CFG.KEY_arr_airport]
departure_date = flight_data[CFG.KEY_dep_date]
arrival_date = flight_data[CFG.KEY_arr_date]
operating_airline = flight_data[CFG.KEY_airline]
flight_number = flight_data[CFG.KEY_flight_number]
db, cur = db_create_cursor()
# change type of data from bs4.element.NavigableString to string
for e in flight_data.keys():
flight_data[e] = str(flight_data[e])
# check if there is an entry for this data:
is_observation = db_select_flight(dep_airport, flight_id)
if is_observation:
stmt = f"""UPDATE {table} SET
{CFG.KEY_flight_status} = '{flight_status}',
{CFG.KEY_dep_date} = '{departure_date}',
{CFG.KEY_arr_airport} = '{arrival_airport}',
{CFG.KEY_arr_date} = '{arrival_date}',
{CFG.KEY_airline} = '{operating_airline}',
{CFG.KEY_flight_number} = {flight_number}
WHERE {CFG.KEY_dep_airport}='{dep_airport}' AND {CFG.KEY_flight_id}='{flight_id}';"""
cur.execute(stmt)
else:
placeholder = ", ".join(["%s"] * len(flight_data))
stmt = "INSERT INTO {table} ({columns}) VALUES ({values});".format(
table=table,
columns=",".join(flight_data.keys()),
values=placeholder)
try:
cur.execute(stmt, list(flight_data.values()))
except mysql.connector.errors.IntegrityError as err:
print(stmt)
print(list(flight_data.values()))
print("Error caught while inserting flights table: {}".format(err))
db.commit()
def create_conversion_tables():
"""this function creates a conversion table that connects the COVID19 tables with the airport table"""
db, cur = db_create_cursor()
# add iata code to the table and drop iso_region in the airports table?
# iata_code
# VARCHAR(10), FOREIGN
# KEY(iata_code)
# REFERENCES
# airports(iata_code),
# country - airports conversion table:
cur.execute("""CREATE TABLE IF NOT EXISTS country_airports(
iso_country VARCHAR(255) PRIMARY KEY
)""")
try:
cur.execute(
"CREATE INDEX idx_country ON country_airports(iso_country)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
# add iata code to the table and drop iso_region in the airports table?
# iata_code
# VARCHAR(10), FOREIGN
# KEY(iata_code)
# REFERENCES
# airports(iata_code),
# region - airports conversion table:
cur.execute("""CREATE TABLE IF NOT EXISTS region_airports(
iso_region VARCHAR(255) PRIMARY KEY
)""")
try:
cur.execute("CREATE INDEX idx_region ON region_airports(iso_region)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
# add iata code to the table and drop municipality in the airports table?
# iata_code
# VARCHAR(10), FOREIGN
# KEY(iata_code)
# REFERENCES
# airports(iata_code)
# city - airports conversion table:
cur.execute("""CREATE TABLE IF NOT EXISTS city_airports(
municipality VARCHAR(255) PRIMARY KEY
)""")
try:
cur.execute(
"CREATE INDEX idx_municipality ON city_airports(municipality)")
except mysql.connector.errors.ProgrammingError as err:
print(err)
def db_select_event(flight_id, event_date):
"""
This function creates a SQL query given a flight_id and an event date and returns the result.
:param flight_id: unique id for the specific flight
:param event_date: date of the event
:return: result of the SQL query
"""
table = 'events'
db, cur = db_create_cursor()
stmt = f"SELECT * " \
f"FROM {table} " \
f"WHERE {CFG.KEY_event_date}='{event_date}' AND {CFG.KEY_flight_id}='{flight_id}';"
cur.execute(stmt)
return cur.fetchall()
def db_select_flight(airport, flight_id):
"""
This function creates a SQL query given a flight_id and an airport date and returns the result.
:param flight_id: unique id for the specific flight
:param airport: iata code of the specific airport
:return: result of the SQL query
"""
table = 'departures'
db, cur = db_create_cursor()
stmt = f"SELECT * " \
f"FROM {table} " \
f"WHERE {CFG.KEY_dep_airport}='{airport}' AND {CFG.KEY_flight_id}='{flight_id}';"
cur.execute(stmt)
return cur.fetchall()
def db_select_country_data(country):
"""
This function creates a SQL query given a country returns the last entry created.
:param country: iso_country
:return: result of the SQL query
"""
table = 'covid19_country'
db, cur = db_create_cursor()
stmt = f"SELECT * " \
f"FROM {table} " \
f"WHERE iso_country='{country}' ORDER BY updated DESC LIMIT 1;"
cur.execute(stmt)
return cur.fetchall()
def db_insert_conversion_tables():
"""This fills airports and in the database flights_departures"""
airports = get_airports()
db, cur = db_create_cursor()
# city
cities = airports['municipality'].dropna().unique()
query = """INSERT INTO city_airports (municipality) VALUES (%s);"""
for city in cities:
# catch error if there are duplicates in the data set
try:
cur.execute(query, [city])
except mysql.connector.errors.IntegrityError as err:
print("Error caught while updating country_airports table: {}".
format(err))
# region
regions = airports['iso_region'].dropna().unique()
query = """INSERT INTO region_airports (iso_region) VALUES (%s);"""
for region in regions:
# catch error if there are duplicates in the data set
try:
cur.execute(query, [region])
except mysql.connector.errors.IntegrityError as err:
print(
"Error caught while updating region_airports table: {}".format(
err))
# country
countries = airports['iso_country'].dropna().unique()
query = """INSERT INTO country_airports (iso_country) VALUES (%s);"""
for country in countries:
# catch error if there are duplicates in the data set
try:
cur.execute(query, [country])
except mysql.connector.errors.IntegrityError as err:
print("Error caught while updating city_airport table: {}".format(
err))
db.commit()
def db_feed_covid19_travel_restrictions(df):
"""
This function feeds the table covid19_country with the travel restrictions scraped
:param df: dataframe created with an API request
:return: None
"""
db, cur = db_create_cursor()
table = 'covid19_country'
for index, country in df.iterrows():
# check for last entry in covid19_country table:
is_observation = db_select_country_data(country[CFG.KEY_country])
data = (country['data'].replace("\'", ''))
# print(type(is_observation[CFG.first_elem][CFG.updated]))
if is_observation:
stmt = f"""UPDATE {table} SET
travel_restrictions='{data}'
WHERE
'{CFG.KEY_country}'='{country[CFG.KEY_country]}' AND
updated='{is_observation[CFG.first_elem][CFG.updated]}';"""
cur.execute(stmt)
