python 将execl测试数据导入数据库操作

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数据库!')