python3--mysql数据库成功率计算

统计成功率函数

import pymysql
mysql_ip=mysql_ip
mysql_pass=password
mysql_dbname='test'
image_sql='select gantryId,vehiclePlate from dfs_gantry_travelimage;'
trans_sql='select gantryId,SpecialType,TradeResult,VehiclePlate from dfs_gantry_transaction where MediaType = 1'
conn = pymysql.connect(host=mysql_ip, port=3306, user='root', passwd=mysql_pass, db=mysql_dbname, charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 取图片流水表数据
cursor.execute(image_sql)
result_of_image = cursor.fetchall()
# 取交易流水表数据
cursor.execute(trans_sql)
result_of_trans = cursor.fetchall()

cursor.close()
conn.close()

# 处理操作
# 存门架编号以及数量
# 索引字典 存储内容 {门架编号:门架索引}
gantry_index=0
gantryId_list={}
# 数组字典
gantry_info=[]
gantryname=''
# 图片表数据处理
for li in result_of_image:
    for key in li:
        if key == 'gantryId':
            gantryname = li[key]
            if gantryname not in gantryId_list.keys():
                # 添加到索引字典
                dict_temp={gantryname: gantry_index}
                gantryId_list.update(dict_temp)
                # 初始化数组字典
                dict_temp={'gantry_name' : gantryname, 
                           'gantry_image_correct_num' : 0,
                           'gantry_image_num' :0,
                           'gantry_trans_correct_num' : 0,
                           'gantry_trans_correct_device_num' : 0,
                           'gantry_trans_sum' : 0,
                           'gantry_trans_num' :0,}
                gantry_info.append(dict_temp)
                gantry_index=gantry_index+1
        elif key == 'vehiclePlate':
            vehiclePlatename = li[key]
            index=gantryId_list[gantryname]
            # 车牌识别正确的情况
            if vehiclePlatename != '默A00000_9':
                gantry_info[index]['gantry_image_correct_num']+=1
                gantry_info[index]['gantry_image_num']+=1
            # 车牌识别错误的情况
            else:
                gantry_info[index]['gantry_image_num']+=1


# 流水表数据处理
# trans_sql='select gantryId,SpecialType,TradeResult,VehiclePlate from dfs_gantry_transaction where MediaType = 1'
# 交易总数中不包含的SpecialType，即无效的SpecialType
useless_SpecialType = [101,102,103,106,110,118,119,130,135,136,137,185,186,193]
for li in result_of_trans:
    for key in li:
        if key == 'gantryId':
            gantryname = li[key]
        elif key == 'SpecialType':
            index = gantryId_list[gantryname]
            # gantry_trans_sum 不做任何判断 就是计算总数
            gantry_info[index]['gantry_trans_sum']+=1
            sptype=999
            if '|' not in str(li[key]) and str(li[key]) != '':
                sptype = int(li[key])
            elif '|' in str(li[key]):
                temp_sptype=str(li[key]).split('|')
                for li_spt in temp_sptype:
                    if int(li_spt) in useless_SpecialType:
                        sptype=int(li_spt)
                        break
            if sptype not in useless_SpecialType:
                # gantry_trans_num 交易总数
                gantry_info[index]['gantry_trans_num']+=1
            if sptype not in range(155,184):
                gantry_info[index]['gantry_trans_correct_device_num']+=1
        elif key == 'TradeResult':
            index = gantryId_list[gantryname]
            trdrelt = int(li[key])
            if trdrelt == 0:
                gantry_info[index]['gantry_trans_correct_num']+=1
        elif key == 'vehiclePlate':
            vehiclePlatename = li[key]
            index=gantryId_list[gantryname]
            # 车牌识别正确的情况
            if vehiclePlatename != '默A00000_9':
                gantry_info[index]['gantry_trans_correct_num']+=1


# 汇总结果
'''
for li in gantry_info:
    gantryname = li['gantry_name']
    sum_image_num = li['gantry_image_num']
    sum_correct_image_num = li['gantry_image_correct_num']
    rate_of_right_image = sum_correct_image_num/sum_image_num*100
    rate_of_right_image = round(rate_of_right_image,2)
    total_str=str(gantryname)+" "
    total_str+=str(sum_correct_image_num)+" "
    total_str+=str(sum_image_num)+" "
    total_str+=str(rate_of_right_image)+" "
    total_str+=str(rate_of_wrong_image)
    print(total_str)
'''

'''
'gantry_trans_correct_device_num' : 0,
                           'gantry_trans_sum' : 0,
                           'gantry_trans_num' :0,}
'''
for li in gantry_info:
    gantryname = li['gantry_name']
    n1 = int(li['gantry_trans_correct_num'])
    n2 = int(li['gantry_trans_num'])
    rate=n1/n2*100
    rate=round(rate,4)
    n3 = int(li['gantry_trans_correct_device_num'])
    n4 = int(li['gantry_trans_sum'])
    print(gantryname+" "+str(n1)+" "+str(n2)+" "+str(rate)+" "+str(n3)+" "+str(n4))