#!/usr/bin/env python import datetime import json from typing import List, Any import pymysql import time from sanic import Sanic from sanic.response import text, json from sanic_cors import CORS, cross_origin from sanic import response from sanic.response import file import math import base64 import logging from video_capture import LOG_LEVEL from configparser import ConfigParser config = ConfigParser() config.read(r'./config.ini',encoding='utf-8') #Mysql MYSQL_HOST = config['mysql']['MYSQL_HOST'] MYSQL_PORT = int(config['mysql']['MYSQL_PORT']) MYSQL_USER = config['mysql']['MYSQL_USER'] MYSQL_PASSWORD = config['mysql']['MYSQL_PASSWORD'] MYSQL_DATABASE = config['mysql']['MYSQL_DATABASE'] SONAR_DATABASE = config['mysql']['SONAR_DATABASE'] def logger_init(LOG_LEVEL): logger = logging.getLogger('mylogger.c') #set log level WARNING logger.setLevel(LOG_LEVEL) return logger logger = logger_init(LOG_LEVEL) app = Sanic(__name__) CORS(app) @app.route('/readReal', methods=['POST']) async def post_handler(request): # print(request) # print(request.json['dataB']) # print(request.json['dataE']) dataB = request.json["dataB"] + " 00:00:00" dataE = request.json["dataE"] + " 23:59:59" cameraid = request.json["camera"] specices = request.json["specices"] # print("this0") # print(dataB) # print(dataE) # print(cameraid) # print(specices) # print("this1") list1 = [] list2: List[Any] = [] list3 = [] list4 = [] # print("this2") try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=MYSQL_DATABASE) cursor = db.cursor() # print("read database0") #尺寸分布横坐标固定 size_section_num = 10 size_Xaxis_str = [] # print("read database3") size_Xaxis_str.append("<100") # print("read database0") size_Xaxis_str.append("100~150") size_Xaxis_str.append("150~200") size_Xaxis_str.append("200~250") size_Xaxis_str.append("250~300") size_Xaxis_str.append("300~350") size_Xaxis_str.append("350~400") size_Xaxis_str.append("400~450") size_Xaxis_str.append("450~500") size_Xaxis_str.append(">500") # print("read database1") size_Xaxis = [100,150,200,250,300,350,400,450,500] # 重量分布横坐标固定 # print("read database") weight_section_num = 11 weight_Xaxis_str = [] weight_Xaxis_str.append("<100") weight_Xaxis_str.append("100~200") weight_Xaxis_str.append("200~300") weight_Xaxis_str.append("300~400") weight_Xaxis_str.append("400~500") weight_Xaxis_str.append("500~600") weight_Xaxis_str.append("600~700") weight_Xaxis_str.append("700~800") weight_Xaxis_str.append("800~900") weight_Xaxis_str.append("900~1000") weight_Xaxis_str.append(">1000") weight_Xaxis = [100,200,300,400,500,600,700,800,900,1000] print("the sql2") ######**************************** list1：查询刷选条件，更新详情报表 ********************************** sql = "select id, DATE_FORMAT(datetime, '%Y-%m-%d %H:%i:%s') as datetime, binocular_id, fish_species, fish_" \ "size, fish_weight from binocular_data where datetime > " + "'" + dataB + "'" + " and datetime < " + "'" \ + dataE + "'" + "and binocular_id in (" + cameraid + ")" + 'and fish_species in (' + specices + ");" # print("the sql1 is: " + sql) count = cursor.execute(sql) # print("select the number of sql is") # print(count) results = cursor.fetchall() for row in results: dict = {} dict['id'] = row[0] dict['datetime'] = row[1] dict['binocular_id'] = row[2] dict['fish_species'] = row[3] dict['fish_size'] = row[4] dict['fish_weight'] = row[5] list1.append(dict) # print("the final dict is:") # print(dict) # #####**************************** 拆分有多少种鱼 ********************************** specicesList = str(specices).split(",") specicesNumber = len(specicesList) specicesListStr = [] for i in range(specicesNumber): str1 = specicesList[i].replace('"','') specicesListStr.append(str1.replace(" ","")) # #####**************************** list4：百分比重图表 ********************************** # 查询黑头鱼数量 percent_dict = {} sql = "select *from binocular_data where datetime > " + "'" + dataB + "'" + " and datetime < " + \ "'" + dataE + "'" + " and fish_species in ('许氏平鲉');" # print("the sql6 is: " + sql) percent_dict["许氏平鲉"] = cursor.execute(sql) # sql = "select *from binocular_data where datetime > " + "'" + dataB + "'" + " and datetime < " + \ # "'" + dataE + "'" + " and fish_species in ('鲈鱼');" # print("the sql6 is: " + sql) # percent_dict["鲈鱼"] = cursor.execute(sql) list4.append(percent_dict) # print("the percent graph list4 is :") # print(list4) # #####**************************** list2：尺寸分布图表 ********************************** # for j in range(specicesNumber):0 list2_single = [] #筛选各分段尺寸鱼数量 for i in range(0, size_section_num): if i == 0: sql = "select *from binocular_data where fish_size < " + str(size_Xaxis[i]) + " and datetime > " +\ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in (" + specicesList[0] + ");" elif i < (size_section_num - 1): sql = "select *from binocular_data where fish_size between " + str(size_Xaxis[i-1]) + " and " + \ str(size_Xaxis[i]) + " and datetime > " + "'" + dataB + "'" + " and datetime < " + \ "'" + dataE + "'" + " and binocular_id in (" + cameraid + ")" + " and fish_species in (" + \ specicesList[0] + ");" else: sql = "select *from binocular_data where fish_size > " + str(size_Xaxis[i-1]) + " and datetime > " + \ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in (" + specicesList[0] + ");" # print(sql) # print("the sql count is") # print(i) size_dict = {} if percent_dict[specicesListStr[0]] == 0: size_dict[size_Xaxis_str[i]] = 0 else: size_dict[size_Xaxis_str[i]] = round(cursor.execute(sql) * 100 / percent_dict[specicesListStr[0]],2) list2_single.append(size_dict) list2.append({specicesListStr[0]: list2_single}) # print(list2) # print("the size graph list2 is :") # print(list2) ######**************************** list3：重量分布图表 ********************************** # print("fish size over") #for j in range(specicesNumber): list3_single = [] i = 0 for i in range(0, weight_section_num): # print("the sql weight count is") # print(i) if i == 0: sql = "select *from binocular_data where fish_weight < " + str(weight_Xaxis[i]) + " and datetime > " +\ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in (" + specicesList[0] + ");" elif i < (weight_section_num - 1): sql = "select *from binocular_data where fish_weight between " + str(weight_Xaxis[i-1]) + " and " + \ str(weight_Xaxis[i]) + " and datetime > " + "'" + dataB + "'" + " and datetime < " + \ "'" + dataE + "'" + " and binocular_id in (" + cameraid + ")" + " and fish_species in (" + \ specicesList[0] + ");" else: sql = "select *from binocular_data where fish_weight > "+ str(weight_Xaxis[i-1]) + " and datetime > "+ \ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in (" + specicesList[0] + ");" # print(sql) weight_dict = {} # print("0") weight_value = cursor.execute(sql) # print(weight_value) if percent_dict[specicesListStr[0]] == 0: weight_dict[weight_Xaxis_str[i]] = 0 else: weight_dict[weight_Xaxis_str[i]] = round(cursor.execute(sql) * 100 / percent_dict[specicesListStr[0]],2) # print(weight_dict[weight_Xaxis_str[i]]) list3_single.append(weight_dict) # print("2") list3.append({specicesListStr[0]: list3_single}) # print("the weight graph list3 is :") # print(list3) ######**************************** list1-4数据整合成js ********************************** result_list = {"reports": list1, "size_graph": list2, "weight_graph": list3, "percent_graph": list4} # print("the result_list is:") # print(result_list) result_json = json(result_list) cursor.close() db.close() return result_json except Exception as e: #print(e) logger.warning(e) list1 = [] return json(list1) return False @app.route('/get_pic', methods=['POST']) async def post_handler(request): imageID = request.json["id"] #print("the image id is: "+str(imageID)) try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=MYSQL_DATABASE) cursor = db.cursor() # #####**************************** list1：查询刷选条件，更新详情报表 ********************************** sql = "select id, DATE_FORMAT(datetime, '%Y-%m-%d %H:%i:%s.%f') from binocular_data where id = " + str(imageID) + " ;" #print("the sql1 is: " + sql) count = cursor.execute(sql) #print("select the number of sql is") #print(count) results = cursor.fetchall() cursor.close() db.close() for row in results: datestr = row[1] #print("the final date str is:") #print(datestr) imageNameStr = datestr.replace(":","-") imageNameStr = imageNameStr.replace(" ","-") imageNameStr = imageNameStr[:21] #print(imageNameStr) #imagePath = '/home/sencott/binocular_project_1/image_path/'+imageNameStr+'.jpg' imagePath = '/data/image_path/'+imageNameStr+'.jpg' #print(imagePath) return await file(imagePath) except Exception as e: logger.warning(e) list1 = [] return json(list1) return False @app.route('/setSpecices', methods=['POST']) async def post_handler(request): imageID = request.json["id"] specices = request.json["specices"] #print(id) #print(specices) list1 = [] try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=MYSQL_DATABASE) cursor = db.cursor() # #####**************************** list1：查询刷选条件，更新统计报表 ********************************** sql = "update binocular_data set species_add = '"+ specices +"' WHERE id = "+ str(imageID) +";"; #print("the sql1 is: " + sql) count = cursor.execute(sql) db.commit() #print("the sql count is "+str(count)) dict = {} if count == 1: dict['state'] = "sucess" else: dict['state'] = "failed" #print(dict) cursor.close() db.close() list1.append(dict) #print(list1) listJson = json(list1) #print(listJson) return listJson except Exception as e: logger.warning(e) list1 = [] return json(list1) return False @app.route('/statisticsReport', methods=['POST']) async def post_handler(request): dataB = request.json["dataB"] dataE = request.json["dataE"] specices = request.json["specices"] #print(dataB) #print(dataE) #print(specices) list1 = [] try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=MYSQL_DATABASE) cursor = db.cursor() # #####**************************** list1：查询刷选条件，更新统计报表 ********************************** sql = "select id,DATE_FORMAT(datetime, '%Y-%m-%d') as datetime,fish_species,size_average," \ "size_median,weight_average,weight_median from binocular_dailyreport where " \ "datetime between " + "'" + dataB + "'" + " and " + "'" \ + dataE + "'" + 'and fish_species in (' + specices + ");" #print("the sql1 is: " + sql) count = cursor.execute(sql) #print("select the number of sql is") #print(count) results = cursor.fetchall() for row in results: dict = {} dict['id'] = row[0] dict['datetime'] = row[1] dict['fish_species'] = row[2] dict['size_average'] = row[3] dict['size_median'] = row[4] dict['weight_average'] = row[5] dict['weight_median'] = row[6] list1.append(dict) #print("the final dict is:") #print(dict) result_json = json(list1) cursor.close() db.close() return result_json except Exception as e: logger.warning(e) list1 = [] return json(list1) return False @app.route('/growTimeSave', methods=['POST']) async def post_handler(request): dataB = request.json["dataB"] dataE = request.json["dataE"] list1 = [] try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=SONAR_DATABASE) cursor = db.cursor() # #####**************************** list1：查询刷选条件，更新统计报表 ********************************** sql = "insert into growtimesave(growTime_begin,growTime_end)values(" + "'" + dataB + "'," + "" + "'" + dataE + "'" + ");" # print("the sql1 is: " + sql) cursor.execute(sql) db.commit() result_json = json(list1) cursor.close() db.close() return result_json except Exception as e: logger.warning(e) list1 = [] return json(list1) return False @app.route('/growTimeRead', methods=['POST']) async def post_handler(request): list1 = [] try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=SONAR_DATABASE) cursor = db.cursor() # #####**************************** list1：查询刷选条件，更新统计报表 ********************************** sql = "select id,DATE_FORMAT(growTime_begin,'%Y-%m-%d') as growTime_begin,DATE_FORMAT(growTime_end,'%Y-%m-%d') as growTime_end from growtimesave ORDER BY id desc LIMIT 1;" # print("the sql1 is: " + sql) count = cursor.execute(sql) # print("select the number of sql is") # print(count) results = cursor.fetchall() for row in results: dict = {} dict['id'] = row[0] dict['growTime_begin'] = row[1] dict['growTime_end'] = row[2] list1.append(dict) # print("the final dict is:") # print(dict) result_json = json(list1) cursor.close() db.close() return result_json except Exception as e: logger.warning(e) list1 = [] return json(list1) return False @app.route('/compassDataRead', methods=['POST']) async def post_handler(request): list1 = [] try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=SONAR_DATABASE) cursor = db.cursor() # #####**************************** list1：查询刷选条件，更新统计报表 ********************************** sql = "select *from sonar_sensor_data LIMIT 1;" # print("the sql1 is: " + sql) count = cursor.execute(sql) # print("select the number of sql is") # print(count) results = cursor.fetchall() for row in results: dict = {} dict['depth'] = row[0] dict['pitch'] = row[1] dict['roll'] = row[2] dict['heading'] = row[3] list1.append(dict) # print("the final dict is:") # print(dict) result_json = json(list1) cursor.close() db.close() return result_json except Exception as e: logger.warning(e) list1 = [] return json(list1) return False @app.route('/homePage', methods=['POST']) async def post_handler(request): dataB_growCycle = request.json["dataB_growCycle"] + " 00:00:00" dataE_growCycle = request.json["dataE_growCycle"] + " 23:59:59" dataB = request.json["dataB_distribute"] + " 00:00:00" dataE = request.json["dataE_distribute"] + " 23:59:59" cameraid = '"1","2","3","4","5"' specices = '"许氏平鲉"' # print(dataB) # print(dataE) list1 = [] #统计报表数据,鱼尺寸与重量生长曲线 list2 = [] #详情报表尺寸分布 list3 = [] #详情报表的重量分布 list4 = [] #种类分布 list5 = [] #鱼群数量变化曲线 try: db = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=MYSQL_DATABASE) cursor = db.cursor() sql = "SELECT MAX(datetime) FROM binocular_data;" count = cursor.execute(sql) results = cursor.fetchall()[0][0].strftime("%Y-%m-%d") dataB = results + " 00:00:00" dataE = results + " 23:59:59" # #####**************************** list1：查询刷选条件，更新统计报表 ********************************** sql = "select id,DATE_FORMAT(datetime, '%Y-%m-%d') as datetime,fish_species,size_average," \ "size_median,weight_average,weight_median from binocular_dailyreport where datetime > " + "'" + dataB_growCycle + "'" + " and datetime < " + \ "'" + dataE_growCycle + "'" + ";" #print("the sql1 is: " + sql) count = cursor.execute(sql) #print("select the number of sql is") #print(count) results = cursor.fetchall() for row in results: dict = {} dict['id'] = row[0] dict['datetime'] = row[1] dict['fish_species'] = row[2] dict['size_average'] = row[3] dict['size_median'] = row[4] dict['weight_average'] = row[5] dict['weight_median'] = row[6] list1.append(dict) # print("the final dict is:") # print(dict) # 尺寸分布横坐标固定 size_section_num = 10 size_Xaxis_str = [] # print("read database3") size_Xaxis_str.append("<100") # print("read database0") size_Xaxis_str.append("100~150") size_Xaxis_str.append("150~200") size_Xaxis_str.append("200~250") size_Xaxis_str.append("250~300") size_Xaxis_str.append("300~350") size_Xaxis_str.append("350~400") size_Xaxis_str.append("400~450") size_Xaxis_str.append("450~500") size_Xaxis_str.append(">500") # print("read database1") size_Xaxis = [100, 150, 200, 250, 300, 350, 400, 450, 500] # 重量分布横坐标固定 # print("read database") weight_section_num = 11 weight_Xaxis_str = [] weight_Xaxis_str.append("<100") weight_Xaxis_str.append("100~200") weight_Xaxis_str.append("200~300") weight_Xaxis_str.append("300~400") weight_Xaxis_str.append("400~500") weight_Xaxis_str.append("500~600") weight_Xaxis_str.append("600~700") weight_Xaxis_str.append("700~800") weight_Xaxis_str.append("800~900") weight_Xaxis_str.append("900~1000") weight_Xaxis_str.append(">1000") weight_Xaxis = [100, 200, 300, 400, 500, 600, 700, 800, 900, 1000] # print("the sql2") # #####**************************** list4：百分比重图表 ********************************** # 查询黑头鱼数量 percent_dict = {} sql = "select *from binocular_data where datetime > " + "'" + dataB + "'" + " and datetime < " + \ "'" + dataE + "'" + " and fish_species in ('许氏平鲉');" # print("the sql6 is: " + sql) percent_dict["许氏平鲉"] = cursor.execute(sql) # sql = "select *from binocular_data where datetime > " + "'" + dataB + "'" + " and datetime < " + \ # "'" + dataE + "'" + " and fish_species in ('鲈鱼');" # print("the sql6 is: " + sql) # percent_dict["鲈鱼"] = cursor.execute(sql) list4.append(percent_dict) # print("the percent graph list4 is :") # print(list4) # #####**************************** list2：尺寸分布图表 ********************************** # for j in range(specicesNumber):0 list2_single = [] # 筛选各分段尺寸鱼数量 for i in range(0, size_section_num): if i == 0: sql = "select *from binocular_data where fish_size < " + str(size_Xaxis[i]) + " and datetime > " + \ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in ('许氏平鲉');" elif i < (size_section_num - 1): sql = "select *from binocular_data where fish_size between " + str(size_Xaxis[i - 1]) + " and " + \ str(size_Xaxis[i]) + " and datetime > " + "'" + dataB + "'" + " and datetime < " + \ "'" + dataE + "'" + " and binocular_id in (" + cameraid + ")" + " and fish_species in " \ "('许氏平鲉');" else: sql = "select *from binocular_data where fish_size > " + str( size_Xaxis[i - 1]) + " and datetime > " + \ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in ('许氏平鲉');" # print(sql) # print("the sql count is") # print(i) size_dict = {} if percent_dict["许氏平鲉"]==0: size_dict[size_Xaxis_str[i]] = 0 else: size_dict[size_Xaxis_str[i]] = round(cursor.execute(sql) * 100 / percent_dict["许氏平鲉"], 2) list2_single.append(size_dict) list2.append({"许氏平鲉": list2_single}) # print(list2) # print("the size graph list2 is :") # print(list2) ######**************************** list3：重量分布图表 ********************************** # print("fish size over") # for j in range(specicesNumber): list3_single = [] i = 0 for i in range(0, weight_section_num): # print("the sql weight count is") # print(i) if i == 0: sql = "select *from binocular_data where fish_weight < " + str( weight_Xaxis[i]) + " and datetime > " + \ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in ('许氏平鲉');" elif i < (weight_section_num - 1): sql = "select *from binocular_data where fish_weight between " + str( weight_Xaxis[i - 1]) + " and " + \ str(weight_Xaxis[i]) + " and datetime > " + "'" + dataB + "'" + " and datetime < " + \ "'" + dataE + "'" + " and binocular_id in (" + cameraid + ")" + " and fish_species in " \ "('许氏平鲉');" else: sql = "select *from binocular_data where fish_weight > " + str( weight_Xaxis[i - 1]) + " and datetime > " + \ "'" + dataB + "'" + " and datetime < " + "'" + dataE + "'" + " and binocular_id in (" + \ cameraid + ")" + " and fish_species in ('许氏平鲉');" # print(sql) weight_dict = {} # print("0") weight_value = cursor.execute(sql) # print(weight_value) if percent_dict["许氏平鲉"] == 0: weight_dict[weight_Xaxis_str[i]] = 0 else: weight_dict[weight_Xaxis_str[i]] = round(cursor.execute(sql) * 100 / percent_dict["许氏平鲉"], 2) # print(weight_dict[weight_Xaxis_str[i]]) list3_single.append(weight_dict) # print("2") list3.append({"许氏平鲉": list3_single}) # print("the weight graph list3 is :") # print(list3) cursor.close() db.close() # print("database close") db2 = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=SONAR_DATABASE) # print("database open") cursor2 = db2.cursor() # print("sql") # #####**************************** list5：查询刷选条件，更新鱼群数量曲线 ********************************** sql5 = "select id,DATE_FORMAT(datetime, '%Y-%m-%d') as datetime,assess_total_number from fish_distribution_dailyReport " \ "where datetime > " + "'" + dataB_growCycle + "'" + " and datetime < " + "'" + dataE_growCycle + "';" # print(sql5) count = cursor2.execute(sql5) # print("select the number of sql is") # print(count) results = cursor2.fetchall() # print("sql work") for row in results: dict = {} dict['id'] = row[0] dict['datetime'] = row[1] dict['assess_total_number'] = row[2] list5.append(dict) # print(list5) ######**************************** list1-4数据整合成json ********************************** result_list = {"statisticsReport": list1, "size_graph": list2, "weight_graph": list3, "percent_graph": list4, "fishNumber": list5} # print("the result_list is:") # print(result_list) result_json = json(result_list) cursor2.close() db2.close() return result_json except Exception as e: logger.warning(e) list1 = [] return json(list1) return False def sanic_server(): app.run(host="0.0.0.0", port=7000) if __name__ == "__main__": app.run(host="0.0.0.0", port=7000)