Python/MySQL实现Excel文件自动处理数据功能

下面就为您详细讲解Python/MySQL实现Excel文件自动处理数据功能的完整实例教程。

确定需求

我们要实现的功能是读取Excel文件中的数据，将其存储到MySQL数据库中，并对数据进行统计分析。因此，需要用到xlrd和pymysql这两个Python库。

安装依赖库

在开始之前，需要确保已经安装了xlrd和pymysql这两个依赖库。可以通过下面的命令来安装：

pip install xlrd
pip install pymysql

数据库建表

在开始之前，需要先建立一个数据表，用来存储Excel文件中的数据。我们可以通过下面的命令在MySQL数据库中创建一张表：

CREATE TABLE `excel_data` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

读取Excel文件并存储到MySQL数据库中

接下来，我们就开始编写Python代码，将Excel文件中的数据读取出来，并存储到MySQL数据库中。代码如下：

import xlrd
import pymysql

# 打开Excel文件
workbook = xlrd.open_workbook('data.xlsx')
# 获取第一个sheet
sheet = workbook.sheet_by_index(0)

# 连接MySQL数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset='utf8')
cursor = conn.cursor()

# 遍历Excel文件中的每一行数据，并将其存储到MySQL数据库中
for i in range(1, sheet.nrows):
    name = sheet.cell(i, 0).value
    score = sheet.cell(i, 1).value
    sql = "INSERT INTO `excel_data` (`name`, `score`) VALUES ('{}', {})".format(name, score)
    cursor.execute(sql)

conn.commit()
cursor.close()
conn.close()

在这段代码中，xlrd库用来读取Excel文件，pymysql库用来连接MySQL数据库，并将Excel文件中的数据存储到MySQL数据库中。需要注意的是，在存储数据的时候，要将字符串类型的数据用单引号包含起来，数值类型的数据则不需包含。

统计分析Excel文件数据

在将Excel文件中的数据存储到MySQL数据库中之后，我们可以对其进行统计分析，得出一些有用的信息。例如，我们要得到Excel文件中成绩最高的学生姓名和分数，可以运行下面的代码：

import pymysql

# 连接MySQL数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset='utf8')
cursor = conn.cursor()

# 查询成绩最高的学生姓名和分数
sql = "SELECT `name`, `score` FROM `excel_data` ORDER BY `score` DESC LIMIT 1"
cursor.execute(sql)
result = cursor.fetchone()

print('成绩最高的学生是{}，分数是{}'.format(result[0], result[1]))

cursor.close()
conn.close()

在这段代码中，我们使用了SELECT语句查询了成绩最高的学生姓名和分数，并使用了ORDER BY和LIMIT关键字进行排序和限制结果集的数量。查询结果使用fetchone()方法获取。

示例说明

假设我们有一个Excel文件，名为data.xlsx，里面记录了四位学生的姓名和分数，内容如下：

姓名	成绩
张三	95
李四	88
王五	66
赵六	78

我们的程序需要将这些数据读取出来，并存储到MySQL数据库中。首先，我们需要在MySQL数据库中创建一个数据库和一张名为excel_data的数据表。然后，运行下面的代码将Excel文件中的数据存储到MySQL数据库中：

import xlrd
import pymysql

# 打开Excel文件
workbook = xlrd.open_workbook('data.xlsx')
# 获取第一个sheet
sheet = workbook.sheet_by_index(0)

# 连接MySQL数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset='utf8')
cursor = conn.cursor()

# 遍历Excel文件中的每一行数据，并将其存储到MySQL数据库中
for i in range(1, sheet.nrows):
    name = sheet.cell(i, 0).value
    score = sheet.cell(i, 1).value
    sql = "INSERT INTO `excel_data` (`name`, `score`) VALUES ('{}', {})".format(name, score)
    cursor.execute(sql)

conn.commit()
cursor.close()
conn.close()

接着，我们可以运行下面的代码，查询成绩最高的学生姓名和分数：

import pymysql

# 连接MySQL数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset='utf8')
cursor = conn.cursor()

# 查询成绩最高的学生姓名和分数
sql = "SELECT `name`, `score` FROM `excel_data` ORDER BY `score` DESC LIMIT 1"
cursor.execute(sql)
result = cursor.fetchone()

print('成绩最高的学生是{}，分数是{}'.format(result[0], result[1]))

cursor.close()
conn.close()

输出结果为：

成绩最高的学生是张三，分数是95.0

这样，我们就实现了一个简单的Python/MySQL实现Excel文件自动处理数据功能的示例。