Python将Excel数据写入数据库实例

一、引言

工作中，对于重复性很高技术性不那么强的工作，通过更简单的方式去实现能减轻工作的压力提高自己的工作效率。对着Excel将里面的数据录入数据库也就是这样一个场景，50条摸摸鱼就录了，1000条呢？简化重复写入的操作，一定程度提高录入数据的准确率。以下就是使用Python将Excel数据写入数据库的简单案例。

二、涉及技术

• Excel数据读取
• 数据库连接
• 数据写入
• 事务处理
• 关联表处理

三、实现步骤

3.1 环境准备

mysql-connector-python 是 MySQL 官方提供的 Python 连接器和驱动。

pip install openpyxl mysql-connector-python

3.2 Excel数据

假设有一个Excel文件data.xlsx（emmm…都不用假设了，就是汇总表格数据，需要的保留，不需要的删除，场景假设就是医生的诊断单），其中包含患者、医生和病因信息，需要分别写入到patients、doctors和causes三个表中。

待处理的Excel文件

3.3 数据库结构

• Patients表：包含患者ID和姓名。

  DROP TABLE IF EXISTS `Patients`;
  CREATE TABLE `Patients`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 115 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

• Doctors 表：包含医生ID和姓名。

  DROP TABLE IF EXISTS `Doctors`;
  CREATE TABLE `Doctors`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 92 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

• Diagnoses表：包含就诊记录ID、患者ID、医生ID和病情描述，其中患者ID和医生ID是外键。

  DROP TABLE IF EXISTS `Diagnoses`;
  CREATE TABLE `Diagnoses`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `patient_id` int(11) NOT NULL,
    `doctor_id` int(11) NOT NULL,
    `cause` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `patient_id`(`patient_id`) USING BTREE,
    INDEX `doctor_id`(`doctor_id`) USING BTREE,
    CONSTRAINT `Diagnoses_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `Patients` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT `Diagnoses_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `Doctors` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 57 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

3.4 Python代码实现

import mysql.connector  
from openpyxl import load_workbook  

# Excel文件路径
file_path = r'C:\Users\30735\Desktop\test.xlsx'

# 数据库配置
db_config = {  
    'user': 'username',  
    'password': 'password',  
    'host': 'localhost',  
    'database': 'database',  
    'raise_on_warnings': True  
}  

# 初始化连接和游标
try:
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()
except Error as e:
    print(f"Error connecting to MySQL database: {e}")
    exit(1)

# 准备三个表的SQL插入语句模板和查询语句模板
insert_patient_query = "INSERT INTO Patients (name) VALUES (%s)"
insert_doctor_query = "INSERT INTO Doctors (name) VALUES (%s)"
select_doctor_query = "SELECT * FROM Doctors WHERE name = %s"
insert_cause_query = "INSERT INTO Diagnoses (patient_id, doctor_id, cause) VALUES (%s, %s, %s)"

# 初始化字典以存储已插入患者和医生的ID
inserted_patients = {}
inserted_doctors = {}

try:
    # 加载Excel工作簿
    workbook = load_workbook(filename=file_path)
    sheet = workbook.active

    # 跳过第一行（表头），从第二行开始读取数据
    for row in sheet.iter_rows(min_row=2, values_only=True):
        patient_name = row[0]
        doctor_name = row[1]
        cause_description = row[2]

        # 检查患者是否已经插入，如果没有则插入并获取ID
        if patient_name not in inserted_patients:
            cursor.execute(insert_patient_query, (patient_name,))
            inserted_id = cursor.lastrowid
            inserted_patients[patient_name] = inserted_id
        patient_id = inserted_patients[patient_name]

        # 检查医生是否已经插入
        if doctor_name not in inserted_doctors:
            # 检查医生是否已存在于数据库中
            cursor.execute(select_doctor_query, (doctor_name,))
            doctor = cursor.fetchone()
            if doctor is None:
                # 如果不存在，则插入医生并获取ID
                cursor.execute(insert_doctor_query, (doctor_name,))
                inserted_id = cursor.lastrowid
            else:
                # 如果已存在，则获取医生的ID
                inserted_id = doctor[0]  # 假设医生ID是查询结果的第一列
            inserted_doctors[doctor_name] = inserted_id
        doctor_id = inserted_doctors[doctor_name]

        # 插入就诊记录，使用患者和医生的ID
        cursor.execute(insert_cause_query, (patient_id, doctor_id, cause_description))

        # 提交事务
    cnx.commit()

except Error as e:
    if cnx.is_connected():
        cursor.close()
        cnx.rollback()  # 如果发生错误，回滚事务
    print(f"Error inserting data into the database: {e}")

finally:
    if cnx.is_connected():
        cursor.close()
        cnx.close()
        print("Database connection closed.")

print("数据已成功写入数据库。")

四、延伸

使用mysql-connector-python还可以查询MySQL数据库的很多其他信息。

1. 数据库连接

   使用用户名、密码、主机名和端口号建立到 MySQL 服务器的连接。

   支持 SSL 连接，增强数据传输的安全性。

   可以配置连接池，用于管理多个数据库连接，提高性能。

2. 执行 SQL 查询

   执行 SELECT 语句，从数据库中检索数据。

   执行 INSERT、UPDATE 和 DELETE 语句，修改数据库中的数据。

   执行 DDL（数据定义语言）语句，如 CREATE、ALTER 和 DROP，用于管理数据库结构。

3. 事务管理

   支持 ACID 属性的事务，确保数据的完整性和一致性。

   提供提交（commit）和回滚（rollback）操作，以控制事务的边界和结果。

4. 参数化查询

   允许使用参数化查询，防止 SQL 注入攻击，提高查询的安全性。

   参数化查询也使得代码更加清晰和易于维护。

5. 结果集处理

   将查询结果作为字典或元组的列表返回，方便 Python 代码处理。

   提供游标（cursor）对象，用于遍历和访问查询结果。

6. 错误处理

   提供异常处理机制，当连接失败、查询错误或发生其他数据库相关问题时，可以捕获和处理这些异常。

7. 批量操作

   支持批量插入、更新或删除操作，提高数据处理效率。

8. 存储过程和函数

   调用 MySQL 中的存储过程和函数，执行预定义的数据库操作。

9. 复制和负载均衡

   支持连接到 MySQL 复制集，实现读写分离读取和负载均衡。

10. 二进制数据处理

    可以处理二进制数据，如 BLOB 和 TEXT 类型字段，允许在 Python 和 MySQL 之间传输二进制数据。

11. 字符集和编码

    支持设置连接和查询的字符集和编码，确保字符数据的正确传输和处理。

12. 其他高级功能

    提供对预处理语句的支持，用于优化重复执行的查询。

    支持多种认证插件，如 Unix 套接字认证和 PAM 认证。

    允许配置连接的超时时间和重试策略。