pn_recognize_xny2.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
from pathlib import Path
"""
    统计佛山市所有卡口的港澳过车总数，识别率
"""
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 = "2022-07-12 08:00:00"
    et = "2022-07-12 21:00:00"
    sql = "select license_plate2,location_id from yisa_oe.vehicle_all where plate_type_id2 in (19,20) and license_plate2 != '未识别'  and toDateTime(capture_time) >= '{}' and toDateTime(capture_time) < '{}'".format(ct,et)
    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：澳门识别率
    pn_list = [] # 元素是列表，0:卡口名称，1：卡口id，2：二次识别总量，3：二次识别错误
    am_recognize = 0
    for row in results:
        row_list = list(row)
        tmp_list = ['','',1,0]
        if len(row_list[0]) != 7:
            tmp_list[3] = 1
        localtion_id = int(row_list[1])
        cursor = mysql_db.cursor()
        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 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]
                    flag = 1
            if flag == 0: #pn_list没有这个卡口
                pn_list.append(tmp_list)
        else:
            pn_list.append(tmp_list)
    sort_pn_list = sorted(pn_list,key=(lambda x:x[2]),reverse=True)
    df = pd.DataFrame(sort_pn_list,columns=['卡口名称','卡口ID','二次总量','二次识别错误'])
    df.to_csv('pn_recognize_xny_0712.csv',index=False)
    print(df.head())