Email with Postgres and Python

Introduction

In this tutorial, we will see how to send email with Postgres and Python. We will cover using Python for sending email messages where values were retrieved from a Postgres table. Some functions we’ll use along the way include Python list, render_template from Flask, fetchall from psycopg2, and smtp from flask_mail. We’ll also use Postgres’ Declare, string concatenation (in both Python and PostgreSQL), TRIM, and WHERE.

Prerequisites

  • Use PIP to install Flask and psycopg2 libraries.
  • We’ll be using fairly simple SQL statements like “SELECT” and “WHERE” in order to pull data from a database table.

Knowing users are not perfect – that they typo from time to time – we will assume the “t_email_address” column in our table called tbl_users has values in some rows with unecessary spaces, so we’ll use the TRIM function to remove those extra spaces.

PostgreSQL Trim Function Syntax

Syntax of the TRIM() function

t_cleaned_string = TRIM([LEADING OR TRAILING OR BOTH] [optional: t_chars_to_remove] FROM t_source_string);

Analysis

  • LEADING: This is an optional parameter. Instructs PostgreSQL to start at the beginning of t_source_string and move forward.
  • TRAILING: Optional. Instructs PostgreSQL to start at the end of t_source_string and move backwards.
  • BOTH: Optional. Instructs PostgreSQL to remove the t_remove_these_chars character from both the beginning and end of t_source_string.
  • t_chars_to_remove: Supplies TRIM() with characters to look for. If not supplied, space (chr(32)) is used by default.
  • t_source_string: Mandatory. The value we input into to the function. In the examples below, it is the first one.

PostgreSQL Trim Function Examples

  • trim(” How do you prefer your tea? “) returns “How do you prefer your tea?” Same as “BOTH FROM” use.
  • trim(TRAILING FROM ” How do you prefer your tea? “) returns ” How do you prefer your tea?”
  • trim(LEADING FROM ” How do you prefer your tea? “) returns “How do you prefer your tea? “
  • trim(BOTH FROM ” How do you prefer your tea? “) returns “How do you prefer your tea?” Might as well put no parameter, since trim without “BOTH FROM” will remove the space from the start and end of our source string.

Trim Function and Array

Let’s use the syntax we just learned and use the TRIM function with items in a PostgreSQL array. We’ll use the WHILE LOOP to iterate through each item in the array.

-- variable declarations
DECLARE
    a_t_products TEXT[4];
    a_t_products := {" AMD ", " Intel ", " Motorola ", " Qualcomm "};
    t_remove_these_chars TEXT := " ";
    i_array_index INTEGER := 0;
    i_array_number_of_items INTEGER := ARRAY_LENGTH(a_t_products,1);
BEGIN
-- loop through our four phrases
    WHILE i_array_index < i_array_number_of_items
        LOOP
            i_array_index := i_array_index + 1;
            t_source_string := a_t_products[i_array_index]
            t_cleaned_string := TRIM(BOTH t_remove_these_chars FROM t_source_string);
            RAISE NOTICE t_cleaned_string;
        END LOOP;
END

Analysis

  • a_t_products TEXT[4]. Declares the array named a_t_products to be prepared to store 4 values.
  • a_t_products := {” AMD “, ” Intel “, ” Motorola “, ” Qualcomm “};. Fills the a_t_products array with four values, all of which have spaces at the leading and trailing edges.
  • t_remove_these_chars TEXT := ” “. Using t_remove_these_chars to store a space.
  • i_array_index INTEGER := 0. Initializing i_array_index to be 0 so that our WHILE LOOP below begins at zero.
  • i_array_number_of_items INTEGER := ARRAY_LENGTH(a_t_products,1). “ARRAY_LENGTH(a_t_products,1)” returns a four because that is the number of items (the array length) stored in a_t_products right now. So in this line of code we are saying “i_array_number_of_items” is an integer data type and we want it to store that number 4.
  • WHILE i_array_index < i_array_number_of_items. Setting up our WHILE LOOP to begin at zero and end at 3 because 3 is smaller than i_array_number_of_items.
  • i_array_index := i_array_index + 1. At the beginning of each iteration of our loop we add 1 to i_array_index. So at the very beginning we are taking the value of i_array_index, which is zero, and bumping it up to equal “1”.
  • t_source_string := a_t_products[i_array_index]. Here we are filling t_source_string with the value stored in a_t_products array at whatever place (1, 2, 3, or 4) i_array_index currently points to.
  • t_cleaned_string := TRIM(BOTH t_remove_these_chars FROM t_source_string). This is the “meat” of our little Postgres program. We are getting a t_cleaned_string from t_source_string, where the “cleaning” was to remove the value stored in t_remove_these_chars.

Python libraries for email and PostgreSQL

To begin, we’ll set up the frameworks Python needs for render_template (display HTML page), email, and PostgreSQL connection from Python.

from flask import Flask
from flask import render_template
from flask_mail import Mail
from flask_mail import Message
import psycopg2

Analysis

  • psycopg2 – Framework we use here for connecting to PostgreSQL.
  • flask_mail – For the email functions we need.

Configure Email in Python

Next step we will set up a Mail object for use later to create and send messages.

def configuration_smtp(configuration_name, smtp=1):
    with open(configuration_name) as f:
            configuration_values = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        MAIL_USERNAME = configuration_values['MAIL_USERNAME'][1]
        MAIL_PASSWORD = configuration_values['MAIL_PASSWORD'][1]
    else:
        MAIL_USERNAME = configuration_values['MAIL_USERNAME'][0]
        MAIL_PASSWORD = configuration_values['MAIL_PASSWORD'][0]
    MAIL_SERVER = configuration_values['MAIL_SERVER']
    MAIL_PORT = configuration_values['MAIL_PORT']
    MAIL_USE_TLS = bool(configuration_values['MAIL_USE_TLS'])
    return [MAIL_USERNAME, MAIL_PASSWORD, MAIL_SERVER, MAIL_PORT, MAIL_USE_TLS]
    mail = Mail()

Query data from PostgreSQL

Next we want to do is extract some data from PostgreSQL using a fairly simple query that includes SELECT, TRIM, FROM, and WHERE.

s = ""
s += "SELECT"
s += " TRIM(BOTH t_remove_these_chars FROM t_email_address) AS t_email_address_cleaned"
s += " FROM tbl_users"
s += " WHERE"
s += "("
s += " b_is_verified = true"
s += ")"
db_cursor.execute(s)
try:
    array_recordset_values= db_cursor.fetchall()
except psycopg2.Error as e:
    t_message = "Postgres Database error: " + e + "/n SQL: " + s
    return render_template("error.html", t_message = t_message)
db_cursor.close()

Analysis

  • SELECT TRIM(BOTH t_remove_these_chars FROM t_email_address) AS t_email_address_cleaned…: Retrieve email addresses (t_email_address) for every row (user) in tbl_users that has a value of “true” in the “b_is_verified” column.
  • TRIM(): Using PostgreSQL’s “trim” function to remove extra space in the t_email_address column. We are using the BOTH argument in order to remove spaces from both the beginning and end of the target string. We could leave out these parameters altogether, since our t_remove_these_chars IN THIS INSTANCE is a space. But we are futureproofing by setting the code up so that later if we change the value of t_remove_these_chars, the only line needing change would be t_remove_these_chars TEXT := ” “.
  • array_with_all_rows_returned: This is an array we are creating on the fly.
  • fetchall: Python’s “fetchall()” function is used to get all rows in the recordset returned.
  • NOTE: This query has no parameters, thus no need for protection from SQL injection.

Send email messages from Python

for t_email_address in array_with_all_rows_returned:
    s = ""
    s += "To " + t_email_address + "<br>"
    s += "<br>"
    s += "Thank you for your application. Your brains and good looks have gained you access!" + "<br>"
    s += "<br>"
    s += "If you have any questions or lies, please feel free to tell us only flattering things by replying to this email address." + "<br>"
    s += "<br>"
    object_message = Message(
        body = s,
        subject = "Your account is now active!",
        recipients = t_email_address,
        sender = t_sender,
        reply_to = t_sender
        )
    mail.send(object_message)

This section loops through each user’s email address and sends an email each iteration. So the “mail.send(object_message)” you see above will happen every time a row is returned from PostgreSQL.

Analysis

  • for t_email_address in array_with_all_rows_returned: This loops through the array created earlier via fetchall() called array_with_all_rows_returned, which contains every email address in the “tbl_users” Postgres table who has been approved.
  • object_message = Message(): Set up the object_message object with the essential variables in order to create a Python Flask email message object.
  • mail.send(object_message): Use SMTP to send the message object we set up above.

Now that we get the more difficult to understand parts needed to send off the emails, it’s time to look at a full listing of our source code:

The Source Code

from flask import Flask
import psycopg2
from flask_mail import Mail
from flask_mail import Message

app = Flask(__name__)
@app.route("/")

# --------------
# database creds
# --------------
t_host = "PostgreSQL database host address" # either "localhost", a domain name, or an IP address.
t_port = "5432" # default postgres port
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

@app.route("/main")

# --------------
# email creds
# --------------
def configuration_smtp(configuration_name, smtp=1):
    with open(configuration_name) as f:
            configuration_values = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        MAIL_USERNAME = configuration_values['MAIL_USERNAME'][1]
        MAIL_PASSWORD = configuration_values['MAIL_PASSWORD'][1]
    else:
        MAIL_USERNAME = configuration_values['MAIL_USERNAME'][0]
        MAIL_PASSWORD = configuration_values['MAIL_PASSWORD'][0]
        MAIL_SERVER = configuration_values['MAIL_SERVER']
        MAIL_PORT = configuration_values['MAIL_PORT']
        MAIL_USE_TLS = bool(configuration_values['MAIL_USE_TLS'])
    return [MAIL_USERNAME, MAIL_PASSWORD, MAIL_SERVER, MAIL_PORT, MAIL_USE_TLS]

# ----------------
# primary function
# ----------------
def main():
    s = ""
    s += "SELECT"
    s += " TRIM(BOTH t_remove_these_chars FROM t_email_address) AS t_email_address_cleaned"
    s += " FROM tbl_users"
    s += " WHERE"
    s += "("
    s += " b_is_verified = true"
    s += ")"
    db_cursor.execute(s)
    try:
        array_recordset_values = db_cursor.fetchall()
    except psycopg2.Error as e:
        t_message = "Postgres Database error: " + e + "/n SQL: " + s
        return render_template("error.html", t_message = t_message)
    db_cursor.close()

    # ----------------------------
    # choose an SMTP configuration
    # ----------------------------
    configuration_smtp("config_01")
    t_sender = "recruiter@yomamma.net"
    mail = Mail()

    # ------------------------------------
    # Loop, create email content, and send
    # ------------------------------------
    for t_email_address in array_recordset_values:
        s = ""
        s += "To " + t_email_address + "<br>"
        s += "<br>"
        s += "Thank you for your application. Your brains and good looks have gained you access!" + "<br>"
        s += "<br>"
        s += "If you have any questions or lies, please feel free to tell us only flattering things by replying to this email address." + "<br>"
        s += "<br>"
        object_message = Message(
            body = s,
            subject = "Your account is now active!",
            recipients = t_email_address,
            sender = t_sender,
            reply_to = t_sender
            )
        mail.send(object_message)

# This is for command line testing
if __name__ == "__main__":
    app.run(debug=True)

Conclusion

In this tutorial we built an application to send emails with PostgreSQL and Python. We covered using Python for sending email messages with values retrieved from a Postgres table using SELECT, WHERE, and TRIM. Some other functions we used along the way include Python’s list (array), Flask’s render_template, fetchall from psycopg2, and SMTP from flask_mail. We also used Postgres’ Declare. We used string concatenation in both Postgres and Python.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.