Python实战：使用python进行全量测试，并将结果输出到Excel

以下是一个使用python进行全量测试的示例，有兴趣的同学可以参考以下代码进行接口的全量测试工作：

import mysql.connector
import psycopg2
import requests
import openpyxl
import datetime
import argparse
import logging

env = {
    "local": {
        "url": "http://127.0.0.1:48080/app-api/v2/localcharge/getFclLocalCharge"
    }

}
config = env["local"]

# 创建数据库连接
cnx = mysql.connector.connect(
    host='192.168.17.173',
    user='root',
    password='123456',
    database='lead_platform'
)

# 创建一个解析器
parser = argparse.ArgumentParser(description="Script description")

# 添加一个参数
parser.add_argument("parameter", type=str, help="Value of the parameter")

# 解析命令行参数
args = parser.parse_args()

# 获取参数的值
token = args.parameter

startTime = datetime.datetime.now().timestamp() * 1000

level = 0

logging.basicConfig(filename=".\Level_" + str(level) + ".txt", level=logging.INFO)


def fetchFclLocalchargeData(carrier,polCode):
    url = config['url']
    headers = {
        "Content-Type": "application/json",
        "Authorization":"Bearer " + token,
        "Tenant-Id":"24"
    }
    postData = '{"gp20":1,"gp20Soc":0,"gp40":1,"gp40Soc":0,"hq40":1,"hq40Soc":0,"hq45":1,"hq45Soc":0,"nor40":1,"nor40Soc":0,"incotermCode":"EXWORK","provinceCode":"310000","cityCode":"310100","districtCode":"310151","streetCode":"310151101000","portCode":"' + polCode + '","carrierCode":"' + carrier + '","etd":"2023-09-05","serviceName":["拖车服务"],"terminal":"外二","zipCode":"202150"}'
    postData = postData.encode('utf-8')
    #print(postData)
    response = requests.post(url=url, headers=headers, data=postData,timeout=10)
    #print(response.text)
    return response.json()
    

def fetchFclLocalchargeDataFromDb(level,carrier,polCode):
    # 创建游标对象
    cursor = cnx.cursor()

    # 编写查询语句
    #query = "SELECT carrier_code,port_code,service_code,service_name,service_name_cn,gp20,gp40,hq40,hq45,nor40,bill FROM oversea_localcharge WHERE export_type = 1 AND level = " + str(level) + " AND carrier_code  = '" + carrier + "' AND port_code = '" + polCode + "'"
    query = "SELECT carrier_code,port_code,service_code,service_name,service_name_cn,gp20,gp40,hq40,hq45,nor40,bill FROM oversea_localcharge WHERE export_type = 1 AND level = " + str(level) + " AND port_code = '" + polCode + "'"
    #print(query)
    # 执行查询
    cursor.execute(query)

    # 获取查询结果
    return cursor.fetchall()   

carrier_pares = ['COSCO']
port_pares = ['CNSHA','CNNBO','CNYTN','CNNSA','CNSHK','CNHUA','CNQDG','CNTXG','CNXMN']


result = [['起运港五字码','费用编码','费用英文名','费用中文名','20GP单价','40GP单价','40HQ单价','45HQ单价','40NOR单价','票单价','总价']]

exceptMessage =[]
port_count=0

time_now = datetime.datetime.now().timestamp() * 1000

for carrier in carrier_pares:
    for portCode in port_pares:
        port_count+=1
        try:  
            data = fetchFclLocalchargeData(carrier,portCode)
            if data['code']== 0:
                level = data['data']['level']
                
                #print("*************level=",level)
                origin = fetchFclLocalchargeDataFromDb(level,carrier,portCode)
                #print("*************origin=",origin)
                fee_obj = {row[2]: row for row in origin}
                #print("*************fee_obj=",fee_obj)
                notTuochePriceData = []
                for row in data['data']['feeDetail']:
                    if('OTHF' != row['serviceCode']):
                        notTuochePriceData.append(row)
                if(len(origin) == len(notTuochePriceData)):
                    print(carrier,portCode,"费用条目数量匹配成功")
                    logging.info("%s%s费用条目数量匹配成功",carrier,portCode)
                else :
                    exceptMessage.append(carrier + "_" + portCode + "费用条目数量匹配失败,数据库数量为" + len(origin) + "接口数据为" + len(data['data']['feeDetail']))
                for d in notTuochePriceData:
                    if(d['serviceCode'] != 'OTHF'):
                        #print("*************origin=",fee_obj[d['serviceCode']])
                        #print(d['gp20Price'],fee_obj[d['serviceCode']][5])
                        
                        if(d['gp20Price'] == str(fee_obj[d['serviceCode']][5])) and (d['gp40Price'] == str(fee_obj[d['serviceCode']][6]) ) and ( d['hq40Price'] == str(fee_obj[d['serviceCode']][7]) ) and ( d['nor40Price'] == str(fee_obj[d['serviceCode']][9])):
                            print(d['serviceCode'],"所有箱型价格验证成功")
                            logging.info("%s所有箱型价格验证成功",d['serviceCode'])
                        elif (d['billPrice'] == str(fee_obj[d['serviceCode']][10])):
                            print(d['serviceCode'],"按票计价验证成功")
                            logging.info("%s按票计价验证成功",d['serviceCode'])
                        else :
                            result.append([portCode,
                                           d['serviceCode'],
                                           d['serviceName'],
                                           d['serviceNameCn'],
                                           d['gp20Price'],
                                           d['gp40Price'],
                                           d['hq40Price'],
                                           d['hq45Price'],
                                           d['nor40Price'],
                                           d['billPrice'],
                                           d['totalPrice']
                                           ])
        except BaseException as e:
            #print(e)
            exceptMessage.append(portCode + "获取数据失败")
        end = datetime.datetime.now().timestamp() * 1000       
        print("总抓取数量为",len(port_pares)*len(carrier_pares),"当前抓取",port_count,"船司",carrier,"起运港",portCode,"抓取进度",port_count*100/(len(port_pares)*len(carrier_pares)),"%","耗时",end - time_now)
        logging.info("总抓取数量为%s当前抓取%s船司%s起运港%s抓取进度%s耗时%s",str(len(port_pares)*len(carrier_pares)),str(port_count),carrier,portCode,str(port_count*100/(len(port_pares)*len(carrier_pares))),str(end - time_now))
        time_now = end
    
    
# 创建一个新的工作簿
workbook = openpyxl.Workbook()

# 选择默认的工作表
sheet = workbook.active


for v in result:
    sheet.append(v)
        
# 保存工作簿到文件

print("*************level=",level)
workbook.save('Level_' + str(level) + '.xlsx')


for s in exceptMessage:
    print(s)
endTime = datetime.datetime.now().timestamp() * 1000      
print("累计查询数据数为",len(port_pares)*len(carrier_pares),"接口异常数量为",len(exceptMessage),"总耗时",endTime - startTime)
logging.info("累计查询数据数为%s接口异常数量为%s总耗时%s",len(port_pares)*len(carrier_pares),len(exceptMessage),endTime - startTime)