python连接oracle，把计算结果存入本地excel并发送带附件邮件

1、首先是主工作脚本，连接oracle，计算后保存到本地excel中，work.py代码如下

__author__ = 'chunyang.wu'# -*- coding: utf-8 -*-from openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.cell import get_column_letterimport cx_Oracleimport osimport timefrom sendmail import sendmailos.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 += 1try: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 exif 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_nicknamews.cell('%s%s' %(col[5],count)).value = data['content']except Exception,ex:print exwb.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 = "[e-mail];"    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 smtplibimport email.MIMEMultipart# import MIMEMultipartimport email.MIMEText# import MIMETextimport email.MIMEBase# import MIMEBaseimport os.pathimport sysimport timeimport mimetypesimport email.MIMEImage# import MIMEImageclass sendmail:"""this is for sendmail"""def __init__(self,email_addr,filename):#命令 mail.py <1:发送方（回复地址）[e-mail]> <2:发送地址，多个以;隔开> <3:发送文件>self.From = "%s<[e-mail]>" % Header("带附件email","utf-8")self.ReplyTo = email_addrself.To = email_addrself.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.Fromself.main_msg['Reply-to'] = self.ReplyToself.main_msg['To'] = self.Toself.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()

上面的类传入邮件接收人邮箱（支持多个）及需要发送的本地附件即可。