数据报表-定时任务-增量增加数据

• 1.定时导出数据，保存为CSV文件
• 2. 将导出的csv文件定时发送

1.导出CSV文件

# - * - coding: utf-8 - * -

import pymysql
import xlwt
import sys
from datetime import datetime
import os 
import pandas as pd
import send_mail

host = "xxx"
user = "xxx"
password = ""
database = "xxx"
port = 3316

sql1 = "SELECT date_add(curdate(), interval -1 day) AS '日期',COUNT(DISTINCT user_id) AS '自然月活跃' FROM smt_statistic_login_log WHERE create_date >= '2019-03-01' AND create_date < '%s' ;" %(datetime.now().strftime("%Y-%m-%d"))
# sql1 = "SELECT date_add(curdate(), interval -2 day) AS '日期',COUNT(DISTINCT user_id) AS '自然月活跃' FROM smt_statistic_login_log WHERE create_date >= '2019-03-01' AND create_date < '2019-03-05' ;" 
# sql2 = "select current_date AS '日期';"
MYSQLDB = pymysql.connect(host=host, user=user, password=password,database=database, port=port,charset='utf8')

def export_excel():
    '''导出到excel表格中'''

    os.chdir(r"D:\运维资料\script-统计自然月数据")
    try:
        df1 = pd.read_sql(sql1,MYSQLDB)
      ## 使用df.to_csv() 方法可以保留之前的数据，增量增加数据到Excel表格中
        df1.to_csv(r"数据导出\统计自然月活跃数据.csv",index=False,mode='a',encoding='utf_8_sig',header=False)
        print("get sql1 data  success.")
        Logfile = open(r'log\export-data.log', 'a')
        Logfile.write("[" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "]" + " INFO:" + " write sql1 data to excel success.\n")
        Logfile.close()
    except:
        print("Error: unable to fetch data or can not export data to excel.")
        Logfile = open(r'log\export-data.log', 'a')
        Logfile.write("[" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "]" + " INFO:" + " Error: unable to fetch data or can not export data to excel.\n")
        Logfile.close()
        sys.exit(1)
    send_mail.sendmail()

if __name__ == "__main__":
    export_excel()

2.定时发送邮件[带附件]

# -*- coding: utf-8 -*-                                                                                                                                 
import sys,os
import smtplib
from email.mime.text import MIMEText
from email.header import Header
import datetime
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication 

mail_host ="xxxx"                                                                                                               
mail_user = "xxx"
mail_pass = "xxx"


def sendmail():                                                                                                                                 
    u"""发送邮件."""

    content = "大家好，这是%s日前的自然月活跃统计报表，请查收，谢谢！" %(datetime.datetime.now().strftime("%Y.%m.%d"))
    textApart = MIMEText(content)
    ExcelFile = r"统计自然月活跃数据.csv"
    ExcelApart = MIMEApplication(open(r'D:\运维资料\script-统计自然月数据\数据导出\%s' %(ExcelFile), 'rb').read())
    ExcelApart.add_header('Content-Disposition', 'attachment', filename=ExcelFile)

    os.chdir(r'D:\运维资料\script-统计自然月数据')
    to_reciver = "xx,xx"
    cc_reciver = "xx,xx"
    message = MIMEMultipart()
    message.attach(textApart)
    message.attach(ExcelApart)
    message['From'] = Header(mail_user, 'utf-8')
    message['To'] = to_reciver
    message['Cc'] = cc_reciver
    message['Subject'] = Header("统计自然月数据报表", 'utf-8') 
    receiver = to_reciver.split(',') + cc_reciver.split(',')

    try:
        smtpObj = smtplib.SMTP(mail_host,25)
        smtpObj.ehlo()
        smtpObj.login(mail_user,mail_pass)
        smtpObj.sendmail(mail_user, receiver,message.as_string())
        smtpObj.quit()
        Logfile = open(r'log\send-email.log', 'a')
        Logfile.write("[" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "]" + " INFO:" + " send email success.\n")
        Logfile.close()
        print("sendmail  success. ")
    except Exception as e:
        Logfile = open(r'log\send-email.log', 'a')
        Logfile.write("[" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "]" + " ERROR:" + " send email failed.\n")
        Logfile.close()
        sys.exit(1)