tcc_pn_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
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-14"
    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 date = '{}'".format(ct)
    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：过车总数
    for row in results:
        row_list = list(row)
        #tmp_list = ['','',0,0,0,0,0,0,0]
        tmp_list = ['','',1]
        localtion_id = int(row_list[7])
        cursor = mysql_db.cursor()
        try:
            sql = "select  pointname,PROVIDER,location_type from location where id = {};".format(localtion_id)
        except:
            logging.error("mysql语句执行错误！")
            sys.exit(1)
        print(sql)
        cursor.execute(sql)
        result = cursor.fetchall()
        if result and int(result[0][2]) == 5:
            pn = result[0][0]
            tmp_list[0] = pn
            tmp_list[1] = result[0][1]
        else:
            continue
        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]
                    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('tcc_pn_recognize_fail_0719.csv',index=False)
    print(df.head())