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

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()

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