"""
 用于从文件中 提取 文件名 与 site_id 之间的映射关系 并写入数据库
"""

import os
import re

import pymysql


class Collect:
    def __init__(self, path):
        db_config = {
            "host": "localhost",
            "port": 3306,
            "user": "root",
            "password": "root",
            "db": "gva"
        }
        self.db = pymysql.connect(**db_config)
        self.cursor = self.db.cursor(pymysql.cursors.DictCursor)
        self.FileList = []
        self.Map = {}
        self.Path = path


    # 获取目录下所有文件名
    def getAllSub(self):
        for home, dirs, files in os.walk(self.Path):
            for filename in files:
                self.FileList.append(os.path.join(home, filename))

    # 便利文件列表 提取映射
    def getMap(self):

        for file in self.FileList:
            # 如果不是py文件直接跳过
            if ".py" != file[-3:]:
                continue
            if file in ["go.py", "add_go.py"]:
                continue

            # 读取文件中的代码
            fp = open(file, "r", encoding="utf-8")
            code = fp.read()

            # 提取 文件名 和site_id 映射
            key = file.split("\\")[-1].replace(".py", "").strip()
            value = re.findall(r"['|\"]([0-9A-Z]{10})['|\"]", code)
            self.Map[key] = value

            fp.close()

    def insert(self, project, mess):
        for key in self.Map.keys():
            for siteId in self.Map[key]:
                sql = "UPDATE spider_mon SET project='{}',spider_name='{}', mess='{}' WHERE site_id='{}'"\
                    .format(project, key, mess, siteId)
                try:
                    self.cursor.execute(sql)
                    self.db.commit()
                    # print(sql)
                except Exception as e:
                    print(e)
                    self.db.rollback()

    def __del__(self):
        self.cursor.close()
        self.db.close()


if __name__ == '__main__':
    coll = Collect(path=r"D:\kyls-yr\PostCrawl\spiders")
    coll.getAllSub()
    coll.getMap()
    coll.insert("PostCrawl_yr", "scrapy")