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

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

数据库是MS SQL Server，访问数据库用的是开源的pymssql，可以到

主程序：

# coding=cp936

import pymssql

from time import strftime, localtime

from EmailSender import EmailSender

# 邮件接收人列表

toAddress = ['[email]m1@qq.com[/email]','[email]m2@126.com[/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 + '

content = content + '

'

content = content + '

端口号'

content = content + '

接收人'

content = content + '

单位'

content = content + '

科室'

content = content + '

短信内容'

content = content + '

提交次数'

content = content + '

状态'

content = content + '

'

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 + '

'

content = content + '

%s' % row[0]

content = content + '

%s%s' % (row[1], userType)

content = content + '

%s' % schoolName

content = content + '

%s' % className

content = content + '

%s' % row[5]

content = content + '

%s' % row[6]

content = content + '

%s' % row[7]

content = content + '

'

content = content + '

'

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