Happy New Year!

Happy New Year 2013

It’s a new year, and a new opportunity to update skill sets! So far, my poll indicates that the most popular technologies for the upcoming year (at least for those who answered the poll) will be jQuery and Python. Given the increased number of articles and blog posts about jQuery and also Python’s use in the world of “Big Data”, I’m not surprised.

There are plenty of free classes and tutorials out there for Python; I’m currently working on the CS 101 class from Udacity now, and will be looking at Kaggle for some experience using Python for data science.

Sending SQL Server Stored Procedure Results as an HTML Table in Email Using Python

Python logo

By modifying the code I used for sending email attachments, I am able to execute a SQL Server stored proc and format the results as an HTML table in an email. Also, I use the sys.exit command if there are no rows returned so that the email is not sent if there is no data returned.

# disabled username / password logon for use in our Exchange environment

######### Setup your stuff here #######################################

host = 'smtp.whatever.com' # specify port, if required, using a colon and port number following the hostname

fromaddr = '[email protected]' # must be a vaild 'from' address in your environment
toaddr  = ['[email protected]'] # list of email addresses
ccaddr  = [''] # list of email addresses
bccaddr  = ['[email protected]'] # list of email addresses
replyto = fromaddr # unless you want a different reply-to

# username = 'username' # not used in our Exchange environment
# password = 'password' # not used in our Exchange environment

msgsubject = 'Email Subject'
htmlmsgtext = "<h2>Report for "  # text with appropriate HTML tags
connstring = 'DRIVER={SQL Server};SERVER=servername;DATABASE=dbname;UID=userid;PWD=password'

######### In normal use nothing changes below this line ###############

import smtplib, pyodbc, sys
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE
from HTMLParser import HTMLParser
import datetime as dt
yesterday = dt.datetime.now() - dt.timedelta(days=1)

date = "'" + yesterday.strftime('%m-%d-%Y') + "'"
htmlmsgtext = htmlmsgtext + date + "</h2>"
conn=pyodbc.connect(connstring)
cursor=conn.cursor()
cursor.execute("exec dbo.sp_RandomStoredProc " + date)
rows = cursor.fetchall()
column_names = [d[0] for d in cursor.description]
cursor.close()
del cursor
if len(rows) == 0:
    sys.exit
htmlmsgtext = htmlmsgtext + '<table style="border:2px solid black">n'
htmlmsgtext = htmlmsgtext + '<tr>n'
for column_name in column_names:
    htmlmsgtext = htmlmsgtext + '<th style="border:1px solid black; text-align:center">' + column_name + '</th>'
htmlmsgtext = htmlmsgtext + 'n</tr>n'
for row in rows:
    for column in row:
        htmlmsgtext = htmlmsgtext + '<td style="border:1px solid black; text-align:center">' + str(column) + '</td>'
    htmlmsgtext = htmlmsgtext + 'n</tr>n'
htmlmsgtext = htmlmsgtext + '</table>n'

# A snippet - class to strip HTML tags for the text version of the email

class MLStripper(HTMLParser):
    def __init__(self):
        self.reset()
        self.fed = []
    def handle_data(self, d):
        self.fed.append(d)
    def get_data(self):
        return ''.join(self.fed)

def strip_tags(html):
    s = MLStripper()
    s.feed(html)
    return s.get_data()

########################################################################

try:
    # Make text version from HTML - First convert tags that produce a line break to carriage returns
    msgtext = htmlmsgtext.replace('</br>',"r").replace('
',"r").replace('
',"r")
    # Then strip all the other tags out
    msgtext = strip_tags(msgtext)

    # necessary mimey stuff
    msg = MIMEMultipart()
    msg.preamble = 'This is a multi-part message in MIME format.n'
    msg.epilogue = ''

    body = MIMEMultipart('alternative')
    body.attach(MIMEText(msgtext))
    body.attach(MIMEText(htmlmsgtext, 'html'))
    msg.attach(body)   

    msg['From'] = fromaddr
    msg['To'] = COMMASPACE.join(toaddr)
    msg['CC'] = COMMASPACE.join(ccaddr)
    msg['Subject'] = msgsubject
    msg['Reply-To'] = replyto

    print 'To addresses follow:'
    print toaddr

    # The actual email sendy bits
    server = smtplib.SMTP(host)
    server.set_debuglevel(False) # set to True for verbose output
    recipients = toaddr + ccaddr + bccaddr
    # Comment this block and uncomment the below try/except block if TLS or user/pass is required.
    server.sendmail(fromaddr, recipients, msg.as_string())
    print 'Email sent.'
    server.quit() # bye bye

    # try:
        # # If TLS is used
        # server.starttls()
        # server.login(username,password)
        # server.sendmail(fromaddr, recipients, msg.as_string())
        # print 'Email sent.'
        # server.quit() # bye bye
    # except:
        # # if tls is set for non-tls servers you would have raised an exception, so....
        # server.login(username,password)
        # server.sendmail(fromaddr, recipients, msg.as_string())
        # print 'Email sent.'
        # server.quit() # bye bye        

except:
    print "Email NOT sent to %s successfully. ERR: %s %s %s " % (str(toaddr), str(sys.exc_info()[0]), str(sys.exc_info()[1]), str(sys.exc_info()[2]) )
    #just in case

Sending an Email with Attachments using Python – Archiving Functionality Added

Python logo

(This article has an update.)

I have added code to the program I wrote in my previous post that now allows for folders to exist in the path.  This one does not pull files out of the subfolders, however.  Also, after being attached to the email, the files are renamed with the current date at the end of the filename and moved into an archive folder underneath the original path.  If the archive folder does not already exist, it will be created at runtime.

# Adapted from https://gist.github.com/4009671 and other sources by David Young
# added directory searching functionality to add all files in folder
# disabled username / password logon for use in our Exchange environment
######### Setup your stuff here #######################################

path='<<file path>>' # location of files
archiveFolderName = 'archive' # name of folder under path where files will be archived
host = 'smtp.whatever.com' # specify port, if required, using a colon and port number following the hostname

fromaddr = '[email protected]' # must be a vaild 'from' address in your environment
toaddr  = ['user'[email protected]','[email protected]','[email protected]'] # list of email addresses
replyto = fromaddr # unless you want a different reply-to

# username = 'username' # not used in our Exchange environment
# password = 'password' # not used in our Exchange environment

msgsubject = 'This is the email subject'

htmlmsgtext = "<h2>Email body here</h2>" # text with appropriate HTML tags

######### In normal use nothing changes below this line ###############

import smtplib, os, sys, shutil
from datetime import date
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE
from email import Encoders
from HTMLParser import HTMLParser

archivePath = os.path.join(path, archiveFolderName) # full path where files will be archived

if not os.path.exists(archivePath): # create archive folder if it doesn't exist
os.makedirs(archivePath)
print 'Archive folder created at ' + archivePath + '.'

# A snippet - class to strip HTML tags for the text version of the email

class MLStripper(HTMLParser):
def __init__(self):
self.reset()
self.fed = []
def handle_data(self, d):
self.fed.append(d)
def get_data(self):
return ''.join(self.fed)

def strip_tags(html):
s = MLStripper()
s.feed(html)
return s.get_data()

########################################################################

try:
# Make text version from HTML - First convert tags that produce a line break to carriage returns
msgtext = htmlmsgtext.replace('</br>',"r").replace('<br />',"r").replace('</p>',"r")
# Then strip all the other tags out
msgtext = strip_tags(msgtext)

# necessary mimey stuff
msg = MIMEMultipart()
msg.preamble = 'This is a multi-part message in MIME format.n'
msg.epilogue = ''

body = MIMEMultipart('alternative')
body.attach(MIMEText(msgtext))
body.attach(MIMEText(htmlmsgtext, 'html'))
msg.attach(body)
attachments = os.listdir(path)

if 'attachments' in globals() and len('attachments') > 0: # are there attachments?
for filename in attachments:
if os.path.isfile(os.path.join(path, filename)):
f = os.path.join(path, filename)
part = MIMEBase('application', "octet-stream")
part.set_payload( open(f,"rb").read() )
Encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(f))
msg.attach(part)

msg['From'] = fromaddr
msg['To'] = COMMASPACE.join(toaddr)
msg['Subject'] = msgsubject
msg['Reply-To'] = replyto

print 'To addresses follow:'
print toaddr

# The actual email sendy bits
server = smtplib.SMTP(host)
server.set_debuglevel(False) # set to True for verbose output

# Comment this block and uncomment the below try/except block if TLS or user/pass is required.
server.sendmail(fromaddr, toaddr, msg.as_string())
print 'Email sent.'
server.quit() # bye bye

# try:
# # If TLS is used
# server.starttls()
# server.login(username,password)
# server.sendmail(msg['From'], [msg['To']], msg.as_string())
# print 'Email sent.'
# server.quit() # bye bye
# except:
# # if tls is set for non-tls servers you would have raised an exception, so....
# server.login(username,password)
# server.sendmail(msg['From'], [msg['To']], msg.as_string())
# print 'Email sent.'
# server.quit() # bye bye

try:
if 'attachments' in globals() and len('attachments') > 0: # are there attachments?
for filename in attachments:
if os.path.isfile(os.path.join(path, filename)):
f1 = os.path.join(path, filename)
x = filename.find('.')
filename2 = filename[:x] + '_' + str(date.today()) + filename[x:]
f2 = os.path.join(path, filename2)
os.rename(f1, f2)
print "File " + filename + " renamed to " + filename2 + "."
shutil.move(f2, archivePath)
print "File " + filename2 + " moved to " + archivePath + "."

except:
print "Files not successfully renamed and/or archived."

except:
print "Email NOT sent to %s successfully. ERR: %s %s %s " % (str(toaddr), str(sys.exc_info()[0]), str(sys.exc_info()[1]), str(sys.exc_info()[2]) )
#just in case