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

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

主要介绍了Python实现的查询mysql数据库并通过邮件发送信息功能,可实现Python针对mysql的查询与宕机信息通过邮件发送的相关操作技巧,需要的朋友可以参考下

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

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

Python代码如下：

#-*- coding: UTF-8 -*-

#!/usr/bin/env python

'''''

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):

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:

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):

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:

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 + "")

self.cur.execute(strsql)

name_list = [tuple[0] for tuple in self.cur.description]

strresult += str(name_list) + ""

strresult += str(col[0]) + " " + str(col[1]) + " " + str(col[2]) + " " + str(col[3]) + ""

todaynum += int(col[2])

yestodaynum += int(col[3])

strresult += "今日宕机总数：" + str(todaynum) + " 昨日宕机总数：" + str(yestodaynum) + " 同昨日相比增加: " + str(todaynum - yestodaynum) + ""

return strresult

except MySQLdb.Error as e:

print("Mysql Error:%s" %(e))

def close(self):

self.cur.close()

self.conn.close()

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 + "")

self.cur.execute(strsql)

name_list = [tuple[0] for tuple in self.cur.description]

#strresult += str(name_list) + ""

# for row in self.cur.fetchall():

# return row

s = self.cur.fetchall()