python写的调用ms sqlserver数据并发送邮件的小程序

　　今天第一天在这里开博，中午写了篇关于smtplib的博文，没想到有朋友回复，希望能公开源码，呵呵。那我就把程序代码出来，也“开源”一把，希望对有需要的朋友有用，最近才开始学用python，程序写的比较“丑陋”，见笑了。

　　数据库是MS SQL Server，访问数据库用的是开源的pymssql，可以到pymssql.sourceforge.net下载。

 

主程序：

# coding=cp936

 

import pymssql
from time import strftime, localtime
from EmailSender import EmailSender

 

# 邮件接收人列表
toAddress = ['[email][e-mail][/email]','[email][e-mail][/email]']

 

# 邮件服务器验证及验证信息
authInfo = {}
authInfo['server'] = 'smtp.sina.com.cn'
authInfo['user'] = '****'
authInfo['password'] = '******'

def getContent():
    # 读取数据
    conn = pymssql.connect(host='(local)', user='sa', password='******', database='mydb')
    rs = conn.cursor()  
    
    # 异常数据
    sql = "select SPNumber,UserNumber,PhoneUserType,SchoolName,ClassName,MsgContent,SendTimes,State,ReportState,ReportErrorCode \
            from mt \
            where datediff(d, timestamp, getdate())=0 and (sendtimes=0 or state<>'9' or ReportState<>'0') \
            order by id desc"   
    rs.execute(sql)
       
    # 生成HTML
    content = content + '<table width="100%" height="69" border="0" cellpadding="3" cellspacing="1" bgcolor="#999999">'
    content = content + '<tr>'
    content = content + '<td width="9%" height="29" align="center" bgcolor="#E0E0E0">端口号</td>'
    content = content + '<td width="15%" align="center" bgcolor="#E0E0E0">接收人</td>'
    content = content + '<td width="5%" align="center" bgcolor="#E0E0E0">单位</td>'
    content = content + '<td width="5%" align="center" bgcolor="#E0E0E0">科室</td>'
    content = content + '<td width="48%" align="center" bgcolor="#E0E0E0">短信内容</td>'
    content = content + '<td width="7%" align="center" bgcolor="#E0E0E0">提交次数</td>'
    content = content + '<td width="11%" align="center" bgcolor="#E0E0E0">状态</td>'
    content = content + '</tr>'

    for row in rs.fetchall():
        userType = row[2]
        schoolName = row[3]
        className = row[4]
        if not userType:
            userType = ''
           
        if not schoolName:
            schoolName = ''
                        
        if not className:
            className = ''   
       
        content = content + '<tr>'
        content = content + '<td align="center" bgcolor="#FFFFFF">%s</td>' % row[0]
        content = content + '<td align="center" bgcolor="#FFFFFF">%s%s</td>' % (row[1], userType)
        content = content + '<td align="center" bgcolor="#FFFFFF">%s</td>' % schoolName
        content = content + '<td align="center" bgcolor="#FFFFFF">%s</td>' % className
        content = content + '<td align="center" bgcolor="#FFFFFF">%s</td>' % row[5]
        content = content + '<td align="center" bgcolor="#FFFFFF">%s</td>' % row[6]
        content = content + '<td align="center" bgcolor="#FFFFFF">%s</td>' % row[7]
        content = content + '</tr>'
       
    content = content + '</table>'
    rs.close()
    conn.close()

    return content

 

def main():  
    fromAdd = '****@sina.com'
    subject = '短信平台%s日报告' % strftime("%Y-%m-%d", localtime())
    plainText = ''
    htmlText = getContent()
   
    emailSender = EmailSender(authInfo, fromAdd)
    if emailSender.send(toAddress, subject, plainText, htmlText) == 0:

        print '数据发送成功'   

    else:

        print '发送失败'

 

if __name__ == '__main__':
    main()

 

 

 

封装的邮件发送类：

#!/usr/bin/env python
#coding=utf-8

 

import email
import mimetypes

from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
from email.MIMEImage import MIMEImage
import smtplib

 

class EmailSender:
    def __init__(self, authInfo, fromAddress):
        self.AuthInfo = authInfo
        self.FromAddress = fromAddress
       
    def send(self, toAddr, subject, plainText, htmlText):
        strFrom = self.FromAddress

        server = self.AuthInfo.get('server')
        user = self.AuthInfo.get('user')
        passwd = self.AuthInfo.get('password')

 

        if not (server and user and passwd) :
            print 'incomplete login info, exit now'
            return 1

 

        # 设定root信息
        msgRoot = MIMEMultipart('related')
        msgRoot['Subject'] = subject
        msgRoot['From'] = strFrom
        msgRoot['To'] = ','.join(toAddr)
        msgRoot.preamble = 'This is a multi-part message in MIME format.'

        # Encapsulate the plain and HTML versions of the message body in an
        # 'alternative' part, so message agents can decide which they want to display.
        msgAlternative = MIMEMultipart('alternative')
        msgRoot.attach(msgAlternative)

 

        #设定纯文本信息
        msgText = MIMEText(plainText, 'plain', 'gb2312')
        msgAlternative.attach(msgText)

        #设定HTML信息

        msgText = MIMEText(htmlText, 'html', 'gb2312')
        msgAlternative.attach(msgText)

 

        #发送邮件
        smtp = smtplib.SMTP()
        #设定调试级别，依情况而定
        smtp.set_debuglevel(1)
        smtp.connect(server)
        smtp.login(user, passwd)
        smtp.sendmail(strFrom, toAddr, msgRoot.as_string())
        smtp.quit()
       
        return 0