def import_old_data():
# Connect to the database
pool = get_Pool(host=CURW_FCST_HOST,
user=CURW_FCST_USERNAME,
password=CURW_FCST_PASSWORD,
port=CURW_FCST_PORT,
db=CURW_FCST_DATABASE)
connection = pool.connection()
curw_fcst_new_to_old_hash_id_mapping = read_csv(
"curw_fcst_new_to_old_hash_id_mapping.csv")
TS = Timeseries(pool=pool)
try:
for hash_index in range(len(curw_fcst_new_to_old_hash_id_mapping)):
print("##### Hash index: ", hash_index, " #####")
fgt_list = []
# Extract fgts
with connection.cursor() as cursor1:
sql_statement = "select distinct `fgt` from `data_v3` where `id`=%s order by `fgt` desc;"
cursor1.execute(
sql_statement,
curw_fcst_new_to_old_hash_id_mapping[hash_index][1])
fgts = cursor1.fetchall()
for fgt in fgts:
fgt_list.append(fgt.get('fgt'))
for fgt in fgt_list:
timeseries = []
with connection.cursor() as cursor2:
sql_statement = "select * from `data_v3` where `id`=%s and `fgt`=%s;"
cursor2.execute(
sql_statement,
(curw_fcst_new_to_old_hash_id_mapping[hash_index][1],
fgt))
results = cursor2.fetchall()
for result in results:
timeseries.append([
curw_fcst_new_to_old_hash_id_mapping[hash_index]
[0],
result.get('time'),
result.get('fgt'),
result.get('value')
])
TS.insert_data(timeseries=timeseries, upsert=True)
TS.update_start_date(
id_=curw_fcst_new_to_old_hash_id_mapping[hash_index][0],
start_date=fgt)
except Exception as ex:
traceback.print_exc()
finally:
connection.close()
destroy_Pool(pool=pool)
print()
def save_forecast_timeseries_to_db(pool, timeseries, run_date, run_time,
tms_meta):
print('EXTRACTFLO2DWATERLEVEL:: save_forecast_timeseries >>', tms_meta)
# {
# 'tms_id' : '',
# 'sim_tag' : '',
# 'station_id' : '',
# 'source_id' : '',
# 'unit_id' : '',
# 'variable_id': ''
# }
date_time = datetime.strptime('%s %s' % (run_date, run_time),
COMMON_DATE_TIME_FORMAT)
forecast_timeseries = []
if 'utcOffset' in tms_meta: # If there is an offset, shift by offset before proceed
print('Shift by utcOffset:', tms_meta['utcOffset'].resolution)
# Convert date time with offset
date_time = date_time + tms_meta['utcOffset']
run_date = date_time.strftime('%Y-%m-%d')
run_time = date_time.strftime('%H:%M:%S')
for item in timeseries:
forecast_timeseries.append([
datetime.strptime(item[0], COMMON_DATE_TIME_FORMAT) +
tms_meta['utcOffset'], item[1]
])
else:
forecast_timeseries = timeseries
try:
TS = Timeseries(pool=pool)
tms_id = TS.get_timeseries_id_if_exists(meta_data=tms_meta)
if tms_id is None:
tms_id = TS.generate_timeseries_id(meta_data=tms_meta)
tms_meta['tms_id'] = tms_id
TS.insert_run(run_meta=tms_meta)
TS.update_start_date(id_=tms_id,
start_date=('%s %s' % (run_date, run_time)))
TS.insert_data(timeseries=forecast_timeseries,
tms_id=tms_id,
fgt=('%s %s' % (run_date, run_time)),
upsert=True)
TS.update_latest_fgt(id_=tms_id, fgt=('%s %s' % (run_date, run_time)))
except Exception:
logger.error(
"Exception occurred while pushing data to the curw_fcst database")
traceback.print_exc()
def save_forecast_timeseries_to_db(pool, output, mike_stations, fgt, tms_meta):
print('EXTRACT_MIKE_DISCHARGE:: save_forecast_timeseries >>', tms_meta)
# {
# 'tms_id' : '',
# 'sim_tag' : '',
# 'station_id' : '',
# 'source_id' : '',
# 'unit_id' : '',
# 'variable_id': ''
# }
# iterating the stations
for station in output.columns:
if station in mike_stations.keys():
ts = output[station].reset_index().values.tolist(
) # including index
tms_meta['latitude'] = str(mike_stations.get(station)[1])
tms_meta['longitude'] = str(mike_stations.get(station)[2])
tms_meta['station_id'] = mike_stations.get(station)[0]
try:
TS = Timeseries(pool=pool)
tms_id = TS.get_timeseries_id_if_exists(meta_data=tms_meta)
if tms_id is None:
tms_id = TS.generate_timeseries_id(meta_data=tms_meta)
tms_meta['tms_id'] = tms_id
TS.insert_run(run_meta=tms_meta)
TS.update_start_date(id_=tms_id, start_date=fgt)
TS.insert_data(timeseries=ts,
tms_id=tms_id,
fgt=fgt,
upsert=True)
TS.update_latest_fgt(id_=tms_id, fgt=fgt)
except Exception:
logger.error(
"Exception occurred while pushing data to the curw_fcst database"
)
traceback.print_exc()
else:
print("### {} not included in the database. ###".format(station))
def save_forecast_timeseries_to_db(pool, timeseries, run_date, run_time, opts,
flo2d_stations, fgt):
print('EXTRACTFLO2DWATERLEVEL:: save_forecast_timeseries >>', opts)
# {
# 'tms_id' : '',
# 'sim_tag' : '',
# 'station_id' : '',
# 'source_id' : '',
# 'unit_id' : '',
# 'variable_id': ''
# }
# Convert date time with offset
date_time = datetime.strptime('%s %s' % (run_date, run_time),
COMMON_DATE_TIME_FORMAT)
if 'utcOffset' in opts:
date_time = date_time + opts['utcOffset']
run_date = date_time.strftime('%Y-%m-%d')
run_time = date_time.strftime('%H:%M:%S')
# If there is an offset, shift by offset before proceed
forecast_timeseries = []
if 'utcOffset' in opts:
print('Shift by utcOffset:', opts['utcOffset'].resolution)
for item in timeseries:
forecast_timeseries.append([
datetime.strptime(item[0], COMMON_DATE_TIME_FORMAT) +
opts['utcOffset'], item[1]
])
forecast_timeseries = extractForecastTimeseries(
timeseries=forecast_timeseries,
extract_date=run_date,
extract_time=run_time)
else:
forecast_timeseries = extractForecastTimeseries(timeseries=timeseries,
extract_date=run_date,
extract_time=run_time)
elementNo = opts.get('elementNo')
tms_meta = opts.get('tms_meta')
tms_meta['latitude'] = str(flo2d_stations.get(elementNo)[1])
tms_meta['longitude'] = str(flo2d_stations.get(elementNo)[2])
tms_meta['station_id'] = flo2d_stations.get(elementNo)[0]
try:
TS = Timeseries(pool=pool)
tms_id = TS.get_timeseries_id_if_exists(meta_data=tms_meta)
if tms_id is None:
tms_id = TS.generate_timeseries_id(meta_data=tms_meta)
tms_meta['tms_id'] = tms_id
TS.insert_run(run_meta=tms_meta)
TS.update_start_date(id_=tms_id, start_date=fgt)
TS.insert_data(timeseries=forecast_timeseries,
tms_id=tms_id,
fgt=fgt,
upsert=True)
TS.update_latest_fgt(id_=tms_id, fgt=fgt)
except Exception:
logger.error(
"Exception occurred while pushing data to the curw_fcst database")
traceback.print_exc()