本文实例讲述了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程序设计有所帮助。
华山资源网 Design By www.eoogi.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
华山资源网 Design By www.eoogi.com
暂无评论...
更新日志
2024年09月25日
2024年09月25日
- 纯音入心系列纯音乐《天籁古筝》1CD[MP3][331MB]
- 男女对唱典藏天碟《发烧对唱·那个季节里的歌DSD》10CD[WAV]
- 群星2010-歌林精选辑[歌林][WAV+CUE]
- TrioZimbalist-PianoTriosofWeinberg,Auerbach,Dvorak(2024)[24-44,1]wav
- 群星.1992-滚石第一流台湾歌·12王牌大车拼【滚石】【WAV+CUE】
- 林子祥.1984-创作歌集【华纳】【WAV+CUE】
- 周汤豪.2010-周汤豪【华纳】【FLAC分轨】
- Mozart-TheStringQuintets-AmadeusQuartet,CecilAronowitz(2017)[24-44,1][WAV+CUE]
- JamesWilliamsDennisIrwin-Focus(2024,Red)[24-48]FLAC
- 藤泽麻衣《空みあげて》[WAV]
- 纯音入心系列纯音乐《中国古筝经典名曲》1CD[MP3][1.3GB]
- 纯音入心系列纯音乐《古筝新奏:岁月静好与筝语,细水流年与筝同》1CD[MP3][846.9MB]
- 670《脆弱敏感小女生》[320K/MP3][27.53MB]
- 曾琳.2011-好好爱我【南方】【WAV+CUE】
- 范晓萱.1995-RAIN【福茂】【WAV+CUE】