L19-将多表分批次从数据库导出到Excel

1. 应用场景

最近经常需要手工从后台数据库导出某些数据表到Excel文件，而且源数据表的数据量大小不一，导致在导出到本地文件这个过程中需要根据情况来调整每个批次的写入数据量。为了避免反复的手工操作、高效率到多份离线数据，便做了这个小工具来一键完成任务。

2. 功能事项

• 支持一次性导出多个数据源表、自动获取各表的字段名。
• 支持控制批次的写入速率。 例如：每5000行一个批次写入到excel。
• 支持结构相同的表导入到同一个Excel文件。可适用于经过水平切分后的分布式表。

3. 主要实现

概览

主要方法

• 首先需要安装第三方库pymssql实现对SQLServer的连接访问，自定义方法__getConn()需要指定如下五个参数：服务器host、登录用户名user、登录密码pwd、指定的数据库db、字符编码charset。连接成功后，通过cursor()获取游标对象，它将用来执行数据库脚本，并得到返回结果集和数据总量。

• 附上创建数据库连接和执行SQL的源码：

    def __init__(self,host,user,pwd,db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
 
    def __getConn(self):
        if not self.db:
            raise(NameError,'没有设置数据库信息')
        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset='utf8')
        cur = self.conn.cursor()
        if not cur:
            raise(NameError,'连接数据库失败')
        else:
            return cur

• 方法3中写入Excel时，注意一定要用到Pandas中的公共句柄ExcelWriter对象writer。当数据被分批多次写入同一个文件时，如果直接使用to_excel()方法，则前面批次的结果集将会被后续结果覆盖。增加了这个公共句柄限制后，后面的写入会累加到前面写入的数据尾部行，而不是全部覆盖。

writer = pd.ExcelWriter(file)
df_fetch_data[rs_startrow:i*N].to_excel(writer, header=isHeader, index=False, startrow=startRow)

• 分批次写入到目标Excel时的另一个要注意的参数是写入行startrow的设置。每次写入完成后需要重新指下一批次数据的初始位置值。每个批次的数据会记录各自的所属批次信息。

• 利用关键字参数**args 指定多个数据源表和数据库连接。

def exportToExcel(self, **args):
    for sourceTB in args['sourceTB']:       
        arc_dict = dict(
            sourceTB = sourceTB,
            path=args['path'],
            startRow=args['startRow'],
            isHeader=args['isHeader'],
            batch=args['batch']
        )
        print('\n当前导出的数据表为：%s' %(sourceTB))
        self.writeToExcel(**arc_dict)
    return 'success'

4. 使用示例

如下是调用样例。先用类MSSQL创建对象，再定义关键字参数args，最终调用方法导出到文件即完成数据导出。

import pandas as pd
ms = MSSQL(host="localhost",user="test",pwd="test",db="db_jun")
args = dict(
sourceTB = ['tb2', 'tb1'],# 待导出的表
path='D:\\myPC\\Python\\',# 导出到指定路径
startRow=1,#设定写入文件的首行，第2行为数据首行
isHeader=False,# 是否包含源数据的标题
batch=5
)
# 导出到文件
ms.exportToExcel(**args)

5. 总结

本篇主要介绍完成多表(相同表结构)导出数据、分批次写入到同一Excel的实现过程，需要注意使用文件公共句柄、写入行初始化、关键字参数配置等。后续也可以继续扩展，将指定数据源表导出到指定目标文件。