HOOOS

Python自动化：将Excel数据导入MySQL数据库的完整指南

0 3 数据搬运工小李 PythonMySQLExcel数据导入
Apple

还在手动将Excel数据复制粘贴到MySQL数据库？太out啦！今天就教你如何用Python写个脚本，一键搞定数据导入，解放你的双手！

准备工作

首先，确保你已经安装了以下Python库：

  • pandas: 用于读取Excel文件。
  • mysql.connector 或 SQLAlchemy: 用于连接和操作MySQL数据库。

你可以使用pip来安装它们：

pip install pandas mysql-connector-python  # 或者 pip install sqlalchemy

当然，别忘了你的MySQL数据库服务要正常运行，并且你有相应的权限进行数据导入。

核心代码

下面是一个示例脚本，它使用pandas读取Excel文件，然后使用mysql.connector将数据插入到MySQL数据库中。

import pandas as pd
import mysql.connector

# 1. 数据库连接配置
mysql_config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'your_host',
    'database': 'your_database'
}

# 2. Excel文件路径
excel_file = 'your_excel_file.xlsx'

# 3. MySQL表名
mysql_table = 'your_table'


def import_excel_to_mysql(excel_file, mysql_table, mysql_config):
    try:
        # 3.1 读取Excel文件
        df = pd.read_excel(excel_file)

        # 3.2 连接到MySQL数据库
        mydb = mysql.connector.connect(**mysql_config)
        mycursor = mydb.cursor()

        # 3.3 构建插入SQL语句
        columns = ', '.join(df.columns)
        placeholders = ', '.join(['%s'] * len(df.columns))
        sql = f"""INSERT INTO `{mysql_table}` ({columns}) VALUES ({placeholders})"""

        # 3.4 准备数据
        data = df.values.tolist()

        # 3.5 执行批量插入
        mycursor.executemany(sql, data)

        # 3.6 提交更改
        mydb.commit()

        print(f"成功导入{len(df)}行数据到表 {mysql_table}")

    except Exception as e:
        print(f"导入失败: {e}")
    finally:
        # 3.7 关闭连接
        if mydb.is_connected():
            mycursor.close()
            mydb.close()
            print("数据库连接已关闭")

# 调用函数，开始导入
if __name__ == '__main__':
    import_excel_to_mysql(excel_file, mysql_table, mysql_config)

代码解释：

  1. 数据库连接配置: 你需要修改mysql_config字典，填入你的MySQL数据库的用户名、密码、主机地址和数据库名。
  2. Excel文件路径: 将excel_file变量设置为你的Excel文件的路径。
  3. MySQL表名: 将mysql_table变量设置为你要将数据导入的MySQL表名。
  4. 读取Excel文件: pd.read_excel(excel_file) 使用pandas读取Excel文件，并将数据存储在一个DataFrame对象中。
  5. 连接到MySQL数据库: mysql.connector.connect(**mysql_config) 使用mysql.connector库连接到MySQL数据库。
  6. 构建插入SQL语句: 这段代码动态地构建了插入数据的SQL语句。columns变量包含了Excel表格的列名，placeholders变量包含了占位符%s，用于后续的数据插入。
  7. 准备数据: df.values.tolist()将DataFrame对象转换为一个列表，其中每个元素都是一行数据。
  8. 执行批量插入: mycursor.executemany(sql, data)使用executemany()方法批量插入数据，提高效率。
  9. 提交更改: mydb.commit()提交对数据库的更改。
  10. 关闭连接: 确保在完成操作后关闭数据库连接，释放资源。

使用 SQLAlchemy (可选)

如果你更喜欢使用 SQLAlchemy，可以修改代码如下：

import pandas as pd
from sqlalchemy import create_engine

# 1. 数据库连接配置
mysql_config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'your_host',
    'database': 'your_database'
}

# 2. Excel文件路径
excel_file = 'your_excel_file.xlsx'

# 3. MySQL表名
mysql_table = 'your_table'


def import_excel_to_mysql_sqlalchemy(excel_file, mysql_table, mysql_config):
    try:
        # 3.1 读取Excel文件
        df = pd.read_excel(excel_file)

        # 3.2 构建数据库连接字符串
        engine_string = f"mysql+mysqlconnector://{mysql_config['user']}:{mysql_config['password']}@{mysql_config['host']}/{mysql_config['database']}"

        # 3.3 创建 SQLAlchemy Engine
        engine = create_engine(engine_string)

        # 3.4 将 DataFrame 写入 MySQL
        df.to_sql(mysql_table, engine, if_exists='append', index=False)

        print(f"成功导入{len(df)}行数据到表 {mysql_table}")

    except Exception as e:
        print(f"导入失败: {e}")

# 调用函数，开始导入
if __name__ == '__main__':
    import_excel_to_mysql_sqlalchemy(excel_file, mysql_table, mysql_config)

代码解释：

  1. 数据库连接配置: 和之前一样，修改mysql_config字典。
  2. 构建数据库连接字符串: engine_string变量包含了用于连接MySQL数据库的连接字符串，包括用户名、密码、主机地址和数据库名。
  3. 创建 SQLAlchemy Engine: create_engine(engine_string)创建了一个SQLAlchemy Engine对象，用于与数据库进行交互。
  4. 将 DataFrame 写入 MySQL: df.to_sql(mysql_table, engine, if_exists='append', index=False)使用to_sql()方法将DataFrame对象写入MySQL数据库。if_exists='append'表示如果表已存在，则将数据追加到表中。index=False表示不将DataFrame的索引写入数据库。

错误处理

在实际应用中，可能会遇到各种错误，例如：

  • Excel文件不存在或格式错误。
  • 数据库连接失败。
  • SQL语句错误。
  • 数据类型不匹配。

为了提高脚本的健壮性，建议添加适当的错误处理机制。例如，可以使用try...except语句来捕获异常，并打印错误信息。

进阶技巧

  • 处理大型Excel文件: 如果Excel文件非常大，可以分批读取数据，避免内存溢出。
  • 数据清洗和转换: 在将数据导入数据库之前，可以使用pandas对数据进行清洗和转换，例如去除空格、转换数据类型等。
  • 日志记录: 使用logging模块记录脚本的运行日志，方便排查问题。
  • 配置化: 将数据库连接信息、Excel文件路径等配置信息放在一个单独的配置文件中，方便修改和管理。

总结

通过本文的介绍，你已经掌握了使用Python将Excel数据导入MySQL数据库的基本方法。无论是使用mysql.connector还是SQLAlchemy，都可以实现自动化数据导入。希望这个脚本能帮助你提高工作效率，告别手动复制粘贴的时代！快去试试吧！别忘了根据你的实际情况修改代码哦！

点评评价

captcha
健康