area_recognize_fail.py

#!/usr/bin/python3
import os
import sys
import re
import pymysql
import time
import logging
import pandas as pd
import requests
from clickhouse_driver import Client
if __name__ == '__main__':
    logging.basicConfig(filename=os.path.dirname(os.path.abspath(__file__)) + "/pn_recognize_fail.log",level=logging.DEBUG)
    try:
        cursor = Client(host='68.109.211.36', port=9001, password='Yisa_fs_2021')
    except:
        logging.info("lighting连接失败！")
        sys.exit(1)
    ct = "date = '2022-07-12'"
    sql = "select license_plate2,xgbdp,ambdp,viid_object_id,location_id,capture_time,image_url1,location_id  from yisa_oe.vehicle_all where (license_plate2 like '粤Z%澳' or license_plate2 LIKE '粤Z%港') and " + ct
    try:
        results = cursor.execute(sql)
        sql = "select  count(*)  from yisa_oe.vehicle_all where license_plate2 LIKE '粤Z%港' and " + ct
        xg_len = cursor.execute(sql)
        sql = "select  count(*)  from yisa_oe.vehicle_all where license_plate2 LIKE '粤Z%澳' and " + ct
        am_len = cursor.execute(sql)
    except:
        logging.error("lighting语句执行错误！")
        sys.exit(1)
    try:
        mysql_db = pymysql.connect(host='68.109.211.67',user='yisa_oe',password='Yisa_fs_2021',database='yisa_oe')
    except:
        logging.info("mysql连接失败！")
        sys.exit(1)
    area_list = [] # 元素是列表，0:区域名称，1：区域编码，2：香港内地牌，3：香港本地牌，4：澳门内地牌，5：澳门本地牌，6：香港识别率，7：澳门识别率
    am_recognize = 0
    for row in results:
        row_list = list(row)
        tmp_list = ['','',0,0,0,0,0,0,0]
        localtion_id = int(row_list[7])
        cursor = mysql_db.cursor()
        if  row_list[0]: # 二次识别成功
            if re.findall(r"澳+",row_list[0]): # 二次识别是澳牌
                tmp_list[4] = 1
                try:
                    sql = "select  regioncode,PROVIDER from location where id = {};".format(localtion_id)
                    print(sql)
                    cursor.execute(sql)
                    result = cursor.fetchall()
                except:
                    logging.error("查询区域编码mysql语句执行错误！")
                    sys.exit(1)
                if result:
                    region_code = result[0][0]
                    if region_code == 0:
                        region_code = result[0][1][:6]
                    tmp_list[1] = region_code
                    try:
                        sql = "select  name  from region where code = '{}';".format(region_code) # 找区域名称
                        print(sql)
                        cursor = mysql_db.cursor()
                        cursor.execute(sql)
                        result = cursor.fetchall()
                        if not result:
                            result = '区域名称未找到'
                            
                    except:
                        logging.error("查询区域名称mysql语句执行错误！")
                        sys.exit(1)
                    print("区域名称:{}".format(result[0][0]))
                    if result != '区域名称未找到':
                        tmp_list[0] = result[0][0]
                    else:
                        tmp_list[0] = result
                else:
                    pn = '缺失点位'
                    continue
                if  row_list[2]: #识别澳门本地牌成功
                    tmp_list[5] = 1
                    
            if re.findall(r"港+",row_list[0]):
                tmp_list[2] = 1
                try:
                    sql = "select  regioncode,PROVIDER from location where id = {};".format(localtion_id)
                    print(sql)
                    cursor.execute(sql)
                    result = cursor.fetchall()
                except:
                    logging.error("查询区域编码mysql语句执行错误！")
                    sys.exit(1)
                if result:
                    region_code = result[0][0]
                    if region_code == 0:
                        region_code = result[0][1][:6]
                    tmp_list[1] = region_code
                    try:
                        sql = "select  name  from region where code = '{}';".format(region_code) #找区域名称
                        print(sql)
                        cursor = mysql_db.cursor()
                        cursor.execute(sql)
                        result = cursor.fetchall()
                        if not result:
                            result = '区域名称未找到'
                    except:
                        logging.error("查询区域名称mysql语句执行错误！")
                        sys.exit(1)
                    if result != '区域名称未找到':
                        tmp_list[0] = result[0][0]
                    else:
                        tmp_list[0] = result
                else:
                    pn = '缺失点位'
                    continue
                if row_list[1]: #识别香港本地牌成功
                    tmp_list[3] = 1
            if area_list:
                flag = 0
                for i in range(len(area_list)):
                    if tmp_list[0] in area_list[i]:
                        area_list[i][2] = tmp_list[2] + area_list[i][2]
                        area_list[i][3] = tmp_list[3] + area_list[i][3]
                        area_list[i][4] = tmp_list[4] + area_list[i][4]
                        area_list[i][5] = tmp_list[5] + area_list[i][5]
                        flag = 1
                if flag == 0: #area_list没有这个区域
                    area_list.append(tmp_list)
            else:
                area_list.append(tmp_list)
    for i in range(len(area_list)):
        #print(area_list[i][2],area_list[i][3],area_list[i][4],area_list[i][5])
        flag1 = 0
        flag2 = 0
        area_list[i][6] = area_list[i][2] + area_list[i][4]
        if area_list[i][2] == 0:
            area_list[i][7] = '0' + '%'
            flag1 = 1
        if area_list[i][4] == 0:
            area_list[i][8] = '0' + '%'
            flag2 = 1
        if flag1 == 0:
            a = (area_list[i][3] / area_list[i][2]) * 100
            xg_recognize_rate = round(a ,2)
            area_list[i][7] = str(xg_recognize_rate) + '%'
        if flag2 == 0:
            b = (area_list[i][5] / area_list[i][4]) * 100
            am_recognize_rate = round(b ,2)
            area_list[i][8] = str(am_recognize_rate) + '%'
    sort_area_list = sorted(area_list,key=(lambda x:x[6]),reverse=True)
    df = pd.DataFrame(sort_area_list,columns=['区域名称','区域编码','香港内地牌','香港外地牌','澳门内地牌','澳门本地牌','区域过车总数','香港识别率','澳门识别率'])
    df.to_csv('area_recognize_fail_0712.csv',index=False)
    print(df)