import xlrd
import pymysql
# 打开execl表
book = xlrd.open_workbook('XXXX测试用例.xlsx')
sheet = book.sheet_by_name('Sheet1')
# print(sheet.nrows)
# 创建mysql连接
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='123456',
db='demo1',
port=3306,
charset='utf8'
)
# 获得游标
cur = conn.cursor()
# 创建插入语句
query = 'insert into yongsheng(UseCaseNumber, TestIteam, TestSubItem,'
'Testoint, Precondition, ExecutionSteps,'
'ExpectedResults, ActualResult, Adopt,'
'DesignDate, ConclusionDate, Testers)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
# 创建一个for循环迭代读取xls文件每行数据的,
# 从第二行开始是要跳过标题行
# 括号里面1表示从第二行开始(计算机是从0开始数)
for r in range(1, sheet.nrows):
# (r, 0)表示第二行的0就是表里的A1:A1
UseCaseNumber = sheet.cell(r, 0).value
TestIteam = sheet.cell(r, 1).value
TestSubItem = sheet.cell(r, 2).value
Testoint = sheet.cell(r, 3).value
Precondition = sheet.cell(r, 4).value
ExecutionSteps = sheet.cell(r, 5).value
ExpectedResults = sheet.cell(r, 6).value
ActualResult = sheet.cell(r, 7).value
Adopt = sheet.cell(r, 8).value
DesignDate = sheet.cell(r, 9).value
ConclusionDate = sheet.cell(r, 10).value
Testers = sheet.cell(r, 11).value
values = (UseCaseNumber, TestIteam, TestSubItem,
Testoint, Precondition, ExecutionSteps,
ExpectedResults, ActualResult, Adopt,
DesignDate, ConclusionDate, Testers)
# 插入数据库
try:
# 检查db连接状态
conn.ping(reconnect=True)
cur.execute(query, values)
except Exception as e:
# 有异常,回滚事务
conn.rollback()
finally:
conn.close()
# 显示导入多少列
columns = str(sheet.ncols)
# 显示导入多少行
rows = str(sheet.nrows)
print('导入' + columns + '列' + rows + '行数据到MySQL数据库!')