def push_to_db(dataset): db = connect_db('MySQLdb') c = db.cursor() sql = "DROP TABLE IF EXISTS {}".format('new_small_ds1_tse_temporal_lookback4_balanced') c.execute(sql) sql = "CREATE TABLE IF NOT EXISTS `new_small_ds1_tse_temporal_lookback4_balanced` (`timestamp` datetime DEFAULT NULL,\ `epoch` bigint(20) DEFAULT NULL, `RCMPL_0` float DEFAULT NULL, `dt_RCMPL_0` float DEFAULT NULL,\ `n_stdev_0` float DEFAULT NULL, `msp_order_a_0` float DEFAULT NULL, `msp_order_b_0` float DEFAULT NULL,\ `sdp_client_cpu_0` float DEFAULT NULL, `sdp_client_mem_0` float DEFAULT NULL, `navapp_cpu_0` float DEFAULT NULL,\ `navapp_mem_0` float DEFAULT NULL, `RCMPL_1` float DEFAULT NULL, `dt_RCMPL_1` float DEFAULT NULL, `n_stdev_1` float DEFAULT NULL,\ `msp_order_a_1` float DEFAULT NULL, `msp_order_b_1` float DEFAULT NULL, `sdp_client_cpu_1` float DEFAULT NULL,\ `sdp_client_mem_1` float DEFAULT NULL, `navapp_cpu_1` float DEFAULT NULL, `navapp_mem_1` float DEFAULT NULL,\ `RCMPL_2` float DEFAULT NULL, `dt_RCMPL_2` float DEFAULT NULL, `n_stdev_2` float DEFAULT NULL,\ `msp_order_a_2` float DEFAULT NULL, `msp_order_b_2` float DEFAULT NULL, `sdp_client_cpu_2` float DEFAULT NULL,\ `sdp_client_mem_2` float DEFAULT NULL, `navapp_cpu_2` float DEFAULT NULL, `navapp_mem_2` float DEFAULT NULL,\ `RCMPL_3` float DEFAULT NULL, `dt_RCMPL_3` float DEFAULT NULL, `n_stdev_3` float DEFAULT NULL,\ `msp_order_a_3` float DEFAULT NULL, `msp_order_b_3` float DEFAULT NULL, `sdp_client_cpu_3` float DEFAULT NULL,\ `sdp_client_mem_3` float DEFAULT NULL, `navapp_cpu_3` float DEFAULT NULL, `navapp_mem_3` float DEFAULT NULL,\ `RCMPL_4` float DEFAULT NULL, `dt_RCMPL_4` float DEFAULT NULL, `n_stdev_4` float DEFAULT NULL,\ `msp_order_a_4` float DEFAULT NULL, `msp_order_b_4` float DEFAULT NULL, `sdp_client_cpu_4` float DEFAULT NULL,\ `sdp_client_mem_4` float DEFAULT NULL, `navapp_cpu_4` float DEFAULT NULL, `navapp_mem_4` float DEFAULT NULL,\ `tse` int(11) DEFAULT NULL, `error` int(11) DEFAULT NULL, `e_5` int(11) DEFAULT NULL, `e_10` int(11) DEFAULT NULL,\ `e_15` int(11) DEFAULT NULL, `e_20` int(11) DEFAULT NULL, `e_25` int(11) DEFAULT NULL, `e_30` int(11) DEFAULT NULL\ ) ENGINE=InnoDB DEFAULT CHARSET=latin1;" c.execute(sql) file = write_temp_file(dataset) sql = "LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} FIELDS TERMINATED BY ','".format(file, 'new_small_ds1_tse_temporal_lookback4_balanced') c.execute(sql) db.commit()
def remove_points(table): f = open('output.txt', 'a') #sys.stdout = f error = {} start_time = '' end_time = '' ts_list = [] sql = "SELECT timestamp, error from {}".format(table) print(sql) db = connect_db() c = db.cursor() c.execute(sql) rows = [list(i) for i in list(c.fetchall())] # print(type(rows[0][0])) indeces = [] for i in range(len(rows)): if rows[i][1] == 1: indeces.append(i) error[rows[i][0]] = rows[i][1] #print(sorted(error.items())) error_sorted = collections.OrderedDict(sorted(error.items())) for key, value in error_sorted.iteritems(): ts_list.append(key) print(ts_list) dataset = get_data(db, table) for i in range(len(ts_list)-1): j = i+1 timeDiff = ts_list[j] - ts_list[i] # print("Time before next error occurs : {} days : {} hours : {} minutes".format(timeDiff.days, # timeDiff.seconds/3600, (timeDiff.seconds/60)%60)) hours = timeDiff.seconds/3600 if hours >= 7: print("Time before next error occurs : {} days : {} hours : {} minutes".format(timeDiff.days, timeDiff.seconds/3600, (timeDiff.seconds/60)%60)) print("Next error occurs at : {} and today's date : {}".format(ts_list[j], ts_list[i].date())) print("Conserving 6hr of data before an error occurs, we can remove {} hours of data points".format(timeDiff - datetime.timedelta(hours=6))) print("\n") # choice = raw_input("Enter choice of method (1/2)") choice = '1' if choice == '1': dataset = method1(ts_list[i], ts_list[j], dataset) print("Final length of dataset is : ",len(dataset)) table = 'balanced_manual_new_small_ds1_tse_temporal_lookback4_m1_29_04' elif choice == '2': dataset = method2(ts_list[i], dataset) print("Final length of dataset is : ",len(dataset)) table = 'balanced_manual_new_small_ds1_tse_temporal_lookback4_method2' print type(dataset) ll_dataset = dict_to_list(dataset) ts = [] for i in range(len(ll_dataset)): ts.append(ll_dataset[i][0].date()) print "Length of ts list :",len(set(ts)) push_to_db(ll_dataset, table) ds = list(set(ts)) start(table, ds)
def get_resp_db(ts, x): db = connect_db() c = db.cursor() table = 'new_small_ds1_tse_temporal_lookback4' print table, ts sql = "SELECT e_{0} FROM {1} WHERE timestamp >= '{2}'".format(str(5*x), table, ts) print sql c.execute(sql) print ts, c.fetchone() return c.fetchone()[0]
def get_value(date, shift_value, col_name,table): db = connect_db() end_time = date e_time = parser.parse(end_time) s_time = e_time - datetime.timedelta(minutes=5*shift_value) start_time = "'"+s_time.strftime("%Y-%m-%d %H:%M:%S")+"'" end_time = "'"+e_time.strftime("%Y-%m-%d %H:%M:%S")+"'" sql = 'SELECT {} FROM {} WHERE `timestamp` >= {} AND `timestamp` < {}'.format(col_name, table, start_time, end_time) c = db.cursor() c.execute(sql) values = c.fetchall() return values
def push_to_db(dataset, table): db = connect_db('MySQLdb') c = db.cursor() sql = "DROP TABLE IF EXISTS '{}'".format(table) print sql print "Table name", table c.execute("DROP TABLE IF EXISTS `{}`".format(table)) print "Table doesn't exost. Creating new table" sql = "CREATE TABLE IF NOT EXISTS `{}` (`timestamp` datetime DEFAULT NULL,\ `epoch` bigint(20) DEFAULT NULL, `RCMPL_0` float DEFAULT NULL, `dt_RCMPL_0` float DEFAULT NULL,\ `n_stdev_0` float DEFAULT NULL, `msp_order_a_0` float DEFAULT NULL, `msp_order_b_0` float DEFAULT NULL,\ `sdp_client_cpu_0` float DEFAULT NULL, `sdp_client_mem_0` float DEFAULT NULL, `navapp_cpu_0` float DEFAULT NULL,\ `navapp_mem_0` float DEFAULT NULL, `RCMPL_1` float DEFAULT NULL, `dt_RCMPL_1` float DEFAULT NULL, `n_stdev_1` float DEFAULT NULL,\ `msp_order_a_1` float DEFAULT NULL, `msp_order_b_1` float DEFAULT NULL, `sdp_client_cpu_1` float DEFAULT NULL,\ `sdp_client_mem_1` float DEFAULT NULL, `navapp_cpu_1` float DEFAULT NULL, `navapp_mem_1` float DEFAULT NULL,\ `RCMPL_2` float DEFAULT NULL, `dt_RCMPL_2` float DEFAULT NULL, `n_stdev_2` float DEFAULT NULL,\ `msp_order_a_2` float DEFAULT NULL, `msp_order_b_2` float DEFAULT NULL, `sdp_client_cpu_2` float DEFAULT NULL,\ `sdp_client_mem_2` float DEFAULT NULL, `navapp_cpu_2` float DEFAULT NULL, `navapp_mem_2` float DEFAULT NULL,\ `RCMPL_3` float DEFAULT NULL, `dt_RCMPL_3` float DEFAULT NULL, `n_stdev_3` float DEFAULT NULL,\ `msp_order_a_3` float DEFAULT NULL, `msp_order_b_3` float DEFAULT NULL, `sdp_client_cpu_3` float DEFAULT NULL,\ `sdp_client_mem_3` float DEFAULT NULL, `navapp_cpu_3` float DEFAULT NULL, `navapp_mem_3` float DEFAULT NULL,\ `RCMPL_4` float DEFAULT NULL, `dt_RCMPL_4` float DEFAULT NULL, `n_stdev_4` float DEFAULT NULL,\ `msp_order_a_4` float DEFAULT NULL, `msp_order_b_4` float DEFAULT NULL, `sdp_client_cpu_4` float DEFAULT NULL,\ `sdp_client_mem_4` float DEFAULT NULL, `navapp_cpu_4` float DEFAULT NULL, `navapp_mem_4` float DEFAULT NULL,\ `tse` int(11) DEFAULT NULL, `error` int(11) DEFAULT NULL, `e_5` int(11) DEFAULT NULL, `e_10` int(11) DEFAULT NULL,\ `e_15` int(11) DEFAULT NULL, `e_20` int(11) DEFAULT NULL, `e_25` int(11) DEFAULT NULL, `e_30` int(11) DEFAULT NULL\ ) ENGINE=InnoDB DEFAULT CHARSET=latin1;".format(table) print sql c.execute(sql) fname = write_temp_file(dataset) print "File name",fname print "Table name", table sql = "LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} FIELDS TERMINATED BY ',';".format(fname, table) print sql c.execute(sql) db.commit() if table == 'new_small_ds1_tse_temporal_lookback4_balanced': os.remove(file)