def threat_log_engine(input_threat_log): if check_create_bookmark(input_threat_log) == "log": log = DailyThreatLogEngine(input_threat_log, config.COUNTRY_DB_FILEPATH, utils.get_db_engine(), config.SPARK) log.run() set_bookmark(input_threat_log, "complete")
def traffic_log_engine(input_traffic_log): if check_create_bookmark(input_traffic_log) == "log": log = DailyTrafficLogEngine(input_traffic_log, config.COUNTRY_DB_FILEPATH, utils.get_db_engine(), config.SPARK) log.run() set_bookmark(input_traffic_log, "rule")
def is_threat_log_already_processed(input_threat_log): processed_logs_from_db = pd.read_sql_table( 'fh_prd_thrt_log_f', utils.get_db_engine()).set_index("log_name").to_dict()["id"] if input_threat_log in processed_logs_from_db: return True return False
def main(): engine = utils.get_db_engine() today = utils.get_current_time() resp = utils.get_uri_content(uri=const.DARK_SKY_URI, content_type='json') for key in resp.keys(): if isinstance(resp.get(key), dict) and 'data' in resp.get(key): for n, i in enumerate(resp.get(key)['data']): resp.get(key)['data'][n]['currentTime'] = today resp['currently']['lat'] = resp['latitude'] resp['currently']['lng'] = resp['longitude'] current_df = pd.DataFrame([resp['currently']]) daily_df = pd.DataFrame(resp['daily']['data']) hourly_df = pd.DataFrame(resp['hourly']['data']) minutely_df = pd.DataFrame(resp['minutely']['data']) tables = [ 'current_weather', 'daily_weather', 'hourly_weather', 'minutely_weather' ] data_to_import = [current_df, daily_df, hourly_df, minutely_df] for data, table in zip(data_to_import, tables): data.to_sql(table, con=engine, if_exists='append', index=False)
def traffic_chart_engine(input_traffic_log): if check_create_bookmark(input_traffic_log) == "chart": chart = DailyChartEngine(input_traffic_log, spark=config.SPARK, db_engine=utils.get_db_engine()) chart.run() set_bookmark(input_traffic_log, "complete")
def main(): engine = utils.get_db_engine() results = engine.execute( 'select * from results order by dateof desc limit 1').fetchone() prediction = results.prediction if prediction >= .6: utils.send_email('Take Action', 'Preemptive action may be necessary')
def set_bookmark(input_log, bookmark): engine = utils.get_db_engine() Session = sessionmaker(bind=engine, autocommit=True) session = Session() session.begin() rs = session.execute( f"UPDATE fh_bookmark set bookmark = '{bookmark}' where log_name='{input_log}'" ) session.commit() session.close()
def get_database_and_connection(self, local_base_name: str): database_obj = self.execute_database_action( BasesTable, "first", local_name=local_base_name ) return database_obj, get_db_engine( database_obj.type, ip=database_obj.ip, port=database_obj.port, username=database_obj.username, database=database_obj.database, password=database_obj.password, )
def ready_for_staging(): engine = utils.get_db_engine() Session = sessionmaker(bind=engine, autocommit=True) session = Session() session.begin() try: rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_dy_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_new_app_dy_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_new_dst_ip_dy_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_new_src_ip_dy_a") rs = session.execute( "TRUNCATE TABLE fh_stg_trfc_mis_req_frm_blip_dy_a") rs = session.execute( "TRUNCATE TABLE fh_stg_trfc_mis_req_frm_blip_dy_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_res_to_blip_dy_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_log_dtl_f") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_log_dtl_hr_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_log_dtl_dy_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_rule_f") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_app_dt_hr_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_con_dt_hr_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_ip_dt_hr_a") rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_tm_srs_dt_hr_a") rs = session.execute("TRUNCATE TABLE fh_stg_thrt_log_dtl_f") rs = session.execute("TRUNCATE TABLE fh_stg_thrt_log_dtl_evnt_f") rs = session.execute("TRUNCATE TABLE fh_stg_tt_anmly_f") print("TRUNCATE STAGE TABLE COMPLETED") logger.info(f'truncate stage table completed') except: session.rollback() raise finally: session.commit() session.close() pass
def check_create_bookmark(input_log): engine = utils.get_db_engine() Session = sessionmaker(bind=engine, autocommit=True) session = Session() session.begin() rs = session.execute( f"SELECT log_name from fh_bookmark where log_name = '{input_log}'") if (rs.fetchone() is None): rs = session.execute( f"INSERT INTO fh_bookmark (datetime, log_name, bookmark) VALUES ('{datetime.datetime.now()}','{input_log}','none')" ) session.commit() session.close() return "none" else: rs = session.execute( f"SELECT bookmark from fh_bookmark where log_name = '{input_log}'") return (rs.fetchone()[0]) session.commit() session.close()
def main(): engine = utils.get_db_engine() today = utils.get_current_time() resp = utils.get_uri_content(uri=const.POLLEN_URI, headers=const.POLLEN_HEADERS, content_type='json') allergens = resp['Location']['periods'] for n, row in enumerate(allergens): allergens[n]['Triggers'] = ', '.join( [i['Name'] for i in allergens[n]['Triggers']]) allergens[n]['dateof'] = today allergens_df = pd.DataFrame(allergens) allergens_df.to_sql('allergens', con=engine, if_exists='append', index=False)
def main(): engine = utils.get_db_engine() today = utils.get_current_time() soup = utils.get_uri_content(uri=const.AIRNOW_URI, content_type='html') aqi_table = soup.find_all('table', {'width': '65%'})[0] rows = [] for table_row in aqi_table.find_all('tr'): pollutant = table_row.find_all( 'td', {'class': 'AQDataPollDetails'})[0].text.strip() value = table_row.find_all('td', {'height': '27'})[0].text.strip() row = {'pollutant': pollutant, 'value': value, 'dateof': today} rows.append(row) pollutant_df = pd.DataFrame(rows) pollutant_df.to_sql('pollutants', con=engine, if_exists='append', index=False)
def test_insert_into_table(): engine = utils.get_db_engine() Temp_table.__table__.create(bind=engine, checkfirst=True) data = main.read_data_from_csv(source="test/vehicle.csv0001_part_00") long, short = main.split_into_long_and_normal_lists(data_list=data, mater=list(), vehicles=list()) test_short = list() for row in short: test_short.append(row[:3]) test_short.append(test_short[6]) main.insert_into_table(data_list=test_short, table=Temp_table, engine=engine) length = engine.execute("SELECT COUNT(*) FROM test_table").scalar() Temp_table.__table__.drop(bind=engine) assert length == 7
def add_database( self, base_type, description, local_name=None, **con_params ) -> str: if not database_health_check(get_db_engine(base_type, **con_params)): raise ConnectionError("Can't connect to database") if not local_name: local_name = "_".join( [ con_params["ip"], con_params["port"], con_params["database"], con_params["user"], ] ) if self._is_db_exists(local_name): raise DatabaseAlreadyExistsError(local_name) new_database = BasesTable( type=base_type, description=description, local_name=local_name, **con_params ) self.db_session.add(new_database) self._commit() return local_name
def threat_mis_engine(input_threat_log): if (check_create_bookmark(input_threat_log) == "none"): mis = DailyThreatMISEngine(config.SPARK, utils.get_db_engine(), input_threat_log) mis.run() set_bookmark(input_threat_log, "log")
def traffic_mis_engine(input_traffic_log): if check_create_bookmark(input_traffic_log) == "none": mis = DailyTrafficMISEngine(config.SPARK, utils.get_db_engine(), input_traffic_log) mis.run() set_bookmark(input_traffic_log, "log")
def commit_changes_to_production(): engine = utils.get_db_engine() Session = sessionmaker(bind=engine, autocommit=True) session = Session() session.begin() try: # traffic log mis insert_stage_data_to_prod_table(session, 'fh_stg_trfc_mis_dy_a', 'fh_prd_trfc_mis_dy_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_mis_new_app_dy_a', 'fh_prd_trfc_mis_new_app_dy_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_mis_new_dst_ip_dy_a', 'fh_prd_trfc_mis_new_dst_ip_dy_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_mis_new_src_ip_dy_a', 'fh_prd_trfc_mis_new_src_ip_dy_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_mis_req_frm_blip_dy_a', 'fh_prd_trfc_mis_req_frm_blip_dy_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_mis_res_to_blip_dy_a', 'fh_prd_trfc_mis_res_to_blip_dy_a') # traffic log log insert_stage_data_to_prod_table(session, 'fh_stg_trfc_log_dtl_f', 'fh_prd_trfc_log_dtl_f') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_log_dtl_hr_a', 'fh_prd_trfc_log_dtl_hr_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_log_dtl_dy_a', 'fh_prd_trfc_log_dtl_dy_a') # traffic log rule insert_stage_data_to_prod_table(session, 'fh_stg_trfc_rule_f', 'fh_prd_trfc_rule_f') # traffic log chart insert_stage_data_to_prod_table(session, 'fh_stg_trfc_chrt_app_dt_hr_a', 'fh_prd_trfc_chrt_app_dt_hr_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_chrt_con_dt_hr_a', 'fh_prd_trfc_chrt_con_dt_hr_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_chrt_ip_dt_hr_a', 'fh_prd_trfc_chrt_ip_dt_hr_a') insert_stage_data_to_prod_table(session, 'fh_stg_trfc_chrt_tm_srs_dt_hr_a', 'fh_prd_trfc_chrt_tm_srs_dt_hr_a') # traffic log trouble ticket # threat log log insert_stage_data_to_prod_table(session, 'fh_stg_thrt_log_dtl_f', 'fh_prd_thrt_log_dtl_f') insert_stage_data_to_prod_table(session, 'fh_stg_thrt_log_dtl_evnt_f', 'fh_prd_thrt_log_dtl_evnt_f') insert_stage_data_to_prod_table(session, 'fh_stg_tt_anmly_f', 'fh_prd_tt_anmly_f') logger.info(f'stage data written to prod table completed') except: session.rollback() raise finally: session.commit() session.close()
def traffic_rule_engine(input_traffic_log): if check_create_bookmark(input_traffic_log) == "rule": rule = DailyTrafficRuleEngine(input_traffic_log, utils.get_db_engine(), config.SPARK) rule.run() set_bookmark(input_traffic_log, "chart")
def test_get_db_engine_works(): engine = utils.get_db_engine() r = engine.execute("SHOW DATABASES;").scalar() assert r == "ACT"
finally: print( f"All update done. Result can be found in table {pistoncup.__table__.name}." ) session.close() if __name__ == '__main__': # Download, extract and save the data files to disk utils.mkdir(directory=constants.DATA_FOLDER) for file in constants.FILES: download_and_save_gzip_from_url(source=file, destination=constants.DATA_FOLDER) # initialize objects to talk to database engine = utils.get_db_engine() if not database.is_initialized(engine): database.initialize_database(engine) if not check_db_filled(engine): vehicle_table = database.Vehicle mater_table = database.Mater # Read csv files into lists mater: List[List[str]] = list() vehicles: List[List[str]] = list() for file in os.listdir(constants.DATA_FOLDER): file_vehicle_list = read_data_from_csv( source=os.path.join(constants.DATA_FOLDER, file)) mater, vehicles = split_into_long_and_normal_lists( file_vehicle_list, mater, vehicles)
def traffic_log_engine(input_traffic_log): log = DailyTrafficLogEngine(input_traffic_log, config.TRAFFIC_LOGS_OUTPUT_DIR, config.COUNTRY_DB_FILEPATH, utils.get_db_engine(), config.SPARK) log.run()