博客

使用Python实现将多表分批次从数据库导出到Excel

  • Post category:Python

下面是一份使用 Python 实现将多表分批次从数据库导出到 Excel 的完整实例教程。

1. 环境准备

  • Python 3.x 版本
  • 安装 pandas、numpy 库
  • 安装 SqlAlchemy 库连接数据库
  • 有可供导出的数据库及表格

2. 数据库连接

首先需要使用 SqlAlchemy 库连接数据库，下面这段代码可以作为参考：

from sqlalchemy import create_engine

# 连接 MySQL 数据库
engine = create_engine('mysql+pymysql://username:password@host:port/dbname')

# 连接 Oracle 数据库
engine = create_engine('oracle+cx_oracle://username:password@host:port/dbname')

# 连接 SQL Server 数据库
engine = create_engine('mssql+pymssql://username:password@host:port/dbname')

其中，username、password、host、port、dbname 等参数需要根据实际情况进行替换。

3. 数据导出

接下来就可以使用 pandas 库读取数据库中的表格，并进行数据处理和导出。为了避免一次性处理过多数据，可以将数据分批次进行处理。

下面是一份数据导出的示例代码，其中的注释可以帮助理解每个操作的意义：

import pandas as pd

# 设置每批次处理的行数
batch_size = 10000

# 获取数据库中的所有表格
tables = pd.read_sql("show tables", engine)["Tables_in_dbname"].tolist()

# 将每个表格的数据分批次导出到 Excel 中，并保存在当前目录下
for table in tables:
    # 获取表格的总行数
    count = pd.read_sql(f"select count(*) from {table}", engine).iloc[0].iloc[0]
    # 计算分批次处理的次数
    batch_count = count // batch_size + 1
    for i in range(batch_count):
        # 计算每批次处理的起始行号和结束行号
        start = i * batch_size
        end = (i+1) * batch_size - 1 if (i+1) * batch_size < count else count - 1
        # 从数据库中获取数据
        data = pd.read_sql(f"select * from {table} limit {start}, {end-start+1}", engine)
        # 将表格数据导出到 Excel 文件中
        data.to_excel(f"{table}_part{i+1}.xlsx", index=False)

以上代码中，首先通过 pd.read_sql("show tables", engine)["Tables_in_dbname"].tolist() 获取了数据库中所有的表格名，然后在 for table in tables: 循环中依次对每个表格进行分批次处理。对于每个表格，首先获取它的总行数，然后计算分批次处理的次数，之后在 for i in range(batch_count): 循环中对每个批次的数据进行处理。通过 pd.read_sql(f"select * from {table} limit {start}, {end-start+1}", engine) 从数据库中获取每个批次的数据，并通过 data.to_excel(f"{table}_part{i+1}.xlsx", index=False) 将它们导出为 Excel 文件。

4. 示例说明

下面是两个示例说明：

示例 1

我们有一个 MySQL 数据库，里面有 customers 和 orders 两个表格，我们想要将它们分批次导出到 Excel 文件中。假设设置每批次处理 5000 行，那么可以使用以下代码：

import pandas as pd
from sqlalchemy import create_engine

# 连接 MySQL 数据库
engine = create_engine('mysql+pymysql://username:password@host:port/dbname')

# 设置每批次处理的行数
batch_size = 5000

# 获取数据库中的所有表格
tables = ["customers", "orders"]

# 将每个表格的数据分批次导出到 Excel 中，并保存在当前目录下
for table in tables:
    # 获取表格的总行数
    count = pd.read_sql(f"select count(*) from {table}", engine).iloc[0].iloc[0]
    # 计算分批次处理的次数
    batch_count = count // batch_size + 1
    for i in range(batch_count):
        # 计算每批次处理的起始行号和结束行号
        start = i * batch_size
        end = (i+1) * batch_size - 1 if (i+1) * batch_size < count else count - 1
        # 从数据库中获取数据
        data = pd.read_sql(f"select * from {table} limit {start}, {end-start+1}", engine)
        # 将表格数据导出到 Excel 文件中
        data.to_excel(f"{table}_part{i+1}.xlsx", index=False)

在执行完该代码后，当前目录下会生成 customers_part1.xlsx、customers_part2.xlsx 等多个 Excel 文件，分别保存了 customers 表格的不同批次的数据。同样地，还会生成 orders_part1.xlsx、orders_part2.xlsx 等多个 Excel 文件，分别保存了 orders 表格的不同批次的数据。

示例 2

假设我们有一个 Oracle 数据库，里面有 employees、departments、jobs 三个表格，我们想要将它们分批次导出到 Excel 文件中。假设我们设置每次处理 10000 行，那么可以使用以下代码：

import pandas as pd
from sqlalchemy import create_engine

# 连接 Oracle 数据库
engine = create_engine('oracle+cx_oracle://username:password@host:port/dbname')

# 设置每批次处理的行数
batch_size = 10000

# 获取数据库中的所有表格
tables = ["employees", "departments", "jobs"]

# 将每个表格的数据分批次导出到 Excel 中，并保存在当前目录下
for table in tables:
    # 获取表格的总行数
    count = pd.read_sql(f"select count(*) from {table}", engine).iloc[0].iloc[0]
    # 计算分批次处理的次数
    batch_count = count // batch_size + 1
    for i in range(batch_count):
        # 计算每批次处理的起始行号和结束行号
        start = i * batch_size
        end = (i+1) * batch_size - 1 if (i+1) * batch_size < count else count - 1
        # 从数据库中获取数据
        data = pd.read_sql(f"select * from {table} offset {start} rows fetch next {batch_size} rows only", engine)
        # 将表格数据导出到 Excel 文件中
        data.to_excel(f"{table}_part{i+1}.xlsx", index=False)

在执行完该代码后，当前目录下会生成 employees_part1.xlsx、employees_part2.xlsx 等多个 Excel 文件，分别保存了 employees 表格的不同批次的数据。同样地，还会生成 departments_part1.xlsx、departments_part2.xlsx 等多个 Excel 文件，分别保存了 departments 表格的不同批次的数据。最后，还会生成 jobs_part1.xlsx、jobs_part2.xlsx 等多个 Excel 文件，分别保存了 jobs 表格的不同批次的数据。

以上就是本次对于使用 Python 实现将多表分批次从数据库导出到 Excel 的完整实例教程，希望对您有所帮助。

Tags: ,