python批量导入excle数据到mysql数据库，实操可用！

import xlrd
import pymysql  //需要提前装好xlrd和pymysql两个库，直接使用pip install xlrd/pymysql 安装即可，比较简单。
import datetime
def open_excel():
    try:
        book = xlrd.open_workbook("C://Users//hp//XXX… …//XXX集.xlsx") #文件名，可以放绝对路径，相对路径也行
        print("open excel file succeed!")
    except:
        print("open excel file failed!")
    try:
        sheet = book.sheet_by_name("Sheet1")  #execl里面的worksheet1
        print("open excel sheet succeed!")
        return sheet
    except:
        print("locate worksheet in excel failed!") 
def insert_deta():
    db = pymysql.connect(
        host="127.0.0.1",
        user="root",
        passwd="XXXXXX",        #密码
        db="XXXXXX"             #数据库名字,
        charset='utf8')         #编码格式，注意不要写成utf-8
    print("connect to mysql server succceed")

    sheet = open_excel()
    cursor = db.cursor()
    sql = "INSERT INTO " + "数据库中表名字" + "(title,en_title,img,level,teaching_years, occupation,certificate,mobile,comment,del)\
        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"  #例如我这样是一一对应表中的字段，也可以不写表字段名，那就是全部插入，详细的可以看我写的原生SQL写法。
    for i in range(1, sheet.nrows): #第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
        name = sheet.cell(i,0).value #取第i行第0列
        en_title = sheet.cell(i,0).value #sheet.cell(i,1).value#取第i行第1列，下面依次类推
        level = sheet.cell(i, 2).value  #2
        img = "non-existent"#sheet.cell(i,2).value
        teaching_years = sheet.cell(i, 3).value
        occupation = sheet.cell(i,4).value
        certificate = sheet.cell(i, 5).value
             
        mobile = sheet.cell(i, 7).value
        comment = sheet.cell(i, 8).value   
        delete = "0" #sheet.cell(i, 9).value
        #addtime = datetime.datetime.now()
        #deltime = "0000-00-00 00:00:00"
        #updatetime = "0000-00-00 00:00:00"

        value = (name,en_title,img,level,teaching_years,occupation,certificate,mobile,comment,delete)
        print(value)
      
        cursor.execute(sql,value) #执行sql语句
        db.commit()
    cursor.close() #关闭连接
    db.close()#关闭数据
''' '''''''''''''''''''''
insert_deta()
print ("ok ")