def get_probe():
db = mariadb.MySQLDatabase()
result = map(lambda item: list(item), db.select("SELECT `probe_name`, `ip_address`, `mac_address`,`last_updated`, `date_added`, DATEDIFF(`last_updated`,`date_added`) as uptime FROM PROBES WHERE `probe_status`=1 order by `probe_name` ASC;"))
option = {
'probe_name':'Probe Name',
'ip_address': 'IP Address',
'mac_address': 'MAC Address',
'last_updated': 'Last Activity',
'date_added': 'Installed Date',
'uptime': 'Up time day(s)'
}
restict = [
'uptime'
]
description = [field[0] for field in db.mycursor.description]
#### generate header table ####
probe_description = [option[field] for field in description]
result.insert(0, probe_description)
###############################
#### generate table max column ####
sql_max = "SELECT MAX(LENGTH(`{attribute}`)) FROM PROBES;"
output = map(lambda item: (db.select(sql_max.format(attribute=item))[0][0])*5 if item not in restict else len(option[item])*4, description)
return output, result
def statement1():
db = maria.MySQLDatabase()
db.insert('PROBES', [('hello20', '', '', '', 3, 'NOW()', 'NOW()', None)])
update = 'UPDATE PROBES SET collection_number="abc" where probe_id="hello20"'
db.mycursor.execute(update)
all_probe = db.select("SELECT distinct(collection_number) FROM PROBES")
list_services = map(lambda item: item[0],
db.select("SELECT `service_id` FROM SERVICES"))
list_run_svc = map(lambda item: (all_probe[0][0], item, 2), list_services)
# print list_run_svc
db.insert('RUNNING_SERVICES', list_run_svc)
list_destinations = map(
lambda item: item[0],
db.select("SELECT `destination_id` FROM DESTINATIONS"))
list_run_dest = map(lambda item: (all_probe[0][0], item, 1),
list_destinations)
# print list_run_dest
db.insert('RUNNING_DESTINATIONS', list_run_dest)
db.mycursor.execute("set FOREIGN_KEY_CHECKS=0;")
db.mycursor.execute("DELETE FROM PROBES WHERE probe_id='hello20'")
db.mycursor.execute("set FOREIGN_KEY_CHECKS=1;")
db.connection.commit()
def get_list_performance_service(service_type):
# mariadb_connection = mariadb.connect(user='******', password='******', database='project',
# host='192.168.254.31')
# cursor = mariadb_connection.cursor()
# if (service_type == 'speedtest'):
# cursor.execute(
# "select distinct performance_service.location from service join destination join performance_service join running_service on service.service_id = running_service.service_id and service.service_id = destination.service_id and destination.destination_id = performance_service.destination_id where service.service_name = '{}' and performance_service.download != 0;".format(
# service_type))
# else:
# cursor.execute(
# "select distinct destination.destination from service join destination join performance_service join running_service on service.service_id = running_service.service_id and service.service_id = destination.service_id and destination.destination_id = performance_service.destination_id where service.service_name = '{}' and performance_service.download != 0;".format(
# service_type))
# for i in cursor:
# if (i[0] != 'NULL'):
# list_location.insert(0, i[0])
list_location = []
db = mariadb.MySQLDatabase()
if (service_type == 'speedtest'):
db.mycursor.execute(
"select distinct performance_service.location from service join destination join performance_service join running_service on service.service_id = running_service.service_id and service.service_id = destination.service_id and destination.destination_id = performance_service.destination_id where service.service_name = '{}' and performance_service.download != 0;"
.format(service_type))
else:
db.mycursor.execute(
"select distinct destination.destination from service join destination join performance_service join running_service on service.service_id = running_service.service_id and service.service_id = destination.service_id and destination.destination_id = performance_service.destination_id where service.service_name = '{}' and performance_service.download != 0;"
.format(service_type))
for i in db.mycursor:
if (i[0] != 'NULL'):
list_location.insert(0, i[0])
return list_location
def insert_new_service():
form = cgi.FieldStorage()
service_name = form.getvalue('service_name')
file_name = form.getvalue('file_name')
try:
database = mariadb.MySQLDatabase()
data = [(None, service_name, file_name)]
database.insert(table='service', list_data=data)
condition = 'file_name = %s'
data = database.select('service',
condition,
'service_id',
file_name=file_name)
temp_data = []
all_probe = database.select('probe', None, 'probe_id')
for probe in all_probe:
probe_id = probe[0]
temp = (probe_id, data[0][0], '1')
temp_data.append(temp)
database.insert(table='running_service', list_data=temp_data)
print """<p class="title is-6">Insert {} Successfully!</p>""".format(
service_name)
print """<p class="title is-6"> service_id is '{}'</p>""".format(
data[0][0])
print """<a class="button is-link" href="../template.txt" download>Download file</a>"""
except Exeception as error:
print """<p class="title is-6" >{}</p>""".format(error)
def __init__(self, arg, environ):
self.db = database.MySQLDatabase()
self.sql = None
self.attribute = None
self.argument = arg
self.environ = environ
self.prepare_statement()
self.execute_sql(self.sql)
def __init__(self, environ, form_data, select):
self.database = mysql.MySQLDatabase()
self.environ = environ
self.form_data = form_data
self.table = self.prepare_table()
self.action(form_data, select)
# print self.sql
self.sql_action(self.sql)
def method_insert(self):
db = mariadb.MySQLDatabase()
self.sql = "INSERT INTO {table} VALUES (".format(
table=self.table) + ",".join(["%s"] * len(self.pattern)) + ")"
# query += " VALUES (" + ",".join(["%s"] * len(list_data[0])) + ")"
# print self.sql
db.mycursor.execute(self.sql, self.pattern)
db.connection.commit()
def get_anything(column_name, table, where=None):
db = mariadb.MySQLDatabase()
if where:
db.mycursor.execute("select {} from {} where {};".format(
column_name, table, where))
else:
db.mycursor.execute("select {} from {};".format(column_name, table))
result = db.mycursor.fetchone()
return result
def prototype(sql):
database = mariadb.MySQLDatabase()
database.mycursor.execute(sql)
row_headers = [x[0] for x in database.mycursor.description]
rv = database.mycursor.fetchall()
json_data = []
for result in rv:
json_data.append(dict(zip(row_headers, result)))
print json.dumps(json_data, default=json_serial)
def get_running_service():
database = mariadb.MySQLDatabase()
query_sql = """select running_service.probe_id, probe_name, running_service.service_id, service_name, running
from running_service
inner join service on running_service.service_id=service.service_id
inner join probe on running_service.probe_id=probe.probe_id;"""
database.mycursor.execute(query_sql)
return database.mycursor.fetchall()
def method_action(self):
db = mariadb.MySQLDatabase()
db.mycursor.execute(self.sql)
row_headers = [x[0] for x in db.mycursor.description]
rv = db.mycursor.fetchall()
json_data = []
for result in rv:
json_data.append(dict(zip(row_headers, result)))
print json.dumps(json_data, default=self.json_serial)
def statement2():
db = maria.MySQLDatabase()
db.insert('PROBES', [('hello48', '', '', '', 3, 'NOW()', 'NOW()', None)])
update1 = 'UPDATE PROBES SET collection_number="abc" where probe_id="hello48"'
db.mycursor.execute(update1)
db.connection.commit()
update2 = 'UPDATE PROBES SET collection_number="newcollection.bkk" where probe_id="hello48"'
db.mycursor.execute(update2)
db.connection.commit()
def __init__(self, table, file_name, logo):
self.tab = " "
self.master_font = "Times-Roman"
self.styles = getSampleStyleSheet()
self.table = table
self.file_name = file_name
self.logo = logo
self.db = mariadb.MySQLDatabase()
self.flowables = []
self.create_pdf()
def action(self, select, table, table_condition, condition):
self.database = mariadb.MySQLDatabase()
if table == 'service':
self.delete_all_service_reference(condition)
elif table == 'destination':
self.delete_all_destination(condition)
self.prepare_statement(select, table)
if condition != None and condition != '':
self.prepare_condition(condition)
self.method_action()
def __init__(self, arg, environ):
self.db = database.MySQLDatabase()
self.sql = None
self.attribute = None
self.argument = arg
self.environ = environ
self.prepare_statement()
try:
self.execute_sql(self.sql)
except Exception as e:
self.log_error(e)
def get_list_anything(column_name, table, where=None):
db = mariadb.MySQLDatabase()
if where:
db.mycursor.execute("select {} from {} where {};".format(
column_name, table, where))
else:
db.mycursor.execute("select {} from {};".format(column_name, table))
list_result = []
for i in db.mycursor:
list_result.insert(0, i)
return list_result
def update_running():
database = mariadb.MySQLDatabase()
form = cgi.FieldStorage()
probe_id = form.getvalue('probe_id')
service_id = form.getvalue('service_id')
status_service = form.getvalue('status_service')
update_sql = "UPDATE running_service SET running='{}' WHERE probe_id='{}' and service_id='{}' ".format(status_service, probe_id, service_id)
database.mycursor.execute(update_sql)
database.connection.commit()
print "Update Successfully"
def get_availability_result(probe_id, service_type, availability_item, day):
# mariadb_connection = mariadb.connect(user='******', password='******', database='project',
# host='192.168.254.31')
# cursor = mariadb_connection.cursor()
# cursor.execute(
# "select round(avg(availability_service.response_time), 2) from availability_service join destination join service join probe on availability_service.destination_id = destination.destination_id and availability_service.probe_id = probe.probe_id and destination.service_id = service.service_id where availability_service.probe_id = '{}' and service.service_name = '{}' and destination.destination = '{}' and availability_service.status = 0 and availability_service.time >= now() + interval - {} day and availability_service.time < now() + interval 0 day;".format(
# probe_id, service_type, availability_item, day))
# average_responsetime = cursor.fetchone()[0]
db = mariadb.MySQLDatabase()
db.mycursor.execute(
"select round(avg(availability_service.response_time), 2) from availability_service join destination join service join probe on availability_service.destination_id = destination.destination_id and availability_service.probe_id = probe.probe_id and destination.service_id = service.service_id where availability_service.probe_id = '{}' and service.service_name = '{}' and destination.destination = '{}' and availability_service.status = 0 and availability_service.time >= now() + interval - {} day and availability_service.time < now() + interval 0 day;"
.format(probe_id, service_type, availability_item, day))
average_responsetime = db.mycursor.fetchone()[0]
return average_responsetime
def insert_new_destination():
database = mariadb.MySQLDatabase()
form = cgi.FieldStorage()
form_dest = form.getvalue('destination')
form_port = form.getvalue('destination_port')
form_service = form.getvalue('TypeList')
if form_port is None:
form_port = None
data = [(None, form_service, form_dest, form_port)]
database.insert(table='destination', list_data=data)
print "Insert Successfully"
def update_destination():
database = mariadb.MySQLDatabase()
form = cgi.FieldStorage()
form_dest = form.getvalue('dest')
form_port = form.getvalue('port')
form_dest_id = form.getvalue('d_id')
if form_port is None or form_port == 'None':
form_port = 'NULL'
update_sql = "UPDATE destination SET destination='{}', destination_port={} WHERE destination_id='{}'".format(
form_dest, form_port, form_dest_id)
database.mycursor.execute(update_sql)
database.connection.commit()
print "Update Successfully!"
def delete_destination():
database = mariadb.MySQLDatabase()
form = cgi.FieldStorage()
del_id = form['del_id'].value
del_sql_availability = "DELETE FROM availability_service WHERE destination_id='{}';".format(
del_id)
del_sql_performance = "DELETE FROM performance_service WHERE destination_id='{}';".format(
del_id)
del_sql_destination = "DELETE FROM destination WHERE destination_id='{}';".format(
del_id)
database.mycursor.execute(del_sql_availability)
database.mycursor.execute(del_sql_performance)
database.mycursor.execute(del_sql_destination)
database.connection.commit()
print """1 row successfully deleted"""
def get_list_availability_service(service_type):
# mariadb_connection = mariadb.connect(user='******', password='******', database='project',
# host='192.168.254.31')
# cursor = mariadb_connection.cursor()
# cursor.execute(
# "select distinct destination.destination from destination join service join availability_service join running_service on service.service_id = running_service.service_id and destination.service_id = service.service_id and destination.destination_id = availability_service.destination_id where service.service_name = '{}' and availability_service.status = 0;".format(
# service_type))
# for i in cursor:
# if (i[0] != 'NULL'):
# list_destination.insert(0, i[0])
list_destination = []
db = mariadb.MySQLDatabase()
db.mycursor.execute(
"select distinct destination.destination from destination join service join availability_service join running_service on service.service_id = running_service.service_id and destination.service_id = service.service_id and destination.destination_id = availability_service.destination_id where service.service_name = '{}' and availability_service.status = 0;"
.format(service_type))
for i in db.mycursor:
if (i[0] != 'NULL'):
list_destination.insert(0, i[0])
return list_destination
def get_performance_result(probe_id, service_type, performance_item,
range_day):
# mariadb_connection = mariadb.connect(user='******', password='******', database='project',
# host='192.168.254.31')
# cursor = mariadb_connection.cursor()
# if (service_type == 'speedtest'):
# cursor.execute(
# "select performance_service.ping, performance_service.download, performance_service.upload, performance_service.time from performance_service join destination join service join probe on performance_service.destination_id = destination.destination_id and performance_service.probe_id = probe.probe_id and destination.service_id = service.service_id where probe.probe_id = '{}' and service.service_name = '{}' and performance_service.location = '{}' and performance_service.download != 0 and performance_service.time >= now() + interval - {} day and performance_service.time < now() + interval 0 day;".format(
# probe_id, service_type, performance_item, range_day))
# else:
# cursor.execute(
# "select performance_service.ping, performance_service.download, performance_service.upload, performance_service.time from performance_service join destination join service join probe on performance_service.destination_id = destination.destination_id and performance_service.probe_id = probe.probe_id and destination.service_id = service.service_id where probe.probe_id = '{}' and service.service_name = '{}' and destination.destination = '{}' and performance_service.download != 0 and performance_service.time >= now() + interval - {} day and performance_service.time < now() + interval 0 day;".format(
# probe_id, service_type, performance_item, range_day))
# for i in cursor:
# ping.insert(0, i[0])
# download.insert(0, i[1])
# upload.insert(0, i[2])
# time.insert(0, str(i[3]))
# return performance_item, ping, download, upload, time
performance_item = performance_item
ping = []
download = []
upload = []
time = []
db = mariadb.MySQLDatabase()
if (service_type == 'speedtest'):
db.mycursor.execute(
"select performance_service.ping, performance_service.download, performance_service.upload, performance_service.time from performance_service join destination join service join probe on performance_service.destination_id = destination.destination_id and performance_service.probe_id = probe.probe_id and destination.service_id = service.service_id where probe.probe_id = '{}' and service.service_name = '{}' and performance_service.location = '{}' and performance_service.download != 0 and performance_service.time >= now() + interval - {} day and performance_service.time < now() + interval 0 day;"
.format(probe_id, service_type, performance_item, range_day))
else:
db.mycursor.execute(
"select performance_service.ping, performance_service.download, performance_service.upload, performance_service.time from performance_service join destination join service join probe on performance_service.destination_id = destination.destination_id and performance_service.probe_id = probe.probe_id and destination.service_id = service.service_id where probe.probe_id = '{}' and service.service_name = '{}' and destination.destination = '{}' and performance_service.download != 0 and performance_service.time >= now() + interval - {} day and performance_service.time < now() + interval 0 day;"
.format(probe_id, service_type, performance_item, range_day))
for i in db.mycursor:
ping.insert(0, i[0])
download.insert(0, i[1])
upload.insert(0, i[2])
time.insert(0, str(i[3]))
return performance_item, ping, download, upload, time
def method_action(self):
db = mariadb.MySQLDatabase()
db.mycursor.execute(self.sql, self.tuple_insert)
db.connection.commit()
'rtt': None,
'download': None,
'upload': None,
'other': None,
'other_description': None,
'other_unit': None,
}
results = stdout.replace(' ', '').replace('\n', '').split(',')
map(lambda item: dict_result.update(
{item.split('=')[0]: item.split('=')[1] if 'none' not in item.lower() else None}), results)
pattern = (None, probe_id, service_id, start_date, destination_id, dict_result['status'],
dict_result['rtt'], dict_result['download'], dict_result['upload'], dict_result['other'],
dict_result['other_description'], dict_result['other_unit'])
result.append(pattern)
destination_id = 1
stdouts = ["status=1, rtt=0.128",
"status=1, rtt=21, download=100, upload= 50",
"status=1, rtt=4, upload=6",
"status=1, other=123456789, other_description=external_output, other_unit=sec"]
for i in stdouts:
parse_parameter(destination_id, i)
db = mariadb.MySQLDatabase()
db.insert('TESTRESULTS', result)
def sql_action(self, sql_syntax):
self.database = mysql.MySQLDatabase()
self.database.mycursor.execute(sql_syntax)
self.database.connection.commit()
def __init__(self, form):
self.database = mysql.MySQLDatabase()
self.prepare(form)
def __init__(self, environ, form_data, select):
self.database = mysql.MySQLDatabase()
service_id = self.insert_service(form_data)
self.insert_running_service(service_id)
def execute_sql(self, sql):
db = mysql.MySQLDatabase()
return db.select(sql)[0][0]
def method_update(self):
db = mariadb.MySQLDatabase()
db.mycursor.execute(self.sql)
db.connection.commit()
