def __init__(self): self.db_load = Load('../cfg/RcDb.json') # db_operator是pymysql库中pymysql.connect()的返回对象 self.db_operator = self.db_load.get_DB_operator() # db_cur与pymysql库中cursor用法完全一致 self.db_cur = self.db_load.get_DB_operator() self.table_record = 'operator_log' self.close = self.db_load.close()
def __init__(self): """ 在此类初始化时就已经自动连接目标数据库 """ self.db_load = Load('cfg/RcDb.json') # db_operator是pymysql库中pymysql.connect()的返回对象 self.db_operator = self.db_load.get_DB_operator() # db_cur与pymysql库中cursor用法完全一致 self.db_cur = self.db_load.get_DB_cur() self.table_items = 'Can_status'
class UserDb(object): def __init__(self): """ 在此类初始化时就已经自动连接目标数据库 """ self.db_load = Load('../cfg/RcDb.json') # db_operator是pymysql库中pymysql.connect()的返回对象 self.db_operator = self.db_load.get_DB_operator() # db_cur与pymysql库中cursor用法完全一致 self.db_cur = self.db_load.get_DB_cur() # 操作表名 self.table_items = 'user' """
class LogDb(object): """ 此类旨在记录管理人员对后台数据的操作记录 """ def __init__(self): self.db_load = Load('../cfg/RcDb.json') # db_operator是pymysql库中pymysql.connect()的返回对象 self.db_operator = self.db_load.get_DB_operator() # db_cur与pymysql库中cursor用法完全一致 self.db_cur = self.db_load.get_DB_operator() self.table_record = 'operator_log' self.close = self.db_load.close() def get_log_date(self, start_date, days): """ 获取一定时间内的操作记录 :param start_date: 起始日期 :param days: 天数 :return: 字典{'日期':{'name':'xxx','operator':'operator_text'}...} """ pass def get_log_user(self, user_name): """ 查询指定管理员操作记录 :param user_name: 查询用户名 :return: 字典{'日期':{'name':'xxx','operator':'operator_text'}...} """ pass def get_log_all(self): """ 获取所有操作日志 :return: 字典{'日期':{'name':'xxx','operator':'operator_text'}...} """ pass def add_log(self, operator_user, operator_action): """
class CanStatusDb(object): """ 1可回收垃圾 2其他垃圾 3有害垃圾 4厨余垃圾 """ def __init__(self): """ 在此类初始化时就已经自动连接目标数据库 """ self.db_load = Load('cfg/RcDb.json') # db_operator是pymysql库中pymysql.connect()的返回对象 self.db_operator = self.db_load.get_DB_operator() # db_cur与pymysql库中cursor用法完全一致 self.db_cur = self.db_load.get_DB_cur() self.table_items = 'Can_status' def Can_status_add(self, Can_ID: str, GK152_state: str, GK152_data: str, HCSR04_state: str, HCSR04_distance, LEDBuzzer_1: str, LEDBuzzer_2: str, LEDBuzzer_3: str, LEDBuzzer_4: str, LEDBuzzer_5: str, Time: str): ''' 向数据库中添加垃圾桶运行的状态 :param Can_ID: 垃圾桶编号 :param GK152_state: 红外对射状态 :param GK152_data: 红外对射工作状态 :param HCSR04_state: 超声波状态 :param HCSR04_distance: 超声波测出的距离 :param LEDBuzzer_1: 蜂鸣器1状态 :param LEDBuzzer_2: 蜂鸣器2 状态 :return: ''' Can_ID = 'IMX6_ENV_RBELONG_001' # sql = 'insert into Can_Records(Can_ID,GK152_state,GK152_data,HCSR04_state,HCSR04_distance,LEDBuzzer_1,LEDBuzzer_2,Time) values('"{0}"','"{1}"','"{2}"','"{3}"','"{4}"','"{5}"','"{6}"','"{7}"') '.format( # Can_ID, GK152_state, GK152_data, HCSR04_state, HCSR04_distance, LEDBuzzer_1, LEDBuzzer_2, Time) sql = 'insert into Can_status(Can_ID,GK152_state,GK152_data,HCSR04_state,HCSR04_distance,LEDBuzzer_1,LEDBuzzer_2,LEDBuzzer_3,LEDBuzzer_4,LEDBuzzer_5,Time) values("{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}","{8}","{9}","{10}") '.format( Can_ID, GK152_state, GK152_data, HCSR04_state, HCSR04_distance, LEDBuzzer_1, LEDBuzzer_2, LEDBuzzer_3, LEDBuzzer_4, LEDBuzzer_5, Time) self.db_cur.execute(sql) self.db_operator.commit() return '成功向数据库中添加信息' def Can_status_search(self, Can_ID: str): """ 通过设备读取到的设备ID 在数据库中进行搜索 并返回数据库中当前设备ID的最新一条记录 :param Can_ID: 设备号 :return: 返回当前数据中当前设备号的最新一条记录 """ result = {} sql = 'SELECT * FROM Can_status WHERE Can_ID = "{}"'.format(Can_ID) self.db_cur.execute(sql) search_results = self.db_cur.fetchall() res = Can_ID + "号垃圾桶工作一切正常;\n" if len(search_results) != 0: print(len(search_results)) result['Can_ID'] = search_results[len(search_results) - 1][0] result['GK152_state'] = search_results[len(search_results) - 1][1] result['GK152_data'] = search_results[len(search_results) - 1][2] result['HCSR04_state'] = search_results[len(search_results) - 1][3] result['HCSR04_distance'] = search_results[len(search_results) - 1][4] result['LEDBuzzer_1'] = search_results[len(search_results) - 1][5] result['LEDBuzzer_2'] = search_results[len(search_results) - 1][6] result['LEDBuzzer_3'] = search_results[len(search_results) - 1][7] result['LEDBuzzer_4'] = search_results[len(search_results) - 1][8] result['LEDBuzzer_5'] = search_results[len(search_results) - 1][9] result['Time'] = search_results[len(search_results) - 1][10] for key in result.keys(): if result[key] == "error": res = Can_ID + "号垃圾桶出现故障\n请联系管理员进行维修;\n" res = res + "上次检查时间:" + result['Time'] else: res = '不存在该设备ID的垃圾桶或该垃圾桶尚未有检查记录' return res def close(self): self.db_load.close()
class RecordsDb(object): """ 1可回收垃圾 2其他垃圾 3有害垃圾 4厨余垃圾 """ def __init__(self): """ 在此类初始化时就已经自动连接目标数据库 """ self.db_load = Load('cfg/RcDb.json') # db_operator是pymysql库中pymysql.connect()的返回对象 self.db_operator = self.db_load.get_DB_operator() # db_cur与pymysql库中cursor用法完全一致 self.db_cur = self.db_load.get_DB_cur() self.table_items = 'Can_Records' def records_add(self, Can_ID: str, Rubbish_Class: int, Time: str): """ 向数据库添加物品条目 :param Can_ID: 垃圾桶编号 :param Rubbish_Class: 此参数类型为字典为垃圾所属类别 :param Time:程序运行时的时间 :return: 添加条目的信息 """ Can_ID = 'IMX6_ENV_RBELONG_001' sql = 'insert into Can_Records(Can_ID,Rubbish_Class,Time) values("{0}",{1},"{2}") '.format(Can_ID, Rubbish_Class, Time) self.db_cur.execute(sql) self.db_operator.commit() return '成功向数据库中添加如下信息:{{Can_ID:{0},Rubbish_Class:{1},Time:{2}}}\n'.format(Can_ID, Rubbish_Class, Time) def records_search(self, Can_ID: str): """ 通过设备读取到的设备ID 在数据库中进行搜索 并返回数据库中当前设备ID的最新一条记录 :param Can_ID: 设备号 :return: 返回当前数据中当前设备号的最新一条记录 """ result = {} sql = 'SELECT * FROM Can_Records WHERE Can_ID = "{}"'.format(Can_ID) self.db_cur.execute(sql) search_results = self.db_cur.fetchall() if len(search_results) != 0: result['Can_ID'] = search_results[len(search_results) - 1][0] result['ClassID'] = int(search_results[len(search_results) - 1][1]) result['Time'] = search_results[len(search_results) - 1][2] else: return '不存在该设备ID的垃圾桶或该垃圾桶尚未有工作记录' rubbishclass = "" if result['ClassID'] == 1: rubbishclass = "可回收垃圾" elif result['ClassID'] == 2: rubbishclass = "其他垃圾" elif result['ClassID'] == 3: rubbishclass = "有害垃圾" elif result['ClassID'] == 4: rubbishclass = "厨余垃圾" res = Can_ID + "号设备:\n上一次工作结果为:" + rubbishclass + "\n上次工作时间为:" + result['Time'] return res def cal_same_rubbish_class(self, Rubbish_Class: int): """ :return: """ sql = 'SELECT * FROM Can_Records WHERE Rubbish_Class = {}'.format(Rubbish_Class) self.db_cur.execute(sql) search_results = self.db_cur.fetchall() result = str(len(search_results)) return result def cal_all_records(self): """ :return: 数据库中所有同类的信息 """ class1 = self.cal_same_rubbish_class(1) class2 = self.cal_same_rubbish_class(2) class3 = self.cal_same_rubbish_class(3) class4 = self.cal_same_rubbish_class(4) res = "当前数据库中:\n可回收垃圾的记录的数目为:" + class1 + " 条\n其它垃圾的记录的数目为:" + class2 + " 条\n有害垃圾的记录的数目为:" + class3 + " 条\n厨余垃圾的记录的数目为:" + class4 + " 条" return res def close(self): self.db_load.close()
class ItemDb(object): """ 1可回收垃圾 2其他垃圾 3有害垃圾 4厨余垃圾 """ def __init__(self): """ 在此类初始化时就已经自动连接目标数据库 """ self.db_load = Load('cfg/RcDb.json') # db_operator是pymysql库中pymysql.connect()的返回对象 self.db_operator = self.db_load.get_DB_operator() # db_cur与pymysql库中cursor用法完全一致 self.db_cur = self.db_load.get_DB_cur() self.table_items = 'items' def item_search_exact_ID(self, item_ID): """ 通过ID准确搜索 :param item_ID: 物品ID :return: 返回一个字典,包含该垃圾ID以及所属所有类别{'ID':1,'Name':xxx,'ClassID':1/2/3/4,} """ result = {} # sql = 'SELECT * FROM items WHERE ID = ' + str(item_ID) sql = 'SELECT * FROM items WHERE ID = {}'.format(item_ID) self.db_cur.execute(sql) search_results = self.db_cur.fetchall() if len(search_results) != 0: result['ID'] = search_results[0][0] result['Name'] = search_results[0][1] result['ClassID'] = int(search_results[0][2]) else: return {'ID': -1, 'Name': "NOT EXIST", 'ClassID': -1} return result def item_search_exact(self, item_name: str): """ 搜索数据库某个物品所属垃圾类别 :param item_name: 物品名称 :return: 返回一个字典,包含该垃圾ID以及所属所有类别{'ID':1,'Name':xxx,'ClassID':1/2/3/4,} """ result = {} sql = 'SELECT * FROM items WHERE Name = ' + '"' + item_name + '"' self.db_cur.execute(sql) search_results = self.db_cur.fetchall() if len(search_results) != 0: result['ID'] = search_results[0][0] result['Name'] = search_results[0][1] result['ClassID'] = int(search_results[0][2]) else: return {'ID': -1, 'Name': "NOT EXIST", 'ClassID': -1, } return result def items_search_vague(self, item_key: str): """ 搜索含有指定关键字条目 :param item_key: 该批物品的共同含有的字符(鸡蛋,鸡蛋壳,鸡蛋黄,鸡蛋清) :return: 所有符合搜索条件的物品条目格式{'item_ID':{"item_name":"xxx","CLASSID":1/2/3/4},....} """ result = {} sql = 'SELECT * FROM items WHERE Name LIKE "%' + item_key + '%"' self.db_cur.execute(sql) search_result = self.db_cur.fetchall() if len(search_result) != 0: result['items_num'] = len(search_result) for item in search_result: result[str(item[0])] = {"Name": item[1], 'CLassID': item[2]} else: return {'item_num': 0} return result def items_read_all(self, ClassID: str): """ 一次性读取数据库所有内容(为减小数据量,只返回ID,与物品名) :param ClassID: 需要获取的物品类别默认为0所有 :return: 字典形式,格式为{'item_ID':{"item_name":"xxx","CLASSID":1/2/3/4},....} """ result = {} sql = '' if ClassID == '0': sql = 'SELECT * FROM items' else: sql = 'SELECT * FROM items where ClassID_1 = "' + str(ClassID) + '"' self.db_cur.execute(sql) search_result = self.db_cur.fetchall() if len(search_result) != 0: result['items_num'] = len(search_result) for item in search_result: result[str(item[0])] = {"Name": item[1], 'CLassID': item[2]} else: return {'item_num': 0} return result def items_add(self, item_name: str, item_Class: dir()): """ 向数据库添加物品条目 此函数不面向客户使用,用于后台数据管理 :param item_name: 物品名称 :param item_Class: 此参数类型为字典为垃圾所属类别 :return: 添加条目的信息 """ pass def item_edit(self, ID): """ 编辑某个物品信息 此函数不面向客户使用,用于后台数据管理 :param ID: 物品ID :return:字典修改后的信息{'ID':1,'Name':xxx,'C1':true/false,'C2':true/false,'C3':true/false,'C4':true/false} """ pass def item_del(self, ID): """ 用于删除某一个物品条目 此函数不面向客户使用,用于后台数据管理 :param ID: 要删除的物品ID :return: 字典,被删除的物品信息{'ID':1,'Name':xxx,'C1':true/false,'C2':true/false,'C3':true/false,'C4':true/false} """ pass def add_operator_record(self, operator_user, operator_action): """ 记录后台数据的修改(只对后台管理对数据库的更改操作记录) :param operator_user: 操作管理员 :param operator_action: 具体操作内容 :return: 无 """ pass def close(self): self.db_load.close()