def log(server, database, table, run_time_start, data_from_time, internal_table, run_result, comment, username=None, password=None): """ """ run_time_end = datetime.today().strftime('%Y-%m-%d %H:%M:%S') log1 = pd.DataFrame([[ run_time_start, run_time_end, data_from_time, internal_table, run_result, comment ]], columns=[ 'RunTimeStart', 'RunTimeEnd', 'DataFromTime', 'InternalTable', 'RunResult', 'Comment' ]) mssql.to_mssql(log1, server, database, table, username=username, password=password) return log1
def test_del_rows(): ## Write table mssql.to_mssql(df1, server, database, table) ## Read table beta1 = mssql.rd_sql(server, database, table) ## Delete parts mssql.del_mssql_table_rows(server, database, table, pk_df=df2) ## Test beta2 = mssql.rd_sql(server, database, table) beta3 = beta1.set_index(['pk1', 'pk2']) beta3.index.isin(df2.set_index(['pk1', 'pk2']).index) beta4 = beta3.loc[~beta3.index.isin(df2.set_index(['pk1', 'pk2']).index)].reset_index() ## Remove table engine = mssql.create_engine('mssql', server, database) conn = engine.connect() trans = conn.begin() conn.execute("IF OBJECT_ID(" + str([str(table)])[1:-1] + ", 'U') IS NOT NULL drop table " + table) trans.commit() conn.close() assert all(beta2 == beta4)
def create_site_mtype(server, database, site, ref_point, new_mtype): """ Function to create a new mtype for a specific site. A reference point number of an existing mtype of the same site must be used for creation. Run get_sites_mtypes to find a good reference point. Parameters ---------- server : str The server where the Hydrotel database lays. database : str The name of the Hydrotel database. site : str The site to create the new mtype on. ref_point : int The reference point from another mtype on the same site. new_mtype : str The new mtype name. Must be unique for the associated site. Returns ------- DataFrame New object and point values extracted by the get_sites_mtypes function. """ ## Checks site_mtypes = get_sites_mtypes(server, database, sites=site).reset_index() if not (site_mtypes.Point == ref_point).any(): raise ValueError('model_point must be a Point that exists within the mtypes of the site') if (site_mtypes.MType == new_mtype.lower()).any(): raise ValueError('new_name already exists as an mtype, please use a different name') ## Import object/point data point_val = rd_sql(server, database, points_tab, where_in={'Point': [ref_point]}) obj_val = rd_sql(server, database, objects_tab, where_in={'Object': point_val.Object.tolist()}) treeindex1 = int(rd_sql(server, database, stmt='select max(TreeIndex) from {tab} where Site = {site}'.format(tab=objects_tab, site=int(obj_val.Site))).iloc[0]) ## Assign new object data obj_val2 = obj_val.drop('Object', axis=1).copy() obj_val2['Name'] = new_mtype obj_val2['TreeIndex'] = treeindex1 + 1 to_mssql(obj_val2, server, database, objects_tab) ## Find out what the new object value is new_obj = int(rd_sql(server, database, objects_tab, where_in={'Site': obj_val.Site.tolist(), 'Name': [new_mtype]}).Object) ## Assign new point data point_val2 = point_val.drop('Point', axis=1).copy() point_val2['Name'] = new_mtype point_val2['Object'] = new_obj to_mssql(point_val2, server, database, points_tab) ## Return new values site_mtypes = get_sites_mtypes(server, database, sites=site, mtypes=new_mtype) return site_mtypes
def log(run_time_start, data_from_time, data_to_time, database_name, table_name, run_result, comment): """ """ run_time_end = pd.Timestamp.today() log1 = pd.DataFrame([[ run_time_start, run_time_end, data_from_time, data_to_time, database_name[:99], table_name[:99], run_result[:9], comment[:299] ]], columns=[ 'RunTimeStart', 'RunTimeEnd', 'DataFromTime', 'DataToTime', 'DatabaseName', 'TableName', 'RunResult', 'Comment' ]) mssql.to_mssql(log1, param['Output']['log_server'], param['Output']['log_database'], param['Output']['log_table']) return log1
def main(param): logging.basicConfig(filename='dhi-to-hydrotel.log', format='%(asctime)s: %(levelname)s: %(message)s', level=logging.INFO) run_time_start = pd.Timestamp.now() ###################################### ### Parameters base_path = param['Input']['base_path'] result_folders = param['Input']['result_folders'] file_index = param['Input']['file_index'] min_file_size = param['Input']['min_file_size'] #################################### ### Get the files logging.info('--Get the files') files = {} for fold in result_folders: files1 = [ files.update({ os.path.join(base_path, fold, f): int(os.path.getmtime(os.path.join(base_path, fold, f))) }) for f in os.listdir(os.path.join(base_path, fold)) if (f.endswith('.txt') and (os.path.getsize( os.path.join(base_path, fold, f)) > min_file_size)) ] if not files: logging.error('No files found...something is wrong') raise ValueError('No files found...something is wrong') logging.info('Compare to previous run') if os.path.isfile(os.path.join(base_path, file_index)): with open(os.path.join(base_path, file_index)) as file: old_files = json.load(file) else: logging.info('First run') old_files = {} new_files = {} for f in files: if f in old_files: if files[f] > old_files[f]: new_files.update({f: files[f]}) else: new_files.update({f: files[f]}) combo_files = old_files.copy() combo_files.update(new_files) if new_files: #################################### ### Parse txt files and save to Hydrotel logging.info('Saving new data') regex = re.compile('\d+') date_col = 'Time' for f in new_files: data1 = pd.read_table(f, skiprows=1, parse_dates=[date_col], infer_datetime_format=True) cols = data1.columns.copy() data_col = [c for c in cols if c != date_col][0] point_num = int(regex.findall(data_col)[0]) data2 = data1.rename(columns={ date_col: 'DT', data_col: 'SampleValue' }).copy() data2['DT'] = data2['DT'].dt.floor('5Min') data2 = data2[data2.DT >= (run_time_start - pd.DateOffset(hours=12))] data2['Point'] = point_num data2['Quality'] = param['Output']['quality_code'] data2['BypassValidation'] = 0 data2['BypassAlarms'] = 0 data2['BypassScaling'] = 0 data2['BypassTimeOffset'] = 0 data2['Priority'] = 3 try: mssql.to_mssql(data2, param['Output']['server'], param['Output']['database'], param['Output']['sample_table']) except Exception as err: logging.error(str(err)) raise ValueError(str(err)) ## Save new index with open(os.path.join(base_path, file_index), 'w') as outfile: json.dump(combo_files, outfile) else: logging.info('No new files found') logging.info('Success')
allo_site_trig = mssql.rd_sql(param['output']['server'], param['output']['database'], 'CrcAlloSite', ['CrcAlloSiteID', 'RecordNumber', 'AlloBlockID', 'SiteID'], where_in={'SiteType': ['LowFlow', 'Residual']}, username=param['output']['username'], password=param['output']['password']) trig_cond1 = mssql.rd_sql(param['output']['server'], param['output']['database'], 'LowFlowConditions', ['CrcAlloSiteID', 'BandNumber'], username=param['output']['username'], password=param['output']['password']) allo_site_trig1 = pd.merge(trig_cond1, allo_site_trig, on='CrcAlloSiteID') # Combine tables restr_ts2 = pd.merge(sites1, restr5, on='ExtSiteID').drop('ExtSiteID', axis=1) restr_ts3 = pd.merge(restr_ts2, allo_site_trig1, on=['RecordNumber', 'SiteID', 'BandNumber']).drop(['RecordNumber', 'SiteID', 'BandNumber', 'AlloBlockID'], axis=1).drop_duplicates(['CrcAlloSiteID', 'RestrDate']) restr_ts3.RestrDate = restr_ts3.RestrDate.dt.strftime('%Y-%m-%d') # Save results print('Save results') mssql.to_mssql(restr_ts3, param['output']['server'], param['output']['database'], table1, username=param['output']['username'], password=param['output']['password']) # new_restr_ts = mssql.update_from_difference(restr_ts3, param['output']['server'], param['output']['database'], 'TSLowFlowRestr', on=['CrcAlloSiteID', 'RestrDate'], mod_date_col='ModifiedDate') # Log log1 = util.log(param['output']['server'], param['output']['database'], 'log', run_time_start, last_date1, table1, 'pass', '{} rows updated'.format(len(restr_ts3)), username=param['output']['username'], password=param['output']['password']) else: # Log log1 = util.log(param['output']['server'], param['output']['database'], 'log', run_time_start, last_date1, table1, 'pass', 'Todays restrictions were already saved', username=param['output']['username'], password=param['output']['password']) ##################################### ### TSLowFlowSite print('--TSLowFlowSite') table1 = lowflow_site_ts_table ## Determine last restriction date run
############################### ### Log today2 = str(datetime.today().strftime('%Y-%m-%d %H:%M:%S')) today3 = str(today.strftime('%Y-%m-%d %H:%M:%S')) log1 = pd.DataFrame([[ today3, 'Freshwater Maps', 'pass', 'all good', str(map1.start_date), today2 ]], columns=[ 'Time', 'HydroTable', 'RunResult', 'Comment', 'FromTime', 'RunTimeEnd' ]) to_mssql(log1, param.hydro_server, param.hydro_database, 'ExtractionLog') except Exception as err: err1 = err print(err1) today2 = str(datetime.today().strftime('%Y-%m-%d %H:%M:%S')) log2 = pd.DataFrame([[ today2, 'Freshwater Maps', 'fail', str(err1)[:299], str(map1.start_date), today2 ]], columns=[ 'Time', 'HydroTable', 'RunResult', 'Comment', 'FromTime', 'RunTimeEnd' ]) to_mssql(log2, param.hydro_server, param.hydro_database, 'ExtractionLog')
combo2.rename(columns={'DateTime': 'DT'}, inplace=True) ## Nat flow nat_flow = combo2[['DT', 'nat_flow']].copy() nat_flow['Point'] = param['Output']['unmod_point'] nat_flow['Quality'] = param['Output']['quality_code'] nat_flow['BypassValidation'] = 0 nat_flow['BypassAlarms'] = 0 nat_flow['BypassScaling'] = 0 nat_flow['BypassTimeOffset'] = 0 nat_flow['Priority'] = 3 nat_flow.rename(columns={'nat_flow': 'SampleValue'}, inplace=True) mssql.to_mssql(nat_flow, param['Output']['hydrotel_server'], 'hydrotel', 'SampleBuf') str1 = '{det} data points added to {mtype} (Point {point})'.format( det=len(combo2), mtype=param['Input']['unmod_mtype'], point=param['Output']['unmod_point']) print(str1) # util.log(run_time_start, from_date, combo2.DT.max(), 'Hydrotel', 'SampleBuf', 'pass', '{det} data points added to {mtype} (Point {point})'.format(det=len(combo2), mtype=param['Input']['unmod_mtype'], point=param['Output']['unmod_point'])) ## Other flow other_flow = combo2[['DT', 'other']].copy() other_flow['Point'] = param['Output']['other_point'] other_flow['Quality'] = param['Output']['quality_code']
if isinstance(last_val1, pd.Timestamp): det1 = det1[det1.DateTime > last_val1].copy() ##################################### ### Save to Hydrotel and log result if not det1.empty: det1['Point'] = param['Output']['new_point'] det1['Quality'] = param['Output']['quality_code'] det1.rename(columns={ 'DateTime': 'DT', 'de-tided': 'SampleValue' }, inplace=True) mssql.to_mssql(det1, param['Output']['server'], param['Input']['database'], 'Samples') util.log( run_time_start, from_date, det1.DT.max(), 'Hydrotel', 'Samples', 'pass', '{det} data points added to {mtype} (Point {point})'.format( det=len(det1), mtype=param['Input']['new_mtype'], point=param['Output']['new_point'])) else: util.log(run_time_start, to_date, to_date, 'Hydrotel', 'Samples', 'pass', 'No data needed to be added') except Exception as err: err1 = err print(err1)
# Process the results site_summ1 = lf.site_summary_ts(str(last_date2), str(today1), only_active=only_active).reset_index() # Remove potential duplicate sites site_summ1.sort_values('MeasurementMethod', ascending=False, inplace=True) site_summ2 = site_summ1.drop_duplicates(['ExtSiteID', 'RestrDate']) # Save results print('Save results') mssql.to_mssql(site_summ2, param['output']['server'], param['output']['database'], table1, schema=schema1) # Log log1 = util.log(param['output']['server'], param['output']['database'], 'log', run_time_start, last_date1, table1, 'pass', '{} rows updated'.format(len(site_summ1))) else: # Log log1 = util.log(param['output']['server'], param['output']['database'], 'log', run_time_start, last_date1, table1, 'pass', 'Todays restrictions were already saved') ##################################### ### TSCrcBlockRestr
if not det1.empty: det1['Point'] = param['Output']['detided_point'] det1['Quality'] = param['Output']['quality_code'] det1['BypassValidation'] = 0 det1['BypassAlarms'] = 0 det1['BypassScaling'] = 0 det1['BypassTimeOffset'] = 0 det1['Priority'] = 3 det1.rename(columns={ 'DateTime': 'DT', 'de-tided': 'SampleValue' }, inplace=True) mssql.to_mssql(det1, param['Output']['hydrotel_server'], 'hydrotel', 'SampleBuf') str1 = '{det} data points added to {mtype} (Point {point})'.format( det=len(det1), mtype=param['Input']['detided_mtype'], point=param['Output']['detided_point']) print(str1) # util.log(run_time_start, from_date, det1.DT.max(), 'Hydrotel', 'SampleBuf', 'pass', '{det} data points added to {mtype} (Point {point})'.format(det=len(det1), mtype=param['Input']['detided_mtype'], point=param['Output']['detided_point'])) else: print('No data needed to be added') # util.log(run_time_start, to_date, to_date, 'Hydrotel', 'SampleBuf', 'pass', 'No data needed to be added') except Exception as err:
allo_site_trig = mssql.rd_sql(param['output']['server'], param['output']['database'], 'CrcAlloSite', ['CrcAlloSiteID', 'RecordNumber', 'AlloBlockID', 'SiteID'], where_in={'SiteType': ['LowFlow', 'Residual']}) trig_cond1 = mssql.rd_sql(param['output']['server'], param['output']['database'], 'LowFlowConditions', ['CrcAlloSiteID', 'BandNumber']) allo_site_trig1 = pd.merge(trig_cond1, allo_site_trig, on='CrcAlloSiteID') # Combine tables restr_ts2 = pd.merge(sites1, restr5, on='ExtSiteID').drop('ExtSiteID', axis=1) restr_ts3 = pd.merge(restr_ts2, allo_site_trig1, on=['RecordNumber', 'SiteID', 'BandNumber']).drop(['RecordNumber', 'SiteID', 'BandNumber', 'AlloBlockID'], axis=1).drop_duplicates(['CrcAlloSiteID', 'RestrDate']) restr_ts3.RestrDate = restr_ts3.RestrDate.dt.strftime('%Y-%m-%d') # Save results print('Save results') mssql.to_mssql(restr_ts3, param['output']['server'], param['output']['database'], table1) # new_restr_ts = mssql.update_from_difference(restr_ts3, param['output']['server'], param['output']['database'], 'TSLowFlowRestr', on=['CrcAlloSiteID', 'RestrDate'], mod_date_col='ModifiedDate') # Log log1 = util.log(param['output']['server'], param['output']['database'], 'log', run_time_start, last_date1, table1, 'pass', '{} rows updated'.format(len(restr_ts3))) else: # Log log1 = util.log(param['output']['server'], param['output']['database'], 'log', run_time_start, last_date1, table1, 'pass', 'Todays restrictions were already saved') ##################################### ### TSLowFlowSite print('--TSLowFlowSite') table1 = lowflow_site_ts_table ## Determine last restriction date run
only_active=only_active, username=param['misc']['lowflows']['username'], password=param['misc']['lowflows']['password']).reset_index() # Remove potential duplicate sites site_summ1.sort_values('MeasurementMethod', ascending=False, inplace=True) site_summ2 = site_summ1.drop_duplicates(['ExtSiteID', 'RestrDate']) # Save results print('Save results') mssql.to_mssql(site_summ2, param['output']['server'], param['output']['database'], table1, schema=schema1, username=param['output']['username'], password=param['output']['password']) # Log log1 = util.log(param['output']['server'], param['output']['database'], 'log', run_time_start, last_date1, table1, 'pass', '{} rows updated'.format(len(site_summ1)), username=param['output']['username'], password=param['output']['password'])