Python/Powershell读取CSV分组后发送Action 2事件通知邮件方案咨询

一、Pandas实现数据筛选与分组 #

3万行数据量对于Pandas来说没有性能压力，完全可以高效完成筛选、分组操作，示例代码如下：
首先安装依赖：pip install pandas tabulate

import pandas as pd
from tabulate import tabulate

# 读取本地CSV文件
df = pd.read_csv("你的CSV文件路径.csv", dtype=str)

# 筛选Action值为2的记录
filtered_df = df[df["Action"] == "2"].copy()

# 按主管邮箱+姓名分组，避免同邮箱对应多个姓名的异常情况
grouped_data = filtered_df.groupby(["SupervisorEmail", "SupervisorName"], as_index=False)

# 遍历每个主管的分组生成邮件内容
for (supervisor_email, supervisor_name), group in grouped_data:
    # 提取需要在邮件中展示的字段
    show_table = group[["ID", "Name", "Action"]]
    # 格式化为对齐的纯文本表格
    table_content = tabulate(show_table, headers="keys", tablefmt="plain", showindex=False)
    # 拼接完整邮件正文
    mail_body = f"""Dear {supervisor_name},

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

{table_content}

Regards,
Corporate Events
"""
    # 此处调用邮件发送接口即可
    print(f"待发送给{supervisor_email}的邮件内容：\n{mail_body}\n======分割线======")

如果你不想额外安装tabulate库，也可以手动拼接字符串实现表格对齐。

二、Python对接认证SMTP网关发送邮件 #

推荐直接使用Python标准库smtplib+email，无需额外安装第三方依赖，支持身份认证、SSL加密等场景，示例代码如下：

import smtplib
from email.mime.text import MIMEText
from email.header import Header

def send_alert_mail(receiver_email, mail_content, smtp_config):
    # 构造纯文本邮件对象
    msg = MIMEText(mail_content, 'plain', 'utf-8')
    msg['From'] = Header(smtp_config["sender_display_name"], 'utf-8')
    msg['To'] = receiver_email
    msg['Subject'] = Header("Action 2 Event Notification", 'utf-8')

    # 连接SMTP服务器并发送
    with smtplib.SMTP_SSL(smtp_config["smtp_host"], smtp_config["smtp_port"]) as server:
        # SMTP身份认证
        server.login(smtp_config["smtp_account"], smtp_config["smtp_password"])
        server.sendmail(smtp_config["sender_email"], receiver_email, msg.as_string())

# 替换为你实际的SMTP网关配置
smtp_config = {
    "smtp_host": "smtp.你的企业域名.com",
    "smtp_port": 465, # 非SSL端口通常为25，STARTTLS端口通常为587
    "sender_email": "events@你的企业域名.com",
    "sender_display_name": "Corporate Events",
    "smtp_account": "你的SMTP认证账号",
    "smtp_password": "你的SMTP认证密码"
}

# 在前文分组循环中直接调用即可
# send_alert_mail(receiver_email=supervisor_email, mail_content=mail_body, smtp_config=smtp_config)

三、Powershell实现方案 #

Windows环境下可以直接使用Powershell原生能力完成全流程，无需安装任何额外工具，示例代码如下：

# SMTP网关配置
$smtp_params = @{
    SmtpServer = "smtp.你的企业域名.com"
    Port = 587
    UseSsl = $true
    Credential = New-Object System.Management.Automation.PSCredential ("你的SMTP认证账号", (ConvertTo-SecureString "你的SMTP认证密码" -AsPlainText -Force))
    From = "Corporate Events <events@你的企业域名.com>"
}

# 读取CSV并筛选Action=2的记录
$filtered = Import-Csv -Path "你的CSV文件路径.csv" | Where-Object {$_.Action -eq "2"}

# 按主管信息分组
$supervisor_groups = $filtered | Group-Object -Property SupervisorEmail, SupervisorName

foreach ($group in $supervisor_groups) {
    $receiver_mail = $group.Values[0]
    $supervisor_name = $group.Values[1]
    # 生成格式化表格
    $table = $group.Group | Select-Object ID, Name, Action | Format-Table -AutoSize | Out-String
    # 拼接邮件正文
    $mail_body = @"
Dear $supervisor_name,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

$table

Regards,
Corporate Events
"@
    # 发送邮件
    Send-MailMessage @smtp_params -To $receiver_mail -Subject "Action 2 Event Notification" -Body $mail_body
    Write-Host "已成功发送邮件给：$receiver_mail"
}

内容的提问来源于stack exchange，提问作者FP55