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 !!!')