count_ga_5.py

#!/usr/bin/python3
'''
    作用：统计港澳车的识别率，分别输出港牌和澳牌识别失败的港澳车的二次识别车牌、筛选过的时间和图片url的csv文件
'''
import os
import sys
import re
import pymysql
import time
import datetime
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__)) + "/count_ga.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)
    sql = "select license_plate2,xgbdp,ambdp,VIIDOBJECTID,location_id,capture_time,image_url1  from yisa_oe.vehicle_all where (license_plate2 like '粤Z%澳' or license_plate2 LIKE '粤Z%港') and date = '2022-07-06'"
    try:
        results = cursor.execute(sql)
        sql = "select  count(*)  from yisa_oe.vehicle_all where license_plate2 LIKE '粤Z%港' and date = '2022-07-06'"
        xg_len = cursor.execute(sql)
        sql = "select  count(*)  from yisa_oe.vehicle_all where license_plate2 LIKE '粤Z%澳' and date = '2022-07-06'"
        am_len = cursor.execute(sql)
    except:
        logging.error("语句执行错误！")
        sys.exit(1)
    
    data_list = []  # 统计某天的港澳牌的行
    xg_recognize = 0 # 港牌识别成功总数
    am_recognize = 0 # 澳牌识别成功总数
    # df_recog =  []  # 识别失败，港澳能访问图片url的，元素是车牌号码和图片url的list
    xg_fail_recognize = [] # 识别失败，香港牌的，元素是车牌号码和图片url的list
    am_fail_recognize = [] # 识别失败，澳门牌的，元素是车牌号码和图片url的list
    fail_c = 0 # 港澳图片访问失败总数
    url_xg = 0 #港牌识别失败本地牌总数
    url_am = 0 #澳牌识别失败本地牌总数
    for row in results:
        row_list = list(row)
        for i in range(len(row_list)): #循环结果每行的每个字段
            if  row_list[i] and i == 0: # 遍历字段是二次识别
                if re.findall(r"澳+",row_list[i]): # 二次识别是澳牌
                    if  row_list[2]: #澳牌本地牌存在
                        am_recognize=am_recognize+1
                    else:
                        tep_list = []
                        tep_list.append(row_list[i])
                        url_am = url_am + 1 # 澳牌识别失败+1
                        time_int = row_list[5]
                        time_obj = time.localtime(time_int)
                        time_str = time.strftime("%Y-%m-%d_%H-%M-%S",time_obj)
                        tep_list.append(time_str)
                        tep_list.append(row_list[6])
                        # df_recog.append(tep_list) # 识别失败，元素是车牌+捕获时间+图片url
                        am_fail_recognize.append(tep_list)

                if re.findall(r"港+",row_list[i]):  # 二次识别是港牌
                    if row_list[1]: #港牌本地牌存在
                        xg_recognize = xg_recognize+1
                    else:
                                                tep_list = []
                                                tep_list.append(row_list[i])
                                                url_xg = url_xg + 1
                                                time_int = row_list[5]
                                                time_obj = time.localtime(time_int)
                                                time_str = time.strftime("%Y-%m-%d_%H-%M-%S",time_obj)
                                                tep_list.append(time_str)
                                                tep_list.append(row_list[6])
                                                # df_recog.append(tep_list)
                                                xg_fail_recognize.append(tep_list)
            if not row_list[i] and i == 0:
                row_list[i] = "二次识别失败"
            if not row_list[i] and i == 1:
                row_list[i] = "香港牌识别失败"
            if not row_list[i] and i == 2:
                row_list[i] = "澳门牌识别失败"
            if i == 5:
                time_int = row_list[i]
                time_obj = time.localtime(time_int)
                row_list[i] = time.strftime("%Y-%m-%d_%H-%M-%S",time_obj)
        logging.info(row)
        data_list.append(row_list)
    df = pd.DataFrame(data_list,columns=['license_plate2','xgbdp','ambdp','VIIDOBJECTID','location_id','capture_time','image_url1'])
    #df2 = pd.DataFrame(df_recog,columns=['license_plate2','capture_time','image_url1']) # 识别失败的图片url、捕获时间和港澳车牌
    #s_dt = datetime.datetime.strptime('2022-06-23_17-00-00','%Y-%m-%d_%H-%M-%S')
    #df2['capture_time'] = pd.to_datetime(df2['capture_time'], format='%Y-%m-%d_%H-%M-%S')
    #df3 = df2[df2['capture_time']<=s_dt]
    #logging.info("df: {}".format(df))
    df_xg_fail_recognize = pd.DataFrame(xg_fail_recognize,columns=['license_plate2','capture_time','image_url1'])
    
    df_am_fail_recognize = pd.DataFrame(am_fail_recognize,columns=['license_plate2','capture_time','image_url1'])
    df_xg_fail_recognize.to_csv('xg_fail_rec.csv',index=False)
    df_am_fail_recognize.to_csv('am_fail_rec.csv',index=False)