binocular_project/ReportUpload.py

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