Python实现的查询mysql数据库并通过邮件发送信息功能
本文实例讲述了Python实现的查询mysql数据库并通过邮件发送信息功能。分享给大家供大家参考，具体如下：
这里使用Python查询mysql数据库，并通过邮件发送宕机信息。
Python代码如下：
#-*-coding:UTF-8-*-
#!/usr/bin/envpython
'''''
author:qlzhong
Createdon2015-6-29
征途宕机日志统计汇总
'''
importMySQLdb
importtime
importdatetime
importsmtplib
fromemail.mime.textimportMIMEText
mailto_list=["mail@mail.com"]
#mailto_list=["zhongqilong@ztgame.com"]
mail_host="smtp.qq.com"#设置服务器
mail_user=""#用户名
mail_pass=""#口令
mail_postfix=""#发件箱的后缀
defsend_mail(to_list,sub,content):
me="hello"+"<"+mail_user+"@"+mail_postfix+">"
msg=MIMEText(content,_subtype='plain',_charset='utf-8')
msg['Subject']=sub
msg['From']=me
msg['To']=";".join(to_list)
try:
server=smtplib.SMTP()
server.connect(mail_host)
server.login(mail_user,mail_pass)
server.sendmail(me,to_list,msg.as_string())
server.close()
returnTrue
exceptException,e:
printstr(e)
returnFalse
classMySQLHelper:
#配置数据库信息并连接
def__init__(self,host="****",user="****",password="****",port=3306,charset="utf8"):
self.host=host
self.user=user
self.password=password
self.port=port
self.charset=charset
try:
self.conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.password,port=self.port)
self.conn.set_character_set(self.charset)
self.cur=self.conn.cursor()
print("==================connectsuccess====================")
exceptMySQLdb.Errorase:
print("MysqlError%d:%s"%(e.args[0],e.args[1]))
#取出需要统计的数据库名称
defdb_name(self):
un_db_name=['information_schema','cz','ecshop','edutone','gz','mysql','newparent','parent','performance_schema','test','xx','yyhd']
name=[]
try:
self.cur.execute('showdatabases')
forrowinself.cur.fetchall():
foriinrow:
ifinotinun_db_name:
name.append(i)
returnname
exceptMySQLdb.Errorase:
print("MysqlError%d:%s"%(e.args[0],e.args[1]))
#指定查询的数据库名称
defselectDb(self,db):
try:
self.conn.select_db(db)
exceptMySQLdb.Errorase:
print("MysqlError%d:%s"%(e.args[0],e.args[1]))
#使用该语句来直接查询昨天和今天的差异
defmonion_today_yesddiff(self,today,yestoday):
try:
strresult=""
strsql='SELECTaddress,charversion,sum(today)astoday,sum(yesterday)asyesterday'
strsql+='FROM(SELECTaddress,""astoday,tmpasyesterday,charversionFROM(SELECTcount(*)Astmp,address,charversionFrom`'+yestoday
strsql+='`WHEREcharversionlike\'1.0.0.3%\'GROUPBYaddress)AsTESTWHEREtmp>=50'
strsql+='unionall'
strsql+='SELECTaddress,tmpastoday,""asyesterday,charversionFROM(SELECTcount(*)Astmp,address,charversionFrom`'
strsql+=today
strsql+='`WHEREcharversionlike\'1.0.0.3%\'GROUPBYaddress)AsTESTWHEREtmp>=50)AsDiffGROUPBYaddress,charversion'
print(strsql+"\n")
self.cur.execute(strsql)
name_list=[tuple[0]fortupleinself.cur.description]
strresult+=str(name_list)+"\n"
#forrowinself.cur.fetchall():
#returnrow
s=self.cur.fetchall()
todaynum=0
yestodaynum=0
forcolins:
strresult+=str(col[0])+""+str(col[1])+""+str(col[2])+""+str(col[3])+"\n"
todaynum+=int(col[2])
yestodaynum+=int(col[3])
strresult+="今日宕机总数："+str(todaynum)+"昨日宕机总数："+str(yestodaynum)+"同昨日相比增加:"+str(todaynum-yestodaynum)+"\n"
returnstrresult
exceptMySQLdb.Errorase:
print("MysqlError:%s\n"%(e))
defclose(self):
self.cur.close()
self.conn.close()
todayrang=0;
yestodayrang=0;
#按照范围查询
defmonion_rang_today_yesddiff(self,today,yestoday,num1,num2):
try:
strresult=""
strsql='SELECTsum(today)astoday,sum(yesterday)asyesterdayFROM(SELECT""astoday,tmpasyesterdayFROM(SELECTcount(*)AstmpFrom`'+yestoday
strsql+='`WHEREcharversionlike\'1.0.0.3%\'GROUPBYaddress)AsTESTWHEREtmp<'+str(num2)+'ANDtmp>='+str(num1)+'unionall'
strsql+='SELECTtmpastoday,""asyesterdayFROM(SELECTcount(*)AstmpFrom`'+today+'`WHEREcharversionlike\'1.0.0.3%\'GROUPBYaddress)AsTESTWHEREtmp<'+str(num2)+'ANDtmp>='+str(num1)+')AsDiff'
print(strsql+"\n")
self.cur.execute(strsql)
name_list=[tuple[0]fortupleinself.cur.description]
#strresult+=str(name_list)+"\n"
#forrowinself.cur.fetchall():
#returnrow
s=self.cur.fetchall()
todaynum=0
yestodaynum=0
forcolins:
strresult+=str(num1)+"<=tmp<"+str(num2)+""+str(col[0])+""+str(col[1])+"\n"
self.todayrang+=int(col[0])
self.yestodayrang+=int(col[1])
returnstrresult
exceptMySQLdb.Errorase:
print("MysqlError:%s\n"%(e))
defclose(self):
self.cur.close()
self.conn.close()
#宕机数地址50以下最多的版本
defmonion_rang_today_diff(self,today,num):
try:
strresult=""
strsql='SELECTcharversion,sum(today)astodayFROM(SELECTtmpastoday,""asyesterday,charversionFROM(SELECTcount(*)Astmp,charversionFrom`'+today
strsql+='`WHEREcharversionlike\'1.0.0.3%\'GROUPBYaddress)AsTESTWHEREtmp<'+str(num)+')AsDiffGROUPBYcharversion'
print(strsql+"\n")
self.cur.execute(strsql)
name_list=[tuple[0]fortupleinself.cur.description]
#strresult+=str(name_list)+"\n"
#forrowinself.cur.fetchall():
#returnrow
s=self.cur.fetchall()
forcolins:
strresult+=str(col[0])+""+str(col[1])+"\n"
returnstrresult
exceptMySQLdb.Errorase:
print("MysqlError:%s\n"%(e))
defclose(self):
self.cur.close()
self.conn.close()
if__name__=='__main__':
textbody=""
textbody=textbody+"征途宕机日志查询汇总"+"\n"
#时间
timenow=datetime.datetime.now()
textbody=textbody+"时间："+timenow.strftime('%Y-%m-%d%H:%M:%S')+"\n"
#连接
ipadress="192.168.100.38"
port=3306
dbHelper=MySQLHelper(ipadress,"gameerror","errorpasswd",port)
textbody=textbody+"服务器地址："+ipadress+":"+str(port)+"\n"
dbHelper.selectDb("GAMEERROR")
#操作
dbname=dbHelper.db_name()
textbody=textbody+"数据库："+str(dbname[0])+"\n"
time1=timenow+datetime.timedelta(days=-1)
time2=timenow+datetime.timedelta(days=-2)
strtime1=time1.strftime('%Y%m%d')
tabletoday="ErrorDump"+strtime1
strtime2=time2.strftime('%Y%m%d')
tableyestoday="ErrorDump"+strtime2
textbody=textbody+"tablename:today:"+tabletoday+"yestoday:"+tableyestoday+"\n"
textbody=textbody+"\n昨天和今天的差异宕机地址版本号今天宕机次数昨天宕机次数"+"\n"
textbody=textbody+str(dbHelper.monion_today_yesddiff(tabletoday,tableyestoday))+"\n"
textbody=textbody+"50以下地址(tmp代表某个宕机地址的个数)今天昨天:"+"\n"
textbody=textbody+str(dbHelper.monion_rang_today_yesddiff(tabletoday,tableyestoday,30,50))
textbody=textbody+str(dbHelper.monion_rang_today_yesddiff(tabletoday,tableyestoday,10,30))
textbody=textbody+str(dbHelper.monion_rang_today_yesddiff(tabletoday,tableyestoday,0,10))
textbody=textbody+"50以上地址总和"+"今天："+str(dbHelper.todayrang)+"昨天:"+str(dbHelper.yestodayrang)+"今天比昨天增加:"+str(dbHelper.todayrang-dbHelper.yestodayrang)+"\n"
num=50
textbody=textbody+"\n宕机数地址"+str(num)+"以下最多的版本版本号次数"+"\n"
textbody=textbody+str(dbHelper.monion_rang_today_diff(tabletoday,num))
file_object=open('ztdumptip.txt')
try:
all_the_text=file_object.read()
finally:
file_object.close()
textbody+=all_the_text
print(textbody)
ifsend_mail(mailto_list,"征途客户端宕机日志统计",textbody):
print"发送成功"
else:
print"发送失败"
dbHelper.close()
更多关于Python相关内容感兴趣的读者可查看本站专题：《Python+MySQL数据库程序设计入门教程》、《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》
希望本文所述对大家Python程序设计有所帮助。