阅读:18563回复:8
oracle数据查询生成EXCEL并发送邮件python3
import smtplib
import email.mime.multipart import email.mime.text import email.mime.base import os.path import cx_Oracle import xlwt import time #生成excel文件名 v_curr_time = time.strftime('%Y%m%d%H%M%S',time.localtime(time.time())) v_file_name = '文件名-'+ v_curr_time + '.xls ' #连接数据库 conn = cx_Oracle.connect('用户名/密码@数据库') cur1 = conn.cursor() #组查询语句,如果是多行结尾需要加反斜杠连接 v_sql = ("XXX") print (v_sql) cur1.execute(v_sql) rows = cur1.fetchall() v_cnt = len(rows) #生成excel文件 book=xlwt.Workbook() sheet1=book.add_sheet('Sheet1') #把列名当作一行数据写入 sheet1.write(0,0,'列名1') sheet1.write(0,1,'列名2') sheet1.write(0,2,'列名3') #当查出多列数据时,需要一个单元格一个单元格的写入,要不然这四列就会写到excel的一个单元格里 for i in range(len(rows)): for j in range(5): #print (rows[j]) #print ("--------") sheet1.write(i+1,j,rows[j]) book.save(v_file_name) cur1.close() conn.close() #邮件信息 From = "邮件地址" To = "邮件地址" file_name = v_file_name server = smtplib.SMTP("发件服务器") server.login("发件邮箱","邮箱密码") #仅smtp服务器需要验证时 # 构造MIMEMultipart对象做为根容器 main_msg = email.mime.multipart.MIMEMultipart() # 构造MIMEText对象做为邮件显示内容并附加到根容器 #v_str = "备份日志,数据共" + v_cnt + "行" text_msg = email.mime.text.MIMEText("XXX数据,请查收,谢谢。") main_msg.attach(text_msg) # 构造MIMEBase对象做为文件附件内容并附加到根容器 contype = v_file_name maintype, subtype = contype.split(' ') ## 读入文件内容并格式化 data = open(file_name, 'rb') file_msg = email.mime.base.MIMEBase(maintype, subtype) file_msg.set_payload(data.read( )) data.close( ) email.encoders.encode_base64(file_msg) ## 设置附件头 basename = os.path.basename(file_name) file_msg.add_header('Content-Disposition', 'attachment', filename = basename) main_msg.attach(file_msg) # 设置根容器属性 main_msg['From'] = From main_msg['To'] = To main_msg['Subject'] = "xxx文档 " main_msg['Date'] = email.utils.formatdate( ) # 得到格式化后的完整文本 fullText = main_msg.as_string( ) # 用smtp发送邮件 try: server.sendmail(From, To, fullText) print ("发送成功") except Exception as e: print ("发送失败") print (str(e)) finally: server.quit() |
|