Python实现Excel数据高效存入MySQL数据库的方法

一些基础的操作吧

一、准备工作

在开始之前，确保您已经安装了以下必要的工具和库：

1. Python环境：建议使用Python 3.x版本。
2. MySQL数据库：确保MySQL服务正在运行，并且您有足够的权限进行数据操作。
3. 必要的Python库：
4. pymysql：用于连接和操作MySQL数据库。
5. pandas：用于数据分析和处理。

二、连接MySQL数据库

首先，我们需要编写代码来连接MySQL数据库。以下是一个示例代码：

# 数据库配置

DB_CONFIG = { 'host': 'localhost',

'port': 3306,

'user': 'root',

'password': '@@@@',

'db': 'test',

'charset': 'utf8' }

# 动态创建 MySQL 表
def create_table(conn, table_name: str, columns: List[str], dtypes: Dict[str, str]):
    with conn.cursor() as cursor:
        # 构建 SQL 语句
        columns_with_types = [f"`{col}` {dtypes[col]}" for col in columns]
        sql = f"CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, {', '.join(columns_with_types)})"
        # 执行 SQL 语句
        cursor.execute(sql)
        print(f"表 {table_name} 创建成功")

# 连接 MySQL 数据库
conn = pymysql.connect(**DB_CONFIG)

三、读取Excel文件

接下来，我们需要读取Excel文件中的数据。以下是一个示例代码：

# 读取 Excel 文件
def read_excel(file_path: str) -> pd.DataFrame:
    # 读取 Excel 文件
    df = pd.read_excel(file_path)
    # 打印前 5 行数据
    print("Excel 数据预览：")
    print(df.head())
    return df

四、将数据导入MySQL表

最后，我们将读取到的数据导入到MySQL表中。以下是一个示例代码：

# 插入数据
def insert_data(conn, table_name: str, data: List[Dict]):
    with conn.cursor() as cursor:
        # 构建 SQL 语句
        columns = data[0].keys()
        placeholders = ', '.join(['%s'] * len(columns))
        sql = f"INSERT IGNORE INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})"
        # 插入数据
        for row in data:
            values = tuple(row.values())
            cursor.execute(sql, values)
        print(f"数据插入成功，共插入 {len(data)} 条记录")

五、完整代码示例

将上述步骤整合到一个完整的脚本中，以下是一个完整的示例代码：

import pymysql
import pandas as pd
from typing import List, Dict

# *******************************************************
#  * 将 Excel 文件中的数据插入到 MySQL 数据库中
#  *****************************************************

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '@@@@',
    'db': 'test',
    'charset': 'utf8'
}

# Excel 文件路径
EXCEL_FILE = r'C:\Users\ger\Desktop\20250105192033.xlsx'  # 替换为你的 Excel 文件路径
# 目标表名
TABLE_NAME = 'excel_data'  # 替换为你的目标表名

# 读取 Excel 文件
def read_excel(file_path: str) -> pd.DataFrame:
    # 读取 Excel 文件
    df = pd.read_excel(file_path)
    # 打印前 5 行数据
    print("Excel 数据预览：")
    print(df.head())
    return df

# 动态创建 MySQL 表
def create_table(conn, table_name: str, columns: List[str], dtypes: Dict[str, str]):
    with conn.cursor() as cursor:
        # 构建 SQL 语句
        columns_with_types = [f"`{col}` {dtypes[col]}" for col in columns]
        sql = f"CREATE TABLE IF NOT EXISTS {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, {', '.join(columns_with_types)})"
        # 执行 SQL 语句
        cursor.execute(sql)
        print(f"表 {table_name} 创建成功")

# 插入数据
def insert_data(conn, table_name: str, data: List[Dict]):
    with conn.cursor() as cursor:
        # 构建 SQL 语句
        columns = data[0].keys()
        placeholders = ', '.join(['%s'] * len(columns))
        sql = f"INSERT IGNORE INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})"
        # 插入数据
        for row in data:
            values = tuple(row.values())
            cursor.execute(sql, values)
        print(f"数据插入成功，共插入 {len(data)} 条记录")

# 主函数
def main():
    # 读取 Excel 文件
    df = read_excel(EXCEL_FILE)
    # 将 NaN 替换为 0
    df = df.fillna(0)
    # 动态生成列名和数据类型
    columns = df.columns.tolist()
    dtypes = {
        col: 'VARCHAR(255)' if df[col].dtype == 'object' else 'INT' if df[col].dtype == 'int64' else 'FLOAT'
        for col in columns
    }

    # 连接 MySQL 数据库
    conn = pymysql.connect(**DB_CONFIG)

    try:
        # 创建表
        create_table(conn, TABLE_NAME, columns, dtypes)

        # 将 DataFrame 转换为字典列表
        data = df.to_dict(orient='records')

        # 插入数据
        insert_data(conn, TABLE_NAME, data)

        # 提交事务
        conn.commit()
    except Exception as e:
        # 回滚事务
        conn.rollback()
        print(f"发生错误: {e}")
    finally:
        # 关闭连接
        conn.close()

# 运行程序
if __name__ == '__main__':
    main()

更多精彩内容https://mp.weixin.qq.com/s/p4R7IA1ADsqi1J9vOL63Cg?payreadticket=HJPJ07g452MSffzV5HTLdrcZPUZ77pcbggzq7_Cmw6qTF6646DBGWHAG490sKon-9KI5yQg&poc_token=HL9P72ej8Sf-aDPRxlRr5A3ys2hQHw1ZSATvf-Pz

作者：qweqwety