通过Python实现生成excel并邮件发送的功能- Python

1.需求描述

因业务方需求，在一个月内，需要每天从数据库中收集、汇聚相关数据，形成3份excel，然后发给相关人。工作虽然简单，但是还是需要花费大家的时间和经历，所以，通过一份简单的python代码来实现此功能。

2.执行文件

3.代码实现

Python 的运行环境：Python 2.7.5

3.1 邮件配置文件

邮件配置文件smtp_conig.py，可参阅 https://www.cnblogs.com/xuliuzai/p/14156559.html 的发送邮件的模块smtp_config.py,在此不再赘述。

3.2 数据库配置文件

数据库配置文件为db_weixindb_conn.py和db_qqdb_conn.py，因为是连接不同实例上的两个数据库，所以，我们将配置信息放在了两个文件中。其具体的配置可参照

https://www.cnblogs.com/xuliuzai/p/14721413.html 中的《db_conn.py》文件。

3.3 功能代码

主要执行文件report_excel_mail_.py的代码如下

复制代码

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )
import db_weixindb_conn
import db_qqdb_conn
import os
import time
import smtp_config
from email.mime.text import MIMEText
from email.header import Header
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
##引入生成excel库
import string
import xlsxwriter
import datetime

def send_mail(mail_msg):
    # 调用send_mail函数
    mail_body = """
    <style type="text/css">
    </style>
    <html><font color=#0000CC>Dear All,<br><br>&nbsp;&nbsp;&nbsp;本邮件为QQ Order 维护部分测试邮件。主要用来测试生成excel，并且以邮件的形式发送的功能,请互理。</html>
     """
    mail_body = mail_body + mail_msg
    message = MIMEText(mail_body, 'html', 'utf-8')
    msg = MIMEMultipart()
    msg.attach(message)
    msg.attach(xlsxpart_publish)
    msg.attach(xlsxpart)
    msg.attach(xlsxpart_slow)

    subject = 'BBChat日报'
    msg['Subject'] = Header(subject, 'utf-8')
    smtp_config.send_mail(msg)
    return

###查询的时间范围
### TypeError: strptime() argument 0 must be str, not <class 'datetime.datetime'>
e_time = (datetime.datetime.now() + datetime.timedelta(-1)).strftime('%Y-%m-%d')
s_time = (datetime.datetime.now() + datetime.timedelta(-8)).strftime('%Y-%m-%d')

# 定义时间标志变量
sheet_time = datetime.datetime.now()
sheet_mark = sheet_time.strftime('%Y-%m-%d')
book_mark = sheet_time.strftime('%Y%m%d')

# 定义输出excel文件名
workbook_publish = xlsxwriter.Workbook('BBChat_App_publish_'+book_mark+'.xlsx')
# 定义sheet的名字
worksheet_publish = workbook_publish.add_worksheet(sheet_mark)
# 定义sheet中title的字体format
bold_publish = workbook_publish.add_format({'bold': True})

# 定义输出excel文件名
workbook_slow = xlsxwriter.Workbook('BBChat_App_slow_'+book_mark+'.xlsx')
# 定义sheet的名字
worksheet_slow = workbook_slow.add_worksheet(sheet_mark)
# 定义sheet中title的字体format
bold_slow = workbook_slow.add_format({'bold': True})

# 定义输出excel文件名
workbook = xlsxwriter.Workbook('BBChat_App_'+book_mark+'.xlsx')
# 定义sheet的名字
worksheet = workbook.add_worksheet(sheet_mark)
# 定义sheet中title的字体format
bold = workbook.add_format({'bold': True})

#定义邮件体变量
mail_msg = ""

# 获取数据库连接
db = db_weixindb_conn.db
db_qqdb = db_qqdb_conn.db
# 使用cursor()方法获取操作游标
cursor = db_qqdb.cursor()
# SQL 查询语句
# 备份日报
sql_apppublish_report = " select q_id,q_title,q_app_id,q_status,q_r_type,q_creator,q_create_time from qq_release " \
                     " where q_create_time>= %s and q_create_time< %s  " \
                     " and status != 0; "
try:
    # 执行SQL语句
    cursor.execute(sql_apppublish_report,(s_time,e_time))
    # 获取所有记录列表
    results = cursor.fetchall()
    # 查询数据结果和字段名字 赋值给两个变量
    ##result = cur.fetchall() ##前面已经赋值
    fields = cursor.description # get column name

    ## 将结果写入excel中
    ## 定义title的坐标：row=0，col=0~字段总数 也就是excel的第一行：0，0  ~ 0，len(fields),通过fields[field][0] 获取字段名
    for field in range(0,len(fields)):
      worksheet_publish.write(0,field,fields[field][0],bold_publish)

    for row in range(1,len(results)+1):
      for col in range(0,len(fields)):
        worksheet_publish.write(row,col,u'%s' % results[row-1][col])

    workbook_publish.close()

    ##添加邮件附件,xlsx类型的附件
    xlsxFile_publish = '/data/scripts/' + 'BBChat_App_publish_'+book_mark+'.xlsx'
    xlsxpart_publish = MIMEApplication(open(xlsxFile_publish, 'rb').read())
    xlsxpart_publish.add_header('Content-Disposition', 'attachment', filename=xlsxFile_publish)
    ##attachfile = xlsxpart
    ##发送邮件
    #send_mail(mail_msg)

except  Exception as e:
    print str(Exception)
    print str(e)
# 关闭游标
cursor.close()
# 关闭数据库连接
db_qqdb.close()

####调用存储生成数据
# 使用cursor()方法获取操作游标
cursor = db.cursor()
try:
    cursor.execute('CALL usp_collect_appBBChat()')
except  Exception as e:
    print str(Exception)
    print str(e)
cursor.close()
####数据生成

#### 生成第二个文件
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 查询语句
# 备份日报
sql_app_report = " select t1.topic,t1.cnt_1,t2.cnt_2 from " \
                     " ( select topic,count(*) as cnt_1 from qqapp_alertlog_2 group by topic ) t1 " \
                     " join " \
                     " ( select topic,count(*) as cnt_2 from ( select distinct topic,api_id from qqapp_alertlog_2 ) t group by topic ) t2 on t1.topic = t2.topic " \
                     " order by cnt_1 desc;  "

try:
    # 执行SQL语句
    cursor.execute(sql_app_report)
    # 获取所有记录列表
    results = cursor.fetchall()
    # 查询数据结果和字段名字 赋值给两个变量
    ##result = cur.fetchall() ##前面已经赋值
    fields = cursor.description # get column name

    ## 将结果写入excel中
    ## 定义title的坐标：row=0，col=0~字段总数 也就是excel的第一行：0，0  ~ 0，len(fields),通过fields[field][0] 获取字段名
    for field in range(0,len(fields)):
      worksheet.write(0,field,fields[field][0],bold)

    for row in range(1,len(results)+1):
      for col in range(0,len(fields)):
        worksheet.write(row,col,u'%s' % results[row-1][col])

    workbook.close()

    ##添加邮件附件,xlsx类型的附件
    xlsxFile = '/data/scripts/' + 'BBChat_App_'+book_mark+'.xlsx'
    xlsxpart = MIMEApplication(open(xlsxFile, 'rb').read())
    xlsxpart.add_header('Content-Disposition', 'attachment', filename=xlsxFile)
##### 生成第二个文件 End ###
    ##send_mail(mail_msg)

except  Exception as e:
    print str(Exception)
    print str(e)
# 关闭游标
cursor.close()
# 关闭数据库连接
#db.close()

#### 生成第三个文件
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 查询语句
# 备份日报
sql_app_slow = " select id, core_api, post_time,topic,app_id, api_id,method,cnt, taken_avg,fail_cnt,fail_per " \
                     " from qqapp_alertlog_2  "

try:
    # 执行SQL语句
    cursor.execute(sql_app_slow)
    # 获取所有记录列表
    results = cursor.fetchall()
    # 查询数据结果和字段名字 赋值给两个变量
    ##result = cur.fetchall() ##前面已经赋值
    fields = cursor.description # get column name

    ## 将结果写入excel中
    ## 定义title的坐标：row=0，col=0~字段总数 也就是excel的第一行：0，0  ~ 0，len(fields),通过fields[field][0] 获取字段名
    for field in range(0,len(fields)):
      worksheet_slow.write(0,field,fields[field][0],bold_slow)

    for row in range(1,len(results)+1):
      for col in range(0,len(fields)):
        worksheet_slow.write(row,col,u'%s' % results[row-1][col])

    workbook_slow.close()

    ##添加邮件附件,xlsx类型的附件
    xlsxFile_slow = '/data/scripts/' + 'BBChat_App_slow_'+book_mark+'.xlsx'
    xlsxpart_slow = MIMEApplication(open(xlsxFile_slow, 'rb').read())
    xlsxpart_slow.add_header('Content-Disposition', 'attachment', filename=xlsxFile_slow)
##### 生成第二个文件 End ###
    send_mail(mail_msg)

except  Exception as e:
    print str(Exception)
    print str(e)
# 关闭游标
cursor.close()
# 关闭数据库连接
db.close()

复制代码