mysqlconnector将EXCEL表数据导入数据库

时间：2021-09-22 22:23:01
mysqlconnector将EXCEL表数据导入数据库

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





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

 #!/usr/bin/env python

 #coding=utf-8

 import xlrd

 import mysql.connector

 #读取EXCEL中内容到数据库中

 testCase = xlrd.open_workbook('test002.xlsx')

 table = testCase.sheet_by_index(0)

 val = []

 for i in range(1, table.nrows):

     api_purpose = table.cell(i,1).value

     request_url = table.cell(i,2).value

     request_method = table.cell(i,3).value

     request_data_type =  table.cell(i,4).value

     request_data = table.cell(i,5).value

     assert_method= table.cell(i,6).value

     check_point =  table.cell(i,7).value

     correlation =  table.cell(i,8).value

     active = table.cell(i,9).value

     creater =  table.cell(i,10).value

     val.append( (api_purpose,request_url,request_method,request_data_type,request_data,assert_method,check_point,correlation,active,creater))

 ＃通过mysqlconnector与数据库创建连接

 conn = mysql.connector.connect(

     host="xxxxxxxx",

     user="xxxxxx",

     passwd="xxxx",

     database="autotestcase")

 cursor=conn.cursor()

 ＃创建sql语句

 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)"

 ＃执行sql语句

 cursor.executemany(sql, val)

 ＃提交 使执行的sql语句生效

 conn.commit()

 print(cursor.rowcount, "记录插入成功。")

 cursor.close()

 conn.close()






 


相关文章