Python--邮件提醒系统

1. 系统主要功能

1. 读取Excel文件的内容，判定其中的责任人，责任时间和完成情况，将完成情况on going和责任时间临期的项目的责任人筛选出来
2. 对读取到的责任人列表进行加工，去除重复项，如果责任人有多个的且是/分割开的，进行分割，
3. 对加工好的责任人列表，在Department表中查询他们的邮箱和Position里，并生成Email——list
4. 通过Email_list逐个给其发送邮件，邮件中需包含待办事项清单
5. 如果系统中存在问题，需要向管理员发送告警邮件
6. 需要包含日志功能，将系统运行信息记录到logging.txt文件中
7. 所有任务通过Email_Tracing_task查询
8. 每一个Tracing_task都是一个独立线程，线程失败，不影响其他的跟踪

2. 系统难点

1. pandas日期比较，偏差日期之后，可以用大小于号比较，不能日期加减之后与整数对比
2. 读取Excel文件时有些文件是加密的有些没有加密，需要判断文件是否加密，否则未加密的文件用加密的方法打开会报错。
3. 加密Excel文件的读取用msoffcrypto模块打开

3. 系统源代码

#! /user/bin/python
# author Gang ZHou
# date 2022-05-25
# version 2.0
# 项目功能
# 1. 读取Excel文件的内容，判定其中的责任人，责任时间和完成情况，将完成情况on going和责任时间临期的项目的责任人筛选出来
# 2. 对读取到的责任人列表进行加工，去除重复项，如果责任人有多个的且是/分割开的，进行分割，
# 3. 对加工好的责任人列表，在Department表中查询他们的邮箱和Position里，并生成Email——list
# 4. 通过Email_list逐个给其发送邮件，邮件中需包含待办事项清单
# 5. 如果系统中存在问题，需要向管理员发送告警邮件
# 6. 需要包含日志功能，将系统运行信息记录到logging.txt文件中
# 7. 所有任务通过Email_Tracing_task查询
# 8. 每一个Tracing_task都是一个独立线程，线程失败，不影响其他的跟踪


import pandas as pd
import xlrd          # pandas read_excel默认引用
import logging       # 日志文件
from email.mime.text import MIMEText  # 邮件格式
import smtplib       # 发送邮件
import datetime
import pymssql       # 读取数据库中的邮箱和任务列表
import threading     # 多线程
import msoffcrypto   # 读取加密的Excel文件
import io            # 将文件写入到内存中


# 定义一个函数，用来导入Excel文件，并将获取的dataFrame数据结构返回
def read_excel(path, sheet_name, title, header=0, password=''):
    decrypted = io.BytesIO()   # 用来后来将读取到的Excel文件解密之后写入内存中。
    try:
        with open(path, 'rb') as f:   # 用上下文将文件打开
            # 判断Excel文件是否加密，未加密直接pandas读取，加密的就先解密到内存中然后pandas读取。
            if not msoffcrypto.olefile.isOleFile(f):   # mscoffcrypto有可以判断文件是否加密的功能。
                pd_excel = pd.read_excel(f, sheet_name, header=header)
                logging.info("{title}文件读取成功".format(title=title))
            else:
                xlf = msoffcrypto.OfficeFile(f)   # 用msoffcrypto打开加密文件，未加密文件用这个打开会报错
                xlf.load_key(password=password)   # 传入文件的密码
                xlf.decrypt(decrypted)            # 将文件解密并保存到内存中，定义descrypted为io.BytesIO() ，这是msoffcrypto常用方法。
                pd_excel = pd.read_excel(decrypted, sheet_name, header=header)  # 将保存到内存中的文件用pandas打开。
                # 通过pandas读取Excel的内容，指定文件路径名称以及要读取的sheet的名称
                logging.info("{title}文件读取成功".format(title=title))
    except Exception as e:   # 如果文件打开失败，则将报错信息记录到日志中，并向管理员发送邮件。
        error_msg = "Excel文件{title}打开失败，请检查文件路径及权限。".format(title=title)
        logging.critical(error_msg)
        logging.critical(e)
        send_waring_mail(error_msg, title)
        return
    pd.set_option("display.max_columns", None)   # 将pandas输出设置为显示所有列。在辅助print打印时使用
    # 将责任时间的数据格式转换成datetime类型，如此在后期可以参与实践比较
    pd_excel['责任时间'] = pd.to_datetime(pd_excel['责任时间'])
    return pd_excel


# 定义一个函数，用以查询Excel文件中所有状态为on going 并且责任时间临期或超期的项，并提取出责任人列表
def general_user_list(pd_excel, title):
    # pandas的数据结构中必须包含责任人，责任时间，完成情况
    # 设置一个时间偏移量，此处偏移量为1.也就是说如果责任时间是2022-05-26， 那么如果今天是2022-05-25及之后的时间，就符合筛选条件
    delta = datetime.timedelta(days=1)
    # 通过loc[]筛选出完成情况为on going并且当前日期偏差一天之后大于责任时间，& 两边的条件都要用小括号()括起来。
    try:
        pd_loc1 = pd_excel.loc[(pd_excel["完成情况"] == "on going") &
                               (datetime.datetime.now()+delta > pd_excel['责任时间'])]
        # 将获取到的责任人列表进行去重，并复制给send_owners
        send_owners = pd_loc1['责任人'].drop_duplicates()
    except Exception as e:
        error_msg = "{}对Excel的‘责任人’查询失败".format(title)
        logging.info(error_msg)
        logging.info(title, ' ', e)
        return

    receive_list_user = []  # 创建一个而空列表，用来保存筛选出来的数据
    # 清洗列表, 如果责任人中包含/,则将'/'前后的都分别作为一个责任人加入责任人列表。
    for receiver in send_owners:
        if receiver.find('/'):  # 如果责任人中存在/,则表示一条数据中同时包含多个责任人，
            x = receiver.split("/", 3)  # 通过split函数将责任人分割成一个列表
            receive_list_user.extend(x)  # 将分割后的列表与筛选列表进行合并
        else:  # 否则的话，就将用户直接加入到筛选列表中
            receive_list_user.append(receiver)
    receive_list_user = list(set(receive_list_user))
    user_list = []  #创建列表保存清洗过后的用户信息
    for user in receive_list_user:
        user =user.strip()   # 去除用户列表中的空格，并保存到user_list中
        user_list.append(user)
    user_list = list(set(user_list))   # 对user_list进行去重
    receive_list_user = user_list
    print('user_list', receive_list_user)
    logging.info('{title}要发送的用户列表{user_list}'.format(title=title, user_list=receive_list_user))
    return receive_list_user


# 定义一个函数，通过责任人列表查找对应的邮箱地址和Position
def general_email_list(receive_list, title):
    if not receive_list:
        return
    server_name = 'XXXX54'
    username = 'sa'
    password = 'XXX@2022'
    try:
        conn = pymssql.connect(server_name, username, password, "FineReport")
    except Exception as e:
        error_msg = "数据库连接失败，请检查数据库连接"
        logging.critical(error_msg)
        logging.critical(e)
        send_waring_mail(error_msg, title)
        return
    cursor = conn.cursor()

    print(receive_list)
    email_list = []  # 创建一个列表，用来保存用户名和邮箱的键值对。
    for user in receive_list:
        # 通过经理姓名查找经理对应的邮箱，并将姓名和邮箱一起保存到email_list中。
        sql_mail = "select email,Position from [FineReport].[dbo].[Department] where username='{}'".format(user)
        cursor.execute(sql_mail)
        email = cursor.fetchone()
        if not email:  # 如果数据库中未查询到email信息，那么就向管理员发送告警邮件，并生成日志。
            error_msg = "用户{}的邮箱，请维护邮箱表".format(user)
            logging.warning("用户{}的邮箱，请维护邮箱表".format(user))
            send_waring_mail(error_msg, title)
            continue
        email_list.append((user, email[0], email[1]), )  # 查到数据就加入到email_list列表中
    logging.info(email_list)
    cursor.close()
    conn.close()
    print('email_list', email_list)
    logging.info("{title}需要发送邮件的列表为：{list}".format(title=title, list=email_list))
    return email_list


# 定义一个函数，当系统出现问题时，向管理员发送邮件。
def send_waring_mail(error_msg, title):
    # 创建邮箱连接，采用匿名发送邮件的方法，不需要使用登录名和密码，发送邮箱的名称可以自定义，不需要exchange服务器创建。
    smtp = smtplib.SMTP()
    try:
        smtp.connect('172.26.2.10', 587)
    except Exception as e:
        logging.critical('邮件服务器连接失败，请检查邮箱服务器和网络！')
        logging.critical(e)
        return
    mail_msg = """
    管理员您好：
        跟踪系统出现问题，问题消息如下：
        {error}
        请检查。<br/>
        对应excel跟踪文件为：{title}
    """.format(error=error_msg, title=title)
    msg = MIMEText(mail_msg, 'html', 'utf-8')
    msg['Subject'] = "DOM Tracing ERROR message"
    msg['From'] = 'CQ_DOM_OIL@XXXX.com'
    try:
        smtp.sendmail('CQ_OIL@XXXX.com', 'gang.zhou@XXXX.com', msg.as_string())
        logging.info("向管理员发送警告邮件成功。")
    except Exception as e:
        logging.warning("向管理员发送邮件失败，请检查其邮箱配置！")
        logging.warning(e)


# 定义一个发送邮件的函数，遍历email_list,向里面的所有邮箱账号单独发送一次邮件。
def send_mail(email_list, pd_excel, url, title, owner):
    # 创建邮箱连接，采用匿名发送邮件的方法，不需要使用登录名和密码，发送邮箱的名称可以自定义，不需要exchange服务器创建。
    smtp = smtplib.SMTP()
    try:
        smtp.connect('172.26.2.10', 587)
    except Exception as e:
        logging.critical('邮件服务器连接失败，请检查邮箱服务器和网络！')
        logging.critical(e)
    for user in email_list:
        # 查找数据列表包含责任人为ALL和责任人中包含该用户的所有临期信息。
        pd_excel['责任时间'] = pd.to_datetime(pd_excel['责任时间'])
        delta = datetime.timedelta(days=1)
        try:
            pd_loc1 = pd_excel.loc[(pd_excel["完成情况"] == "on going") &
                                   (datetime.datetime.now() + delta > pd_excel['责任时间']) &
                                   (pd_excel['责任人'].str.contains(user[0]))]
            df_html = pd_loc1.to_html(escape=False)
        except Exception as e:
            logging.info("{}查询责任人{}未办事项信息失败！".format(title, user[0]))
            logging.info(title, ' ', e)
            df_html = ''

        mail_msg = """
        <p style = "font:bold 14px/1em '微软雅黑'">
        {name} {position}，您好，<br/>
        <span style="color:red">{title}</span> 有您需要跟踪实施的项目即将临期或已经超期，请尽快实施并更新状态！<br/> 谢谢！<br/>
        跟踪表路径如下：
        {URL}
        <p style = "color:Blue;font: bold 14px/1em 'Aril'"></p></p>
        <p style = "color:Grey;font: italic 14px/1em 'Aril'">若有问题请联系{owner}，谢谢！</p>
        具体清单如下：
        {df_html}
        <h3>系统自动提示邮件，无需回复！</h3>
        <hr />
        """.format(name=user[0], position=user[2], df_html=df_html, URL=url, title=title, owner=owner)

        msg = MIMEText(mail_msg, 'html', 'utf-8')
        msg['Subject'] = title
        msg['From'] = 'CQ_Daily_Tracing@XXXX.com'
        try:
            smtp.sendmail('CQ_Daily_Tracing@XXXX.com', user[1], msg.as_string())
            logging.info("{}向用户{}发送邮件成功。".format(title, user))
        except Exception as e:
            error_msg = "{}向用户{}发送邮件失败，请检查其邮箱配置！".format(title, user)
            logging.warning(error_msg)
            logging.warning(e)
            send_waring_mail(error_msg, title)


# 定义一个函数，封装发送邮件，分别调用文件读取，生成用户清单，生成用户邮箱列表，然后通过邮箱列表向用户发送邮件
def encapsulation_send_mail(path, sheet_name, header, title, password, owner):
    pd_excel_encap = read_excel(path=path, sheet_name=sheet_name,
                                title=title, header=header, password=password)   # 读取Excel文件
    receive_list_encap = general_user_list(pd_excel=pd_excel_encap, title=title)    # 生成责任人列表
    email_list_encap = general_email_list(receive_list=receive_list_encap, title=title)  # 生成邮箱列表
    # email_list_encap = [('王波', 'gang.zhou@XXXX.com', '工程师'), ]   # 测试数据，可删除
    send_mail(email_list=email_list_encap, pd_excel=pd_excel_encap, url=path, title=title, owner=owner)


# 定义一个函数，查询数据表[Email_Tracing_task]，并且采用多线程的运行程序。
def read_task():
    server_name = 'XXXX54'
    username = 'sa'
    password = 'XXXX@2022'
    try:
        conn = pymssql.connect(server_name, username, password, "FineReport")
    except Exception as e:
        error_msg = "数据库连接失败，请检查数据库连接"
        logging.critical(error_msg)
        logging.critical(e)
        send_waring_mail(error_msg, title="hello")
        return
    finally: