def insert_airports(self, airports: List[Dict]) -> None:
logger.info(f"airports: {airports[0]}")
with self.conn:
with self.conn.cursor() as curs:
columns_str, values_str = column_value_to_str(Airport._fields)
insert_query = f"INSERT INTO airports ({columns_str}) VALUES %s"
template = f"({values_str})"
extras.execute_values(curs, insert_query, airports, template)
logger.debug(f"Inserted {len(airports)} airports")
curs.execute("UPDATE airports SET icao = REPLACE (icao, '\"', '');")
curs.execute("UPDATE airports SET iata = REPLACE (iata, '\"', '');")
curs.execute("UPDATE airports SET name = REPLACE (name, '\"', '');")
curs.execute("UPDATE airports SET city = REPLACE (city, '\"', '');")
curs.execute("UPDATE airports SET country = REPLACE (country, '\"', '');")
def insert_departure_airport_stats(self, airport_icao: str, day: str) -> None:
with self.conn.cursor() as curs:
new_stats = AirportStats(
airport_icao=airport_icao,
the_date=day,
airplane_quantity_arrivals=0,
airplane_quantity_departures=1,
)
stats = new_stats._asdict()
columns_str, values_str = column_value_to_str(new_stats._fields)
curs.execute(
f"INSERT INTO airport_stats ({columns_str})" f"VALUES ({values_str})",
stats,
)
def insert_current_states(self, aircraft_states: List[StateVector]) -> None:
with self.conn.cursor() as curs:
"""Row deletion can be implemented either by DELETE or by TRUNCATE TABLE.
Though the DELETE option requires VACUUM to remove dead row versions,
it is still preferred in our context as TRUNCATE would violate MVCC semantics
and hinder parallel requests to access current states"""
curs.execute(f"DELETE FROM current_states;")
resp_time: int = getattr(aircraft_states[0], "request_time")
# aircraft_states = sorted(aircraft_states, key=lambda k: k.icao24)
for state in aircraft_states:
columns_str, values_str = column_value_to_str(state._fields)
row: Dict = state._asdict()
curs.execute(
f"INSERT INTO current_states ({columns_str}) VALUES ({values_str}) " f"ON CONFLICT DO NOTHING",
row,
)
logger.debug(f"Inserted {len(aircraft_states)} aircraft states for the timestamp {resp_time}")
def insert_path(self, path: FlightPath) -> None:
columns_str, values_str = column_value_to_str(path._fields)
flight_path = path._asdict()
with self.conn.cursor() as curs:
curs.execute(f"INSERT INTO flight_paths ({columns_str}) VALUES ({values_str})", flight_path)