123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643 |
- #!/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)
|