一、应用场景
为了避免反复的手手工从后台数据库导出某些数据表到Excel文件、高效率到多份离线数据。
二、功能事项
支持一次性导出多个数据源表、自动获取各表的字段名。
支持控制批次的写入速率。例如:每5000行一个批次写入到excel。
支持结构相同的表导入到同一个Excel文件。可适用于经过水平切分后的分布式表。
三、主要实现
1、概览
- A[创建类] -->|方法1| B(创建数据库连接)
- A[创建类] -->|方法2| C(取查询结果集)
- A[创建类] -->|方法3| D(利用句柄写入Excel)
- A[创建类] -->|方法4| E(读取多个源表)
- B(创建数据库连接) -->U(调用示例)
- C(取查询结果集) -->U(调用示例)
- D(利用句柄写入Excel) -->U(调用示例)
- E(读取多个源表) -->U(调用示例)
2、主要方法
首先需要安装第三方库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、方法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