oracle导出excel,每6万生成一个excel文件

#! /usr/bin/python
#_*_coding:utf8_*_

import cx_Oracle,xlwt,time,sys,os,zipfile
from datetime import timedelta, datetime


default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
      reload(sys)

      sys.setdefaultencoding(default_encoding)

 

def export_excel_format(filename,savepath,dbuser,dbpasswd,address,table_name,start_point,end_point,condition='1=1'):
    yesterday = datetime.today() + timedelta(-1)
    yesterday_format = yesterday.strftime('%Y%m%d')
    tadaypath = os.path.exists("%s/%s"%(savepath,yesterday_format))
    if not tadaypath:
        os.makedirs("%s/%s"%(savepath,yesterday_format))
    conn = cx_Oracle.connect(dbuser,dbpasswd,address)
    cursor = conn.cursor ()
    cursor.execute ("select t.* from (select t.* ,rownum rowidnum from (select t.* from %s t where %s) t   order by rownum)t where rowidnum between %s and %s  ord
er by t.rowidnum"%(table_name,condition,start_point,end_point))    data = cursor.fetchall ()
    fields = cursor.description
    workbook = xlwt.Workbook(encoding = 'utf-8')
    sheet = workbook.add_sheet(filename,cell_overwrite_ok=True)
    for field in range(0,len(fields)):
        sheet.write(0,field,fields[field][0])
    row = 1
    col = 0
    for row in range(1,len(data)+1):
        for col in range(0,len(fields)):
            sheet.write(row,col,u'%s'%data[row-1][col])
    workbook.save(r'%s/%s/%s%s.xls'%(savepath,yesterday_format,filename,yesterday_format))
    cursor.close ()
    conn.close ()
    zf = zipfile.ZipFile('%s.zip'%(yesterday_format),'w',zipfile.ZIP_DEFLATED)


    for dirpath, dirnames, filenames in os.walk(yesterday_format):
        for filename in filenames:
            zf.write(os.path.join(dirpath,filename))


    zf.close()


def get_trade_data(filename,savepath,dbuser,dbpasswd,address,table_name,condition='1=1'):
    conn = cx_Oracle.connect(dbuser,dbpasswd,address)
    cursor = conn.cursor ()
    t=cursor.execute("select count(1)  from %s where %s"%(table_name,condition)).fetchone()[0]
    a=t/60000+1
    b=1
    c=1
    d=60000
    e=60000
    while b <= a:
       filename_temp=filename
       filename=('%s-%s-')%(filename,b)
       export_excel_format(filename,savepath,dbuser,dbpasswd,address,table_name,c,d,condition)
       c=d+1
       b = b +1
       d = e*b
       filename=filename_temp


if __name__ == "__main__":

    get_trade_data('小明',r'/data','oracle','oracle','192.168.10.123:1521/orcl','t1')

    get_trade_data('小红',r'/data','oracle','oracle','192.168.10.123:1521/orcl','t2')

#条件不写，就默认是1=1