Python数据导出demo

Python之Pandas使用教程
1.Pandas概述
    Pandas是Python的一个数据分析包，该工具为解决数据分析任务而创建。
    Pandas纳入大量库和标准数据模型，提供高效的操作数据集所需的工具。
    Pandas提供大量能使我们快速便捷地处理数据的函数和方法。
    Pandas是字典形式，基于NumPy创建，让NumPy为中心的应用变得更加简单。

import datetime
import os
import zipfile
from ftplib import FTP
from oracle_pool import OraclePool
import pandas as pd
# 数据库连接池
from send_email import Email, Messages

oracle = OraclePool('Oracle')
# 原生连接共pandas使用
conn = OraclePool.conn('Oracle')

def export_chsj():
    sql2='''
    select * from temp_table '''
    
    sfcvhb= pd.read_sql(sql2,conn)

    #shape[0]就是读取矩阵第一维度的长度，取查到多少行数据
    column = sfcvhb.shape[0]
    file_list = []
    path = os.getcwd()
    if column < 800000:
        now = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
        file_name = path + '/file/%s导出信息.xlsx' % now
        writer = pd.ExcelWriter(file_name)
        sfcvhb.to_excel(writer, sheet_name='data_param', startcol=0, startrow=0, index=False)
        writer.save()
        file_list.append(file_name)
    else:
        page = 0
        while page * 800000 < column:
            startrow = page * 800000
            if (page + 1) * 800000 > column:
                endrow = column
            else:
                endrow = (page + 1) * 800000
            page += 1
            now = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            file_name = path + '/file/%s导出信息.xlsx' % now
            writer = pd.ExcelWriter(file_name)
            sfcvhb[startrow:endrow].to_excel(writer, sheet_name='data_param', startcol=0, startrow=0, index=False)
            writer.save()
            file_list.append(file_name)
    ftp = FTP()
    ftp.connect("szftp.*****.com", 21)
    ftp.login('********', '********')
    ftp.set_pasv(False)
    ftp.cwd("/file/")
    today = datetime.datetime.now().strftime('%Y%m%d%H%M')
    ftp_file = today + '.zip'
    zip = zipfile.ZipFile(ftp_file, 'w', zipfile.ZIP_DEFLATED)
    for file in file_list:
        zip.write(file)
    zip.close()
    upload(ftp, "ftp_" + ftp_file, ftp_file)
    print('ftp upload ok!')
    ftp.quit()
    try:
        message = """
            <h3><h3>导出信息,数据请通过链接下载：http://szftp.****.com/1807090053/file/ftp_"""+ftp_file+"""</h3>"""
        my_user = ['***@qq.com']
        my_sender = "****@qq.com"
        message = Messages(my_sender, my_user, "导出信息", message).generate_message()
        Email(my_sender, my_user, "*****", "mail.qq.com").send_mail(message)
    except Exception as e:
        print(e)

def upload(f, remote_path, local_path):
    fp = open(local_path, "rb")
    buf_size = 4096
    f.storbinary("STOR {}".format(remote_path), fp, buf_size)
    fp.close()


def download(f, remote_path, local_path):
    fp = open(local_path, "wb")
    buf_size = 1024
    f.retrbinary('RETR {}'.format(remote_path), fp.write, buf_size)
    fp.close()



if __name__=='__main__':
    export_chsj()