如何将excel数据快速导入数据库
import os
import pandas as pd
import pymysql
import numpy as np
from dotenv import load_dotenv, find_dotenv
# ✅ 加载 config.env 文件
env_path = find_dotenv(filename="config.env")
if not env_path:
raise FileNotFoundError("❌ 未找到 config.env 文件，请确保它与脚本在同一目录下")
load_dotenv(env_path)
# ✅ 获取环境变量
excel_path = os.getenv("EXCEL_PATH")
db_host = os.getenv("DB_HOST")
db_user = os.getenv("DB_USER")
db_password = os.getenv("DB_PASSWORD")
db_name = os.getenv("DB_NAME")
table_name = os.getenv("DB_TABLE")
# ✅ 打印调试信息（可注释）
print("???? Excel 路径:", excel_path)
print("???? 数据库:", db_name, "| 表名:", table_name)
# ✅ 检查路径是否存在
if not excel_path or not os.path.exists(excel_path):
raise ValueError("❌ Excel 路径无效，请检查 EXCEL_PATH 配置和文件是否存在")
# ✅ 读取 Excel 文件
df = pd.read_excel(excel_path)
print(f"✅ 成功读取 Excel，共 {len(df)} 行")
# ✅ 替换 NaN 和 NaT 为 None，确保兼容 pymysql
df = df.replace({np.nan: None, pd.NaT: None})
# 或者：df = df.applymap(lambda x: None if pd.isna(x) else x)
# ✅ 连接 MySQL 数据库
conn = pymysql.connect(
host=db_host,
user=db_user,
password=db_password,
database=db_name,
charset='utf8mb4'
)
cursor = conn.cursor()
# ✅ 构造 INSERT SQL
cols = ",".join([f"`{col}`" for col in df.columns])
placeholders = ",".join(["%s"] * len(df.columns))
insert_sql = f"INSERT INTO `{table_name}` ({cols}) VALUES ({placeholders})"
# ✅ 插入数据
try:
cursor.executemany(insert_sql, df.values.tolist())
conn.commit()
print(f"✅ 成功插入 {cursor.rowcount} 行数据到表 `{table_name}`")
except Exception as e:
conn.rollback()
print("❌ 插入数据失败:", str(e))
# 可选：定位第几行出错（调试用）
for i, row in enumerate(df.values.tolist()):
try:
cursor.execute(insert_sql, row)
except Exception as err:
print(f"⚠️ 第 {i} 行插入失败：{err}")
print("???? 数据内容:", row)
break
finally:
cursor.close()
conn.close()
print("???? 数据库连接已关闭")