python处理完数据导入数据库_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数据库!')