Python Send Email from Postgres

Introduction

We will learn to use Python to send an email from Postgres data. This is what you can expect to learn:

  • What? We will go over every aspect of using Python for sending email messages after pulling data from a PostgreSQL database table.
  • How? We will use Python’s array and fetchall() to get data from a table into an array for temporary storage and use. We’ll also use Postgres’ concatenation symbol, “+”, TRIM, SELECT, and WHERE.

Prerequisites

  • Basic understanding of writing SQL for Postgres, using the free PGadmin tool or other database admin tool, and writing applications with languages like PHP, Python, Javascript, Java, C#, ASP.Net, VB.Net, Note.js, Ruby, etc. Here we are using Python.
  • Using SQL statements like “SELECT” and “WHERE” in order to pull data from a database table.
  • Useful and optional: Article on naming conventions showing why we prefix our variables, column names, table names, etc. as you see done in this article. For example, naming “t_variablename” with the “t” prefix to define it as a “text” (string) object and “tbl_” before table names in order to clearly distinguish those objects as tables.

From experience, we happen to know the “t_email” column in our users table has “dirty” values in some rows, in this case meaning extra spaces before and after the actual email address. So we will be using “trim” to remove those extra spaces.

First we’ll explain the use of Postgres’ trim function

We use PostgreSQL’s “TRIM” function to remove excess spaces. Let’s increase our understanding of how TRIM works:

Syntax of the TRIM() function

t_result = TRIM([LEADING OR TRAILING OR BOTH] [optional: t_string_to_remove] FROM t_string_source);

Parameters:

  • LEADING: Optional. Tells Postgres to begin at the start of t_string_source and move forward.
  • TRAILING: Optional. Tells Postgres to begin at the end of t_string_source and move backward.
  • BOTH: Optional. Tells Postgres to remove the t_string_to_remove character from both the beginning and end of t_string_source.
  • t_string_to_remove: Optional. Supplies TRIM() with an optional string to look for. If not supplied, space (chr(32)) is assumed.
  • t_string_source: Mandatory parameter. The string we feed to the function. This is the only parameter required.

Examples with parameters

  • trim(LEADING FROM ” What is your favourite function? “) returns “Which is your favourite function? “
  • trim(TRAILING FROM ” What is your favourite function? “) returns ” Which is your favourite function?”
  • trim(BOTH FROM ” What is your favourite function? “) returns “Which is your favourite function?”
  • trim(” What is your favourite function? “) returns “Which is your favourite function?” Yes, same as using “BOTH FROM”.

How to use the Trim function with arrays

Let’s take the syntax we just studied and apply the TRIM function to every item in an array. We’ll make use of the WHILE LOOP to accomplish this task.

-- variable declarations
DECLARE
    a_t_technologies TEXT[4];
    a_t_technologies := {" Python ", " Postgres ", " MS SQL ", " Javascript "};
    t_string_to_remove TEXT := " ";
    i_current_array_spot INTEGER := 0;
    i_size_of_array INTEGER := ARRAY_LENGTH(a_t_technologies,1); -- this is 4
BEGIN
-- loop through our four phrases
    WHILE i_current_array_spot < i_size_of_array
        LOOP
            i_current_array_spot := i_current_array_spot + 1;
            t_string_source := a_t_technologies[i_current_array_spot]
            t_result := TRIM(BOTH t_string_to_remove FROM t_string_source);
            RAISE NOTICE t_result;
        END LOOP;
END

Now back to our primary goal…

Build an application with Python to send email from Postgres

To begin, we’ll set up the libraries Python needs for what we need to do, including use of the Flask framework, as well as Mail and Message libraries.

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

Analysis

  • psycopg2 is a library we use here for interfacing with our database.
  • flask_mail is necessary for accomplishing mail-related tasks.

Next stage is to initialize a Mail object.

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

The next thing we want to do is pull data from Postgres.

s = ""
s += "SELECT"
s += " TRIM(BOTH t_string_to_remove FROM t_email) AS t_email_trimmed"
s += " FROM tbl_users"
s += " WHERE"
s += "("
s += " b_approved = true"
s += ")"
db_cursor.execute(s)
try:
    array_rows = 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: With this SQL statement, we acquire user email addresses (t_email) for every record (user) that has a value of “true” in the “b_approved” column.
  • TRIM(): Using PostgreSQL’s “trim” function to remove extra space in the t_email column. We are using the BOTH argument in order to remove spaces from both the beginning and end of the target string.
  • array_rows: 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.

Build and send the email messages

    for t_item in array_rows:
        s = ""
        s += "Dear " + t_item + "<br>"
        s += "<br>"
        s += "Thank you for applying. Your application has been approved!" + "<br>"
        s += "<br>"
        s += "If you have any questions, please feel free to reply to this email." + "<br>"
        s += "<br>"
        msg = Message(
            body = s,
            subject = "You have been approved!",
            recipients = t_item,
            sender = t_sender,
            reply_to = t_sender
            )
        mail.send(msg)

Overall this part will loop through each user email address and send an email each time. So the “mail.send(msg)” you see above will happen as many times as rows that were returned. Here is the detailed analysis:

  • for t_item in array_rows: This will loop through the array we created earlier with fetchall(), which contains every email address in the “tbl_users” Postgres table who has been approved.
  • msg=Message(): Plug in all our variables in order to create a Python Flask email message object.
  • mail.send(msg): Send the message object we built up.

Now that we have explained the most complex parts required to build our login screen and validation, 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 credentials
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("/")

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

def main():

    s = ""
    s += "SELECT"
    s += " TRIM(BOTH t_string_to_remove FROM t_email) AS t_email_trimmed"
    s += " FROM tbl_users"
    s += " WHERE"
    s += "("
    s += " b_approved = true"
    s += ")"
    db_cursor.execute(s)
    try:
        array_rows = 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()

    smtp_config("config_01")
    t_sender = "admin@juju.com"
    mail = Mail()

    for t_item in array_rows:
        s = ""
        s += "Dear " + t_item + "<br>"
        s += "<br>"
        s += "Thank you for applying. Your application has been approved!" + "<br>"
        s += "<br>"
        s += "If you have any questions, please feel free to reply to this email." + "<br>"
        s += "<br>"
        msg = Message(
            body = s,
            subject = "You have been approved!",
            recipients = t_item,
            sender = t_sender,
            reply_to = t_sender
            )
        mail.send(msg)

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

SECURITY MEASURE: Use Stored Procedures

Protect the integrity of your users’ data by understanding…

Conclusion

In this lesson we built an application in Python to send email messages filled with some data from a Postgres table. Part of that process was to use various other Python and PostgreSQL functions and features, including but not limited to SELECT, TRIM, WHERE, FOR loop, concatenation, and array. Finally, we shared all the Python source code you need.

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.