微信公众号:数据运营人
本系列为博主的读书学习笔记,如需转载请注明出处。
将mysql数据转换为excel格式并发送邮件
1.将mysql数据转换为excel
import xlwt
import pymysql as MYSQLdb
import datetime
import pandas as pd
conn = MYSQLdb.connect(host='localhost', # 主机
user='root', # 用户名
passwd='010023', # 密码
db='stu' # 库名
)
cursor = conn.cursor()
count = cursor.execute('select * from student')
results = cursor.fetchall()
# 将结果存储到excel中
data = pd.DataFrame(list(results),columns=['序号','姓名','出生日期','性别'])
print(data)
data.to_excel('C:/Users/Admin/Desktop/data.xlsx')
2.将excel格式的数据以邮件的方式发送
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
def send(path,fromadd,passwd,toadd,title,content):
'''
:param path: 文件路径
:param fromadd: 源邮箱地址
:param passwd: 源邮箱密码
:param toadd: 目标地址
:param title: 邮件标题
:param content: 邮件内容
:return:
'''
excelApart = MIMEApplication(open(path, 'rb').read())
excelApart.add_header('Content-Disposition', 'attachment', filename=path)
textApart = MIMEText(content, 'plain', 'utf-8') # 邮件内容
m = MIMEMultipart()
m.attach(textApart)
m.attach(excelApart)
m['Subject'] = title
m['From'] = fromadd
m['To'] = toadd
try:
server = smtplib.SMTP()
server.connect('smtp.163.com')
server.login(fromadd, passwd)
server.sendmail(fromadd, toadd, m.as_string())
print('邮件发送成功')
server.quit()
except smtplib.SMTPException as e:
print('邮件出现错误', e) # 打印错误
if __name__ == '__main__':
send(r'C:/Users/Admin/Desktop/data.xlsx',
'*********', # 源邮件地址
'*********', # 原邮件密码
"*********", # 目标邮件地址
'邮件标题',
'邮件内容')