SMTP email system using RDS database

February 8, 2022

For context here is my code:

import pymysql
import os
import smtplib
from email.message import EmailMessage

# RDS config
endpoint = '**************'
username = '*****'
password = '*********'
database_name = '**********'

#connection config

connection = pymysql.connect(host=endpoint,user=username,passwd=password,db=database_name)

def handler(event, context):
    cursor = connection.cursor()
    cursor.execute('SELECT `Presenters`.Email FROM `Main` INNER JOIN `Presenters` ON `Main`.`PresenterID` = `Presenters`.`PresentersID` WHERE `Main`.`Read Day` ="Wednesday"')

    rows = cursor.fetchall()
    
    for row in rows:
        print("{0}".format(row[0]))
    
    EMAIL_ADDRESS = "***********"
    EMAIL_PASSWORD = "*********"
        
    msg = EmailMessage()
    msg['Subject'] = "***********"
    msg['From'] = EMAIL_ADDRESS
    msg['To'] = ["{0}".format(row[0]),]
        
    msg.set_content('**************')
        
    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as smtp:
        smtp.login(EMAIL_ADDRESS, EMAIL_PASSWORD)
        smtp.send_message(msg)

The system works fine mostly a mysql query retrieves a series of emails from an RDS DB and feeds them into the recipients variable however it only seems to send emails to one of the emails not both/all of them.

here is the output log from the lambda function if it helps:

Test Event Name
test

Response
null

Function Logs
START RequestId: cd5e4bd8-28fc-4a0f-af0c-5f8608b46a57 Version: $LATEST
placeholder@email.example
placeholder@email.example
END RequestId: cd5e4bd8-28fc-4a0f-af0c-5f8608b46a57
REPORT RequestId: cd5e4bd8-28fc-4a0f-af0c-5f8608b46a57  Duration: 2036.07 ms    Billed Duration: 2037 ms    Memory Size: 128 MB Max Memory Used: 43 MB  Init Duration: 153.63 ms

Request ID
cd5e4bd8-28fc-4a0f-af0c-5f8608b46a57

>Solution :

You need to loop through all the rows to send emails to recipients defined in each row. Currently, you are using a single row‘s data to send one email. Following should work for you –

def handler(event, context):
    cursor = connection.cursor()
    cursor.execute('SELECT `Presenters`.Email FROM `Main` INNER JOIN `Presenters` ON `Main`.`PresenterID` = `Presenters`.`PresentersID` WHERE `Main`.`Read Day` ="Wednesday"')

    rows = cursor.fetchall()
    
    for row in rows:
        print("{0}".format(row[0]))
    
    EMAIL_ADDRESS = "***********"
    EMAIL_PASSWORD = "*********"

    for row in rows:
        msg = EmailMessage()
        msg['Subject'] = "***********"
        msg['From'] = EMAIL_ADDRESS
        msg['To'] = ["{0}".format(row[0]),]
            
        msg.set_content('**************')
            
        with smtplib.SMTP_SSL('smtp.gmail.com', 465) as smtp:
            smtp.login(EMAIL_ADDRESS, EMAIL_PASSWORD)
            smtp.send_message(msg)