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數據庫!')