查询数据库表数据转存excel邮件推送-环球快报

1. 前期环境准备

1.1 安装python

(资料图)

1.2 安装引用的库：pandas  smtplib  sqlalchemy 

2. 准备写执行的脚本

2.1 内容如下：

```python

import pandas as pd

import smtplib

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.mime.application import MIMEApplication

from sqlalchemy import create_engine

# MySQL数据库连接信息

mysql_host = '数据库主机地址'

mysql_port = 3306  # 数据库端口

mysql_username = '数据库用户名'

mysql_password = '数据库密码'

mysql_database = '数据库名称'

# 查询语句

query = 'SELECT * FROM 查询的表'

# Excel文件保存路径

excel_file_path = '保存Excel文件的路径'

# 邮件配置信息

smtp_host = 'SMTP服务器地址'

smtp_port = 587  # SMTP端口

smtp_username = '发件人邮箱'

smtp_password = '发件人邮箱密码'

recipient_email = '收件人邮箱'

# 连接到MySQL数据库

mysql_url = f'mysql+pymysql://{mysql_username}:{mysql_password}@{mysql_host}:{mysql_port}/{mysql_database}'

engine = create_engine(mysql_url)

# 执行查询语句，获取数据

df = pd.read_sql_query(query, engine)

# 将数据保存为Excel文件

df.to_excel(excel_file_path, index=False)

# 构建邮件

msg = MIMEMultipart()

msg['From'] = smtp_username

msg['To'] = recipient_email

msg['Subject'] = 'MySQL查询结果'

# 添加邮件正文

body = '数据查询的结果'

msg.attach(MIMEText(body, 'plain'))

# 添加附件

with open(excel_file_path, 'rb') as f:

attachment = MIMEApplication(f.read(), 'xlsx')

attachment.add_header('Content-Disposition', 'attachment', filename='query_results.xlsx')

msg.attach(attachment)

# 发送邮件

with smtplib.SMTP(smtp_host, smtp_port) as server:

server.starttls()

server.login(smtp_username, smtp_password)

server.sendmail(smtp_username, recipient_email, msg.as_string())

print('邮件已发送')

```

关键词：