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()
def read_netcdf_file(pool, rainc_net_cdf_file_path, rainnc_net_cdf_file_path, source_id, variable_id, unit_id, tms_meta, fgt): """ :param pool: database connection pool :param rainc_net_cdf_file_path: :param rainnc_net_cdf_file_path: :param source_id: :param variable_id: :param unit_id: :param tms_meta: :return: rainc_unit_info: mm lat_unit_info: degree_north time_unit_info: minutes since 2019-04-02T18:00:00 """ if not os.path.exists(rainc_net_cdf_file_path): logger.warning('no rainc netcdf') print('no rainc netcdf') elif not os.path.exists(rainnc_net_cdf_file_path): logger.warning('no rainnc netcdf') print('no rainnc netcdf') else: """ RAINC netcdf data extraction """ nc_fid = Dataset(rainc_net_cdf_file_path, mode='r') time_unit_info = nc_fid.variables['XTIME'].units time_unit_info_list = time_unit_info.split(' ') lats = nc_fid.variables['XLAT'][0, :, 0] lons = nc_fid.variables['XLONG'][0, 0, :] lon_min = lons[0].item() lat_min = lats[0].item() lon_max = lons[-1].item() lat_max = lats[-1].item() print('[lon_min, lat_min, lon_max, lat_max] :', [lon_min, lat_min, lon_max, lat_max]) lat_inds = np.where((lats >= lat_min) & (lats <= lat_max)) lon_inds = np.where((lons >= lon_min) & (lons <= lon_max)) rainc = nc_fid.variables['RAINC'][:, lat_inds[0], lon_inds[0]] """ RAINNC netcdf data extraction """ nnc_fid = Dataset(rainnc_net_cdf_file_path, mode='r') rainnc = nnc_fid.variables['RAINNC'][:, lat_inds[0], lon_inds[0]] # times = list(set(nc_fid.variables['XTIME'][:])) # set is used to remove duplicates times = nc_fid.variables['XTIME'][:] # ts_start_date = datetime.strptime(time_unit_info_list[2], '%Y-%m-%dT%H:%M:%S') # ts_end_date = datetime.strptime(time_unit_info_list[2], '%Y-%m-%dT%H:%M:%S') + timedelta( # minutes=float(sorted(set(times))[-2])) # start_date = datetime_utc_to_lk(ts_start_date, shift_mins=0).strftime('%Y-%m-%d %H:%M:%S') # end_date = datetime_utc_to_lk(ts_end_date, shift_mins=0).strftime('%Y-%m-%d %H:%M:%S') start_date = fgt end_date = fgt prcp = rainc + rainnc nc_fid.close() nnc_fid.close() diff = get_per_time_slot_values(prcp) width = len(lons) height = len(lats) ts = Timeseries(pool) for y in range(height): for x in range(width): lat = float('%.6f' % lats[y]) lon = float('%.6f' % lons[x]) tms_meta['latitude'] = str(lat) tms_meta['longitude'] = str(lon) station_prefix = '{}_{}'.format(lat, lon) station_id = wrf_v3_stations.get(station_prefix) if station_id is None: add_station(pool=pool, name=station_prefix, latitude=lat, longitude=lon, description="WRF point", station_type=StationEnum.WRF) tms_id = ts.get_timeseries_id_if_exists(tms_meta) logger.info("Existing timeseries id: {}".format(tms_id)) if tms_id is None: tms_id = ts.generate_timeseries_id(tms_meta) logger.info('HASH SHA256 created: {}'.format(tms_id)) run = (tms_id, tms_meta['sim_tag'], start_date, end_date, station_id, source_id, variable_id, unit_id) try: ts.insert_run(run) except Exception: logger.error( "Exception occurred while inserting run entry {}". format(run)) traceback.print_exc() else: # ts.update_latest_fgt(id_=tms_id, fgt=fgt) ts.update_start_date(id_=tms_id, start_date=fgt) # to run backward data_list = [] # generate timeseries for each station for i in range(len(diff)): ts_time = datetime.strptime( time_unit_info_list[2], '%Y-%m-%dT%H:%M:%S') + timedelta( minutes=times[i].item()) t = datetime_utc_to_lk(ts_time, shift_mins=0) data_list.append([ tms_id, t.strftime('%Y-%m-%d %H:%M:%S'), fgt, float(diff[i, y, x]) ]) push_rainfall_to_db(ts=ts, ts_data=data_list)