#-*- coding: UTF-8 -*-#!/usr/bin/env python'''''author:qlzhongCreated on 2015-6-29征途宕機(jī)日志統(tǒng)計(jì)匯總'''import MySQLdbimport timeimport datetimeimport smtplibfrom email.mime.text import MIMETextmailto_list=["mail@mail.com"]#mailto_list=["zhongqilong@ztgame.com"]mail_host="smtp.qq.com" #設(shè)置服務(wù)器mail_user=""  #用戶名mail_pass=""  #口令mail_postfix="" #發(fā)件箱的后綴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 Falseclass MySQLHelper:  #配置數(shù)據(jù)庫(kù)信息并連接  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]))  #取出需要統(tǒng)計(jì)的數(shù)據(jù)庫(kù)名稱  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]))  #指定查詢的數(shù)據(jù)庫(kù)名稱  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]))  #使用該語(yǔ)句來(lái)直接查詢昨天和今天的差異  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 += "今日宕機(jī)總數(shù)：" + str(todaynum) + "  昨日宕機(jī)總數(shù)：" + 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()  #宕機(jī)數(shù)地址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 + "征途宕機(jī)日志查詢匯總" + "/n"  #時(shí)間  timenow = datetime.datetime.now()  textbody = textbody + "時(shí)間：" + timenow.strftime('%Y-%m-%d %H:%M:%S') + "/n"  #連接  ipadress="192.168.100.38"  port=3306  dbHelper = MySQLHelper(ipadress, "gameerror", "errorpasswd", port)  textbody = textbody + "服務(wù)器地址：" + ipadress + ":" + str(port) + "/n"  dbHelper.selectDb("GAMEERROR")  #操作  dbname = dbHelper.db_name()  textbody = textbody + "數(shù)據(jù)庫(kù)：" + 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昨天和今天的差異 宕機(jī)地址 版本號(hào) 今天宕機(jī)次數(shù) 昨天宕機(jī)次數(shù)" + "/n"  textbody = textbody + str(dbHelper.monion_today_yesddiff(tabletoday, tableyestoday)) + "/n"  textbody = textbody + "50以下地址(tmp代表某個(gè)宕機(jī)地址的個(gè)數(shù)) 今天 昨天:" + "/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宕機(jī)數(shù)地址" + str(num) + "以下最多的版本 版本號(hào) 次數(shù)" + "/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,"征途客戶端宕機(jī)日志統(tǒng)計(jì)",textbody):    print "發(fā)送成功"  else:    print "發(fā)送失敗"  dbHelper.close()