Sending Emails

A customer recently asked me how can they send an email from the command line to alert the DBAs when a backup starts or finishes. It can also be used to notify when something fails. So, here is my Python script that sends an email. You have to specify the SMTP server and this version assumes the SMTP doesn’t need credentials to send an email.

#!/usr/bin/env python
import sys, smtplib, string, getopt

def send_my_mail (email_smtp, email_from, email_to, email_subject, email_body):

  body = string.join((
    "From: %s" % email_from,
    "To: %s" % email_to,
    "Subject: %s" % email_subject,
    "",
    email_body
  ), "\r\n")

  s = smtplib.SMTP(email_smtp)
  s.sendmail(email_from, email_to, body)
  s.quit()

def main(argv):
   email_from = ''
   email_to = ''
   email_subject = ''
   email_body = ''
   email_smtp = ''
   if len(sys.argv)<11:
         print 'mail.py -f  -t  -s  -b  -d '
         sys.exit(2)
   try:
      opts, args = getopt.getopt(argv,"hf:t:d:b:s:")
   except getopt.GetoptError:
      print 'mail.py -f  -t  -s  -b  -d '
      sys.exit(2)
   for opt, arg in opts:
      if opt == '-h':
         print 'mail.py -f  -t  -s  -b  -d '
         sys.exit()
      elif opt in ("-f"):
         email_from = arg
      elif opt in ("-t"):
         email_to = arg
      elif opt in ("-d"):
         email_smtp = arg
      elif opt in ("-b"):
         email_body = arg
      elif opt in ("-s"):
         email_subject = arg

   send_my_mail (email_smtp, email_from, email_to, email_subject, email_body)

if __name__ == "__main__":
   main(sys.argv[1:])

Most of the code is actually parsing the command line arguments. Wouldn’t it be nice to instead use a function in the database that doesn’t need to parse command line arguments? That is exactly what I did! I next created a function in Greenplum that sends an email too!

First you need to install plpythonu if you haven’t already.

 CREATE PROCEDURAL LANGUAGE 'plpythonu'
  HANDLER plpython_call_handler;

Next, you create the function:

create or replace function fn_send_email(email_smtp text, email_from text, email_to text, email_subject text, email_body text) returns void as 
$$
  import smtplib, string
  body = string.join(("From: %s" % email_from, "To: %s" % email_to, "Subject: %s" % email_subject, "", email_body), "\r\n")
  s = smtplib.SMTP(email_smtp)
  s.sendmail(email_from, email_to, body)
  s.quit()
$$ 
language plpythonu;

All of that code around parsing the command line is gone. It is very simple and easy to understand too.

Below is the function being used. Note: I used fake values for the SMTP server, email from, and email to.

select fn_send_email('smtp_example.email.com', 'email_from@email.com', 'email_to@email.com', 'test subject', 'This is the body of my test message');

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.