Using Python and AWS Lambda to send Automated Emails

Indra Teja
Feb 7 · 8 min read

A detailed explanation of the process of sending customized, automated emails using Python, SQL, and AWS Lambda.

Overview:

I am currently working as a Data Scientist and one of the requirements my client asked me was to send a daily automated Email, containing one attachment file and the output from an SQL Query as an Inline table.

Being a beginner in Python, I had to involve in extensive and exhausting searches on the web for a code or process to achieve my desired tasks.
I was able to incorporate bits and pieces of code that I have found on the web and did finally manage to accomplish the task.

The code has been customized as per my need for sending an email from Google Mail Client(Gmail) using Python with an inline-table containing the output of a SQL query(which is being run from this code) and an attachment(also an output of SQL Query saved as a CSV file).

To ease this process I would like to document the steps required, issues I have faced and some workarounds to resolve them.


Code and Setup:

I will provide you with a link (or) file to the final and full code at the end of the story.

Now let’s have a view at the different integral parts of the codes and how to further customize them if required.

The below-explained code can be run on a local machine or on a serverless framework like AWS Lambda to automate the mail sending.

Step 1:
Dependencies

Similar to any other Python Projects, the code is initiated by importing a long list of libraries/dependencies.

from __future__ import unicode_literals
import base64
from os import path
import io
from io import StringIO
import pymysql
import imaplib
import json
import smtplib,ssl
import urllib.parse
import urllib.request
import lxml.html
from datetime import datetime
import pytz
import pandas as pd
from sqlalchemy import create_engine
from datetime import date, timedelta
from email.mime.base import MIMEBase
from email.utils import formatdate
from email import encoders
from email import charset
from email.charset import Charset, BASE64
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.nonmultipart import MIMENonMultipart
from IPython.display import HTML
from premailer import transform

You may not need to import every library that has been listed above or may need additional libraries required as per the users’ needs, but these are the ones I have used for my project.

Step 2:
Initiating Connection to Mail Client

  • As the sender’s email is provided by Gmail, a connection to the Gmail client should be initiated to help log on to the sender’s mail account.
Photo by Christina @ wocintechchat.com on Unsplash
GOOGLE_ACCOUNTS_BASE_URL = ‘https://accounts.google.com'
REDIRECT_URL = ***REDIRECT_URL***
GOOGLE_CLIENT_ID = ***GOOGLE_CLIENT_ID***
GOOGLE_CLIENT_SECRET = ***GOOGLE_CLIENT_SECRET***
GOOGLE_REFRESH_TOKEN = ***GOOGLE_REFRESH_TOKEN***
def command_to_url(command):
return '%s/%s' % (GOOGLE_ACCOUNTS_BASE_URL, command)
def url_escape(text):
return urllib.parse.quote(text, safe='~-._')
def url_unescape(text):
return urllib.parse.unquote(text)
def url_format_params(params):
param_fragments = []
for param in sorted(params.items(), key=lambda x: x[0]):
param_fragments.append('%s=%s' % (param[0], url_escape(param[1])))
return '&'.join(param_fragments)
def generate_permission_url(client_id, scope='https://mail.google.com/'):
params = {}
params['client_id'] = client_id
params['redirect_uri'] = REDIRECT_URI
params['scope'] = scope
params['response_type'] = 'code'
return '%s?%s' % (command_to_url('o/oauth2/auth'), url_format_params(params))
def call_authorize_tokens(client_id, client_secret, authorization_code):
params = {}
params['client_id'] = client_id
params['client_secret'] = client_secret
params['code'] = authorization_code
params['redirect_uri'] = REDIRECT_URI
params['grant_type'] = 'authorization_code'
request_url = command_to_url('o/oauth2/token')
response = urllib.request.urlopen(request_url, urllib.parse.urlencode(params).encode('UTF-8')).read().decode('UTF-8')
return json.loads(response)
def call_refresh_token(client_id, client_secret, refresh_token):
params = {}
params['client_id'] = client_id
params['client_secret'] = client_secret
params['refresh_token'] = refresh_token
params['grant_type'] = 'refresh_token'
request_url = command_to_url('o/oauth2/token')
response = urllib.request.urlopen(request_url, urllib.parse.urlencode(params).encode('UTF-8')).read().decode('UTF-8')
return json.loads(response)
def generate_oauth2_string(username, access_token, as_base64=False):
auth_string = 'user=%s\1auth=Bearer %s\1\1' % (username, access_token)
if as_base64:
auth_string = base64.b64encode(auth_string.encode('ascii')).decode('ascii')
return auth_string
def test_imap(user, auth_string):
imap_conn = imaplib.IMAP4_SSL('imap.gmail.com')
imap_conn.debug = 4
imap_conn.authenticate('XOAUTH2', lambda x: auth_string)
imap_conn.select('INBOX')
def test_smpt(user, base64_auth_string):
smtp_conn = smtplib.SMTP('smtp.gmail.com', 587)
smtp_conn.set_debuglevel(True)
smtp_conn.ehlo('test')
smtp_conn.starttls()
smtp_conn.docmd('AUTH', 'XOAUTH2 ' + base64_auth_string)
def get_authorization(google_client_id, google_client_secret):
scope = "https://mail.google.com/"
print('Navigate to the following URL to auth:', generate_permission_url(google_client_id, scope))
authorization_code = input('Enter verification code: ')
response = call_authorize_tokens(google_client_id, google_client_secret, authorization_code)
return response['refresh_token'], response['access_token'], response['expires_in']
def refresh_authorization(google_client_id, google_client_secret, refresh_token):
response = call_refresh_token(google_client_id, google_client_secret, refresh_token)
return response['access_token'], response['expires_in']

The user needs to provide the REDIRECT_URL, GOOGLE_CLIENT_ID,
GOOGLE_CLIENT_SECRET,
and GOOGLE_REFRESH_TOKEN for initiating a successful connection to the mail sending client.

Steps to get the above details can be found at:
Implementing Server-Side Authorization | Gmail API.

Step 2:
Connecting to a MySQL Database

Initiate a connection to MySQL Database:

engine = create_engine(“mysql+pymysql://username:pass@DB_ADDRESS/DB_Name”)
con_mysql = engine.connect()
  • The connection can be opened with other DB Services like SQL Server, Oracle DB e.t.c but in my case, my database is on a MySQL Server and hence the usage of the library pymysql.

Querying from the Database

As stated earlier my desire is to add an SQL Query Output as an Inline_table in the mail and a second SQL Query, whose output is saved as a CSV file and send it as an attachment with the file.

#Query_for_inline_tables
Query_table =
""" SELECT * FROM `db_name`.`table_name`
WHERE Month(`Date`) = Month((select curdate() - interval 1 DAY))
AND Year(`Date`) = Year((select curdate() - interval 1 DAY))
AND Date = (SELECT curdate() - interval 1 DAY)"""
table = pd.read_sql(Query_table, con_mysql)
  • The above query fetches the data from the specified tables for Yesterday’s date. The query can be customized to cater to the users’ needs.
#Query_for_attachment_files
Query_attach =
""" SELECT * FROM `db_name`.`table_name`
WHERE Month(`Date`) = Month((select curdate() - interval 1 DAY))
AND Year(`Date`) = Year((select curdate() - interval 1 DAY))
AND Date <= (SELECT curdate() - interval 1 DAY)"""
attach = pd.read_sql(Query_attach, con_mysql)
  • For the sake of demonstration, I’m using the above query which fetches the data from the specified tables from the Start of the Current Month until Yesterday.

The outputs of the queries are being stored as the pandas dataframes: table and attach.
More complex SQL queries can be run on the basis of our requirements.

Step 3:
Formatting of Inline-tables

df_index = table.set_index([‘Column1’, ‘Column2’])#Highliting_Totals_in_DataFrame
def highlight_total(s):
is_total = s.index.get_level_values(1).str.contains(pat = “Total”)
return [‘background-color: #D3D3D3’ if v else ‘background-color: #FBFCFC’ for v in is_total]
def bold_total(s):
is_total = s.index.get_level_values(1).str.contains(pat = “Total”)
return [‘font-weight: bolder’ if v else ‘font-weight: normal’ for v in is_total]
X = (((df_index.style.apply(highlight_total)).apply(bold_total)).set_properties(**{‘border-style’: ‘solid’}, **{‘border-width’:’0.5px’}, **{‘text-align’: ‘Left’})).set_table_styles([{‘selector’: ‘th’,’props’: [(‘background’, ‘#D3D3D3’), (‘border-style’, ‘solid’), (‘border-width’, ‘0.5px’)]}])
  • df_index is the resultant dataframe after the index is set on desired columns.
    Different pandas functions can be applied to the dataframe
    table for formatting the numbers, columns or rows as required.
  • Two functions highlight_total() and bold_total() are created which help in formatting the rows satisfying the specified conditions in the functions.
  • Further Styling like Grid, Hover Effects, Borders, Background and Font Colors can be customized by applying Pandas Styler Attributes.
  • X is the Pandas Styling Element with all the styles as specified by the users & X.render() transforms the Pandas Styler Object to an HTML String which is given as a Payload to the email and the HTML code is rendered into the table when viewed on a mail client website or app.
Y = transform(X.render(), pretty_print = True)

transform
The transform module of Premailer helps to convert the above HTML String to multiple Mail Client compatible HTML.
More info later in `Some Known Issues and Workarounds`.

Step 4:
Attachment Setup

  • Earlier the Output of Query2 is saved as a dataframe with the name attach.
attach.reset_index(drop = True, inplace = True)today = date.today()
yest = today - timedelta(days = 1)
start = yest.replace(day= 1)
#FILENAME-SETUP
def filenames(start, yest):
if start == yest:
return "Attachment_{}.csv".format(yest)
else:
return "Attachment_{}_to_{}.csv".format(start, yest)
  • I have created & used a function by the name filenames to name the attachment files.
path= r'/tmp/{}'.format(filenames(start,yest))
attach.to_csv(path, sep = str(','))
footer = "PFA the file - {}".format(filenames(start, yest))
files = filenames(start, yest)
  • The attachment can be of any kind like a CSV or Excel file, Image or a PDF file.
  • In our case, the dataframe attach is saved as a CSV file under the name of the output from the function filenames.
  • The path = r’/tmp/{}’.format(filenames(start,yest)) is modified with tmp so as to support serverless frameworks like AWS Lambda.
  • tmp can be replaced with the Filepaths as needed when the code is being run on a local machine.

Step 5:
Sending the Email:

  • This is the most important part of the code, where the function to send the email is executed.
def send_mail(fromaddr, subject, message):   access_token, expires_in = refresh_authorization(GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET,GOOGLE_REFRESH_TOKEN)   auth_string = generate_oauth2_string(fromaddr, access_token, as_base64=True)   msg = MIMEMultipart('related')   msg['Subject'] = subject   msg['From'] = fromaddr#AddtheListOfDesiredRecipientsfortheEmail   msg['To'] = "email1@gmail.com,email2@gmail.com,email@yahoo.co.in"#AddtheListOfCCfortheEmail   msg['Cc'] = "email4@gmail.com,email5@gmail.com,email7@yahoo.in"   msg.preamble = 'This is a multi-part message in MIME format.'
msg_alternative = MIMEMultipart('alternative')
msg.attach(msg_alternative)
part_text = MIMEText(lxml.html.fromstring(message).text_content().encode('utf-8'), 'plain', _charset='utf-8') part_html = MIMEText(message.encode('utf-8'), 'html', _charset='utf-8') msg_alternative.attach(part_text)
msg_alternative.attach(part_html)
toupload = open(path, "rb")
part = MIMEBase('application', "octet-stream")
part.set_payload(toupload.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename = filename.csv')
msg.attach(part)
server = smtplib.SMTP('smtp.gmail.com:587')
server.ehlo(GOOGLE_CLIENT_ID)
server.starttls()
server.docmd('AUTH', 'XOAUTH2 ' + auth_string)
server.sendmail(fromaddr,msg['To'].split(",") + msg['Cc'].split(","), msg.as_string())
server.quit()
send_mail('sender@gmail.com','Mail Sent From Python for Date: '{}'','<h3><b><u>Mail Sent From Python: {} </u></b></h3><h3><i>{}</i></h3>'''.format(yest, Y, footer))
***END OF CODE***

The above code components work together to perform the task of Sending Emails with Inline-tables and Attachments using Python.

Now all the code is written and done, you can call the function send_mail to send the email.

The code can be bundled with all the dependencies, uploaded to AWS S3 and can be used to run from AWS Lambda with the help of triggers from Cloudwatch to automate the process and send the mails at the desired times.

Some Known Issues and Workarounds:

  • Applying styling to the dataframes requires the indices to be unique.
    Using a function like groupby() may help in creating indices with unique values.
  • Even after applying the styling to the dataframes, some mail clients do not support CSS styling tags.
    premailer is used so that the HTML styling of the tables is not lost while using different mail clients to view the email and enable cross-compatibility.
    Ex: Gmail doesn’t support HTML styling tags without inline styling tags.
    Hence, the transform module from premailer is used to convert the code-generated HTML string to GMail compatible HTML scripts.
  • Be mindful of the styling being applied to the dataframe too.
    As the more styling attributes we provide, the more the size of the HTML string increases and GMail supports only 120 Kb of inline data, which may lead to a clipping of the message that is being sent which requires the recipients to perform a click to view the complete message.
  • Refrain from naming the .py project file as Email.py as to prevent any conflicts when running the code on AWS Lambda.

Conclusion:

Thank you for taking the time to refer to this story, hope it helped you and let me know of any feedback and queries you have.

The complete code can be found on Github at:
https://gist.github.com/kickbat6/45d325711b4ff64c8d315eda8ae0fcc9

Indra Teja

Written by

Data Scientist

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade