I am trying to create a function that can send an email with a dataframe attached as a csv file. Attaching files normally requires the file to be saved to disk first, so I don't know if there is any straight forward way to go about this?
I have created a function that can attach the dataframe as HTML, and also a function that can send attachments as email, but nothing that can directly send the dataframe as an attached file
General setup
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import smtplib
import os
_server = 'MY_SERVER'
_port = 9999
_sender = 'my.email@domain.com'
def create_simple_mail(to, title):
mail = MIMEMultipart()
mail['Subject'] = title
mail['To'] = to
mail['From'] = _sender
Sending dataframe as html
def mail_dataframe_as_html(to, msg, title, df):
mail = create_simple_mail(to, msg, title)
html_str = msg
html_str += '<tr></tr><tr></tr>'
html_str += df.to_html()
mail.attach(MIMEText(html_str, 'html'))
smtp_connection = smtplib.SMTP(_server, _port, timeout=120)
smtp_connection.sendmail(_sender, to, mail.as_string())
Sending attachments
def attach_file_to_mail(mail,f):
with open(f, "rb") as fil:
part = MIMEApplication(fil.read(), Name=os.path.basename(f))
part['Content-Disposition'] = 'attachment; filename="%s"' % os.path.basename(f)
mail.attach(part)
return mail
def mail_html(to, title, html, attachments=None):
mail = create_simple_mail(to=to, msg=None, title=title)
mail.attach(MIMEText(html, 'html'))
if attachments is not None:
for f in attachments:
mail = attach_file_to_mail(mail,f)
smtp_connection = smtplib.SMTP(_server, _port, timeout=120)
smtp_connection.sendmail(_sender, to, mail.as_string())
I figured out a quite neat solution using __enter__ and __exit__ to clean-up a temporary generated file that can be attached to the email.
import os
import uuid
class CreateAttachments:
def __init__(self, dfs, file_names=None):
self._attachments = dfs
# Create generic file names if none was specified
if file_names is None:
self._names = [f'attached_df{i}.csv' for i in range(len(dfs))]
# Ensure all file names ends with the .csv extension
else:
self._names = [fn if fn.endswith('.csv') else fn + '.csv' for fn in file_names]
# If less file names than attachments were provided, generic names are appended
if len(self._names) < len(self._attachments):
self._names += [f'attached_df{i}.csv' for i in range(len(self._attachments) - len(self._names))]
# Create a temporary folder for the files
self._file_location = 'C:/Users/%s/%s/' % (os.getenv('username'), str(uuid.uuid4()))
def __enter__(self):
# If by the odd chance the temporary folder already exists, we abort the program
if os.path.isdir(self._file_location):
raise IsADirectoryError("Directory already exists. Aborting")
# Create temporary folder
os.makedirs(self._file_location)
# For each attachment, save the dataframe to a .csv in the temporary folder
attach_paths = []
for attach, name in zip(self._attachments, self._names):
tmp_path = self._file_location + name
attach.to_csv(tmp_path)
attach_paths.append(tmp_path)
# Save all paths to class, as they are needed in the __exit__ function
self.paths = attach_paths
# Return paths to use for attachments
return self.paths
def __exit__(self, exc_type, exc_val, exc_tb):
# Remove the .csv files for each attachment
for path in self.paths:
os.remove(path)
# Remove the temporary folder
os.removedirs(self._file_location)
With the above code, I can now create attachments like so:
import pandas as pd
df1 = pd.DataFrame([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
df2 = pd.DataFrame([[2, 4, 6], [3, 6, 9], [4, 8, 12]])
html_str = '''<table style="width:100%">'''
html_str += '<tr> <font size="4"><b>Some HTML example</b> </font size> </tr>'
html_str += '<br> This is a test mail.'
with CreateAttachments([df1, df2]) as f:
mail_html(to='receiver@domain.com',
html=html_str,
title='Test email with attachments',
attachments=f)