数据库查询脚本

查询mysql数据库，并通过邮件发送。

#-*- 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()