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()