Python实现的查询mysql数据库并通过邮件发送信息功能

本文实例讲述了Python实现的查询mysql数据库并通过邮件发送信息功能。分享给大家供大家参考，具体如下：

这里使用Python查询mysql数据库，并通过邮件发送宕机信息。

Python代码如下：

#-*- coding: UTF-8 -*-
#!/usr/bin/env python
'''''
author:qlzhong
Created on 2015-6-29
征途宕机日志统计汇总
'''
import MySQLdb
import time
import datetime
import smtplib
from email.mime.text import MIMEText
mailto_list=["mail@mail.com"]
#mailto_list=["zhongqilong@ztgame.com"]
mail_host="smtp.qq.com" #设置服务器
mail_user=""  #用户名
mail_pass=""  #口令
mail_postfix="" #发件箱的后缀
def send_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()
   return True
 except Exception, e:
   print str(e)
   return False
class MySQLHelper:
 #配置数据库信息并连接
 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("==================connect success====================")
   except MySQLdb.Error as e:
     print("Mysql Error ％d: ％s" ％ (e.args[0], e.args[1]))
 #取出需要统计的数据库名称
 def db_name(self):
   un_db_name = ['information_schema','cz','ecshop','edutone','gz','mysql','newparent','parent','performance_schema','test','xx','yyhd']
   name = []
   try:
     self.cur.execute('show databases')
     for row in self.cur.fetchall():
       for i in row:
         if i not in un_db_name:
           name.append(i)
     return name
   except MySQLdb.Error as e:
     print("Mysql Error ％d: ％s" ％ (e.args[0], e.args[1]))
 #指定查询的数据库名称
 def selectDb(self,db):
   try:
     self.conn.select_db(db)
   except MySQLdb.Error as e:
     print("Mysql Error ％d: ％s" ％ (e.args[0], e.args[1]))
 #使用该语句来直接查询昨天和今天的差异
 def monion_today_yesddiff(self, today, yestoday):
   try:
     strresult = ""
     strsql = 'SELECT address, charversion, sum(today) as today, sum(yesterday) as yesterday '
     strsql += 'FROM (SELECT address, "" as today, tmp as yesterday, charversion FROM ( SELECT count(*) As tmp, address, charversion From `' + yestoday
     strsql += '` WHERE charversion like \'1.0.0.3％\' GROUP BY address) As TEST WHERE tmp>=50 '
     strsql += ' union all '
     strsql += 'SELECT address, tmp as today, "" as yesterday, charversion FROM (SELECT count(*) As tmp, address, charversion From `'
     strsql += today
     strsql += '` WHERE charversion like \'1.0.0.3％\' GROUP BY address) As TEST WHERE tmp>=50 ) As Diff GROUP BY address, charversion'
     print(strsql + "\n")
     self.cur.execute(strsql)
     name_list = [tuple[0] for tuple in self.cur.description]
     strresult += str(name_list) + "\n"
     # for row in self.cur.fetchall():
     #   return row
     s = self.cur.fetchall()
     todaynum = 0
     yestodaynum = 0
     for col in s:
       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"
     return strresult
   except MySQLdb.Error as e:
     print("Mysql Error:％s\n" ％(e))
 def close(self):
   self.cur.close()
   self.conn.close()
 todayrang = 0;
 yestodayrang = 0;
 #按照范围查询
 def monion_rang_today_yesddiff(self, today, yestoday, num1, num2):
   try:
     strresult = ""
     strsql = 'SELECT sum(today) as today, sum(yesterday) as yesterday FROM (SELECT "" as today, tmp as yesterday FROM ( SELECT count(*) As tmp From `' + yestoday
     strsql += '` WHERE charversion like \'1.0.0.3％\' GROUP BY address) As TEST WHERE tmp<' + str(num2) + ' AND tmp>=' + str(num1) + ' union all '
     strsql += 'SELECT tmp as today, "" as yesterday FROM (SELECT count(*) As tmp From `' + today + '` WHERE charversion like \'1.0.0.3％\'  GROUP BY address) As TEST WHERE tmp<' + str(num2) + ' AND tmp>=' + str(num1) + ' ) As Diff'
     print(strsql + "\n")
     self.cur.execute(strsql)
     name_list = [tuple[0] for tuple in self.cur.description]
     #strresult += str(name_list) + "\n"
     # for row in self.cur.fetchall():
     #   return row
     s = self.cur.fetchall()
     todaynum = 0
     yestodaynum = 0
     for col in s:
       strresult += str(num1) + " <= tmp < " + str(num2) + "  " + str(col[0]) + " " + str(col[1]) + "\n"
       self.todayrang += int(col[0])
       self.yestodayrang += int(col[1])
     return strresult
   except MySQLdb.Error as e:
     print("Mysql Error:％s\n" ％(e))
 def close(self):
   self.cur.close()
   self.conn.close()
 #宕机数地址50以下最多的版本
 def monion_rang_today_diff(self, today, num):
   try:
     strresult = ""
     strsql = 'SELECT charversion, sum(today) as today FROM (SELECT tmp as today, "" as yesterday, charversion FROM (SELECT count(*) As tmp, charversion From `' + today
     strsql += '` WHERE charversion like \'1.0.0.3％\'  GROUP BY address) As TEST WHERE tmp< ' + str(num) + ') As Diff GROUP BY charversion'
     print(strsql + "\n")
     self.cur.execute(strsql)
     name_list = [tuple[0] for tuple in self.cur.description]
     #strresult += str(name_list) + "\n"
     # for row in self.cur.fetchall():
     #   return row
     s = self.cur.fetchall()
     for col in s:
       strresult += str(col[0]) + " " + str(col[1]) + "\n"
     return strresult
   except MySQLdb.Error as e:
     print("Mysql Error:％s\n" ％(e))
 def close(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 + "table name: 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)
 if send_mail(mailto_list,"征途客户端宕机日志统计",textbody):
   print "发送成功"
 else:
   print "发送失败"
 dbHelper.close()

希望本文所述对大家Python程序设计有所帮助。

来源：https://blog.csdn.net/jesse__zhong/article/details/79696171