【综合案例】sqlalchemy + pandas + xlwings + smtplib + configparser

最新推荐文章于 2024-05-27 11:04:50 发布

Fergus awsl

最新推荐文章于 2024-05-27 11:04:50 发布

阅读量412

点赞数 1

分类专栏： python基础 SQL

本文链接：https://blog.csdn.net/weixin_43650411/article/details/106082385

版权

本文介绍了一个使用Python进行自动化任务的案例，通过结合SQLAlchemy进行数据库操作，Pandas处理数据，xlwings生成Excel报告，SMTPLIB发送邮件，以及ConfigParser读取配置，实现了每周自动发送包含近两周Top30客户周环比的邮件功能。

摘要由CSDN通过智能技术生成

需求

每周邮件发送：近两周 top30 的客户周环比

环境 & 工具

Win7
Python 3.6
Excel

流程

在这里插入图片描述

代码

访问配置文件

# _*_ conding: utf-8 _*_
'''
// getConfig.py
从配置文件中读取信息
'''

from configparser import ConfigParser

class Conf():

    def __init__(self):
        self._path = r'H:\SZ_数据\Python\c.s.conf'

    def getEmail(self, sec, smt, email, pw):
        fil = ConfigParser()
        fil.read(self._path)
        return (fil.get(sec, smt),
                fil.get(sec, email),
                fil.get(sec, pw))
                
    def getToEmail(self, sec, toEmail):
        fil = ConfigParser()
        fil.read(self._path)
        return fil.get(sec, toEmail)

发送邮件

# _*_ coding: utf-8 _*_
'''
// sendEmail.py
Send email

'''

import smtplib
from getConfig import Conf
from email.header import Header
from email.mime.text import MIMEText
from email.utils import parseaddr, formataddr
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

def _format_addr(s):
    # 格式化邮件地址
    # Header 如是中文必须编码
    #
    name, addr = parseaddr(s)
    return formataddr((Header(name, 'utf-8').encode(), addr))

def sendEmail(subject, message, files=None, to_addr='newIOSys'):
    # login info
    conf = Conf()
    smt, fro, pw = conf.getEmail('mail_baidu', 'sender server', 'email'
                                , 'password')
    to = conf.getToEmail('to_addr', to_addr)
    # header of email
    msg = MIMEMultipart()
    msg['From'] = fro
    msg['To'] = to
    msg['Subject'] = Header(subject, 'utf-8').encode()
    # Main Body
    msg.attach(MIMEText(message, 'plain', 'utf-8'))
    # attachment
    import os
    if files != None:
        for i in range(len(files)):
            if os.path.isfile(files[i]):
                with open(files[i], 'rb') as f:
                    xl = MIMEApplication(f.read())
                    xl.add_header('Content-Disposition', 'attachment'
                                , filename=os.path.split(files[i])[-1])
                    msg.attach(xl)
    # Send
    with smtplib.SMTP(smt, 25) as smtp:
        smtp.ehlo()
        smtp.starttls()
        smtp.ehlo()
        smtp.set_debuglevel(1)
        smtp.login(fro, pw)
        try:
            smtp.sendmail(fro, to.split(','), msg.as_string())
        except Exception as e:
            print('Failed send: {}'.format(e))
        else: