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,'沒有設(shè)置數(shù)據(jù)庫信息')
    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,'連接數(shù)據(jù)庫失敗')
    else:
      return cur

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當(dāng)前導(dǎo)出的數(shù)據(jù)表為：%s' %(sourceTB))
    self.writeToExcel(**arc_dict)
  return 'success'

#!/usr/bin/env python
# coding: utf-8

# 主要功能：分批次導(dǎo)出大數(shù)據(jù)量、結(jié)構(gòu)相同的數(shù)據(jù)表到excel 
# 導(dǎo)出多個表的數(shù)據(jù)到各自的文件， 
# 目前問題：to_excel 雖然設(shè)置了分批寫入，但先前的數(shù)據(jù)會被下一次寫入覆蓋，
# 利用Pandas包中的ExcelWriter()方法增加一個公共句柄，在寫入新的數(shù)據(jù)之時保留原來寫入的數(shù)據(jù)，等到把所有的數(shù)據(jù)都寫進(jìn)去之后關(guān)閉這個句柄
import pymssql 
import pandas as pd 
import datetime 
import math
 
class MSSQL(object):
  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,'沒有設(shè)置數(shù)據(jù)庫信息')
    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,'連接數(shù)據(jù)庫失敗')
    else:
      return cur
   
  def executeQuery(self,sql):
    cur = self.__getConn()
    cur.execute(sql)
    # 獲取所有數(shù)據(jù)集
    # fetchall()獲取結(jié)果集中的剩下的所有行
    # 如果數(shù)據(jù)量太大，是否需要分批插入 
    resList, rowcount = cur.fetchall(),cur.rowcount
    self.conn.close()
    return (resList, rowcount)
 
  # 導(dǎo)出單個數(shù)據(jù)表到excel 
  def writeToExcel(self,**args):
    sourceTB = args['sourceTB']
    columns = args.get('columns')
    path=args['path']
    fname=args.get('fname')
    startRow=args['startRow']
    isHeader=args['isHeader']
    N=args['batch']
     
    # 獲取指定源數(shù)據(jù)列
    if columns is None:
      columns_select = ' * '
    else:
      columns_select = ','.join(columns)
     
    if fname is None:
      fname=sourceTB+'_exportData.xlsx'
     
    file = path + fname
    # 增加一個公共句柄，寫入新數(shù)據(jù)時，保留原數(shù)據(jù) 
    writer = pd.ExcelWriter(file)
     
    sql_select = 'select '+ columns_select + ' from '+ sourceTB
    fetch_data, rowcount = self.executeQuery(sql_select)
    # print(rowcount)
     
    df_fetch_data = pd.DataFrame(fetch_data)
    # 一共有roucount行數(shù)據(jù)，每N行一個batch提交寫入到excel 
    times = math.floor(rowcount/N)
    i = 1
    rs_startrow = 0
    # 當(dāng)總數(shù)據(jù)量 > 每批插入的數(shù)據(jù)量時 
    print(i, times)
    is_while=0
    while i <= times:
      is_while = 1
      # 如果是首次，且指定輸入標(biāo)題，則有標(biāo)題
      if i==1:
        # isHeader = True
        startRow = 1
      else:
        # isHeader = False
        startRow+=N
      # 切片取指定的每個批次的數(shù)據(jù)行 ,前閉后開 
      # startrow: 寫入到目標(biāo)文件的起始行。0表示第1行，1表示第2行。。。
      df_fetch_data['batch'] = 'batch'+str(i)
      df_fetch_data[rs_startrow:i*N].to_excel(writer, header=isHeader, index=False, startrow=startRow)
      print('第',str(i),'次循環(huán)，取源數(shù)據(jù)第',rs_startrow,'行至',i*N,'行','寫入到第',startRow,'行')
      print('第',str(i),'次寫入數(shù)據(jù)為：',df_fetch_data[rs_startrow:i*N])
      # 重新指定源數(shù)據(jù)的讀取起始行
      rs_startrow =i * N
      i+=1
 
    # 寫入文件的開始行數(shù)
    # 當(dāng)沒有做任何循環(huán)時，仍然從第一行開始寫入
    if is_while == 0:
      startRow = startRow
    else:
      startRow+=N
    df_fetch_data['batch'] = 'batch'+str(i)
    print('第{0}次讀取數(shù)據(jù)，從第{1}行開始，寫入到第{2}行！'.format(str(i), str(rs_startrow), str(startRow)))
    print('第',str(i),'寫入數(shù)據(jù)為：',df_fetch_data[rs_startrow:i*N])
    df_fetch_data[rs_startrow:i*N].to_excel(writer, header=isHeader, index=False, startrow=startRow)
     
    # 注： 這里一定要saver()將數(shù)據(jù)從緩存寫入磁盤?。。。。。。。。。。。。。。。。。。。?！1
    writer.save()
     
    start_time=datetime.datetime.now()
  # 導(dǎo)出結(jié)構(gòu)相同的多個表到同一樣excel
  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當(dāng)前導(dǎo)出的數(shù)據(jù)表為：%s' %(sourceTB))
      self.writeToExcel(**arc_dict)
       
    return 'success'
    start_time=datetime.datetime.now()
 
if __name__ == "__main__":
  ms = MSSQL(host="localhost",user="test",pwd="test",db="db_jun")
   
  args = dict(
   sourceTB = ['tb2', 'tb1'],# 待導(dǎo)出的表
   path='D:\\myPC\\Python\\',# 導(dǎo)出到指定路徑
   startRow=1,#設(shè)定寫入文件的首行，第2行為數(shù)據(jù)首行
   isHeader=False,# 是否包含源數(shù)據(jù)的標(biāo)題
   batch=5
  )
  # 導(dǎo)出多個文件
  ms.exportToExcel(**args)