DDR爱好者之家 Design By 杰米
本文实例讲述了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相关内容感兴趣的读者可查看本站专题:《Python+MySQL数据库程序设计入门教程》、《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》
希望本文所述对大家Python程序设计有所帮助。
DDR爱好者之家 Design By 杰米
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
DDR爱好者之家 Design By 杰米
暂无评论...
稳了!魔兽国服回归的3条重磅消息!官宣时间再确认!
昨天有一位朋友在大神群里分享,自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址,com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后,确实是网易的网址,也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情,因为以前都没有出现这样的情况,现在突然提示跳转到国服战网的网址,是不是说明了简体中文客户端已经开始进行更新了呢?
更新日志
2024年11月26日
2024年11月26日
- 凤飞飞《我们的主题曲》飞跃制作[正版原抓WAV+CUE]
- 刘嘉亮《亮情歌2》[WAV+CUE][1G]
- 红馆40·谭咏麟《歌者恋歌浓情30年演唱会》3CD[低速原抓WAV+CUE][1.8G]
- 刘纬武《睡眠宝宝竖琴童谣 吉卜力工作室 白噪音安抚》[320K/MP3][193.25MB]
- 【轻音乐】曼托凡尼乐团《精选辑》2CD.1998[FLAC+CUE整轨]
- 邝美云《心中有爱》1989年香港DMIJP版1MTO东芝首版[WAV+CUE]
- 群星《情叹-发烧女声DSD》天籁女声发烧碟[WAV+CUE]
- 刘纬武《睡眠宝宝竖琴童谣 吉卜力工作室 白噪音安抚》[FLAC/分轨][748.03MB]
- 理想混蛋《Origin Sessions》[320K/MP3][37.47MB]
- 公馆青少年《我其实一点都不酷》[320K/MP3][78.78MB]
- 群星《情叹-发烧男声DSD》最值得珍藏的完美男声[WAV+CUE]
- 群星《国韵飘香·贵妃醉酒HQCD黑胶王》2CD[WAV]
- 卫兰《DAUGHTER》【低速原抓WAV+CUE】
- 公馆青少年《我其实一点都不酷》[FLAC/分轨][398.22MB]
- ZWEI《迟暮的花 (Explicit)》[320K/MP3][57.16MB]