mysqlconnector将EXCEL表数据导入数据库

测试excel和脚本放在同一个目录

 1 #!/usr/bin/env python
 2 #coding=utf-8
 3 import xlrd
 4 import mysql.connector
 5 #读取EXCEL中内容到数据库中
 6 testCase = xlrd.open_workbook(‘test002.xlsx‘)
 7 table = testCase.sheet_by_index(0)
 8 val = []
 9 for i in range(1, table.nrows):
10     api_purpose = table.cell(i,1).value
11     request_url = table.cell(i,2).value
12     request_method = table.cell(i,3).value
13     request_data_type =  table.cell(i,4).value
14     request_data = table.cell(i,5).value
15     assert_method= table.cell(i,6).value
16     check_point =  table.cell(i,7).value
17     correlation =  table.cell(i,8).value
18     active = table.cell(i,9).value
19     creater =  table.cell(i,10).value
20     val.append( (api_purpose,request_url,request_method,request_data_type,request_data,assert_method,check_point,correlation,active,creater))



28 conn = mysql.connector.connect(
29     host="xxxxxxxx",
30     user="xxxxxx",
31     passwd="xxxx",
32     database="autotestcase")
33 
34 
35 cursor=conn.cursor()
36 
37 
38 sql = "INSERT INTO testcase (api_purpose,request_url,request_method,request_data_type,request_data,assert_method,check_point,correlation,active,creater) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
39 cursor.executemany(sql, val)
40 conn.commit()
41 print(cursor.rowcount, "记录插入成功。")
42 
43 cursor.close()
44 conn.close()