PySpark: Send Dataframe as Mail

This tutorial will explain how to iterate/loop dataframe content row by row to send it in an email. There are many different types of email packages available in Python, this tutorial will use one such package.

Sample Data:

here


df = spark.read.csv("file:///path_to_files/csv_with_duplicates_and_nulls.csv",header=True)

df.show()
+-----+---------+-------+
|db_id|  db_name|db_type|
+-----+---------+-------+
|   12| Teradata|  RDBMS|
|   14|Snowflake|CloudDB|
|   15|  Vertica|  RDBMS|
|   12| Teradata|  RDBMS|
|   22|    Mysql|   null|
|   50|Snowflake|  RDBMS|
|   51|     null|CloudDB|
+-----+---------+-------+

➠ Sending Dataframe as HTML table: Step by step process to generate and send email.

Step 1: Importing one of the Python package for sending email.


from email.mime.text import MIMEText
import smtplib

emailServer = 'smtp.dbmstutorials.com' #smtp server
emailServerPort = 587 #smtp server port
emailUser = 'tutorials@dbmstutorials.com'
emailUserPassword = 'mail_password'

Step 2: Looping through dataframe using toLocalIterator to generate mail body as HTML table string.


mailBody='''<html>
<head>
<style>
table, th, td {
  border: 1px solid;border-collapse:collapse;
}

table {
  width: 100%;
}
</style>
</head><table>'''

mailBody=mailBody+''.join(["<th>"+cols+"</th>" for cols in df.columns])

for row in df.toLocalIterator():
    mailBody=mailBody+"<tr>"
    
    mailBody=mailBody+ ''.join(["<td>"+str(row[column])+"</td>" for column in df.columns])
    
    mailBody=mailBody+"</tr>\n"

mailBody=mailBody+"</table></html>"

Step 3: Creating a MimeText message using mail body string generated in previous step and defining basic email attributes like email subject, from & to email Ids.


message = MIMEText(mailBody,_subtype='HTML')
message['Subject'] = "Dataframe Mail" #Subject of the email
message['From'] = emailUser
message['To'] = "to@dbmstutorials.com, info@dbmstutorials.com" # email Ids defined here, will be visible in the TO field of email.

toEmailList = ["to@dbmstutorials.com", "info@dbmstutorials.com"] # Actual TO list where emails will be sent.

Step 4: Defining and logging into email Server to send email.


smtp = smtplib.SMTP(emailServer, emailServerPort) # Initialing SMTP class
smtp.starttls()
smtp.login(emailUser, emailUserPassword) # Login to Email Server to send mail.

Step 5: Send email with attributes defined in previous steps.


smtp.sendmail(from_addr=emailUser, to_addrs=toEmailList, msg=message.as_string())