# 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)
Python实现读取MySQL库数据生成excel并用邮件发送
最新推荐文章于 2024-10-03 09:02:12 发布