# -*- coding:utf-8 -*-

# Author: zjc

# Description:send monitor info to someone.

# Date: 20170718

import contextlib

import pymysql

import smtplib

from email.mime.text import MIMEText

# 监控信息变量

TABLE_SCHEMA_LIST = ('db-name1', 'db-name2')

COLUMN_LIST = "table_schema,table_name,table_type,table_rows,table_comment"

# 邮箱信息变量

MAIL_HOST = "smtp.xxxxx.com"  # set you mail host

#定义成列表，实现全局变量功能

MAIL_SUBJECT = ["XX - MONITOR "]

MAIL_SENDER_NAME = "1234567@qq.com"

MAIL_SENDER_PASSWORD = "******"

MAIL_RECEIVERS = ["asdfghj@XXX.com","eqerqreq@163.com"]

MAIL_MESSAGE_BEGIN = """

<p>ETL结果信息</p>

    <table border="1">

        <tr>

            <th>table_schema</th>

            <th>table_name</th>

            <th>table_rows</th>

            <th>table_comment</th>

        </tr>

"""

MAIL_MESSAGE_END = """

    </table>

"""

# 邮局发送函数

def sendMailTo(message):

    msg = MIMEText(message, 'html', 'utf-8')

    msg['Subject'] = MAIL_SUBJECT[0]

    try:

        server = smtplib.SMTP()

        server.connect(MAIL_HOST)

        server.login(MAIL_SENDER_NAME, MAIL_SENDER_PASSWORD)

        server.sendmail(MAIL_SENDER_NAME, MAIL_RECEIVERS, msg.as_string())

        server.close()

        #print("邮件发送成功")

        return True

    except smtplib.SMTPException:

        #print("Error: 无法发送邮件")

        raise

# 定义数据库连接，上下文管理器，连接后自动关闭连接

@contextlib.contextmanager

def mysql(host='10.0.8.*', user='', password='*****',

                                 db='', port=3306, charset='utf8mb4'):

  conn = pymysql.connect(host=host, port=port, user=user, passwd=password, db=db, charset=charset)

  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

  try:

    yield cursor

  finally:

    conn.commit()

    cursor.close()

    conn.close()

# 查询 MySql 元数据信息

def query_schema_table_info():

    resultlist = []

    # Connect to the database

    with mysql() as cursor:

        # Read a single record

        sql = "select "+ COLUMN_LIST + " FROM information_schema.TABLES " \

              "WHERE TABLE_SCHEMA IN "+str(TABLE_SCHEMA_LIST)

        #print(sql)

        cursor.execute(sql)

        for row in cursor.fetchall():

            resultlist.append(row)

    return resultlist

# 根据查询结果，判断数据抽取是否正常

def deal():

    mess = ""

    zero_table_count = 0

    result_list = query_schema_table_info()

    for r in result_list:

            # 总体情况

            if r["table_rows"] == 0:

                mess += "<tr bgcolor='red''>" \

                       + " <td>" + r["table_schema"] + "</td>" \

                       + " <td>" + r["table_name"] + "</td>" \

                       + " <td>" + str(r["table_rows"]) + "</td>" \

                       + " <td>" + r["table_comment"] + "</td>" \

                       + " </tr>"

            else:

                mess += "<tr>" \

                       + " <td>" + r["table_schema"] + "</td>" \

                       + " <td>" + r["table_name"] + "</td>" \

                       + " <td>" + str(r["table_rows"]) + "</td>" \

                       + " <td>" + r["table_comment"] + "</td>" \

                       + " </tr>"

            if r["table_rows"] == 0:

                zero_table_count += 1

    # 如果多于0个表是空的，则表示数据出问题了

    if zero_table_count > 0:

        MAIL_SUBJECT[0] = " - MONITOR 发生错误"

    #print(mess)

    #print(MAIL_SUBJECT[0])

    return mess

# print(query_schema_table_info())

if __name__ == '__main__':

    message = MAIL_MESSAGE_BEGIN + deal() + MAIL_MESSAGE_END

    sendMailTo(message)

import sys

defaultencoding = 'utf-8'

if sys.getdefaultencoding() != defaultencoding:

    reload(sys)

    sys.setdefaultencoding(defaultencoding)