使用python读取Excel导入MySQL，但无法读取xlsx

Question

目前使用的python版本为3.9版本 xlrd库为1.2版本 使用以下代码，无法读取.xlsx表格，只能用.xls表格，不知道怎么修改正确，代码如下，麻烦麻烦了

import pymysql
import xlrd
import sys
import time


def mysql_link(de_name):
    try:
        db = pymysql.connect(host="localhost", user="****",
                             passwd="****",
                             db=de_name,
                             charset='utf8mb4')
        return db
    except:
        print("could not connect to mysql server")


def open_excel(excel_file):
    try:
        book = xlrd.open_workbook(excel_file)  # 文件名，把文件与py文件放在同一目录下
        print(sys.getsizeof(book))
        return book
    except:
        print("open excel file failed!")


def store_to(db_name, table_name, excel_file):
    db = mysql_link(db_name)  # 打开数据库连接
    cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
    book = open_excel(excel_file)  # 打开excel文件
    sheets = book.sheet_names()  # 获取所有sheet表名
    for sheet in sheets:
        sh = book.sheet_by_name(sheet)  # 打开每一张表
        row_num = sh.nrows
        print(row_num)
        list = []  # 定义列表用来存放数据
        num = 0  # 用来控制每次插入的数量
        for i in range(1, row_num):  # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
            row_data = sh.row_values(i)  # 按行获取excel的值
            value = (
            row_data[0], row_data[1], row_data[2], row_data[3], row_data[4], row_data[5], row_data[6], row_data[7], \
            row_data[8], row_data[9], row_data[10], row_data[11], row_data[12], row_data[13], row_data[14], \
            row_data[15], row_data[16], row_data[17], row_data[18], row_data[19], row_data[20], row_data[21],
            row_data[22])
            list.append(value)  # 将数据暂存在列表
            num += 1
            if (num >= 10000):  # 每一万条数据执行一次插入
                print(sys.getsizeof(list))
                sql = "INSERT INTO " + table_name + " (id,job_number,call_type,customer_phone,customer_name,customer_segmentation,customer_code,while_1,IVR_one,IVR_two,IVR_three,while_2,start_time,terminal_time,call_duration,call_connet,work_range,customer_call_space,work_type,evaluate,Type_of_Hook,Evaluation_Channel,Comments_on_Wechat)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                cursor.executemany(sql, list)  # 执行sql语句

                num = 0  # 计数归零
                list.clear()  # 清空list
                print("worksheets: " + sheet + " has been inserted 10000 datas!")

    sql = "INSERT INTO " + table_name + " (id,job_number,call_type,customer_phone,customer_name,customer_segmentation,customer_code,while_1,IVR_one,IVR_two,IVR_three,while_2,start_time,terminal_time,call_duration,call_connet,work_range,customer_call_space,work_type,evaluate,Type_of_Hook,Evaluation_Channel,Comments_on_Wechat)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    cursor.executemany(sql, list)  # 执行sql语句

    print("worksheets: " + sheet + " has been inserted " + str(row_num) + " datas!")
    db.commit()  # 提交
    cursor.close()  # 关闭连接
    db.close()


if __name__ == '__main__':
    now_time_1 = time.strftime("%Y-%m-%d", time.localtime(int(time.time()) - 86400))
    z = 'E:/data/huawu_/jan/'
    tar_file = z + now_time_1 + '呼入.xlsx'
    store_to('huawu', 'huawu_202112', 'D:/539138/Desktop/3-03/电话明细.xls')

Answer 1

xlrd 和 xlwt 只能读写 xls 文件
要用 openpyxl 才能读写 xlsx 文件。