Source code for traveltimes_prediction.data_processing.db_interface

import json
import logging
from datetime import datetime

import pymysql

from ..configs import connection_string_data_det1, connection_string_models, connection_string_results
from ..configs.sections_settings import sections_settings
from ..support_files.helpers import compress, decompress, merge_inner_lists

logger = logging.getLogger('traveltimes_db_interface')


[docs]class DBInterface: """ Class implementing database connections to data sources and storages. """ def __init__(self): """ Constructor. """ self._open_connection() self.mysql_datetime_format = '%Y-%m-%d %H:%M:%S' # Datetime format that MySQL understands #################################################################################################################### ############################################ DATA RETRIEVAL ######################################################## ####################################################################################################################
[docs] def get_last_timestamp(self, section): """ Method for retrieval of the most recent data timestamp of the section. :param string section: e.g. 'KOCE-LNCE' :return: datetime """ cursor = self.mysqlconn_data.cursor() cursor.execute(""" SELECT calculation_time FROM tt3.tt3_output_traveltimes WHERE output_section LIKE %s ORDER BY calculation_time DESC LIMIT 1 """, ('%' + section + '%',)) date = cursor.fetchone() cursor.close() return date[0] if date is not None else None
[docs] def check_latest_referential_traveltime(self, section): """ Method for retrieval of the most recent data timestamp of the section - checking the latest calculated referential traveltime. :param string section: e.g. 'KOCE-LNCE' :return: datetime.datetime - latest calculated referential traveltime for given section """ cursor = self.mysqlconn_data.cursor() cursor.execute(""" SELECT calculation_time_local FROM tt4.tt_real_traveltimes WHERE section_id=%s ORDER BY calculation_time DESC LIMIT 1 """, section) date = cursor.fetchone() cursor.close() return date[0] if date is not None else None
#################################################################################################################### #################################################### MODELS ######################################################## ####################################################################################################################
[docs] def load_model(self, section, model_type): """ Method for loading of the saved trained model from DB. :param string section: name of the section, e.g. 'KOCE-LNCE' :param string model_type: name of the model, e.g. 'TimeDomainModel' :return: dict - dictionary representation of model`s attributes """ cursor = self.mysqlconn_models.cursor() cursor.execute("SELECT model_dump FROM tt_prediction_models WHERE section=%s and model_type=%s", (section, model_type)) model = cursor.fetchone() cursor.close() model = json.loads(decompress(model[0])) if model is not None else None return model
[docs] def save_model(self, section, model, time_from, time_to, model_params): """ Method for saving of the trained model to database. :param string section: name of the section for which the model was created - e.g. 'KOCE-LNCE' :param dict model: dictionary representation of the model :param datetime time_from: timestamp of the earliest data used for creation of this model :param datetime time_to: timestamp of the latest data used for creation of this model :param dict model_params: dictionary with the parameters of the mdoel which have been used by the training """ _inner_sensors = merge_inner_lists(sections_settings[section]['inner_sensors']) used_sensors = sections_settings[section]['input_sensors'] + sections_settings[section]['output_sensors'] + \ _inner_sensors used_sensors = "".join([x + "," for x in used_sensors])[:-1] model_dump = compress(json.dumps(model)) cursor = self.mysqlconn_models.cursor() # Check if the model is in DB, if yes, update else insert if not self._model_in_DB(section=section, model_type=model['model_type']): # TODO apply zlib compression & MEDIUMTEXT in DB is very bad idea... cursor.execute(""" INSERT INTO tt_prediction_models(section,model_dump,model_type,model_parameters,used_sensors,time_from,time_to) VALUES (%s,%s,%s,%s,%s,%s,%s)""", (section, model_dump, model['model_type'], str(model_params), used_sensors, time_from.strftime(self.mysql_datetime_format), time_to.strftime(self.mysql_datetime_format))) else: cursor.execute(""" UPDATE tt_prediction_models SET model_dump=%s,model_parameters=%s,used_sensors=%s,time_from=%s,time_to=%s,update_timestamp=%s WHERE section=%s and model_type=%s""", (model_dump, str(model_params), used_sensors, time_from.strftime(self.mysql_datetime_format), time_to.strftime(self.mysql_datetime_format), datetime.now().strftime(self.mysql_datetime_format), section, model['model_type'])) self.mysqlconn_models.commit() cursor.close()
def _model_in_DB(self, section, model_type): """ Method to find out whether in DB there is model of specified type and trained for specified section. :param string section: name of the section - e.g. 'KOCE-LNCE' :param string model_type: name of the model - e.g. 'TimeDomainModel' :return: boolean - True if such a model exists in DB, false otherwise """ cursor = self.mysqlconn_models.cursor() cursor.execute("""SELECT COUNT(model_dump) FROM tt_prediction_models WHERE section=%s AND model_type=%s""", (section, model_type)) res = cursor.fetchone()[0] cursor.close() return True if res == 1 else False
[docs] def model_timestamp(self, section, model_type): """ Method used to find out the timestamp of model creation. :param string section: name of the section, e.g. 'KOCE-LNCE" :param string model_type: name of the model, e.g. 'TimeDomainModel' :return: boolean """ cursor = self.mysqlconn_models.cursor() cursor.execute("""SELECT update_timestamp as tstamp FROM tt_prediction_models WHERE section=%s and model_type=%s""", (section, model_type)) res = cursor.fetchone() cursor.close() return res[0] if res is not None else None
[docs] def get_model_params(self, section, model_type): """ Method used to retrieve the params of trained model. :param string section: name of the section, e.g. 'KOCE-LNCE" :param string model_type: name of the model, e.g. 'TimeDomainModel' :return: tuple (model`s name, dictionary representation of model params used for training """ cursor = self.mysqlconn_models.cursor() cursor.execute("""SELECT model_type, model_parameters FROM tt_prediction_models WHERE section=%s and model_type=%s""", (section, model_type)) res = cursor.fetchone() cursor.close() return (res[0], json.loads(res[1].replace('\'', '"'))) if res is not None else (None, None)
#################################################################################################################### ######################################## PREDICTION RESULTS ######################################################## ####################################################################################################################
[docs] def save_prediction_result(self, rows): """ Method used for archiving the prediction results. :param list rows: list of tuples - each tuple contains parameters of the query. (set of parameters) """ try: cursor = self.mysqlconn_results.cursor() cursor.executemany(""" INSERT INTO tt_prediction_results(output_section,calculation_time,calculation_time_local,tt_calculated,model_type,confidence,calc_note,calc_delay) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)""", tuple(rows)) self.mysqlconn_results.commit() cursor.close() except Exception as e: logger.exception(e)
def _close_connection(self): """ Method for closing of the DB connections. :return: void """ try: self.mysqlconn_data.close() except: logger.warning("Unable to close the connection to data source !!") try: self.mysqlconn_models.close() except: logger.warning("Unable to close the connection to models` storage !!") try: self.mysqlconn_results.close() except: logger.warning("Unable to close the connection to results` storage !!") def _open_connection(self): """ Method for the opening of the DB connections. :return: void """ try: self.mysqlconn_data = pymysql.connect(**connection_string_data_det1) # Connection to data source - bck, sensors self.mysqlconn_models = pymysql.connect(**connection_string_models) # Connection to models` storage self.mysqlconn_results = pymysql.connect(**connection_string_results) # Connection to results` storage except: raise Exception('Unable to connect to DB !!!')