通过Python实现生成excel并邮件发送的功能
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> 本邮件为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)
db.commit ###因为默认的autocommit = false,SP中有增、删、改，必须显示commit，才能提交落库。 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()
4.功能实现
经过测试验证，满足生产要求。
