def populate(): db = DatabaseConnection() sql = """INSERT INTO accidents_weather_data_mart.accident_fact( hour_key, location_key, accident_key, weather_key, is_fatal, is_intersection ) SELECT AH.hour_key, AL.location_key, AH.accident_key, WL.weather_key, A.collision_classification = 'fatal' as is_fatal, L.is_intersection FROM relations.accident_hour_relation AH, relations.accident_location_relation AL, relations.weather_hour_relation WH, relations.weather_location_relation WL, accidents_weather_data_mart.accident_dimension A, accidents_weather_data_mart.location_dimension L WHERE AL.accident_key = AH.accident_key AND A.accident_key = AL.accident_key AND AH.hour_key = WH.hour_key AND WH.weather_key = WL.weather_key AND AL.location_key = WL.location_key AND L.location_key = AL.location_key""" with db.get_connection().cursor() as cursor: cursor.execute(sql)
def get_count(sql): db = DatabaseConnection() with db.get_connection().cursor( cursor_factory=psycopg2.extras.DictCursor) as cursor: cursor.execute(sql) return cursor.fetchone()['count']
def insert_many(entities): """ Inserts a single entity to the database. :param entities: a tuple of the form -> ([ street_name, intersection_1, intersection_2, longitude, latitude, city, neighbourhood, is_intersection]) :return: None """ db = DatabaseConnection() sql_insert = """INSERT INTO dimension_pre_stage.location_dimension_pre_stage ( street_name, intersection_1, intersection_2, longitude, latitude, city, neighbourhood, is_intersection) VALUES %s;""" with db.get_connection().cursor() as cursor: execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def insert_many(entities): """ Insert many entities to the database. :param entities: a list of tuples of the form -> [( station_name, longitude, latitude, elevation, date, time, temperature, dew_point_temp, relative_humidity, wind_direction, wind_speed, wind_speed_flag, visibility, station_pressure, humidex, wind_chill, wind_chill_flag, weather)] :return: None """ db = DatabaseConnection() sql_insert = """INSERT INTO dimension_pre_stage.weather_dimension_pre_stage ( station_name, longitude, latitude, elevation, date, time, temperature, temperature_flag, dew_point_temp, dew_point_temp_flag, relative_humidity, relative_humidity_flag, wind_direction, wind_direction_flag, wind_speed, wind_speed_flag, visibility, visibility_flag, station_pressure, station_pressure_flag, humidex, humidex_flag, wind_chill, wind_chill_flag, weather, weather_flag) VALUES %s""" with db.get_connection().cursor() as cursor: execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def insert_many_temp(entities): db = DatabaseConnection() sql_insert = """INSERT INTO relations.weather_location_temp_relation (station_name, location_key, date, time) VALUES %s;""" with db.get_connection().cursor() as cursor: execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def connect_accident_hour_dimension(): db = DatabaseConnection() sql = """INSERT INTO relations.accident_hour_relation (accident_key, hour_key) SELECT A.accident_key, H.hour_key FROM dimension_pre_stage.accident_dimension_pre_stage A, dimension_pre_stage.hour_dimension_pre_stage H WHERE date_trunc('hour', A.date + A.time + INTERVAL '30 minute') = H.date + H.hour_start;""" with db.get_connection().cursor() as cursor: cursor.execute(sql)
def connect_weather_to_location_dimension(): db = DatabaseConnection() sql = """INSERT INTO relations.weather_location_relation (weather_key, location_key) SELECT W.weather_key, T.location_key FROM dimension_pre_stage.weather_dimension_pre_stage W, relations.weather_location_temp_relation T WHERE W.station_name = T.station_name AND W.date = T.date AND W.time = T.time;""" with db.get_connection().cursor() as cursor: cursor.execute(sql)
def insert_many(entities): """ Insert many entities to the database. :param entities: a list of tuples of the form -> [( id, collision_id, location, longitude, latitude, date, time, environment, environment_flag, light, light_flag, surface_condition, surface_condition, traffic_control, traffic_control_flag, traffic_control_condition, traffic_control_condition_flag, collision_classification, collision_classification_flag, impact_type, impact_type_flag, no_of_pedestrians)] :return: None """ db = DatabaseConnection() sql_insert = """INSERT INTO dimension_pre_stage.accident_dimension_pre_stage ( longitude, latitude, date, time, street_name, street1, street2, environment, environment_flag, road_surface, road_surface_flag, traffic_control, traffic_control_flag, visibility, visibility_flag, collision_classification, collision_classification_flag, impact_type, impact_type_flag) VALUES %s""" with db.get_connection().cursor() as cursor: execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def connect_event_hour_dimension(): db = DatabaseConnection() sql = """INSERT INTO relations.event_hour_relation (event_key, hour_key) SELECT E.event_key, H.hour_key FROM dimension_pre_stage.event_dimension_pre_stage E, dimension_pre_stage.hour_dimension_pre_stage H WHERE E.start_date = H.date OR E.end_date = H.date""" with db.get_connection().cursor() as cursor: cursor.execute(sql)
def connect_accident_location_dimension(): db = DatabaseConnection() sql = """INSERT INTO relations.accident_location_relation (accident_key, location_key) SELECT A.accident_key, L.location_key FROM dimension_pre_stage.accident_dimension_pre_stage A, dimension_pre_stage.location_dimension_pre_stage L WHERE L.longitude = A.longitude AND L.latitude = A.latitude""" with db.get_connection().cursor() as cursor: cursor.execute(sql)
def connect_weather_hour_dimension(): db = DatabaseConnection() sql = """INSERT INTO relations.weather_hour_relation (weather_key, hour_key) SELECT W.weather_key, H.hour_key FROM dimension_pre_stage.weather_dimension_pre_stage W, dimension_pre_stage.hour_dimension_pre_stage H WHERE W.date = H.date AND H.hour_start <= W.time AND W.time <= H.hour_end""" with db.get_connection().cursor() as cursor: cursor.execute(sql)
def connect_event_location_dimension(): db = DatabaseConnection() sql = """INSERT INTO relations.event_location_relation (event_key, location_key) SELECT E.event_key, L.location_key FROM dimension_pre_stage.event_dimension_pre_stage E, dimension_pre_stage.location_dimension_pre_stage L WHERE E.city = L.city""" with db.get_connection().cursor() as cursor: cursor.execute(sql)
def fetch_all(sql): db = DatabaseConnection() with db.get_connection().cursor( cursor_factory=psycopg2.extras.DictCursor) as cursor: cursor.execute(sql) while True: results = cursor.fetchmany(500) if not results: break for result in results: yield result
def insert_many(entities): db = DatabaseConnection() sql_insert = """INSERT INTO dimension_pre_stage.event_dimension_pre_stage ( name, start_date, end_date, city) VALUES %s;""" with db.get_connection().cursor() as cursor: execute_values(cur=cursor, sql=sql_insert, argslist=entities)
def update_temperature_nulls(): """ Update all temperature pre-stage values """ db = DatabaseConnection() sql_update = \ """ UPDATE dimension_pre_stage.weather_dimension_pre_stage as W SET temperature_flag = 'estimated', temperature = av.temp FROM (select date, AVG(temperature) as temp from dimension_pre_stage.weather_dimension_pre_stage group by date) as av WHERE W.date = av.date AND W.temperature IS NULL """ with db.get_connection().cursor() as cursor: cursor.execute(sql_update)
def update_weather_nulls(): """ Update all weather values """ db = DatabaseConnection() sql_update = \ """ UPDATE dimension_pre_stage.weather_dimension_pre_stage as W SET weather_flag = 'estimated', weather = av.weath FROM (SELECT date, weather as weath, COUNT(weather) AS cnt FROM dimension_pre_stage.weather_dimension_pre_stage GROUP BY date, weather ORDER BY cnt DESC LIMIT 1) as av WHERE W.date = av.date AND W.weather IS NULL """ with db.get_connection().cursor() as cursor: cursor.execute(sql_update)
def copy_data_from_accident_pre_stage(): db = DatabaseConnection() sql = """INSERT INTO accidents_weather_data_mart.accident_dimension ( accident_key, time, environment, environment_flag, road_surface, road_surface_flag, traffic_control, traffic_control_flag, visibility, visibility_flag, collision_classification, collision_classification_flag, impact_type, impact_type_flag ) SELECT accident_key, time, environment, environment_flag, road_surface, road_surface_flag, traffic_control, traffic_control_flag, visibility, visibility_flag, collision_classification, collision_classification_flag, impact_type, impact_type_flag FROM dimension_pre_stage.accident_dimension_pre_stage""" with db.get_connection().cursor() as cursor: cursor.execute(sql)