Send Email from CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

We will learn to use Python to send an email from CockroachDB table contents. This is what you can expect to gain:

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

Prerequisites

  • Basic understanding of writing SQL for Cockroach, using the free dBeaver tool or other database admin tool, and writing applications with languages like PHP, Python, Java, C#, ASP.Net, VB.Net, Node.js, Ruby, etc. Here we have used Python.
  • Using SQL statements like “SELECT” and “WHERE” in order to pull data from a database table.
  • Useful and optional: notice how we prefix our variables, column names, table names, etc. to clearly define text/strings, tables, and other object names.

From experience, we like to assume the “txtEmailAddr” column in our users table has “dirty” values in some records, 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 will explain the use of Cockroach’s trim function

Cockroach Trim

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

Syntax of the TRIM() function

1
txtResult = TRIM([LEADING OR TRAILING OR BOTH] [optional: txtSourceString_to_remove] FROM txtSourceString_source);

Parameters:

  • LEADING: Optional. Tells Cockroach to start out at the start of txtSourceString_source and move forward.
  • TRAILING: Optional. Tells Cockroach to start out at the end of txtSourceString_source and move backward.
  • BOTH: Optional. Tells Cockroach to remove the txtSourceString_to_remove character from both the starting point and end of txtSourceString_source.
  • txtSourceString_to_remove: Optional. Supplies TRIM() with an optional string to look for. If not supplied, space (chr(32)) is assumed.
  • txtSourceString_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 will make use of the WHILE LOOP to attain this goal.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- variable declarations
DECLARE
    arrTxtTechnologies TEXT[4];
    arrTxtTechnologies := {" Python ", " Cockroach ", " MS SQL ", " Java "};
    txtSourceString_to_remove TEXT := " ";
    intCurrent_array_spot INTEGER := 0;
    intSize_of_array INTEGER := ARRAY_LENGTH(arrTxtTechnologies,1); -- this is 4
BEGIN
-- loop through our four phrases
    WHILE intCurrent_array_spot < intSize_of_array
        LOOP
            intCurrent_array_spot := intCurrent_array_spot + 1;
            txtSourceString_source := arrTxtTechnologies[intCurrent_array_spot]
            txtResult := TRIM(BOTH txtSourceString_to_remove FROM txtSourceString_source);
            RAISE NOTICE txtResult;
        END LOOP;
END

Back now to our primary task…

Set up Python environment

To start out, we will set up the libraries Python needs for what we have to do, including use of the Flask framework, as well as Mail and Message libraries.

1
2
3
4
5
from flask import Flask
from flask import render_template
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 attaining mail-related goals.

Next stage is to initialize a Mailer object.

Initialize mailer

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
def smtp_config(myConfigName, smtp=1):
    with open(myConfigName) as f:
            myConfigData = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][1]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][1]
    else:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][0]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][0]
    EMAIL_SERVER = myConfigData['EMAIL_SERVER']
    EMAIL_PORT = myConfigData['EMAIL_PORT']
    EMAIL_USE_TLS = bool(myConfigData['EMAIL_USE_TLS'])
    return [EMAIL_USERNAME, EMAIL_PASSWORD, EMAIL_SERVER, EMAIL_PORT, EMAIL_USE_TLS]
    mail = Mail()

The next thing we do is to pull data from Cockroach.

1
2
3
4
5
6
7
8
9
10
11
s = ""
s += "SELECT"
s += " TRIM(BOTH txtSourceString_to_remove FROM txtEmailAddr) AS txtEmailAddr_trimmed"
s += " FROM tblUsers"
s += " WHERE"
s += "("
s += " b_approved = true"
s += ")"
cursorCRDB.execute(s)
arrayRecords = cursorCRDB.fetchall()
cursorCRDB.close()

Analysis

  • SELECT: With this SQL statement, we acquire user email addresses (txtEmailAddr) for every row that has a value of “true” in the “b_approved” column.
  • TRIM(): Using CockroachDB’s “trim” function to remove extra space in the txtEmailAddr column. We are using the BOTH argument in order to remove spaces from both the starting point and end of the target string.
  • arrayRecords: This is an array we are creating on the fly.
  • fetchall: Python’s “fetchall()” function is used to get all records in the rowset returned.

Send the emails

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
for txtItem in arrayRecords:
   s = ""
   s += "Dear " + txtItem + "<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>"
   myMsg = Message(
       body = s,
       subject = "Your account has been approved!",
       recipients = txtItem,
       sender = txtSender,
       reply_to = txtSender
       )
   mail.send(myMsg)

This part will loop through each user’s email address and send an email each time. So the “mail.send(myMsg)” you see above will happen as many times as records that were returned. Here is the detailed analysis:

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

Now that we have explained the most complex parts required to build our login screen and validation, it’s time to examine a full listing of our source scripts:

Full Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
from flask import Flask
import psycopg2
from flask_mail import Mail
from flask_mail import Message

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

# database credentials
txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "22251"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

@app.route("/")

def smtp_config(myConfigName, smtp=1):
    with open(myConfigName) as f:
            myConfigData = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][1]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][1]
    else:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][0]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][0]
        EMAIL_SERVER = myConfigData['EMAIL_SERVER']
        EMAIL_PORT = myConfigData['EMAIL_PORT']
        EMAIL_USE_TLS = bool(myConfigData['EMAIL_USE_TLS'])
    return [EMAIL_USERNAME, EMAIL_PASSWORD, EMAIL_SERVER, EMAIL_PORT, EMAIL_USE_TLS]

def main():
    s = ""
    s += "SELECT"
    s += " TRIM(BOTH txtSourceString_to_remove FROM txtEmailAddr) AS txtEmailAddr_trimmed"
    s += " FROM tblUsers"
    s += " WHERE"
    s += "("
    s += " b_approved = true"
    s += ")"
    try:
        cursorCRDB.execute(s)
        arrayRecords = cursorCRDB.fetchall()
    except psycopg2.Error as e:
        txtMessage = "Cockroach SQL error: " + e + "/n SQL: " + s
        return render_template("errors.html", txtMessage = txtMessage)
    cursorCRDB.close()

    smtp_config("myConfig")
    txtSender = "admin@500rockets.com"
    mail = Mail()

    for txtItem in arrayRecords:
        s = ""
        s += "Dear " + txtItem + "<br>"
        s += "<br>"
        s += "Thank you for registering. Your registration has been approved!" + "<br>"
        s += "<br>"
        s += "If you have questions, please feel free to reply to this email." + "<br>"
        s += "<br>"
        txtMsg = Message(
            body = s,
            subject = "You have been approved!",
            recipients = txtItem,
            sender = txtSender,
            reply_to = txtSender
            )
        mail.send(txtMsg)

if __name__ == "__main__":
    app.run(debug=True)

Conclusion

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

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.