Python3 cx_Oracle+openpyxl 数据导入excel自动发送邮件

最新推荐文章于 2021-11-24 23:14:19 发布

原创最新推荐文章于 2021-11-24 23:14:19 发布 · 420 阅读

1 ·

CC 4.0 BY-SA版权

python 专栏收录该内容

12 篇文章

订阅专栏

该脚本用于从数据库中获取指定时间段内的特定数据，使用openpyxl库生成Excel报表，然后通过SMTP发送带有附件的邮件。主要涉及Python的数据库操作、Excel处理和邮件发送功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/usr/bin/env python3
# _*_ coding: utf-8 _*_
# @Time     : 2021/7/1 10:50
# @Author   :  Ren

from openpyxl.utils import get_column_letter
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import openpyxl
import datetime
import cx_Oracle
import smtplib
import time


now = datetime.datetime.now()
year = now.year
start_month = now.month
end_month = now.month + 1
start = datetime.date(year,start_month,1)
end = datetime.date(year,end_month,1)
timestr = start.strftime('%Y%m')
name = 'XXX-统计'
filename = name + timestr + '.xlsx'


sql = """
    select t.mawbcode, t.revpima, max(m.send_time) as send_time,
       max(chp.orgid) as orgid 
       　from fsumessage t
  left join fwb_dashboard m on t.mawbcode = m.mawbcode
  left join cfgccspro_fe chp on t.revpima = chp.chppima
 where t.msgdate >= to_date('%s', 'yyyy-MM-dd')
   and t.msgdate < to_date('%s', 'yyyy-MM-dd')
   and t.msgtype = 'CCS.FSU.FROM.CHP'
   and t.mawbcode is not null
 group by t.mawbcode, t.revpima
    """  %(start,end)


def main(sql,filename):
    sql = sql
    filename = filename
    try:
        db = cx_Oracle.connect('ccs','sinoair','10.29.88.17:1521/orcl')
        cursor = db.cursor()

        cursor.execute(sql)
        data = cursor.fetchall()
        title = ['主单号', 'PIMA', '最后发送时间', '代理六子码']
        wb = openpyxl.Workbook()
        ws = wb['Sheet']
        for index in range (1,len(title) +1):
            ws.cell(row=1,column=index).value = title[index - 1]
        for row in range(2,len(data) +2 ):
            for col in range(1,len(title)+1):
                ws.cell(row=row, column=col).value = data[row -2][col -1]

        wb.save(filename)
        wb.close()
        db.close

    except cx_Oracle.Error as e:
        print(e)


def sendMail():
    host = "smtphz.qiye.163.com"
    user = "xxx@csdn.cn"
    passwd = "sFR5VUYWjEnGMNF4"
    recv = ["xxx@csdn.cn"]
    ccrecv = ['xxx@csdn.cn','xxx@csdn.cn','xxx@csdn.cn']
    message = MIMEMultipart()
    message['From'] = user
    message['To'] = ','.join(recv)
    message['Cc'] = ";".join(ccrecv)
    times = start.strftime('%Y年%m月')
    Subject = times + name
    message['Subject'] = Header(Subject,'utf-8')
    text = 'Dear: \n\t' + Subject + '已出,详见附件。\n\t祝好！'
    message.attach(MIMEText(text,'plain','utf-8'))
    att = MIMEText(open(filename,'rb').read(),'base64','utf-8')
    att.add_header('Content-Type','application/octet-stream')
    att.add_header('Content-Disposition','attachment',filename=filename)
    message.attach(att)
    try:
        smtp = smtplib.SMTP()
        smtp.connect(host,25)
        smtp.login(user,passwd)
        smtp.sendmail(user,recv + ccrecv ,message.as_string())
        print("邮件发送成功")
        smtp.quit()
    except smtplib.SMTPException as e:
        print("Error:无法发送邮件 \n%s" %e)
if __name__ == '__main__':
    main(sql,filename)
    time.sleep(10)
    sendMail()