Python Excel数据如何高效导入数据库？-杰瑞科技汇

核心思路

整个过程可以分解为以下几个步骤：

（图片来源网络，侵删）

准备工作：安装必要的 Python 库，准备好 Excel 文件和数据库。
读取 Excel 文件：使用 pandas 库读取 Excel 文件中的数据到一个 DataFrame 对象中。
连接数据库：使用相应的数据库连接库（如 pymysql, psycopg2）建立与数据库的连接。
数据导入：将 DataFrame 中的数据写入到数据库的指定表中，这是最关键的一步，有几种不同的方法,各有优劣。
关闭连接：完成操作后,关闭数据库连接。

第 1 步：准备工作

安装 Python 库

你需要安装以下库：

pandas: 用于数据处理和读取 Excel 文件。
openpyxl: pandas 读取 .xlsx 格式文件时需要依赖此库。
数据库连接库：根据你的数据库类型选择安装一个。
- MySQL: pip install pymysql
- PostgreSQL: pip install psycopg2-binary
- SQLite: Python 标准库自带,无需额外安装。

# 安装核心库
pip install pandas openpyxl
# 安装 MySQL 连接库 (示例)
pip install pymysql

准备示例文件

假设我们有一个名为 users.xlsx 的 Excel 文件,内容如下：

id	name	email	signup_date	is_active
1	Alice	alice@example.com	2025-01-15	1
2	Bob	bob@example.com	2025-02-20	0
3	Carol	carol@example.com	2025-03-10	1
4	David	david@example.com	2025-04-05	1

第 2 步：读取 Excel 文件

使用 pandas 的 read_excel 函数非常简单。

import pandas as pd
# 读取 Excel 文件
# sheet_name='Sheet1' 可以指定工作表名，如果文件只有一个工作表则可以省略
df = pd.read_excel('users.xlsx', sheet_name='Sheet1')
# 查看前几行数据
print("Excel 数据预览:")
print(df)
# 查看数据类型，这很重要，关系到后续数据库导入
print("\n数据类型:")
print(df.dtypes)

输出:

（图片来源网络，侵删）

Excel 数据预览:
   id     name                 email  signup_date  is_active
0   1    Alice  alice@example.com   2025-01-15          1
1   2      Bob     bob@example.com   2025-02-20          0
2   3    Carol   carol@example.com   2025-03-10          1
3   4    David   david@example.com   2025-04-05          1
数据类型:
id                  int64
name               object
email              object
signup_date    datetime64[ns]
is_active            int64
dtype: object

第 3 步：连接数据库

这里以 MySQL 和 SQLite 为例。

MySQL 连接示例

你需要确保 MySQL 服务正在运行，并且你知道用户名、密码、主机地址和数据库名。

import pymysql
# 数据库连接配置
db_config = {
    'host': 'localhost',      # 数据库主机地址
    'user': 'root',          # 数据库用户名
    'password': 'your_password', # 数据库密码
    'database': 'test_db',   # 要连接的数据库名
    'charset': 'utf8mb4'    # 字符集，推荐 utf8mb4
}
try:
    # 创建数据库连接
    connection = pymysql.connect(**db_config)
    print("成功连接到 MySQL 数据库！")
    # ... 在这里执行数据库操作 ...
except pymysql.MySQLError as e:
    print(f"连接数据库失败: {e}")
finally:
    # 确保连接被关闭
    if 'connection' in locals() and connection.open:
        connection.close()
        print("MySQL 连接已关闭。")

SQLite 连接示例

SQLite 非常方便，它是一个文件数据库，无需服务器，你只需要一个数据库文件名（如果不存在，它会自动创建）。

import sqlite3
# 数据库文件名
db_file = 'my_database.db'
try:
    # 创建数据库连接
    connection = sqlite3.connect(db_file)
    print(f"成功连接到 SQLite 数据库: {db_file}")
    # ... 在这里执行数据库操作 ...
except sqlite3.Error as e:
    print(f"连接数据库失败: {e}")
finally:
    # 确保连接被关闭
    if 'connection' in locals() and connection:
        connection.close()
        print("SQLite 连接已关闭。")

第 4 步：数据导入（核心部分）

这是最关键的一步，我们介绍三种主流方法：to_sql (推荐)、循环插入 和 批量插入。

使用 `pandas.to_sql()` (最简单、最推荐)

这是最直接、最高效的方法，pandas 已经为你封装好了所有底层逻辑。

优点:

代码极其简洁,一行代码即可完成。
内部处理了数据类型转换、SQL 语句构建等。
对于大量数据,性能很好。

注意:

需要安装 SQLAlchemy 库，它为 to_sql 提供了统一的数据库接口。
```
pip install SQLAlchemy
```

代码示例 (以 MySQL 为例):

import pandas as pd
from sqlalchemy import create_engine
import pymysql
# 1. 读取 Excel
df = pd.read_excel('users.xlsx')
# 2. 创建数据库连接引擎
# 格式: 'mysql+pymysql://用户名:密码@主机:端口/数据库名'
engine = create_engine('mysql+pymysql://root:your_password@localhost:3306/test_db')
# 3. 使用 to_sql 将数据导入数据库
# if_exists: 如果表已存在，如何处理，可选 'fail', 'replace', 'append'
# index: 是否将 DataFrame 的索引作为一列存入数据库
# chunksize: 分批插入的数据量，对于超大文件可以避免内存溢出
df.to_sql(
    name='users',      # 目标表名
    con=engine,        # 数据库连接引擎
    if_exists='append', # 如果表存在则追加数据
    index=False,       # 不写入 DataFrame 的索引
    chunksize=1000     # 每批插入 1000 行
)
print("数据已成功导入到 users 表！")

代码示例 (以 SQLite 为例):

import pandas as pd
from sqlalchemy import create_engine
import sqlite3
# 1. 读取 Excel
df = pd.read_excel('users.xlsx')
# 2. 创建数据库连接引擎
# SQLite 的格式非常简单
engine = create_engine('sqlite:///my_database.db')
# 3. 使用 to_sql 导入数据
df.to_sql(
    name='users',
    con=engine,
    if_exists='replace', # 如果表已存在则替换它
    index=False
)
print("数据已成功导入到 SQLite 数据库的 users 表！")

循环插入 (不推荐，仅作了解)

这种方法最直观，但性能最差,不适用于生产环境。

import pandas as pd
import pymysql
# 1. 读取 Excel
df = pd.read_excel('users.xlsx')
# 2. 连接数据库
connection = pymysql.connect(host='localhost', user='root', password='your_password', database='test_db')
cursor = connection.cursor()
# 3. 准备 SQL 插入语句 (使用 %s 作为占位符以防 SQL 注入)
sql = "INSERT INTO users (id, name, email, signup_date, is_active) VALUES (%s, %s, %s, %s, %s)"
# 4. 循环 DataFrame 并逐条插入
for index, row in df.iterrows():
    # 注意：日期类型可能需要特殊处理，row['signup_date'].strftime('%Y-%m-%d')
    cursor.execute(sql, tuple(row))
# 5. 提交事务
connection.commit()
# 6. 关闭连接
cursor.close()
connection.close()
print("数据已通过循环插入完成。")

批量插入 (性能较好，代码稍复杂)

这种方法比循环插入快得多,因为它减少了数据库通信的开销。

import pandas as pd
import pymysql
# 1. 读取 Excel
df = pd.read_excel('users.xlsx')
# 2. 连接数据库
connection = pymysql.connect(host='localhost', user='root', password='your_password', database='test_db')
cursor = connection.cursor()
# 3. 准备 SQL 插入语句
sql = "INSERT INTO users (id, name, email, signup_date, is_active) VALUES (%s, %s, %s, %s, %s)"
# 4. 将 DataFrame 转换为元组列表
data_to_insert = [tuple(x) for x in df.values]
# 5. 使用 executemany 进行批量插入
cursor.executemany(sql, data_to_insert)
# 6. 提交事务
connection.commit()
# 7. 关闭连接
cursor.close()
connection.close()
print("数据已通过批量插入完成。")

第 5 步：完整流程示例 (MySQL + `to_sql`)

下面是一个完整的、可以直接运行的脚本，它包含了创建表、导入数据和验证数据的全过程。

import pandas as pd
from sqlalchemy import create_engine
import pymysql
# --- 1. 准备数据 ---
# 假设 users.xlsx 文件存在
try:
    df = pd.read_excel('users.xlsx')
    print("成功读取 Excel 文件。")
except FileNotFoundError:
    print("错误：未找到 users.xlsx 文件，请确保文件在脚本同目录下。")
    exit()
# --- 2. 数据库连接配置 ---
DB_USER = 'root'
DB_PASSWORD = 'your_password'
DB_HOST = 'localhost'
DB_NAME = 'test_db'
# --- 3. 创建数据库连接引擎 ---
# 注意：如果表不存在，to_sql 默认不会创建它。
# 我们可以先手动创建表，或者在 to_sql 中使用 `if_exists='replace'` 来创建/替换表。
engine = create_engine(f'mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}')
# --- 4. (可选) 手动创建表 ---
# 这一步可以确保表结构符合预期，并处理一些特殊情况（如主键）
create_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    signup_date DATE,
    is_active BOOLEAN
);
"""
try:
    with engine.connect() as conn:
        conn.execute(create_table_sql)
        print("users 表已准备就绪。")
except Exception as e:
    print(f"创建表时出错: {e}")
    exit()
# --- 5. 使用 to_sql 导入数据 ---
try:
    df.to_sql(
        name='users',
        con=engine,
        if_exists='append', # 如果表已存在，追加数据
        index=False,
        chunksize=500
    )
    print("数据导入成功！")
except Exception as e:
    print(f"数据导入失败: {e}")
# --- 6. 验证数据 ---
print("\n--- 验证数据库中的数据 ---")
try:
    # 使用 pandas 直接读取数据库表来验证
    df_from_db = pd.read_sql_table('users', con=engine)
    print("从数据库读取的数据:")
    print(df_from_db)
except Exception as e:
    print(f"验证数据时出错: {e}")
# --- 7. 关闭连接 ---
# SQLAlchemy 引擎会在脚本结束时自动关闭，但显式关闭是好习惯
engine.dispose()
print("数据库连接已关闭。")

总结与建议

方法	优点	缺点	适用场景
`pandas.to_sql()`	代码简洁、性能好、功能强大	需要安装 `SQLAlchemy`	强烈推荐，几乎所有场景都适用，尤其是处理中小型数据集。
循环插入	逻辑简单，易于理解	性能极差，不适合大量数据	仅用于学习或插入极少数几条数据。
批量插入	性能较好，比循环快	代码比 `to_sql` 复杂，需要手动处理数据类型	当你无法使用 `SQLAlchemy` 或需要更精细控制时。

最佳实践:

首选 pandas.to_sql()：这是最现代、最 Pythonic 的方式。
处理 if_exists：根据你的需求选择 'fail' (报错), 'replace' (删除旧表重建), 或 'append' (追加数据)，在生产环境中，'append' 通常是最安全的选择。
关注数据类型：确保 Excel 中的数据类型（如日期）能正确映射到数据库的数据类型。pandas 通常能很好地处理。
分批处理：对于非常大的 Excel 文件（几十万行以上），使用 chunksize 参数可以避免内存不足的问题。
错误处理：始终使用 try...except 块来处理可能发生的数据库连接或操作错误。

Python Excel数据如何高效导入数据库？

核心思路

第 1 步：准备工作

安装 Python 库

准备示例文件

第 2 步：读取 Excel 文件

第 3 步：连接数据库

MySQL 连接示例

SQLite 连接示例

第 4 步：数据导入（核心部分）

使用 `pandas.to_sql()` (最简单、最推荐)

循环插入 (不推荐，仅作了解)

批量插入 (性能较好，代码稍复杂)

第 5 步：完整流程示例 (MySQL + `to_sql`)

总结与建议

99ANYc3cd6

Linux如何查看是否已安装Python？

米5s刷机视频教程怎么操作？

Java中char到底占几个字节？

iPhone刷固件教程，详细步骤是怎样的？

python win32gui下载

Java Socket编程实例具体怎么实现？

Java P2P网贷系统源码如何获取？

Python学到什么程度能找到工作？

Python如何将数据写入Excel文件？

如何用Python玩转Selenium自动化测试？

Java LocalTime如何正确使用？

3dmax渲染教程视频如何快速上手？

java基本数据类型的默认值

Maya Python如何高效处理索引？

Java如何使用EhCache？

Python与Python串口显示有何区别？

Python Excel数据如何高效导入数据库？

核心思路

第 1 步：准备工作

安装 Python 库

准备示例文件

第 2 步：读取 Excel 文件

第 3 步：连接数据库

MySQL 连接示例

SQLite 连接示例

第 4 步：数据导入（核心部分）

使用 pandas.to_sql() (最简单、最推荐)

循环插入 (不推荐，仅作了解)

批量插入 (性能较好，代码稍复杂)

第 5 步：完整流程示例 (MySQL + to_sql)

总结与建议

相关推荐

Java Socket编程实例具体怎么实现？

使用 `pandas.to_sql()` (最简单、最推荐)

第 5 步：完整流程示例 (MySQL + `to_sql`)