背景
利用pandas从数据库读取数据到内存中时,如果需要生成xlsx文件,在本地通常采用以以下方法
python库的准备
pip install pandas
pip install openpyxl
在当前目录下生成data.xlsx文件,其中encoding = "utf-8"用于解决中文乱码问题
>>>import pandas as pd
>>>data = [[1,2,3],[4,5,6]]
>>>df = pd.DataFrame(data)
>>>df.to_excel("./data.xlsx", encoding = "utf-8")
邮箱授权码准备
登录QQ邮箱,获取授权码,步骤为:
设置->账户->POP3/IMAP/SMTP/Exchange/CardDAV/CalDAV服务
开启SMTP服务
点击生成授权码
复制授权码
读取data.xlsx到内存
>>>open("data.xlsx", 'rb').read()
发送邮件
from io import BytesIO # 实现了在内存中读写bytes
import pandas as pd
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import formataddr
from email.mime.application import MIMEApplication
import smtplib
def send_email(recevie_email):
subject = "title"#邮件标题
text_message = "content"#邮件正文
EMAIL_HOST = 'smtp.qq.com'#邮箱服务器(默认)
EMAIL_PORT = 25 #邮箱端口号(默认)
EMAIL_HOST_USER = "xxx@qq.com"#发送者的邮箱
EMAIL_HOST_PASSWORD = "lkcuieoscscsacasd"#发送者的邮箱的授权码
EMAIL_FROM = "xxx202" #显示发送者别名
file_name = "data.xlsx"
data = [[1,2,3],[4,5,6]]
pd.DataFrame(data).to_excel(file_name, encoding="utf-8")
try:
m = MIMEMultipart()
m['From'] = formataddr([EMAIL_FROM, EMAIL_HOST_USER])
m['Subject'] = Header(subject, 'utf-8')
m.attach(MIMEText(text_message, 'plain', 'utf-8'))
att1 = MIMEApplication(open(file_name, 'rb').read())
att1["Content-Type"] = 'application/vnd.ms-excel'
att1.add_header('Content-Disposition', 'attachment', filename=Header(file_name, 'utf-8').encode())
m.attach(att1)
smtpObj = smtplib.SMTP()
smtpObj.connect(EMAIL_HOST, EMAIL_PORT)
smtpObj.login(EMAIL_HOST_USER, EMAIL_HOST_PASSWORD)
smtpObj.sendmail(EMAIL_HOST_USER, recevie_email, m.as_string())
return "发送成功"
except Exception as e:
print("发送邮箱[异常][email: %s, error: %s]" % (str(recevie_email), e))
return "发送失败:{}".format(e)
执行代码
>>>recevie_email = "xxx@qq.com"#收件人邮箱
>>>send_email(recevie_email)
'发送成功'
发送给多个用户
>>>recevie_email = ["xxx123@qq.com", "xxx456@qq.com"]#收件人邮箱
>>>send_email(recevie_email)
'发送成功'
遇到的问题以及改进措施
通常在服务器端是不能生成生产xlsx文件,然后读取到内存进行发送邮件的。因此,我们使用BytesIO模块跳过生成xlsx文件的步骤,直接把xlsx文件读写成bytes
from io import BytesIO # 实现了在内存中读写bytes
import pandas as pd
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import formataddr
from email.mime.application import MIMEApplication
import smtplib
def send_email():
subject = "title"#邮件标题
text_message = "content"#邮件正文
EMAIL_HOST = 'smtp.qq.com'#邮箱服务器(默认)
EMAIL_PORT = 25 #邮箱端口号(默认)
EMAIL_HOST_USER = "xxx@qq.com"#发送者的邮箱
EMAIL_HOST_PASSWORD = "lkcuieoscscsacasd"#发送者的邮箱的授权码
EMAIL_FROM = "xxx202" #显示发送者别名
file_name = "data.xlsx"
data = [[1,2,3],[4,5,6]]
bio = BytesIO()
with pd.ExcelWriter(bio, engine='openpyxl') as writer:
pd.DataFrame(data).to_excel(writer, encoding="utf-8")
bio.seek(0)
try:
m = MIMEMultipart()
m['From'] = formataddr([EMAIL_FROM, EMAIL_HOST_USER])
m['Subject'] = Header(subject, 'utf-8')
m.attach(MIMEText(text_message, 'plain', 'utf-8'))
att1 = MIMEApplication(bio.read())
att1["Content-Type"] = 'application/vnd.ms-excel'
att1.add_header('Content-Disposition', 'attachment', filename=Header(file_name, 'utf-8').encode())
m.attach(att1)
smtpObj = smtplib.SMTP()
smtpObj.connect(EMAIL_HOST, EMAIL_PORT)
smtpObj.login(EMAIL_HOST_USER, EMAIL_HOST_PASSWORD)
smtpObj.sendmail(EMAIL_HOST_USER, email, m.as_string())
return "发送成功"
except Exception as e:
print("发送邮箱[异常][email: %s, error: %s]" % (str(email), e))
return "发送失败:" + e
两种方案成的bytes对比
(1)使用pd.to_excel()生成xlsx再用open()读成bytes
>>>open("data.xlsx", 'rb').read()
b'PK\x03\x04\x14\x00\x00\x00\x08\x00NO+S\x07AMb\x81\x00\x00\x00\xb1\x00\x00\x00\x10\x00\x00\x00docProps/app.xmlM\x8e=\x0b\x021\x10D\xff\xcaq\xbd\xb7A\xc1Bb@\xd0R\xb0\xb2\x0f{\x1b/\x90dC\xb2B~\xbe9\xc1\x8fn\x1eo\x18F\xdf\ng*\xe2\xa9\x0e-\x86T\x8f\xe3"\x92\x0f\x00\x15\x17\x8a\xb6N]\xa7n\x1c\x97h\xa5cy\x00;\xe7\x91\xce\x8c\xcfHI`\xab\xd4\x1e\xa8\t\xa5\x99\xe6M\xfe\x0e\x8eF\x9fr\x0e\x1e\xadxN\xe6\xea\xb1pe\'\xc3\xa5!\x05\r\xffrm\xde\xa9\xd45\xef&\xf5\x96\x1f\xd6\xf0;i^PK\x03\x04\x14\x00\x00\x00\x08\x00NO+S@\xdb\x9c\x97\xeb\x00\x00\x00\xcb\x01\x00\x00\x11\x00\x00\x00docProps/core.xml\xa5\x91\xc1N\xc30\x0c\x86_e\xca\xbdu\xd3\xc1\x10Q\x96\x0b\x88\x13HHL\x02q\x8b\x1co\xabh\xda(1j\xf7\xf6\xb4e\xeb@p\xdb1\xfe?\x7f\xb6\x15\x8dAa\x1b\xe99\xb6\x81"W\x94\x16\xbd\xaf\x9b\xa40\xac\xc5\x9e9(\x80\x84{\xf26\xe5\x03\xd1\x0c\xe1\xb6\x8d\xde\xf2\xf0\x8c;\x08\x16?\xec\x8e\xa0,\x8a\x15xb\xeb,[\x18\x85Y\x98\x8d\xe2\xa8t8+\xc3g\xac\'\x81C\xa0\x9a<5\x9c@\xe6\x12\xce,S\xf4\xe9\xdf\x86)\x99\xc9>U3\xd5u]\xde-\'n\xd8H\xc2\xdb\xd3\xe3\xcb\xb4|V5\x89m\x83$\x8cv\xa80\x92\xe56\x9a\xf1\xa2p\xe8k\r?\x8a\xfa8\xfb\xbb@n1LP|\x08\xb4\x16\xa7\xe4uyw\xbfy\x1