如何查询MySQL表中过期物料名称并展示在邮件正文中

现有Python代码可正常发送邮件，但无法从MySQL表中查询status = 'EXPIRED'条件对应的物料名称，需要在邮件正文中展示MySQL表内状态为EXPIRED的单个物料名称或物料名称列表，原始实现代码如下：

def email_alert(subject, body, to):
    msg = EmailMessage()
    msg.set_content(body)
    msg['subject'] = subject
    msg['to'] = to

    user = "*******@server.com"
    msg['from'] = user
    password = "iuhfiudsfhe"

    server = smtplib.SMTP("smtp.gmail.com", 587)
    server.starttls()
    server.login(user, password)
    server.send_message(msg)

    server.quit()


try:
    email_list = "SELECT Email FROM users"
    mycursor.execute(email_list)
    emails = mycursor.fetchall()
    emails = [i[0] for i in emails]
    str(emails).strip('[]')

except Exception as e:
    print(e)
    DB.rollback
    DB.close()

if matexpir <= d1:
    #Update material status on table.
    matstat = "EXPIRED"
    #GET material names from material table.
    try:
         matnames = "SELECT Name FROM material WHERE Status = 'EXPIRED'"
         mycursor.execute(matnames)
         #matids = mycursor.fetchall()
         #matids = [i[0] for i in matids]
    except Exception as e:
         print(e)
         DB.rollback
         DB.close()
    #Email alert for expired materials.
    email_alert("ALERT!", "your material {} has expired", emails).__format__(matnames)

• 物料查询逻辑未拉取结果：执行查询SQL后，fetchall()相关代码被注释，matnames变量存储的是SQL语句字符串，不是实际查询到的物料名称数据。
• 字符串格式化逻辑位置错误：__format__()被调用在email_alert()的返回值上（函数无返回值时默认返回None），格式化后的邮件正文根本没有传入发件函数。
• 收件人列表处理无效：str(emails).strip('[]')执行后没有赋值给变量，属于无效代码，没有实际处理收件人格式。
• 物料状态更新逻辑缺失：仅定义了matstat = "EXPIRED"变量，没有实际执行UPDATE语句将过期物料的状态更新为EXPIRED，后续查询自然拿不到对应数据。
• 数据库异常处理无效：DB.rollback、DB.close没有加括号，只是引用了方法对象，没有实际执行回滚、关闭连接的操作。
• 多物料场景正文适配缺失：原有正文模板仅支持单个物料占位，查询到多个过期物料时无法正确展示列表。

def email_alert(subject, body, to):
    msg = EmailMessage()
    msg.set_content(body)
    msg['subject'] = subject
    # 自动适配单个/多个收件人格式
    msg['to'] = to if isinstance(to, str) else ", ".join(to)

    user = "*******@server.com"
    msg['from'] = user
    password = "iuhfiudsfhe"

    server = smtplib.SMTP("smtp.gmail.com", 587)
    server.starttls()
    server.login(user, password)
    server.send_message(msg)
    server.quit()


try:
    email_list = "SELECT Email FROM users"
    mycursor.execute(email_list)
    emails = mycursor.fetchall()
    # 提取有效收件人邮箱列表
    emails = [i[0] for i in emails]
except Exception as e:
    print(e)
    # 加括号实际调用数据库回滚、关闭方法
    DB.rollback()
    DB.close()
    # 拉取收件人失败直接终止流程
    exit()

if matexpir <= d1:
    try:
        # 先执行物料状态更新，注意把expire_time替换为你表中实际存储过期时间的字段
        update_sql = "UPDATE material SET Status = 'EXPIRED' WHERE expire_time <= %s"
        mycursor.execute(update_sql, (d1,))
        DB.commit()

        # 查询所有状态为过期的物料名称
        mat_query = "SELECT Name FROM material WHERE Status = 'EXPIRED'"
        mycursor.execute(mat_query)
        # 拉取结果并提取为纯名称列表
        expired_materials = [row[0] for row in mycursor.fetchall()]
    except Exception as e:
        print(e)
        DB.rollback()
        DB.close()
        exit()

    # 没有过期物料就不发邮件
    if not expired_materials:
        DB.close()
        exit()

    # 适配单个/多个过期物料生成正文
    if len(expired_materials) == 1:
        mail_body = f"你的物料 {expired_materials[0]} 已过期"
    else:
        material_str = "、".join(expired_materials)
        mail_body = f"以下物料已过期：{material_str}"

    # 传入生成好的正文发送邮件
    email_alert("过期提醒！", mail_body, emails)

DB.close()

注意：UPDATE语句里的expire_time字段请替换为你自己业务表中存储物料过期时间的实际字段名，和外层过期判断逻辑保持一致即可。

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