1、首先是主工作脚本,连接oracle,计算后保存到本地excel中,work.py代码如下
__author__ = 'chunyang.wu'
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.cell import get_column_letter
import cx_Oracle
import os
import time
from sendmail import sendmail
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' --该行解决了数据从数据库中取出来,中文编码问题,不然会报错
class Create_xlsx:
"""Create a xlsx and fill with the data"""
def __init__(self):
self.date = time.strftime("%Y%m%d", time.localtime(time.time()))
self.dest_filename = r'/Data/code/huangwen/'+self.date+'.xlsx'
self._init_sheet()
self._init_db()
def _release_db(self):
self.conn.commit()
# self.curs.close()
self.conn.close()
def _init_db(self):
self.conn = cx_Oracle.connect('username','pass','192.168.10.11:1521/db')
self.curs = self.conn.cursor()
yh_sql = 'select member_id,nickname,czy from TBL_HQ_USER'
self.curs.execute(yh_sql)
yh_res = self.curs.fetchall()
yh_nickname = {}
yh_czy = {}
for i in yh_res:
yh_nickname[i[0]] = i[1]
yh_czy[i[0]] = i[2]
sql_fid = 'select distinct fid from TBL_FRAGMENT_COMMENT where member_id in (select member_id from tbl_hq_user)'
self.curs.execute(sql_fid)
res_fid = self.curs.fetchall()
count = 0
for k in res_fid:
fid = k[0]
data = {}
content = []
fidsql = 'select fid,member_id,content,reply_uid from TBL_FRAGMENT_COMMENT where fid = %s and (member_id in (select member_id from tbl_hq_user) or reply_uid in (select member_id from tbl_hq_user)) order by create_time' %fid
self.curs = self.conn.cursor()
self.curs.execute(fidsql)
info_comments = self.curs.fetchall()
count += 1
for info in info_comments:
count += 1
try:
data['fid'] = info[0]
data['member_id'] = info[1]
data['content'] = info[2]
data['reply'] = info[3]
data['nickname'] = yh_nickname[info[1]]
data['czy'] = yh_czy[info[1]]
except Exception,ex:
print ex
if yh_nickname.has_key(data['reply']):
reply_nickname = yh_nickname[data['reply']]
else:
reply_nickname = ''
# print "czy:%s,uid:%s,nickname:%s,fid:%s,rep_nickname:%s,content:%s" %(data['czy'],data['member_id'],data['nickname'],data['fid'],reply_nickname,data['content'])
col = ['A','B','C','D','E','F']
wb = load_workbook(filename = self.dest_filename)
wb.get_sheet_by_name(name = u'评论内容')
ws = wb.worksheets[0]
try:
ws.cell('%s%s' %(col[0],count)).value = data['czy']
ws.cell('%s%s' %(col[1],count)).value = data['member_id']
ws.cell('%s%s' %(col[2],count)).value = data['nickname']
ws.cell('%s%s' %(col[3],count)).value = data['fid']
ws.cell('%s%s' %(col[4],count)).value = reply_nickname
ws.cell('%s%s' %(col[5],count)).value = data['content']
except Exception,ex:
print ex
wb.save(filename = self.dest_filename)
# count += 1
# self.curs.close()
def _init_sheet(self):
wb = Workbook()
ws = wb.worksheets[0]
ws.title = u"评论内容"
bt ={ "A1":"操作员",\
"B1":"马甲ID",\
"C1":"马甲昵称",\
"D1":"原文链接",\
"E1":"回复谁",\
"F1":"回复内容(如,未回复为空白)"}
for i in bt:
ws.cell('%s' %i).value = '%s' %bt[i]
wb.save(filename = self.dest_filename)
def _update_sheet(self,cow,row,data):
wb = load_workbook(filename = self.dest_filename)
ws = wb.get_sheet_by_name(sheetnames[0])
ws.cell('%s%s'%(col, row)).value = '%s%s' % data
def main():
p = Create_xlsx()
p._release_db()
mail_file = p.dest_filename
mail_list = "123456@qq.com;"
sendmail(mail_list,mail_file)
print time.strftime("%Y%m%d", time.localtime(time.time())),"done!"
if __name__ == '__main__':
main()
上面的代码调用下面的sendmail脚本发送邮件,发送功能我给封装在一个类里面,其他地方也可以调用,sendmail.py代码如下
# -*- coding: utf-8 -*-
from email.header import Header
import smtplib
import email.MIMEMultipart# import MIMEMultipart
import email.MIMEText# import MIMEText
import email.MIMEBase# import MIMEBase
import os.path
import sys
import time
import mimetypes
import email.MIMEImage# import MIMEImage
class sendmail:
"""this is for sendmail"""
def __init__(self,email_addr,filename):
#命令 mail.py <1:发送方(回复地址)380968195@qq.com> <2:发送地址,多个以;隔开> <3:发送文件>
self.From = "%s<380968195@qq.com>" % Header("带附件email","utf-8")
self.ReplyTo = email_addr
self.To = email_addr
self.file_name = filename #附件路劲和名称
self._init_server()
def _init_server(self):
self.server = smtplib.SMTP("smtp.qq.com",25)
self.server.login("username","password") #仅smtp服务器需要验证时
self._init_msg()
def _init_msg(self):
self.main_msg = email.MIMEMultipart.MIMEMultipart()# 构造MIMEMultipart对象做为根容器
self.text_msg = email.MIMEText.MIMEText("亲,每天一发,昨天的马甲评论数据,详情请见附件内容。",_charset="utf-8")# 构造MIMEText对象做为邮件显示内容并附加到根容器
self.main_msg.attach(self.text_msg)
ctype,encoding = mimetypes.guess_type(self.file_name)
if ctype is None or encoding is not None:
ctype='application/octet-stream'# 构造MIMEBase对象做为文件附件内容并附加到根容器
maintype,subtype = ctype.split('/',1)
self.file_msg=email.MIMEImage.MIMEImage(open(self.file_name,'rb').read(),subtype)
## 设置附件头
self.basename = os.path.basename(self.file_name)
self.file_msg.add_header('Content-Disposition','attachment', filename = self.basename)#修改邮件头
self.main_msg.attach(self.file_msg)
# 设置根容器属性
self.main_msg['From'] = self.From
self.main_msg['Reply-to'] = self.ReplyTo
self.main_msg['To'] = self.To
self.main_msg['Subject'] = time.strftime("%Y%m%d", time.localtime(time.time()))+"马甲评论数据"
self.main_msg['Date'] = email.Utils.formatdate(localtime = 1)
#main_msg['Bcc'] = To
# 得到格式化后的完整文本
self.fullText = self.main_msg.as_string()
# 用smtp发送邮件
try:
self.server.sendmail(self.From, self.To.split(';'), self.fullText)
finally:
self.server.quit()
上面的类传入邮件接收人邮箱(支持多个)及需要发送的本地附件即可。