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