背景:将excel的用例数据,都写入数据库。
# -*- coding: utf-8 -*-
import pymysql,xlrd
#打开数据库连接
conn = pymysql.connect('localhost',user = "root",passwd = "admin",db = "接口用例表")
#获取游标
cursor=conn.cursor()
print(cursor)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "insert into case_interface (module,ID,UseCase,url,method,params,headers,body,status_code,code_to_compare,code_aclual,params_to_compare) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#打开文件
file = xlrd.open_workbook("mysql/DemoAPITestCase.xlsx")
sheet_1 = file.sheet_by_index(0) #根据sheet页的排序选取sheet
row_content = sheet_1.row_values(0) #获取指定行的数据,返回列表,排序自0开始
row_number = sheet_1.nrows #获取有数据的最大行数
for i in range(1,row_number):
module = sheet_1.cell(i,0).value
ID= sheet_1.cell(i,1).value
UseCase = sheet_1.cell(i,2).value
url = sheet_1.cell(i,3).value
method = sheet_1.cell(i,4).value
params = sheet_1.cell(i,5).value
headers = sheet_1.cell(i,6).value
body = sheet_1.cell(i,7).value
status_code = sheet_1.cell(i,8).value
# response_time单位ms = sheet_1.cell(i,9).value
# result_interface = sheet_1.cell(i,10).value
code_to_compare = sheet_1.cell(i,11).value
code_aclual = sheet_1.cell(i,12).value
# code_expect = sheet_1.cell(i,13).value
# result = sheet_1.cell(i,14).value
# msg = sheet_1.cell(i,15).value
params_to_compare = sheet_1.cell(i,16).value
# params_actual = sheet_1.cell(i,17).value
# case_status = sheet_1.cell(i,18).value
values = (module,ID,UseCase,url,method,params,headers,body,status_code,code_to_compare,code_aclual,params_to_compare)
#执行sql语句插入数据
cursor.execute(sql,values)
conn.commit()
cursor.close()#先关闭游标
conn.close()#再关闭数据库连接
插入成功
网友评论