Python实现读取MySQL库数据生成excel并用邮件发送

最新推荐文章于 2024-06-14 09:50:01 发布

我是星星我会发光i

最新推荐文章于 2024-06-14 09:50:01 发布

阅读量104

点赞数 2

文章标签： python mysql excel

我是星星我会发光

本文链接：https://blog.csdn.net/weixin_43233971/article/details/139126721

版权

# encoding:utf-8
# @Function: 读取标签表数据发送邮件
import sys, os
from email.mime.application import MIMEApplication
from email.mime.text import MIMEText
from urllib.parse import quote_plus
from sqlalchemy import create_engine
import pandas as pd
import smtplib
from email.mime.multipart import MIMEMultipart
from datetime import datetime

sys.path.append(os.path.dirname(os.path.abspath(__file__)) + "/../")
from config import *


# 数据库连接参数
def read_mysql_table(table_name):
    env_vars = {
        'host': os.environ.get("MYSQL_HOST"),
        'port': int(os.environ.get("MYSQL_PORT")),
        'database': os.environ.get("MYSQL_DATABASE_OA"),
        'user': os.environ.get("MYSQL_USER"),
        'password': quote_plus(os.environ.get("MYSQL_PASSWORD"))
    }
    # 查询语句
    query_sql = f'select date, self_operdted,amount_walk,amount_collect,total_consumption,data_type from {table_name}'
    # 获取MySQL数据库连接
    connection_string = f"mysql://{env_vars['user']}:{env_vars['password']}@{env_vars['host']}:{env_vars['port']}/{env_vars['database']}"

    engine = create_engine(connection_string)
    # 读取数据
    df = pd.read_sql(query_sql, engine)
    return df


# 生成Excel文件
def create_excel(data_frames, file_name):
    with pd.ExcelWriter(file_name, engine='openpyxl') as writer:
        for data_type, df in data_frames.items():
            df.to_excel(writer, sheet_name=data_type, index=False)


# 根据data_type字段将数据分割成两个DataFrame
def split_data_by_type(df, types):
    data_frames = {type: df[df['data_type'] == type] for type in types}
    return data_frames


# 发送邮件
def send_email(excel_file):
    # 配置邮件信息
    qq_email = os.environ["SEND_EMAIL_ACCOUNT"]
    qq_password = os.environ["SEND_EMAIL_PWD"]
    sender_email = qq_email
    receiver_email = ['xxx@xxx.com', 'xxx@xxx.com']
    subject = '数据'
    body = '数据'
    # 创建一个带附件的实例
    message = MIMEMultipart()
    message['From'] = sender_email
    message['To'] = ', '.join(receiver_email)
    message['Subject'] = subject

    # 添加邮件正文
    message.attach(MIMEText(body, 'plain'))

    # 添加Excel附件
    xlsx_part = MIMEApplication(open(excel_file, 'rb').read())
    tmp =  f'数据_{datetime.now().strftime("%Y%m%d")}.xlsx'
    xlsx_part.add_header('Content-Disposition', 'attachment', filename=tmp)
    message.attach(xlsx_part)

    # 发送邮件
    server = smtplib.SMTP_SSL('smtp.qq.com', 465)
    server.login(qq_email, qq_password)
    server.sendmail(sender_email, receiver_email, message.as_string())
    server.quit()


if __name__ == '__main__':
    table_name = 'media_account_label_data'
    df = read_mysql_table(table_name)
    # 假设data_type字段的值为'日结'和'月结'
    data_types = ['日结', '月结']
    data_frames = split_data_by_type(df, data_types)
    data_frames['日结'].columns = ['日期', '自运营', '走量', '收量', '总消耗', '数据类型']
    data_frames['月结'].columns = ['日期', '自运营', '走量', '收量', '总消耗', '数据类型']
    excel_file = os.environ['EXCEL_PATH'] + '/' + f'数据_{datetime.now().strftime("%Y%m%d")}.xlsx'
    create_excel(data_frames, excel_file)
    send_email(excel_file)