def get_curw_sim_discharge_id(pool, model, method, grid_id):
"""
Retrieve curw_sim discharge id
:param pool: Database connection pool
:param model: target forecast model
:param method: timeseries population method
:param grid_id: timeseries grid id
:return: curw_sim discharge hash id corresponds to specified mode, method and grid_id
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `id` FROM curw_sim.dis_run WHERE `model`=%s AND `method`=%s AND `grid_id`=%s;"
row_count = cursor.execute(sql_statement, (model, method, grid_id))
if row_count > 0:
return cursor.fetchone()['id']
else:
return None
except Exception as exception:
error_message = "Retrieving curw_sim discharge id failed :: {} :: {} :: {}".format(
model, method, grid_id)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_timeseries(self, id_, start_date, end_date):
"""
Retrieve timeseries by id
:param id_:
:return: list of [time, value] pairs if id exists, else None
"""
connection = self.pool.connection()
ts = []
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `time`,`value` FROM `data` WHERE `id`=%s AND `time` BETWEEN %s AND %s;"
rows = cursor.execute(sql_statement,
(id_, start_date, end_date))
if rows > 0:
results = cursor.fetchall()
for result in results:
ts.append([result.get('time'), result.get('value')])
return ts
except Exception as exception:
error_message = "Retrieving timeseries for id {} failed.".format(
id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def update_grid_id(self, id_, grid_id):
"""
Update gird id for inserted timeseries
:param id_: timeseries id
:param grid_id: link to the grid maps
:return: True if update is successful, else raise DatabaseAdapterError
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "UPDATE `run` SET `grid_id`=%s WHERE `id`=%s"
cursor.execute(sql_statement, (grid_id, id_))
connection.commit()
return True
except Exception as exception:
connection.rollback()
error_message = "Updating grid_id for id={} failed.".format(id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_source_id(pool, model, version) -> str:
"""
Retrieve Source id
:param pool: database connection pool
:param model:
:param version:
:return: str: source id if source exists in the database, else None
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `id` FROM `source` WHERE `model`=%s and `version`=%s"
row_count = cursor.execute(sql_statement, (model, version))
if row_count > 0:
return cursor.fetchone()['id']
else:
return None
except Exception as exception:
error_message = "Retrieving source id: model={} and version={} failed.".format(
model, version)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_obs_end(self, id_):
"""
Retrieve obs_end for a given hash id
:param id_:
:return: obs_end if exists. None if doesn't exist, raise DatabaseAdapterError otherwise
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `obs_end` FROM `run` WHERE `id`=%s"
result = cursor.execute(sql_statement, id_)
if result > 0:
return cursor.fetchone()['obs_end']
else:
return None
except Exception as exception:
error_message = "Retrieving obs_end for id={} failed.".format(id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def read_template(pool, sim_tag, source_id, variable_id, fgt, output_file_path):
"""
Read template (convert BLOB to a file)
:param source_id:
:param sim_tag:
:param fgt:
:param output_file_path: where to write the output
:return:
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `template` FROM `run_info` WHERE `sim_tag`=%s and `source`=%s and " \
"`variable`=%s and `fgt`=%s"
row_count = cursor.execute(sql_statement, (sim_tag, source_id, variable_id, fgt))
if row_count > 0:
template_data = cursor.fetchone()['template']
write_file(data=template_data, filename=output_file_path)
else:
return None
return True
except Exception as exception:
error_message = "Retrieving template failed for run info entry with source={}, variable={}, sim_tag={}, fgt={}" \
.format(source_id, variable_id, sim_tag, fgt)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def delete_source_by_id(pool, id_):
"""
Delete source from Source table by id
:param pool: database connection pool
:param id_:
:return: True if the deletion was successful, else False
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "DELETE FROM `source` WHERE `id`=%s"
row_count = cursor.execute(sql_statement, id_)
connection.commit()
if row_count > 0:
return True
else:
logger.info(
"There's no record of source in the database with the source id {}"
.format(id_))
return False
except Exception as exception:
connection.rollback()
error_message = "Deleting source with id {} failed.".format(id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def delete_all_by_hash_id(self, id_):
"""
Delete all timeseries with different fgts but with the same hash id (same meta data)
:param id_: hash id
:return:
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "DELETE FROM `curw_fcst`.`run` WHERE `id`= %s ;"
row_count = cursor.execute(sql_statement, id_)
connection.commit()
return row_count
except Exception as exception:
connection.rollback()
error_message = "Deletion of timeseries with hash id {} failed".format(
id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def update_latest_obs(self, id_, obs_end):
"""
Update obs_end for inserted timeseries
:param id_: timeseries id
:param obs_end: end time of observations
:return: True if update is successful, else raise DatabaseAdapterError
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "UPDATE `dis_run` SET `obs_end`=%s WHERE `id`=%s"
cursor.execute(sql_statement, (obs_end, id_))
connection.commit()
return True
except Exception as exception:
connection.rollback()
error_message = "Updating obs_end for id={} failed.".format(id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_end_date(self, sim_tag, station_id, source_id, variable_id,
unit_id):
"""
Retrieve the latest fgt of fcst timeseries available for the given parameters
:param sim_tag:
:param station_id:
:param source_id:
:param variable_id:
:param unit_id:
:return:
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor1:
sql_statement = "SELECT `end_date` FROM `run` WHERE `source`=%s AND `station`=%s " \
"AND `sim_tag`=%s AND `variable`=%s AND `unit`=%s;"
is_exist = cursor1.execute(
sql_statement,
(source_id, station_id, sim_tag, variable_id, unit_id))
if is_exist > 0:
return cursor1.fetchone()['end_date']
else:
return None
except Exception as exception:
error_message = "Retrieving latest fgt failed."
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def delete_timeseries(self, id_, fgt):
"""
Delete specific timeseries identified by hash id and a fgt
:param id_: hash id
:param fgt: fgt
:return:
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "DELETE FROM `curw_fcst`.`data` WHERE `id`= %s AND `fgt`=%s ;"
row_count = cursor.execute(sql_statement, (id_, fgt))
connection.commit()
return row_count
except Exception as exception:
connection.rollback()
error_message = "Deletion of timeseries with hash id {} failed".format(
id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_latest_fgt(self, id_):
"""
Retrive latest fgt for given id
:param id_: timeseries id
:return:
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `end_date` FROM `run` WHERE `id`=%s"
row_count = cursor.execute(sql_statement, id_)
if row_count > 0:
return cursor.fetchone()['end_date']
except Exception as exception:
error_message = "Retrieving latest fgt for id={} failed.".format(
id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def clear_initial_conditions(pool, flo2d_model):
"""
Clear existing initial conditions of a given flo2d model from database
:param pool: database connection pool
:param flo2d_model: string: flo2d model (e.g. FLO2D_250, FLO2D_150, FLO2D_30)
:return: affected row count if successful
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "DELETE FROM `grid_map_flo2d_initial_cond` " \
"WHERE `grid_id` like %s ESCAPE '$'"
row_count = cursor.execute(sql_statement,
"{}$_%".format(flo2d_model))
connection.commit()
return row_count
except Exception as exception:
connection.rollback()
error_message = "Deletion of {} initial conditions failed.".format(
flo2d_model)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_flo2d_cells_to_wrf_grid_mappings(pool, grid_interpolation,
flo2d_model):
"""
Retrieve flo2d to wrf stations mappings
:param pool: database connection pool
:param grid_interpolation: grid interpolation method
:param flo2d_model: string: flo2d model (e.g. FLO2D_250, FLO2D_150, FLO2D_30)
:return: dictionary with grid ids as keys and corresponding wrf station ids as values
"""
flo2d_grid_mappings = {}
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `grid_id`, `fcst` FROM `grid_map_flo2d_raincell` WHERE `grid_id` like %s ESCAPE '$'"
row_count = cursor.execute(
sql_statement, "flo2d$_{}$_{}$_%".format(
'$_'.join(flo2d_model.split('_')[1:]), grid_interpolation))
if row_count > 0:
results = cursor.fetchall()
for dict in results:
flo2d_grid_mappings[dict.get("grid_id")] = dict.get("fcst")
return flo2d_grid_mappings
else:
return None
except Exception as exception:
error_message = "Retrieving flo2d cells to obs grid mappings failed"
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_variable_id(pool, variable) -> str:
"""
Retrieve Variable id
:param pool: database connection pool
:param variable:
:return: str: variable id if variable exists in the db, else None
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `id` FROM `variable` WHERE `variable`=%s"
row_count = cursor.execute(sql_statement, variable)
if row_count > 0:
return cursor.fetchone()['id']
else:
return None
except Exception as exception:
error_message = "Retrieving variable id: variable={} failed.".format(
variable)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_description(pool, id_):
"""
Retrieve station description for a given station id
:param pool:
:param id_: station id
:return:
"""
description = {}
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `description` FROM `station` WHERE `id`=%s"
row_count = cursor.execute(sql_statement, id_)
if row_count > 0:
result = cursor.fetchone()['description']
if result is not None:
description = json.loads(result, object_pairs_hook=collections.OrderedDict)
return description
except Exception as exception:
error_message = "Retrieving station description for id={} failed.".format(id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def add_variable(pool, variable):
"""
Insert variables into the database
:param pool: database connection pool
:param variable: string
:return: True if the variable has been added to the "Variable" table of the database, else False
"""
connection = pool.connection()
try:
if get_variable_id(pool=pool, variable=variable) is None:
with connection.cursor() as cursor:
sql_statement = "INSERT INTO `variable` (`variable`) VALUES ( %s)"
row_count = cursor.execute(sql_statement, variable)
connection.commit()
return True if row_count > 0 else False
else:
logger.info(
"Variable with variable={} already exists in the database".
format(variable))
return False
except Exception as exception:
connection.rollback()
error_message = "Insertion of variable: variable={} failed".format(
variable)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def delete_unit(pool, unit, unit_type):
"""
Delete unit from Unit table, given unit and unit_type
:param pool: database connection pool
:param unit: string
:param unit_type: UnitType enum value. This value can be any of {Accumulative, Instantaneous, Mean} set
:return: True if the deletion was successful, else False
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "DELETE FROM `unit` WHERE `unit`=%s and `type`=%s"
row_count = cursor.execute(sql_statement, (unit, unit_type.value))
connection.commit()
if row_count > 0:
return True
else:
logger.info(
"There's no record of unit in the database with unit={} and unit_type={}"
.format(unit, unit_type))
return False
except Exception as exception:
connection.rollback()
error_message = "Deleting unit with unit={} and unit_type={} failed.".format(
unit, unit_type)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def delete_source(pool, model, version):
"""
Delete source from Source table, given model and version
:param pool: database connection pool
:param model: str
:param version: str
:return: True if the deletion was successful
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "DELETE FROM `source` WHERE `model`=%s and `version`=%s"
row_count = cursor.execute(sql_statement, (model, version))
connection.commit()
if row_count > 0:
return True
else:
logger.info(
"There's no record of source in the database with model={} and version={}"
.format(model, version))
return False
except Exception as exception:
connection.rollback()
error_message = "Deleting source with model={} and version={} failed.".format(
model, version)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_unit_id(pool, unit, unit_type) -> str:
"""
Retrieve Unit id
:param pool: database connection pool
:param unit:
:param unit_type: UnitType enum value. This value can be any of {Accumulative, Instantaneous, Mean} set
:return: str: unit id if unit exists in the db, else None
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `id` FROM `unit` WHERE `unit`=%s and `type`=%s"
row_count = cursor.execute(sql_statement, (unit, unit_type.value))
if row_count > 0:
return cursor.fetchone()['id']
else:
return None
except Exception as exception:
error_message = "Retrieving unit id: unit={} and unit_type={} failed.".format(
unit, unit_type)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_source_by_id(pool, id_):
"""
Retrieve source by id
:param pool: database connection pool
:param id_: source id
:return: Source if source exists in the database, else None
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT * FROM `source` WHERE `id`=%s"
row_count = cursor.execute(sql_statement, id_)
if row_count > 0:
return cursor.fetchone()
else:
return None
except Exception as exception:
error_message = "Retrieving source with source_id {} failed".format(
id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def add_unit(pool, unit, unit_type):
"""
Insert units into the database
:param pool: database connection pool
:param unit: string
:param unit_type: UnitType enum value. This value can be any of {Accumulative, Instantaneous, Mean} set
:return: True if the unit has been added to the "Unit" table of the database, else False
"""
connection = pool.connection()
try:
if get_unit_id(pool=pool, unit=unit, unit_type=unit_type) is None:
with connection.cursor() as cursor:
sql_statement = "INSERT INTO `unit` (`unit`, `type`) VALUES ( %s, %s)"
row_count = cursor.execute(sql_statement,
(unit, unit_type.value))
connection.commit()
return True if row_count > 0 else False
else:
logger.info(
"Unit with unit={}, unit_type={} already exists in the database"
.format(unit, unit_type))
return False
except Exception as exception:
connection.rollback()
error_message = "Insertion of unit: unit={}, unit_type={} failed".format(
unit, unit_type)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def add_source(pool, model, version, parameters=None):
"""
Insert sources into the database
:param pool: database connection pool
:param model: string
:param version: string
:param parameters: JSON
:return: True if the source has been added to the "Source' table of the database, else False
"""
connection = pool.connection()
try:
if get_source_id(pool=pool, model=model, version=version) is None:
with connection.cursor() as cursor:
sql_statement = "INSERT INTO `source` (`model`, `version`, `parameters`) VALUES ( %s, %s, %s)"
row_count = cursor.execute(
sql_statement, (model, version, json.dumps(parameters)))
connection.commit()
return True if row_count > 0 else False
else:
logger.info(
"Source with model={} and version={} already exists in the database"
.format(model, version))
return False
except Exception as exception:
connection.rollback()
error_message = "Insertion of source: model={}, version={} and parameters={} failed".format(
model, version, parameters)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_obs_to_d03_grid_mappings_for_rainfall(pool, grid_interpolation):
"""
Retrieve obs to d03 grid mappings
:param pool: database connection pool
:param grid_interpolation: grid interpolation method
:return: dictionary with grid ids as keys and corresponding obs1, obs2, obs3 station ids as a list
"""
obs_grid_mappings = {}
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `grid_id`,`d03_1`,`d03_2`,`d03_3`,`d03_4` FROM `grid_map_obs` " \
"WHERE `grid_id` like %s ESCAPE '$'"
row_count = cursor.execute(sql_statement, "rainfall$_%$_{}".format(grid_interpolation))
if row_count > 0:
results = cursor.fetchall()
for dict in results:
obs_grid_mappings[dict.get("grid_id")] = [dict.get("d03_1"), dict.get("d03_2"),
dict.get("d03_3"), dict.get("d03_4")]
return obs_grid_mappings
else:
return None
except Exception as exception:
error_message = "Retrieving flo2d to obs grid mappings failed"
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def update_hash_id(self, existing_id, new_id):
"""
Update hash id in run table
:param existing_id: existing hash id
:param new_id: newly generated hash id
:return: True if the update was successful, else raise DatabaseAdapterError
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "UPDATE `run` SET `id`=%s WHERE `id`=%s;"
cursor.execute(sql_statement, (new_id, existing_id))
connection.commit()
return True
except Exception as exception:
connection.rollback()
error_message = "Updating hash id {} to id={} failed.".format(
existing_id, new_id)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def insert_data(self, timeseries, upsert=False):
"""
Insert timeseries to Data table in the database
:param timeseries: list of [tms_id, time, value] lists
:param boolean upsert: If True, upsert existing values ON DUPLICATE KEY. Default is False.
Ref: 1). https://stackoverflow.com/a/14383794/1461060
2). https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/
:return: row count if insertion was successful, else raise DatabaseAdapterError
"""
row_count = 0
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
if upsert:
sql_statement = "INSERT INTO `data` (`id`, `time`, `value`) VALUES (%s, %s, %s) " \
"ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)"
else:
sql_statement = "INSERT INTO `data` (`id`, `time`, `value`) VALUES (%s, %s, %s)"
row_count = cursor.executemany(sql_statement, timeseries)
connection.commit()
return row_count
except Exception as exception:
connection.rollback()
error_message = "Data insertion to data table for tms id {}, upsert={} failed.".format(
timeseries[0][0], upsert)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_timeseries_end(self, id_):
"""
Retrieve timeseries by id
:param id_:
:return: last timestamp if id exists, else None
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT max(`time`) AS `time` FROM `data` WHERE `id`=%s ;"
rows = cursor.execute(sql_statement, id_)
if rows > 0:
return cursor.fetchone()['time']
else:
return None
except Exception as exception:
error_message = "Retrieving timeseries end for id {} failed.".format(
id_)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def delete_variable(pool, variable):
"""
Delete variable from Variable table, given variable name
:param pool: database connection pool
:param variable: string
:return: True if the deletion was successful, else False
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "DELETE FROM `variable` WHERE `variable`=%s"
row_count = cursor.execute(sql_statement, variable)
connection.commit()
if row_count > 0:
return True
else:
logger.info(
"There's no record of variable in the database with variable={}"
.format(variable))
return False
except Exception as exception:
connection.rollback()
error_message = "Deleting variable with variable={} failed.".format(
variable)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def get_timeseries_id(self, grid_id, method):
"""
Check whether a timeseries id exists in the database run table for a given grid_id and method
:param grid_id: grid id (e.g.: flo2d_250_954)
:param method: value interpolation method
:return: timeseries id if exist else raise DatabaseAdapterError
"""
connection = self.pool.connection()
try:
with connection.cursor() as cursor:
sql_statement = "SELECT `id` FROM `run` WHERE `grid_id`=%s AND `method`=%s;"
result = cursor.execute(sql_statement, (grid_id, method))
if result > 0:
return cursor.fetchone()['id']
else:
return None
except Exception as exception:
error_message = "Retrieving timeseries id for grid_id={} failed.".format(
grid_id)
logger.error(error_message)
traceback.print_exc()
raise exception
finally:
if connection is not None:
connection.close()
def execute_write_query(pool, query, params):
"""
:param pool: connection pool
:param query: sql query with wild cards
:param params: tuple, parameters need to be passed in to the sql query
:return:
"""
connection = pool.connection()
try:
with connection.cursor() as cursor:
cursor.execute(query, params)
connection.commit()
return True
except Exception as ex:
connection.rollback()
error_message = "Executing sql query {} with params {} failed".format(
query, params)
logger.error(error_message)
traceback.print_exc()
return False
finally:
if connection is not None:
connection.close()