Python解析邮箱，SSH隧道，MySQL存储，Launchctl定时任务

MailReader项目：

1. 通过python读取并解析每日指定邮件内容
2. 通过SSH隧道连接指定MySQL服务器
3. 将解析数据保存至MySQL指定表
4. 使用 Launchctl开启定时任务
5. GitHub: https://github.com/zhoujl48/WY_MailReader/

1 项目代码解析

• 配置文件

# Server Config
LOCAL_HOST = '127.0.0.1'
SSH_HOST = '***'
SSH_PORT = 32200
SSH_PKEY = '***'
SSH_USER = '***'
SSH_PASSWORD = '***'

# MySQL Config
MYSQL_HOST = '***'
MYSQL_PORT = 3306
MYSQL_USER = '***'
MYSQL_PASSWORD = '***'
MYSQL_DB = '***'

# 查询关键字
KEY_WORD_SUBJECT = '***'

# 账户密码
MAIL_ACCOUNT = '***'
MAIL_PASSWORD = '***'
IMAP_HOST = '***'
IMAP_PORT = 993

1.1 数据库

1.1.1 MySQL配置

• 创建新表

-- 新建表
create table if not exists anti_plugin.nsh_ids_scripts (
	`id` int(11) primary key not null auto_increment comment '主键id',
	`role_id` bigint(20) comment 'role_id',
	`ts_start` datetime comment '日志开始时间',
 	`ts_end` datetime comment '日志结束时间',
 	`last_modify_time` timestamp default CURRENT_TIMESTAMP not null on update  CURRENT_TIMESTAMP comment 'timestamp'
) engine=InnoDB  default charset=utf8

• MySQL数据字段示例

1.1.2 MySQL类

# python3连接MySQL包
import pymysql


# Query to insert row into database nsh_evaluate
QUERY_SQL_INSERT = """
INSERT INTO anti_plugin.nsh_ids_scripts(role_id, ts_start, ts_end) 
VALUES ({role_id}, '{ts_start}', '{ts_end}')
"""


# MySQL类
class MysqlDB(object):
    def __init__(self, host, port, user, passwd, db):
        self._conn = pymysql.connect(host=host, port=port, user=user, password=passwd, database=db)
        print('Init')

    def __del__(self):
        self._conn.close()

    # 单行插入操作
    def _insert_row(self, sql):
        cursor = self._conn.cursor()
        try:
            cursor.execute(sql)
            self._conn.commit()
        except:
            self._conn.rollback()

    # 批量上传
    def upload_ids(self, sql_base, ids, ts_start, ts_end):
        print('Start uploading ids with [{ts_start}] ~ [{ts_end}] to MySQL...'.format(ts_start=ts_start, ts_end=ts_end))
        for role_id in ids:
            sql = sql_base.format(role_id=role_id, ts_start=ts_start, ts_end=ts_end)
            self._insert_row(sql)
        print('{} ids uploaded...'.format(len(ids)))

1.1.3 SSH隧道连接MySQL

• 本地无法直接访问MySQL服务器，需要建立ssh隧道，通过跳板服务器进行访问。

from sshtunnel import SSHTunnelForwarder


# SSH隧道连接MySQL
with SSHTunnelForwarder(ssh_address_or_host=(SSH_HOST, SSH_PORT),
                        ssh_password=SSH_PASSWORD,
                        ssh_username=SSH_USER,
                        ssh_pkey=SSH_PKEY,
                        remote_bind_address=(MYSQL_HOST, MYSQL_PORT)) as server:

    db = MysqlDB(host=LOCAL_HOST,
                 port=server.local_bind_port,
                 user=MYSQL_USER,
                 passwd=MYSQL_PASSWORD,
                 db=MYSQL_DB)

1.2 邮箱

1.2.1 连接邮箱

import imaplib

#连接邮箱，登录
conn = imaplib.IMAP4_SSL(IMAP_HOST, IMAP_PORT)
conn.login(MAIL_ACCOUNT, MAIL_PASSWORD)
print('Successfully connect to mail accout: {}!'.format(MAIL_ACCOUNT))

1.2.2 内容解析

• 内容示例

• 内容解析

# 解析邮件内容，返回时间段ts、用户role_id
def parse(msg):
    ts = ''
    ids = list()
    for part in msg.walk():
        if not part.is_multipart():
            txt = part.get_payload(decode=True).decode('utf-8')

            # 提取时间段
            re_ts_script = re.compile('<font color=\'red\'>(.*?)年(.*?)月(.*?)日(.*?):(.*?)到(.*?)年(.*?)月(.*?)日(.*?):(.*?)</font>')
            ts = '_'.join(re_ts_script.search(txt).groups())

            # 提取ids
            re_ids_script = re.compile(r'疑似作弊玩家统计：</h3>(.*?)<h3>报错编号')
            ids = re_ids_script.search(txt).groups()[0].split()

    return ts, ids

# 匹配邮件主题
KEY_WORD_SUBJECT = '***外网疑似作弊统计'
def match(conn, idx_start=720):
    # 获取收件箱
    INBOX = conn.select('INBOX')
    type, data = conn.search(None, 'ALL')
    mail_list = data[0].split()
    # 遍历邮件
    for i in range(int(idx_start), len(mail_list)):
        # try:
            print(i)
            # 获取第idx份邮件并解析内容
            type, mail = conn.fetch(mail_list[i], '(RFC822)')
            msg = email.message_from_string(mail[0][1].decode('utf-8'))

            # 获取邮件主题
            subject_encoded, enc = email.header.decode_header(msg.get('subject'))[0]
            subject_decoded = subject_encoded.decode(enc)

            # 匹配关键字，解析ts和ids
            if KEY_WORD_SUBJECT in subject_decoded:
                print('Index: {}, Subject: {}'.format(i, subject_decoded))
                ts, ids = parse(msg)
                yield ts, ids, i
        # except Exception as e:
        #     print('Cannot read No. {} in mail_list. {}'.format(i, e))


if __name__ == '__main__':
	# 判断是否已经上传
	idx_last = 0
	ts_list = list()
	if os.path.exists('ts_upload'):
	    with open('ts_upload', 'r') as f:
	        for line in f:
	            idx, ts_record = line.strip().split(': ')
	            ts_list.append(ts_record)
	            idx_last = idx
	
	for ts, ids, idx in match(conn, idx_start=800):
	    print(db._conn)
	    ts_start = '-'.join(ts.split('_')[0:3]) + ' ' + ':'.join(ts.split('_')[3:5])
	    ts_end = '-'.join(ts.split('_')[5:8]) + ' ' + ':'.join(ts.split('_')[8:])
	    ts_record = ts_start + ',' + ts_end
	
	    # 若未上传，则保存至MySQL
	    if ts_record not in ts_list:
	        db.upload_ids(QUERY_SQL_INSERT, ids, ts_start, ts_end)
	
	    # 记录已上传的时间段，避免每次从头开始扫描邮箱
	    with open('ts_upload', 'a') as f:
	        f.write(str(idx) + ': ' + ts_record + '\n')

1.3 定时任务 Launchctl

[1] Mac中的定时任务利器：launchctl

1.3.1 mac开启定时任务流程

• 工作目录下创建脚本文件mail.sh
• ~/Library/LaunchAgents目录下编辑com.mail.plist文件
• Launchctl任务命令

# 加载任务, -w选项会将plist文件中无效的key覆盖掉，建议加上
launchctl load -w com.mail.plist

# 删除任务
launchctl unload -w com.mail.plist

# 查看任务列表
launchctl list | grep 'com.mail'

# 开始任务(立即执行，可用于测试)
launchctl start com.mail.plist

# 结束任务
launchctl stop com.mail.plist

1.3.2 shell文件

#!/usr/bin/env bash
# 记录开始时间
echo `date` >> /Users/zhoujl/workspace/netease/auto/log &&

# 切换至工作目录
cd /Users/zhoujl/workspace/netease/auto &&

# 执行python脚本
/anaconda3/envs/tensorflow/bin/python mail.py &&

# 运行完成
echo 'finish' >> /Users/zhoujl/workspace/netease/auto/log

1.3.3 plist文件

• plist文件目录

目录	功能
~/Library/LaunchAgents	由用户自定义的任务项（一般放在此目录下）
/Library/LaunchAgents	由管理员为用户定义的任务项，用户登录系统才被执行
/Library/LaunchDaemons	由管理员定义的守护进程任务项，系统启动即被执行
/System/Library/LaunchAgents	由Mac OS X为用户定义的任务项
/System/Library/LaunchDaemons	由Mac OS X定义的守护进程任务项

• plist文件内容

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <!-- 标签，必须唯一 -->
  <key>Label</key>
  <string>com.mail.plist</string>

  <!-- 脚本文件 -->
  <key>ProgramArguments</key>
  <array>
    <string>/Users/zhoujl/workspace/netease/auto/mail.sh</string>
  </array>

  <!-- 定时配置 -->
  <key>StartCalendarInterval</key>
  <dict>
        <key>Minute</key>
        <integer>00</integer>
        <key>Hour</key>
        <integer>10</integer>
  </dict>

<!-- 标准输出文件 -->
<key>StandardOutPath</key>
<string>/Users/zhoujl/workspace/netease/auto/launchctl.log</string>

<!-- 标准错误输出文件，错误日志 -->
<key>StandardErrorPath</key>
<string>/Users/zhoujl/workspace/netease/auto/launchctl.err</string>

</dict>
</plist>