def __init__(self, interface, table_timewindow, summ_timewindow, log_table, summ_table, user, password, host, database, display_rate, display_size): self.db_manager = db_manager.DBManager(user, password, host, database) # self.data_processor = data_processor.DataProcessor() self.table_timewindow = table_timewindow self.summ_timewindow = summ_timewindow self.capture = pyshark.LiveCapture(interface=interface) self.log_table = log_table self.summ_table = summ_table self.monitor_file = "monitor.csv" open(self.monitor_file, "w").close() monitor_log = open(self.monitor_file, "a") writer = csv.writer(monitor_log, quoting=csv.QUOTE_MINIMAL, quotechar='"', delimiter=",", lineterminator="\r\n") writer.writerow( ["summ row count", "src:port", "dst:port", "pps", "delay"]) monitor_log.close() self.check_ts = time.time() self.display_rate = display_rate # seconds self.delay_count = 0 self.avg_delay = 0 self.total_delay = 0 self.display_size = display_size self.skip_count = 0
def setUp(self): """Because these tests involve writing to the database, we switch out the database file, using a dedicated 'unittest.db' database file. At the end of the test it switches back to the live database. """ self.set_test_database() self.dbm = db_manager.DBManager()
def save_orders(self, last_event): print(last_event) data = self.get_events(last_event) print(len(data['events'])) try: with db_manager.DBManager() as db: if db.check_base(): for event in data['events']: print('id: {},\noccurredAt: {}'.format( event['id'], event['occurredAt'])) time_stamp = event['occurredAt'] order = self.get_order_details( event['order']['checkoutForm']['id']) if not db.find_order_by_id(order['id']): address_id = db.find_delivery_address_id( order['delivery']['address']) db.add_buyer(order['buyer'], time_stamp) db.add_delivery_address( address_id, order['delivery']['address']) db.add_order( order['id'], order['buyer']['id'], address_id, order['status'], order['payment']['type'], order['delivery']['method']['name'], order['summary']['totalToPay']['amount'], order['messageToSeller'] if 'messageToSeller' in order else '', time_stamp) db.connect_address_buyer(order['buyer']['id'], address_id) print('len(lineItems): {}'.format( len(order['lineItems']))) for item in order['lineItems']: db.add_item(item['offer']['id'], item['offer']['name'], item['price']['amount']) db.connect_order_item(order['id'], item['offer']['id'], item['quantity']) db.print_values() return 1 except KeyError as e: print('KeyError: {}'.format(e)) return 0 return 0
def runTest(self): """ @summary - runs tests for VisualisationManager. @description - will run the unit tests for VisualisationManager @author - mah60 @param - None @return - None """ self.db = dbm.DBManager() self.vm = visualisation_manager.VisualisationManager(self.db) self.test_convert_rssi_to_distance() self.test_get_counter_labels() self.db.db_close()
def get_buyers(): limit = 100 with db_manager.DBManager() as db: if 'limit' in request.args and 'from' in request.args: limit = request.args['limit'] from_date = request.args['from'] return jsonify(db.get_buyers_from(from_date, limit)) elif 'limit' in request.args: limit = request.args['limit'] return jsonify(db.get_last_buyers(limit)) elif 'from' and 'to' in request.args: from_date = request.args['from'] to_date = request.args['to'] return jsonify(db.get_buyers_from_to(from_date, to_date)) elif 'from' in request.args: from_date = request.args['from'] return jsonify(db.get_buyers_from(from_date, limit)) return jsonify(db.get_last_buyers(limit))
#!/usr/bin/env python3 import db_manager DB_PATH = 'proxy_db.db' dbManager = db_manager.DBManager(DB_PATH) username = input("username: "******"password: ") dbManager.add_user(username, password) print('test DBManager.user_auth()...') assert dbManager.user_auth(username, password) print('pass') print() print('User added.')
import db_manager as dbm path = 'db/students.sqlite3' db = dbm.DBManager(path) if db.connect(): table_name = 'studenti' # pokud tabulka neexistuje, vytvor novou if not db.does_table_exist(table_name): db.create_table(table_name) print('table created')
import db_manager, commands from ConfigUtils import ConfigUtils from pb2py import CLServerCommand_pb2, LCServerCommand_pb2 import RequestSender db = db_manager.DBManager() conf = ConfigUtils() Database = conf.get('game_db') Table = "configs" def get_configs(serverid): m_conn = db.get_db(Database) m_conn.select_db(Database) m_cursor = m_conn.cursor() retlist = [] m_cursor.execute("select * from " + Table + " order by Name") for config in m_cursor.fetchall(): retlist.append((config[0], config[1])) m_cursor.close() m_conn.close() return retlist def edit_configs(request): m_conn = db.get_db(Database) m_cursor = m_conn.cursor() keys = request.getlist('keys[]') values = request.getlist('values[]') for field, value in request.items(): if field == 'keys[]':
def __init__(self, api_key, api_secret, passphrase, config): self.auth_client = cbpro.AuthenticatedClient(api_key, api_secret, passphrase) self.db_manager = db_manager.DBManager() self.accounts = [] self.coinbase_accounts = [] self.config = config
def main(): """ @summary - the application controller. @description - will controll what is happening at what time. e.g. install data from recordings text files or creating visualisations. @author - mah60 @param - None @return - None """ db = dbm.DBManager() rh = record_handler.RecordHandler(db) vm = visualisation_manager.VisualisationManager(db) #----------------------------------- #db.reset_tables() #file = "recordings_4.txt" #rh.import_txt_to_db(file) # create visualisations #mac = "0C:CB:85:25:D1:F1" #vm.device_rssi_over_time(mac, False, 4) #vm.device_rssi_over_time(mac, True, 4) #vm.total_count_over_time() #vm.count_over_time() #vm.rssi_to_distance_graph() #vm.get_dwelling_time_frequency(60) """ # test turnover bytes - for fasts speed #------------------------------------------------- turn_bytes = [10000, 100000, 250000, 500000, 750000, 1000000, 2000000, 4000000] for b in turn_bytes: for i in range (0, 3): db.reset_tables() print("-------------------------------") print("test " + str(i+1)+ ":") print("bytes limit: " + str(b)) before = int(round(time.time())) file = "recordings_3.txt" rh.import_txt_to_db(file, db, b) after = int(round(time.time())) stopwatch_time = after-before print("time (sec): " + str(stopwatch_time)) """ # get average rssi over time -> testing #----------------------------------- """" for i in range(1,11): file = "meter_test_" + str(i) + ".txt" db.reset_tables() rh.import_txt_to_db(file) mac = "0C:CB:85:25:D1:F1" average, item_amount = vm.get_average_rssi_over_time(mac) print("test " + str(i) + ": average value: " + str(average) + "at 1 meter for 5 mins\nWith " + str(item_amount) + " records") # results """ """ test 1: average value: -78.0at 1 meter for 5 mins With 1 records test 2: average value: -77.33333333333333at 1 meter for 5 mins With 3 records test 3: average value: -69.30674846625767at 1 meter for 5 mins With 163 records test 4: average value: -71.06629834254143at 1 meter for 5 mins With 362 records test 5: average value: -67.04672897196262at 1 meter for 5 mins With 214 records test 6: average value: -70.0863309352518at 1 meter for 5 mins With 278 records test 7: average value: -72.04761904761905at 1 meter for 5 mins With 462 records test 8: average value: -68.49095607235142at 1 meter for 5 mins With 387 records test 9: average value: -71.70263788968825at 1 meter for 5 mins With 417 records test 10: average value: -72.53793103448275at 1 meter for 5 mins With 290 records Average results determines rssi value to be 71.5 or 70 when there are more than 10 records """ db.db_close()
def save_data_to_db(self): manager = db_manager.DBManager() for row in self.data: manager.insert_subproducto(row) manager.close_connection()
def main(screen): parser = argparse.ArgumentParser() parser.add_argument("-pi_hosts", required=True, nargs="+", help="List of host addresses of each raspberry pi") parser.add_argument("-port", required=True, help="Port to communicate with each raspberry pi") parser.add_argument("-user", required=True, help="User of the database") parser.add_argument("-password", required=True, help="User's access password") parser.add_argument("-host", required=True, help="Host database is located on") parser.add_argument("-database", required=True, help="Name of the database") parser.add_argument("-summ_table", required=True, help="Table the stream is summarized to") args = parser.parse_args() hosts = args.pi_hosts port = args.port socks = [] for i in range(len(hosts)): try: sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) sock.connect((hosts[i], port)) socks.append(sock) except ConnectionRefusedError: print(f"Connection failed for {hosts[i]}:{port}") sys.exit(1) dbmanager = db_manager.DBManager(user=args.user, password=args.password, host=args.host, database=args.database) summ_table = args.summ_table max_buffer_size = 5120 while True: dbmanager.clear_summ_table(summ_table) for sock in socks: sock.send(b"1") for sock in socks: sock.recv(max_buffer_size) log_window = 1 pps_info = dbmanager.get_summ_pps_info(summ_table, log_window) sorted_pps_info = sorted(pps_info, key=lambda info: (-info[5], info[4])) screen.clear() screen.print_at(f"Window: {log_window} secs", 0, 1) col_info = [ "src:port", 21, "dst:port", 21, "last occurrence(secs)", 21, "pps", 8 ] # header follow by max width header_str = "" for i in range(0, len(col_info), 2): header_str += "{0:{1}s} | ".format(col_info[i], col_info[i + 1]) header_str = header_str[:-3] screen.print_at(header_str, 0, 2) screen.print_at("-" * len(header_str), 0, 3) data_offset = 4 for row in range(len(sorted_pps_info)): info = sorted_pps_info[row] data_str = "" data_str += "{0:{1}s} | ".format( info[0], col_info[1]) if info[1] is None else "{0:{1}s} | ".format( info[0] + ":" + str(info[1]), col_info[1]) data_str += "{0:{1}s} | ".format( info[2], col_info[3]) if info[3] is None else "{0:{1}s} | ".format( info[2] + ":" + str(info[3]), col_info[3]) for i in range(4, len(info)): j = (i - 2) * 2 + 1 data = "{:0.2f}".format( info[i]) if isinstance(info[i], int) or isinstance( info[i], float) else str(info[i]) data_str += "{0:{1}s} | ".format( data, col_info[j]) if i == 4 else "{0:{1}s} | ".format( data, col_info[j]) data_str = data_str[:-3] screen.print_at(data_str, 0, row + data_offset) screen.refresh() time.sleep(3)
#!/usr/bin/env python3 import os import db_manager TEST_DB_PATH = "test.db" TEST_USER = "******" TEST_PASSWD = "password" TEST_RULE_PATH = db_manager.RULE_PATH_PREFIX + TEST_USER + ".rule" dbManager = db_manager.DBManager(TEST_DB_PATH) def init_db(): """ initialize the database for test """ with open("init.sql") as init_file: init_script = init_file.read() dbManager.cursor.executescript(init_script) dbManager.conn.commit() def clear(): dbManager.conn.close() os.remove(TEST_DB_PATH) os.remove(TEST_RULE_PATH)
class DBInsert(ModuleClass): db_manager = db_manager.DBManager() log, res, err_log = None, None, None def __init__(self): super(DBInsert, self).__init__() self.db_manager.connect() self.log, self.res, self.err_log = LogManager.__call__().get_logger() def get_last_working_day(self, subject_code): query = """ select max(working_day) as last_working_day from %s """ % subject_code if not self.exist_table(subject_code): return '2000-01-01' result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE) if result[0] == None: return '2000-01-01' return str(result[0]) def check_first_input(self, subject_code, working_day): query = """ select date from %s where working_day =\'%s\' limit 1 """ % (subject_code, working_day) result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE) if result == None: return 'err' return result def check_last_input(self, subject_code, working_day): query = """ select date from %s where working_day =\'%s\' order by id desc limit 1 """ % (subject_code, working_day) result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE) if result == None: return 'err' return result #18로 되어있는 월물중에 working_day가 유효한지 판단 def check_subject_code(self, subject_code, working_day): query = """ select date from %s where working_day =\'%s\' """ % (subject_code, working_day) result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE) return result def get_subject_code(self): result_list = [] query = """ show tables like \"%%%s%%\" """ % str(datetime.date.today().year).replace('20', '') return self.db_manager.exec_query(query, fetch_type=FETCH_ALL) def insert_data(self, subject_code, data): try: query = """ insert into %s (date, price, volume, working_day) values """ % subject_code query = query + "(%s, %s, %s, %s)" if not self.exist_table(subject_code): self.create_table(subject_code) self.log.info("%s 종목 데이터 수[%s], 처음 데이터 %s" % (subject_code, len(data), data[0])) ''' while len(data) > 0: if len(data) > 1000: self.db_manager.exec_query_many(query, data[:1000]) data = data[1000:] else: self.db_manager.exec_query_many(query, data) break ''' self.db_manager.exec_query_many(query, data) except Exception as err: print(err) def exist_table(self, subject_code): query = "show tables like '%s'" % subject_code row = self.db_manager.exec_query(query, FETCH_ONE) if row is None: return False return True def drop_table(self, table_name): query = "drop table %s" % table_name return self.db_manager.exec_query(query) def create_table(self, table_name): query = """ CREATE TABLE `%s` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `date` TIMESTAMP NULL DEFAULT NULL, `price` FLOAT NULL DEFAULT NULL, `volume` SMALLINT(6) NULL DEFAULT NULL, `working_day` DATE NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `date` (`date`), INDEX `working_day` (`working_day`) ) """ return self.db_manager.exec_query(query % table_name) def get_table(self, table_name): query = 'select date, price, working_day from %s' % table_name return self.db_manager.exec_query(query, fetch_type=FETCH_ALL) def get_table_list(self, subject_symbol, start_date, end_date): query = "show tables where substr(Tables_in_haedong, 1, %s) = '%s'\ and substr(Tables_in_haedong, (select char_length(Tables_in_haedong))\ - 7, 8) between '%s' and '%s'" % ( len(subject_symbol), subject_symbol, start_date, end_date) return self.db_manager.exec_query(query, fetch_type=FETCH_ALL) def is_empty_table(self, table_name): query = """ select count(*) from %s """ % table_name if not self.exist_table(table_name): return True result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE) if result[0] > 0: return False return True def get_name(self): return str(self.__class__.__name__) def print_status(self): print(self.__getattribute__()) def check_nomal_data(self, table_name): query = 'select * from %s order by id desc limit 1' % table_name return self.db_manager.exec_query(query, fetch_type=FETCH_ONE) # 당일데이터 삭제 def del_err_data(self, table_name, working_day): query = "select date from %s where working_day =\'%s\'" % table_name, table_name return self.db_manager.exec_query(query)
def main(): parser = argparse.ArgumentParser() parser.add_argument("-master", required=True, nargs="+", help="Host address of the master node") parser.add_argument("-port", required=True, help="Port to communicate with the master node") parser.add_argument("-local_user", required=True, help="User of the local database") parser.add_argument("-local_password", required=True, help="User's local access password") parser.add_argument("-local_host", required=True, help="Host local database is located on") parser.add_argument("-local_database", required=True, help="Name of the local database") parser.add_argument("-remote_user", required=True, help="User of the remote database") parser.add_argument("-remote_password", required=True, help="User's remote access password") parser.add_argument("-remote_host", required=True, help="Host remote database is located on") parser.add_argument("-remote_database", required=True, help="Name of the remote database") parser.add_argument("-summ_table", required=True, help="Table the stream is summarized to") args = parser.parse_args() host = args.master port = args.port soc = socket.socket(socket.AF_INET, socket.SOCK_STREAM) soc.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1) print("Socket created!") try: soc.bind((host, port)) except: print(f"Bind failed. Error: {str(sys.exc_info())}") sys.exit(1) soc.listen(5) print("Socket now listening!") max_buffer_size = 5120 while True: connection, address = soc.accept() ip, port = str(address[0]), str(address[1]) print(f"Connection Accepted!") try: local_dbmanager = db_manager.DBManager( user=args.local_user, password=args.local_password, host=args.local_host, database=args.local_database, sql_log_file="sql_log_worker_server.SQL", csv_file="summary.csv") remote_dbmanager = db_manager.DBManager( user=args.remote_user, password=args.remote_password, host=args.remote_host, database=args.remote_database, sql_log_file="sql_log_worker_server.SQL", csv_file="summary.csv", clear_log=False) summ_table = args.summ_table receive_master(connection, local_dbmanager, remote_dbmanager, summ_table, max_buffer_size) except: connection.shutdown(socket.SHUT_RDWR) connection.close() print("Master disconnected!")
def main(): """ @summary - the application controller. @description - will controll what is happening at what time. e.g. install data from recordings text files or creating visualisations. @author - mah60 @param - None @return - None """ db = dbm.DBManager() rh = record_handler.RecordHandler(db) vm = visualisation_manager.VisualisationManager(db) # check tables exist if (db.is_tables()): if (cf.is_import): # check if should be importing data # reset tables db.reset_tables() # add data from record files for i, r in enumerate(cf.counter_records): r = cf.record_path + r print("importing file : " + r) before = int(round(time.time())) rh.import_txt_to_db(r, i, cf.import_byte_limit) after = int(round(time.time())) stopwatch_time = after - before print("time (sec): " + str(stopwatch_time)) # get visulisations if (cf.is_graphs): vm.rssi_to_distance_graph() vm.total_count_over_time(False) vm.total_count_over_time(True) vm.count_over_time(False) vm.count_over_time(True) for timeout in cf.dwellingtime_timeout: vm.get_dwelling_time_frequency(timeout, False) vm.get_dwelling_time_frequency(timeout, True) for mac in cf.rssi_distance_mac_address: vm.device_rssi_over_time(mac, False, cf.rssi_distance_graph_threshold, False) vm.device_rssi_over_time(mac, False, cf.rssi_distance_graph_threshold, True) vm.device_rssi_over_time(mac, True, cf.rssi_distance_graph_threshold, False) vm.device_rssi_over_time(mac, True, cf.rssi_distance_graph_threshold, True) vm.send_graphs_to_pdf() # test turnover bytes - for fasts speed #------------------------------------------------- if (cf.test_time_import_different_byte_size): turn_bytes = [ 10000, 100000, 250000, 500000, 750000, 1000000, 2000000, 4000000 ] for index, r in enumerate(cf.counter_records): r = cf.record_path + r for b in turn_bytes: for i in range(0, 3): db.reset_tables() print("-------------------------------") print("test " + str(i + 1) + ":") print("test file : " + r) print("bytes limit: " + str(b)) before = int(round(time.time())) rh.import_txt_to_db(r, index, b) after = int(round(time.time())) stopwatch_time = after - before print("time (sec): " + str(stopwatch_time)) # get average rssi over time -> testing #----------------------------------- """" for i in range(1,11): file = "meter_test_" + str(i) + ".txt" db.reset_tables() rh.import_txt_to_db(file) mac = "0C:CB:85:25:D1:F1" average, item_amount = vm.get_average_rssi_over_time(mac) print("test " + str(i) + ": average value: " + str(average) + "at 1 meter for 5 mins\nWith " + str(item_amount) + " records") # results """ """ test 1: average value: -78.0at 1 meter for 5 mins With 1 records test 2: average value: -77.33333333333333at 1 meter for 5 mins With 3 records test 3: average value: -69.30674846625767at 1 meter for 5 mins With 163 records test 4: average value: -71.06629834254143at 1 meter for 5 mins With 362 records test 5: average value: -67.04672897196262at 1 meter for 5 mins With 214 records test 6: average value: -70.0863309352518at 1 meter for 5 mins With 278 records test 7: average value: -72.04761904761905at 1 meter for 5 mins With 462 records test 8: average value: -68.49095607235142at 1 meter for 5 mins With 387 records test 9: average value: -71.70263788968825at 1 meter for 5 mins With 417 records test 10: average value: -72.53793103448275at 1 meter for 5 mins With 290 records Average results determines rssi value to be 71.5 or 70 when there are more than 10 records """ db.db_close()
def get_order_items(order_id): with db_manager.DBManager() as db: data = db.get_order_items(order_id) return jsonify(data) if data else abort(404)
def get_delivery_address(buyer_id): with db_manager.DBManager() as db: data = db.get_buyer_delivery_addresses(buyer_id) return jsonify(data) if data else abort(404)
def get_buyer_orders(buyer_id): with db_manager.DBManager() as db: response = db.get_buyer_orders(buyer_id) return jsonify(response) if response else abort(404)