pn_recognize_fail_SJKK_4.py

 

 

复制代码
#!/usr/bin/python3
import os,stat
import sys
import re
import pymysql
import time
from datetime import timedelta
from datetime import datetime
import logging
import pandas as pd
import requests
from clickhouse_driver import Client
from pathlib import Path
"""
    统计市级卡口的港澳过车总数，识别率.生成3层目录的图片
"""
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)
    yesterday = (datetime.now() - timedelta(days = 1)).strftime("%Y-%m-%d")
    query_time = yesterday
    ct = "date >= '2022-07-18' and date <= '2022-07-20'"
    #sql = "select license_plate2,xgbdp,ambdp,plate_type_id2,location_id,capture_time,image_url1,location_id  from yisa_oe.vehicle_all where (license_plate2 like '粤Z%澳' or license_plate2 LIKE '粤Z%港') and {}".format(ct)
    sql = "select license_plate2,xgbdp,ambdp,plate_type_id2,location_id,capture_time,image_url1,location_id  from yisa_oe.vehicle_all where (license_plate2 like '粤Z%澳' or license_plate2 LIKE '粤Z%港') and date = '{}'".format(query_time)
    try:
        results = 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)
    pn_list = [] # 元素是列表，0:卡口名称，1：卡口id，2：香港内地牌，3：香港本地牌，4：澳门内地牌，5：澳门本地牌，6：香港识别率，7：澳门识别率
    os.system('rm -rf count/港澳车识别失败图片/*')
    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  pointname,PROVIDER from location where id = {};".format(localtion_id)
                    #print(sql)
                    cursor.execute(sql)
                    result = cursor.fetchall()
                    if result:
                        pn = result[0][0]
                        tmp_list[0] = pn
                        tmp_list[1] = result[0][1]
                    else:
                        pn = '缺失点位'
                        continue
                except:
                    logging.error("mysql语句执行错误！")
                    sys.exit(1)
                if    row_list[2]: #识别澳门本地牌成功
                    tmp_list[5] = 1
                else:
                    my_file = Path("/home/mypy/count/港澳车识别失败图片/{}".format(result[0][0]))
                    if not my_file.is_dir():
                        os.makedirs("/home/mypy/count/港澳车识别失败图片/{}".format(result[0][0]))
                    kk_file = Path("/home/mypy/count/港澳车识别失败图片/{}/澳门.html".format(result[0][0]))
                    if not kk_file.is_file():
                    #os.mknod("/home/mypy/count/港澳车识别失败图片/{}/澳门{}".format(result[0][0],(row_list[0] + str(row_list[5]))))
                        os.mknod(kk_file)
                        with open("/home/mypy/count/港澳车识别失败图片/{}/澳门.html".format(result[0][0]),'w+') as f:
                            f.write('<html> <head></head> <body>' + '\n' +  '</body> </html>')
                    tmp_file = []
                    with open("/home/mypy/count/港澳车识别失败图片/{}/澳门.html".format(result[0][0]),'r') as f:
                        tmp_file = f.readlines()
                        tmp_file.insert(1,'<a href="{}">{}</a><br>'.format(row_list[6],(row_list[0] + str(row_list[5]) + '.jpg')))
                        #print(tmp_file)
                    a = '\n'.join(tmp_file)
                    with open("/home/mypy/count/港澳车识别失败图片/{}/澳门.html".format(result[0][0]),'w+') as f:
                        f.write(a)            
                    #os.system('chmod 755 ' + "/home/mypy/count/港澳车识别失败图片/{}/澳门.html".format(result[0][0]))
            if re.findall(r"港+",row_list[0]):
                tmp_list[2] = 1
                try:
                    sql = "select  pointname,PROVIDER from location where id = {};".format(localtion_id)
                    #print(sql)
                    cursor.execute(sql)
                    result = cursor.fetchall()
                    if result:
                        pn = result[0][0]
                        tmp_list[0] = pn
                        tmp_list[1] = result[0][1]
                    else:
                        pn = '缺失点位'
                        continue
                except:
                    logging.error("mysql语句执行错误！")
                    sys.exit(1)
                if row_list[1]: #识别香港本地牌成功
                    tmp_list[3] = 1
                else:
                    my_file = Path("/home/mypy/count/港澳车识别失败图片/{}".format(result[0][0]))
                    if not my_file.is_dir():
                        os.makedirs("/home/mypy/count/港澳车识别失败图片/{}".format(result[0][0]))
                    kk_file = Path("/home/mypy/count/港澳车识别失败图片/{}/香港.html".format(result[0][0]))
                    if not kk_file.is_file():
                        os.mknod(kk_file)
                        with open("/home/mypy/count/港澳车识别失败图片/{}/香港.html".format(result[0][0]),'w+') as f:
                            f.write('<html> <head></head> <body>' + '\n' +  '</body> </html>')
                    tmp_file = []
                    with open("/home/mypy/count/港澳车识别失败图片/{}/香港.html".format(result[0][0]),'r+') as f:
                        tmp_file = f.readlines()
                        tmp_file.insert(1,'<a href="{}">{}</a><br>'.format(row_list[6],(row_list[0] + str(row_list[5]) + '.jpg')))
                    a = '\n'.join(tmp_file)
                    with open("/home/mypy/count/港澳车识别失败图片/{}/香港.html".format(result[0][0]),'w+') as f:
                        f.write(a)
                    #os.system('chmod 755 ' + "/home/mypy/count/港澳车识别失败图片/{}/香港.html".format(result[0][0]))
            if pn_list:
                flag = 0
                for i in range(len(pn_list)):
                    if tmp_list[0] in pn_list[i]:
                        pn_list[i][2] = tmp_list[2] + pn_list[i][2]
                        pn_list[i][3] = tmp_list[3] + pn_list[i][3]
                        pn_list[i][4] = tmp_list[4] + pn_list[i][4]
                        pn_list[i][5] = tmp_list[5] + pn_list[i][5]
                        flag = 1
                if flag == 0: #pn_list没有这个卡口
                    pn_list.append(tmp_list)
            else:
                pn_list.append(tmp_list)
    os.system('chmod -R 755 /home/mypy/count/港澳车识别失败图片/*')
    for i in range(len(pn_list)):
        #print(pn_list[i][2],pn_list[i][3],pn_list[i][4],pn_list[i][5])
        flag1 = 0
        flag2 = 0
        pn_list[i][6] = pn_list[i][2] + pn_list[i][4]
        if pn_list[i][2] == 0:
            pn_list[i][7] = '0' + '%'
            flag1 = 1
        if pn_list[i][4] == 0:
            pn_list[i][8] = '0' + '%'
            flag2 = 1
        if flag1 == 0:
            a = (pn_list[i][3] / pn_list[i][2]) * 100
            xg_recognize_rate = round(a ,2)
            pn_list[i][7] = str(xg_recognize_rate) + '%'
        if flag2 == 0:
            b = (pn_list[i][5] / pn_list[i][4]) * 100
            am_recognize_rate = round(b ,2)
            pn_list[i][8] = str(am_recognize_rate) + '%'
    sort_pn_list = sorted(pn_list,key=(lambda x:x[6]),reverse=True)
    df = pd.DataFrame(sort_pn_list,columns=['卡口名称','卡口ID','香港内地牌','香港外地牌','澳门内地牌','澳门本地牌','港澳过车总数','香港识别率','澳门识别率'])
    #df.to_csv('pn_recognize_fail.csv',index=False)
    df2 = pd.read_excel('/home/mypy/SJKK.xlsx')
    #df2 = pd.read_excel('/home/mypy/vehicle_barrier_province_export.xlsx')    
    df['卡口ID'] = df['卡口ID'].astype(int)
    #print('df_type: {}'.format(df['卡口ID'].dtypes))
    #print('df2_type: {}'.format(df2['proid'].dtypes))
    df3 = df[df['卡口ID'].isin(df2['proid'].values)] #导出csv的卡口
    l = [] #无港澳车经过的卡口
    for i in df2['proid'].values:
        tmp_list = [['无港澳过车数据',0,0,0,0,0,0,0,0]]
        if i not in df3['卡口ID'].values:
            l.append(i)
            sql = "select  pointname,PROVIDER from location where PROVIDER = '{}';".format(i)
            cursor.execute(sql)
            result = cursor.fetchall()
            if not result:
                print('不存在点位名称的proid: {}'.format(i))
                pn = '未找到点位名称'
            else:
                pn = result[0][0]
            tmp_list[0][0] = pn
            tmp_list[0][1] = i
            tmp_df = pd.DataFrame(tmp_list,columns=['卡口名称','卡口ID','香港内地牌','香港外地牌','澳门内地牌','澳门本地牌','港澳过车总数','香港识别率','澳门识别率'])
            df3 = df3.append(tmp_df,ignore_index = True)
    csv_name = '市际卡口港澳车情况-' + yesterday + '.csv'
    df3.to_csv('/home/mypy/count/市际卡口/{}'.format(csv_name),index=False)
    print(df3.head())
    print(l)
复制代码

 

posted @   苦逼yw  阅读(7)  评论(0)  编辑  收藏  举报
相关博文：
· pn_recognize_fail_SJKK_2.py
· pn_recognize_fail_YLKK.py
· 【pytorch】从零开始，利用yolov5、crnn+ctc进行车牌识别
· *CTFのWP&复现
· 数据分析笔记
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示