python 数据驱动自动化测试脚本

-- coding:utf-8 -- ＃

以下运行时如果报没有模块的错误，可以直接安装一下

import urllib, http.client, urllib.request
from time import ctime
import threading, pymysql
import requests
import csv
import json, random, time
import schedule, sched
import xlwt,yaml,xlrd
from xlwt import *
import os, sys
from xlutils.copy import copy
from functools import wraps

数据库访问封装类

class db_con_sql: ## 数据库连接类
def db_con_config(): ## 数据库连接参数配置
ipadderss = ‘xxx.xxx.xxx.xxx’
user = ‘xxx’
passwd = ‘xxx’
port = 33306
return ipadderss, user, passwd, port

##数据库访问连接
def con_get_account():
    try:
        ip = db_con_sql.db_con_config()
        con_date = pymysql.connect(host=ip[0], user=ip[1], passwd=ip[2], port=ip[3], db='account', charset='utf8')
        # print('数据库连接正常...')
        return con_date
    except pymysql.Error as e:
        print('pymysql Error:%s' % e)

def con_get_account_order():
    try:
        ip = db_con_sql.db_con_config()
        con_date = pymysql.connect(host=ip[0], user=ip[1], passwd=ip[2], port=ip[3], db='account_order',
                                   charset='utf8')
        # print('数据库连接正常...')
        return con_date
    except pymysql.Error as e:
        print('pymysql Error:%s' % e)

操作数据库类

class db_exec():
def reverse_rows():
db = db_con_sql.con_get_account()
cursor = db.cursor()
sql = ‘select account_Id,amount,delay_Flag,direction,in_Out,journal_type_id,order_Seq,subject_Id,order_No,remark,source,third_No,trade_Time from account_record_20180516
where delay_Flag =4 and record_status in(100) ORDER BY trade_Time desc LIMIT 5’
cursor.execute(sql)
while True:
row = cursor.fetchone()
if row:
yield row
else:
break

def queryByOrderNos_row():
    db = db_con_sql.con_get()
    cursor = db.cursor()
    sql = ''
    cursor.execute(sql)
    while True:
        row = cursor.fetchone()
        if row:
            yield row
        else:
            break

接口配置信息

class interface_config:
def config_data():
url = ‘xxx.xxx.xxx.xxx’
port = [‘25418’, ‘25419’] ##接口不同服务得端口号可以继续按顺序增加，不要在前面乱插
return url, port

###读取excel 文件中的测试数据执行
class get_excle_param:
def get_param():## 读取excel测试用例数据
data = xlrd.open_workbook(‘D:/Porject work/Testwork/TestScript/testdate/account/test_case.xlsx’)
#table = data.sheets()[0]
table=data.get_sheet_name()
print (table)
for s in range(len(table)):
nrow = table.nrows
id = []
name = []
postJson = []
url = []
request = []
yuqi =[]
#report=[]
for i in range(1, nrow):
id.append(table.cell_value(i, 0)) ##sheet 中的单元格坐标
name.append(table.cell_value(i, 1))
postJson.append(json.loads(table.cell_value(i, 2).replace(" “, “”).replace(”\n", “”).replace("\", “”).strip()))##赋值json 数据参数
url.append(table.cell_value(i, 3))
request.append(table.cell_value(i, 4))
yuqi.append(table.cell_value(i, 5))
ids = [int(i) for i in id]
return ids,name,postJson,url,request,yuqi
def wb_param():## 写入case执行结果及返回参数状态
oldWb=xlrd.open_workbook(‘D:/Porject work/Testwork/TestScript/testdate/account/test_case.xlsx’)
newWb=copy(oldWb)
newWs=newWb.get_sheet(0)
return newWb,newWs
##测试类实体
class Test_case():
def test_interface(): ## 订单记账请求接口，按照excel 格式进行填写，该接口可以同时支持post及get请求
conf = interface_config.config_data()
param = get_excle_param.get_param()
wbprarm=get_excle_param.wb_param()
for i in range(len(param[0])):
params = json.dumps(param[2][i]).encode(‘UTF-8’)
requrl = param[3][i]
method = param[4][i]
yuqis = param[5][i]
headers = {“Content-type”: “application/json”}
#requrl = “http://” + conf[0] + “:” + (conf[1])[1] + “/record/entry” ## 访问地址拼接配置，读取配置公共参数得参数值
conn = http.client.HTTPConnection(conf[0], (conf[1])[1]) ## 建立连接
conn.request(method=method, url=requrl, body=params, headers=headers)
response = conn.getresponse()
data = response.read().decode(‘UTF-8’) # 获取返回的请求码及message 校验信息
data_dic = json.loads(data)
#print(data_dic)
if response.status == 200:
if yuqis == int(data_dic[‘code’]):
print(“entry_case_No:%s” % param[1][i] + ‘——’ + “请求测试成功,数据建立成功%s” % data)
wbprarm[1].write(i+1,6,“pass”)
wbprarm[1].write(i+1,7,“返回信息为:%s” % data)
wbprarm[0].save(‘D:/Porject work/Testwork/TestScript/testdate/account/test_case.xlsx’)
else:
print(“entry_case_No:%s” % param[1][i] + ‘——’ + “请求测试成功,返回信息为:%s” % data)
wbprarm[1].write(i+1,6,“check”)
wbprarm[1].write(i+1,7,“返回信息为:%s” % data)
wbprarm[0].save(‘D:/Porject work/Testwork/TestScript/testdate/account/test_case.xlsx’)
else:
print(“entry_case_No:%s” % param[1][i] + ‘——’ + “请求测试失败,返回信息为:%s” % data)
wbprarm[1].write(i+1,6,“failed”)
wbprarm[1].write(i+1,7,“返回信息为:%s” % data)
wbprarm[0].save(‘D:/Porject work/Testwork/TestScript/testdate/account/test_case.xlsx’)
#time.sleep(1)
def reverse_post(): ##订单冲正请求接口,接口读取数据库数据进行处理，不能与excel 文件用例通用
conf = interface_config.config_data()
for row in db_exec.reverse_rows():
postJson = {
“details”:
[
{
“accountId”: row[0],
“amount”: str(row[1]), # 根据参数类型进行强制类型转换
“delayFlag”: row[2],
“direction”: row[3],
“inOut”: row[4],
“orderSeq”: row[5],
“journalType”: row[6],
“subjectId”: row[7]
},
],
“orderNo”: row[8],
“remark”: row[9],
“source”: row[10],
“thirdNo”: row[11],
“tradeTime”: row[12]
}
params = json.dumps(postJson) # 提交json 数据参数
print(params)
headers = {“Content-type”: “application/json”}
requrl = “http://” + conf[0] + “:” + (conf[1])[1] + “/record/reverse” ## 访问地址拼接配置，读取配置公共参数得参数值
conn = http.client.HTTPConnection(conf[0], (conf[1])[1]) ## 建立连接
conn.request(method=“POST”, url=requrl, body=params, headers=headers)
response = conn.getresponse()
data = response.read().decode(‘UTF-8’) # 获取返回的请求码及message 校验信息
if response.status == 200:
print(“reverse_case_No:请求测试成功,数据建立成%s” % data)
else:
print(“reverse_case_No:请求测试失败,返回信息为:%s” % data)
time.sleep(5)
def test_login():

创建数组存放线程

threads = []
for i in range(1):
t = threading.Thread(target=Test_case.test_interface, args=()) # 设置线程数据
# t1=threading.Thread(target=Test_case.reverse_post,args=())
threads.append(t)
# threads.append(t1)
print(‘The Test Start:’, ctime())
if name == ‘main’:
# 线程启动方法
#for i in threads:
# i.start()
#for i in threads:
# i.join()
#直接调用函数法
Test_case.test_interface()
print(‘The Test End:’, ctime())

读取case的格式，执行完按成之后，会将结果反馈到case中pass or failed