mysqlconnector将EXCEL表数据导入数据库
测试excel和脚本放在同一个目录
1 测试excel和脚本放在同一个目录 2 #!/usr/bin/env python 3 #coding=utf-8 4 import xlrd 5 import mysql.connector 6 #读取EXCEL中内容到数据库中 7 testCase = xlrd.open_workbook('test002.xlsx') 8 table = testCase.sheet_by_index(0) 9 val = [] 10 for i in range(1, table.nrows): 11 api_purpose = table.cell(i,1).value 12 request_url = table.cell(i,2).value 13 request_method = table.cell(i,3).value 14 request_data_type = table.cell(i,4).value 15 request_data = table.cell(i,5).value 16 assert_method= table.cell(i,6).value 17 check_point = table.cell(i,7).value 18 correlation = table.cell(i,8).value 19 active = table.cell(i,9).value 20 creater = table.cell(i,10).value 21 val.append( (api_purpose,request_url,request_method,request_data_type,request_data,assert_method,check_point,correlation,active,creater)) 22 23 ＃通过mysqlconnector与数据库创建连接 24 conn = mysql.connector.connect( 25 host="xxxxxxxx", 26 user="xxxxxx", 27 passwd="xxxx", 28 database="autotestcase") 29 30 31 cursor=conn.cursor() 32 33 ＃创建sql语句 34 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)" 35 ＃执行sql语句 36 cursor.executemany(sql, val) 37 ＃提交 使执行的sql语句生效 38 conn.commit() 39 print(cursor.rowcount, "记录插入成功。") 40 41 cursor.close() 42 conn.close()
