如何将Excel表格中的数据导入到MySQL数据库中（利用Python的xlrd和openpyxl模块 分别实现批量新增）

 

实验环境：

PyCharm 2018.1

CentOS Linux 7

实验前提：

1.已完成虚拟环境的创建以及pycharm连接linux操作

2.在linux上已经安装mysql数据库（mariadb也可以）

 

方法1： 利用xlrd模块，实现将excel表格中的数据导入到mysql数据库中

1.创建excel文件并写入数据

新建 test.xlsx 文件，并写入数据：

2.设计数据库表结构

mysql -uroot -pwestos

create database test_excel charset=utf8;

use test_excel;

create table `excel_message` (
  `num` int(20) not null auto_increment,
  `name` varchar(40) not null,
  `age` int(20) not null,
  `classes` varchar(40) not null,
  `score` int(20) not null,
  primary key (`num`)
) engine=InnoDB auto_increment=1 default charset=utf8mb4;

3.安装软件包

首先需要安装xlrd与pymysql包
pip install xlrd
pip install pymysql

# 如果安装失败，则临时指定一个镜像源
pip install xlrd -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

4.编写代码

注意：我这里需要将test.xlsx文件放到项目的根目录下

# 使用xlrd将excel表格导入mysql数据库中

import pymysql
import xlrd

# workbook = xlrd.open_workbook(filename=r'D:\file\test.xlsx')

# 打开数据所在的工作簿
book = xlrd.open_workbook(r'test.xlsx')
# 选择存有数据的工作表
sheet = book.sheet_by_name("Sheet1")

# 连接MySQL数据库
conn = pymysql.connect(
        # host='192.168.56.20',
        host='localhost',
        user='root',
        passwd='westos',
        db='test_excel',
        port=3306,
        charset='utf8'
        )

# 获得游标
cur = conn.cursor()
# 创建插入SQL语句
query = 'insert into excel_message (num, name, age, classes, score) values (%s, %s, %s, %s, %s)'
# for循环迭代读取xls文件中的每行数据, 从第二行开始因为需要跳过标题行
# 注意：xlrd方式表格的列标和行标都是从0开始计算
for r in range(1, sheet.nrows):
      num    = sheet.cell(r,0).value
      name   = sheet.cell(r,1).value
      age    = sheet.cell(r,2).value
      classes  = sheet.cell(r,3).value
      score  = sheet.cell(r,4).value
      values = (num, name, age, classes, score)
      # 执行sql语句
      cur.execute(query, values)
cur.close()
conn.commit()
conn.close()

# ncols和nrows表示获取excel表格的有效列数与行数
columns = str(sheet.ncols)
rows = str(sheet.nrows-1)

# 打印输出信息
print ("成功导入 " +columns + " 列 " + rows + " 行数据到MySQL数据库!")

5.测试

(venv) [root@localhost fast]# python test_xlrd.py
成功导入 5 列 6 行数据到MySQL数据库!

MariaDB [test_excel]> select * from excel_message;

 

方法2： 利用openpyxl模块，实现将excel表格中的数据导入到mysql数据库中

1.清空表数据

MariaDB [test_excel]> TRUNCATE TABLE excel_message;
MariaDB [test_excel]> select * from excel_message;

2.安装软件包

pip install openpyxl  -i https://pypi.tuna.tsinghua.edu.cn/simple

3.编写代码

# 使用openpyxl将excel表格导入mysql数据库中

import pymysql
from openpyxl import load_workbook

# 打开工作薄与工作表
wb = load_workbook('test.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

# 计算表格数据的有效行数rows
num = 1
while 1:
    cell = sheet.cell(row=num, column=1).value
    if cell:
        num = num + 1
    else:
        # print(num)
        break
rows = num-1
# print(rows)


# 连接MySQL数据库
conn = pymysql.connect(
        # host='192.168.56.20',
        host='localhost',
        user='root',
        passwd='westos',
        db='test_excel',
        port=3306,
        charset='utf8'
        )
# 获得游标
cur = conn.cursor()
# 创建插入SQL语句
query = 'insert into excel_message (num, name, age, classes, score) values (%s, %s, %s, %s, %s)'

# for循环迭代读取xls文件中的每行数据, 从第二行开始因为需要跳过标题行
# 注意：openpyxl方式表格列标与行标都是从1开始计算的
for r in range(2, rows+1):
      num    = sheet.cell(row=r, column=1).value
      name   = sheet.cell(row=r, column=2).value
      age    = sheet.cell(row=r, column=3).value
      classes  = sheet.cell(row=r, column=4).value
      score  = sheet.cell(row=r, column=5).value
      values = (num, name, age, classes, score)
      # print(values)
      # 执行sql语句
      cur.execute(query, values)
cur.close()
conn.commit()
conn.close()

# max_column：获取表格数据的有效列数
columns = str(sheet.max_column)
# 除去首行表头
rows = str(rows-1)

# print(sheet.max_row)
# print(sheet.max_column)

# 打印输出信息
print ("成功导入 " +columns + " 列 " + rows + " 行数据到MySQL数据库!")
# print("导入成功")

测试：

(venv) [root@localhost fast]# python test_openpyxl.py
成功导入 5 列 6 行数据到MySQL数据库!

MariaDB [test_excel]> select * from excel_message;