利用xlrd库将excel数据导入MySQL

作者: NewForMe | 来源:发表于2019-02-21 21:26 被阅读0次

前几天去面试一份python工作，当时有道面试题需要将他那边给过来的数据用页面展示出来，这个展示数据页面就比较简单，我就是用django写了一个页面，将那些数据分页，导航到详细页而已，就是一开始对方给过来的数据就是一个excel表，里面大概有一万多条数据吧，所以我要先将数据弄进数据库，这样才方便操作。

这里用到的需要用到的库有xlrd,pymysql，没有的话需先自行pip安装。

excel表数据源

先根据excel表的数据创建mysql表，

DROP TABLE IF EXISTS `app01_product`;
CREATE TABLE `app01_product`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Asin` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `Total_reviews` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `Total_score` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6601 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

下面是源码：

# -*- coding: utf-8 -*-
# @Time    : 2019/2/19 14:52
# @Author  : Xin
# @File    : db.py
# @Software: PyCharm

import xlrd
import pymysql

def leading_in_detail():
    book = xlrd.open_workbook("1.xlsx")
    sheet = book.sheet_by_name("front")

    # 建立一个MySQL连接
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test')
    # 创建游标
    cursor = conn.cursor()
    # 创建插入SQL语句
    sql ="""INSERT INTO app01_product(Asin, Total_reviews, Total_score) VALUES (%s,%s,%s)"""

    # 创建一个for循环迭代读取xls文件每行数据的, 从第四行开始是要跳过标题和前面两空行
    for r in range(3,sheet.nrows):
        Asin=sheet.cell(r, 1).value
        Total_reviews=sheet.cell(r, 2).value
        Total_score = sheet.cell(r, 3).value
        values = (Asin,Total_reviews,Total_score)
        # 执行sql语句
        cursor.execute(sql, values)
        # 提交
        conn.commit()

    # 关闭游标
    cursor.close()
    # 关闭数据库连接
    conn.close()
    columns = str(sheet.ncols)
    rows = str(sheet.nrows)
    print("导入detail表成功")
    print("我刚导入了 " + columns + " 列 and " + rows + " 行数据到MySQL!")

if __name__=="__main__":
    leading_in_detail()

补充两点：

一、
当时将excel数据导入到mysql后发现一个问题，那就是那个日期变成了一串数据，网上搜索一下答案才知道，原来excel的日期是以序列数的形式存储的，即保存的日期实际是这个日期到1900-1-1相差的天数。因此导入数据之前应该先将日期数据格式化，直接右击“设置单元格格式”这种操作是无效的，下面再补充一下修改日期时间格式的步骤。

image.png

选择固定宽度，下一步

image.png
下一步

image.png
选择文本，点击完成

image.png

这时候再重新导入即可

二、
如果有安装Nivacat的话，其实还可以直接使用Nivacat工具来直接导入数据，下面简单说一下这种方法的步骤。

1. 先自主创建表

CREATE TABLE `test_date` (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `date_time` varchar(50) DEFAULT NULL,
  `money` varchar(50) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;