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