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)
