python实现读取excel写入mysql的小工具详解

下面我将详细讲解“python实现读取excel写入mysql的小工具详解”的完整实例教程。

介绍

在实际应用场景中，我们很可能需要将Excel表格中的数据导入到数据库中，其中MySQL是比较常用的关系型数据库。本文将介绍如何使用Python实现读取Excel并将数据写入MySQL的小工具。

需求分析

我们需要实现的功能是将Excel表格的内容批量导入到MySQL中，导入的表格示例如下：

姓名	学科	成绩
张三	数学	90
李四	英语	80
王五	语文	95

导入到MySQL中的表结构如下：

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `subject` varchar(50) NOT NULL COMMENT '学科',
  `score` float NOT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分数表';

实现步骤

在Python中，我们可以使用pandas库来读取Excel表格的数据，并使用MySQL Connector/Python库来连接MySQL数据库并实现数据批量写入。

具体实现步骤如下：

1. 安装必要的库

pip install pandas mysql-connector-python

1. 引入库并连接MySQL数据库

```python
import pandas as pd
import mysql.connector

# 连接数据库
config = {
'host': 'localhost',
'user': 'root',
'password': 'password',
'database': 'test',
'charset': 'utf8mb4'
}
cnx = mysql.connector.connect(**config)
```

1. 读取Excel表格并转化为DataFrame格式

```python
# 读取Excel表格
data = pd.read_excel('score.xlsx')

# 转化为DataFrame格式
df = pd.DataFrame(data, columns=['姓名', '学科', '成绩'])
```

1. 批量写入数据到MySQL中

```python
# 获得MySQL游标对象
cursor = cnx.cursor()

# 把DataFrame转换成tuple
tuples = [tuple(x) for x in df.to_numpy()]

# 定义写入MySQL的命令
stmt = "INSERT INTO score(name, subject, score) VALUES (%s, %s, %s)"

# 执行写入操作
cursor.executemany(stmt, tuples)

# 提交更改
cnx.commit()

# 关闭连接
cursor.close()
cnx.close()
```

1. 最后得到的结果是数据成功写入到了MySQL的score表中。

示例说明

以下是示例1，展示如何读取一个本地的Excel文件score.xlsx中的数据，并将数据写入到MySQL中的score表中。

import pandas as pd
import mysql.connector

# 连接数据库
config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'password',
    'database': 'test',
    'charset': 'utf8mb4'
}
cnx = mysql.connector.connect(**config)

# 读取Excel表格
data = pd.read_excel('score.xlsx')

# 转化为DataFrame格式
df = pd.DataFrame(data, columns=['姓名', '学科', '成绩'])

# 获得MySQL游标对象
cursor = cnx.cursor()

# 把DataFrame转换成tuple
tuples = [tuple(x) for x in df.to_numpy()]

# 定义写入MySQL的命令
stmt = "INSERT INTO score(name, subject, score) VALUES (%s, %s, %s)"

# 执行写入操作
cursor.executemany(stmt, tuples)

# 提交更改
cnx.commit()

# 关闭连接
cursor.close()
cnx.close()

以下是示例2，展示如何读取一个网络上的Excel文件https://example.com/score.xlsx中的数据，并将数据写入到MySQL中的score表中。

import pandas as pd
import requests
import mysql.connector

# 连接数据库
config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'password',
    'database': 'test',
    'charset': 'utf8mb4'
}
cnx = mysql.connector.connect(**config)

# 下载Excel表格
url = 'https://example.com/score.xlsx'
response = requests.get(url)
file = open('score.xlsx', 'wb')
file.write(response.content)
file.close()

# 读取Excel表格
data = pd.read_excel('score.xlsx')

# 转化为DataFrame格式
df = pd.DataFrame(data, columns=['姓名', '学科', '成绩'])

# 获得MySQL游标对象
cursor = cnx.cursor()

# 把DataFrame转换成tuple
tuples = [tuple(x) for x in df.to_numpy()]

# 定义写入MySQL的命令
stmt = "INSERT INTO score(name, subject, score) VALUES (%s, %s, %s)"

# 执行写入操作
cursor.executemany(stmt, tuples)

# 提交更改
cnx.commit()

# 关闭连接
cursor.close()
cnx.close()

以上就是这个小工具的详细实现步骤和示例说明。