# 核心工具包
import os
import random
import smtplib
import socket
import sys
import time
from email.header import Header
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

import logbook
import logbook.more
import openpyxl
import pandas as pd
import requests
import zhconv
import redis
from retry import retry
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
import langid

# 创建连接池
import pymysql
from DBUtils.PooledDB import PooledDB
# import sys
# sys.path.append('D://zzsn_spider//base//fdfs_client')

from fdfs_client.client import get_tracker_conf, Fdfs_client
import uuid

# tracker_conf = get_tracker_conf('D:\\kkwork\\zzsn_spider\\base\\client.conf')
# client = Fdfs_client(tracker_conf)

from obs import ObsClient
import fitz
from urllib.parse import unquote

obsClient = ObsClient(
    access_key_id='VEHN7D0TJ9316H8AHCAV',  # 你的华为云的ak码
    secret_access_key='heR353lvSWVPNU8pe2QxDtd8GDsO5L6PGH5eUoQY',  # 你的华为云的sk
    server='https://obs.cn-north-1.myhuaweicloud.com'  # 你的桶的地址
)


# 注意 程序退出前 调用BaseCore.close() 关闭相关资源

class BaseCore:
    # 序列号
    __seq = 0
    # 代理池 数据库连接
    # __cnx_proxy =None
    # __cursor_proxy = None
    cnx = None
    cursor = None
    cnx_ = None
    cursor_ = None
    r = None
    # agent 池
    __USER_AGENT_LIST = [
        'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.90 Safari/537.36',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/525.13 (KHTML, like Gecko) Chrome/0.2.149.29 Safari/525.13',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/531.4 (KHTML, like Gecko) Chrome/3.0.194.0 Safari/531.4',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.11 Safari/534.16',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/1.0.154.50 Safari/525.19',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.211.7 Safari/532.0',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; Lunascape 5.0 alpha2)',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.7 Safari/532.2',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; ru-RU) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.11 Safari/534.16',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.10 Safari/532.0',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Maxthon;',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.1 (KHTML, like Gecko) Chrome/2.0.169.0 Safari/530.1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; ja-JP; rv:1.7) Gecko/20040614 Firefox/0.9',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.810.0 Safari/535.1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.211.0 Safari/532.0',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Maxthon/4.4.3.4000 Chrome/30.0.1599.101 Safari/537.36',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.6 (KHTML, like Gecko) Chrome/7.0.500.0 Safari/534.6',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; TencentTraveler)',
        'Mozilla/5.0 (Windows NT 6.0; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/534.4 (KHTML, like Gecko) Chrome/6.0.481.0 Safari/534.4',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/533.4 (KHTML, like Gecko) Chrome/5.0.370.0 Safari/533.4',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/0.4.154.31 Safari/525.19',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.1.17) Gecko/20110123 (like Firefox/3.x) SeaMonkey/2.0.12',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB) AppleWebKit/534.1 (KHTML, like Gecko) Chrome/6.0.428.0 Safari/534.1',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; de-DE) AppleWebKit/534.10 (KHTML, like Gecko) Chrome/7.0.540.0 Safari/534.10',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; de-DE) Chrome/4.0.223.3 Safari/532.2',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/12.0.702.0 Safari/534.24',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/1.0.154.42 Safari/525.19',
        'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.3 (KHTML, like Gecko) Chrome/4.0.227.0 Safari/532.3',
        'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/535.8 (KHTML, like Gecko) Chrome/16.0.912.63 Safari/535.8',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.460.0 Safari/534.3',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.463.0 Safari/534.3',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/528.9 (KHTML, like Gecko) Chrome/2.0.157.0 Safari/528.9',
        'Mozilla/5.0 (Windows NT 5.2) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.794.0 Safari/535.1',
        'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.694.0 Safari/534.24',
        'Mozilla/5.0 (Windows NT 6.0) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.36 Safari/536.5',
        'Mozilla/5.0 (Windows NT 5.1; U; en; rv:1.8.1) Gecko/20061208 Firefox/2.0.0 Opera 9.50',
        'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20120427 Firefox/15.0a1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.6 (KHTML, like Gecko) Chrome/20.0.1092.0 Safari/536.6',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; .NET CLR 1.1.4322)',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.223.4 Safari/532.2',
        'Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.65 Safari/535.11',
        'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/13.0.782.41 Safari/535.1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.21 (KHTML, like Gecko) Chrome/11.0.682.0 Safari/534.21',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/530.0 (KHTML, like Gecko) Chrome/2.0.182.0 Safari/531.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.9 (KHTML, like Gecko) Chrome/7.0.531.0 Safari/534.9',
        'Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; WOW64; Trident/6.0)',
        'Mozilla/5.0 (Windows NT 6.1; rv:2.0.1) Gecko/20100101 Firefox/4.0.1',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.811.0 Safari/535.1',
        'ozilla/5.0 (Windows; U; Windows NT 5.0; de-DE; rv:1.7.5) Gecko/20041108 Firefox/1.0',
        'Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_8; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1 Safari/534.50',
        'Mozilla/5.0 (SymbianOS/9.4; Series60/5.0 NokiaN97-1/20.0.019; Profile/MIDP-2.1 Configuration/CLDC-1.1) AppleWebKit/525 (KHTML, like Gecko) BrowserNG/7.1.18124',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/533.4 (KHTML, like Gecko) Chrome/5.0.375.127 Safari/533.4',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.3; .NET4.0C; .NET4.0E) QQBrowser/6.9.11079.201',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/531.21.8 (KHTML, like Gecko) Version/4.0.4 Safari/531.21.10',
        'Mozilla/5.0 (Windows NT 6.0) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.120 Safari/535.2',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; zh-cn) Opera 8.50',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.13 (KHTML, like Gecko) Chrome/7.0.0 Safari/700.13',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.211.4 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/1.0.154.53 Safari/525.19',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.6 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.1 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041107 Firefox/0.9.2 StumbleUpon/1.994',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-GB; rv:1.9.0.11) Gecko/2009060215 Firefox/3.0.11 (.NET CLR 3.5.30729)',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.7.5) Gecko/20041110 Firefox/1.0',
        'Mozilla/5.0 (Windows NT 6.2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1467.0 Safari/537.36',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; en) Opera 8.0',
        'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/13.0.782.41 Safari/535.1 QQBrowser/6.9.11079.201',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2',
        'Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.11 (KHTML, like Gecko) Chrome/20.0.1132.11 TaoBrowser/2.0 Safari/536.11',
        'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:2.0b4pre) Gecko/20100815 Minefield/4.0b4pre',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0 x64; en-US; rv:1.9pre) Gecko/2008072421 Minefield/3.0.2pre',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.6 Safari/530.5',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705)',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.21 Safari/532.0',
        'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.792.0 Safari/535.1',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/530.1 (KHTML, like Gecko) Chrome/2.0.168.0 Safari/530.1',
        'Mozilla/5.0 (X11; U; Linux x86_64; zh-CN; rv:1.9.2.10) Gecko/20100922 Ubuntu/10.10 (maverick) Firefox/3.6.10',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; rv:1.7.3) Gecko/20040913 Firefox/0.10',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.8 (KHTML, like Gecko) Chrome/2.0.177.1 Safari/530.8',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/533.17.8 (KHTML, like Gecko) Version/5.0.1 Safari/533.17.8',
        'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0)',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.40 Safari/530.5',
        'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.24 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/528.10 (KHTML, like Gecko) Chrome/2.0.157.2 Safari/528.10',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.223.2 Safari/532.2',
        'Mozilla/5.0 (Windows NT 6.0) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.75 Safari/535.7',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; T312461)',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; TencentTraveler 4.0)',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.461.0 Safari/534.3',
        'Mozilla/5.0 (Windows; U; Windows NT 5.0; rv:1.7.3) Gecko/20041001 Firefox/0.10.1',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)',
        'Mozilla/5.0 (Windows NT 6.2) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1062.0 Safari/536.3',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; de-DE) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.202.2 Safari/532.0',
        'Mozilla/5.0 (Windows NT 6.2; Win64; x64; rv:16.0) Gecko/16.0 Firefox/16.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/531.3 (KHTML, like Gecko) Chrome/3.0.193.2 Safari/531.3',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)',
        'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.864.0 Safari/535.2',
        'Mozilla/5.0 (Windows NT 5.2) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.813.0 Safari/535.1',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.6 Safari/532.0',
        'Mozilla/5.0 (Windows NT 5.1; rv:2.1.1) Gecko/20110415 Firefox/4.0.2pre Fennec/4.0.1',
        'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.801.0 Safari/535.1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.212.0 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.13 (KHTML, like Gecko) Chrome/0.2.149.27 Safari/525.13',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.5 (KHTML, like Gecko) Chrome/4.0.249.0 Safari/532.5',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.36 Safari/535.7',
        'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.697.0 Safari/534.24',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.10 (KHTML, like Gecko) Chrome/7.0.548.0 Safari/534.10',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/534.17 (KHTML, like Gecko) Chrome/11.0.652.0 Safari/534.17',
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.62 Safari/537.36',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/534.10 (KHTML, like Gecko) Chrome/8.0.552.224 Safari/534.10 ChromePlus/1.5.2.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.1 (KHTML, like Gecko) Chrome/4.0.219.0 Safari/532.1',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.211.7 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/533.2 (KHTML, like Gecko) Chrome/5.0.342.2 Safari/533.2',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.1 (KHTML, like Gecko) Chrome/4.0.219.4 Safari/532.1',
        'Mozilla/5.0 (Windows NT 6.0; rv:2.1.1) Gecko/20110415 Firefox/4.0.2pre Fennec/4.0.1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/0.2.153.0 Safari/525.19',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; sv-SE; rv:1.7.5) Gecko/20041108 Firefox/1.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.462.0 Safari/534.3',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; de-DE; rv:1.7.5) Gecko/20041122 Firefox/1.0',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; uZardWeb/1.0; Server_JP)',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; HCI0449; .NET CLR 1.0.3705)',
        'Mozilla/4.0 (compatible; MSIE 5.0; Windows 98; DigExt); Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1);',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.23 Safari/530.5',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.208.0 Safari/532.0',
        'Mozilla/5.0 (Windows NT 6.0; rv:14.0) Gecko/20100101 Firefox/14.0.1',
        'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36',
        'Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/19.77.34.5 Safari/537.1',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/530.7 (KHTML, like Gecko) Chrome/2.0.176.0 Safari/530.7',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.21 (KHTML, like Gecko) Chrome/11.0.678.0 Safari/534.21',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.21 Safari/532.0',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)',
        'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; InfoPath.1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/1.0.154.55 Safari/525.19',
        'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0a1) Gecko/20110623 Firefox/7.0a1 Fennec/7.0a1',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.30 (KHTML, like Gecko) Chrome/12.0.724.100 Safari/534.30',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.33 Safari/534.3 SE 2.X MetaSr 1.0',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; WOW64; SV1; uZardWeb/1.0; Server_HK)',
        'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20100101 Firefox/7.0.1',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506)',
        'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.3; .NET4.0C; .NET4.0E)',
        'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1063.0 Safari/536.3',
        'Mozilla/5.0 (Windows NT 6.0) yi; AppleWebKit/345667.12221 (KHTML, like Gecko) Chrome/23.0.1271.26 Safari/453667.1221',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/531.2 (KHTML, like Gecko) Chrome/3.0.191.3 Safari/531.2',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.39 Safari/530.5',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.1 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.38 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.27 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8b) Gecko/20050118 Firefox/1.0+',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; ja-JP; rv:1.7) Gecko/20040707 Firefox/0.9.2',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.202.0 Safari/532.0',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SE 2.X MetaSr 1.0; SE 2.X MetaSr 1.0; .NET CLR 2.0.50727; SE 2.X MetaSr 1.0)',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/530.4 (KHTML, like Gecko) Chrome/2.0.171.0 Safari/530.4',
        'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648)',
        'Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/535.24 (KHTML, like Gecko) Chrome/19.0.1055.1 Safari/535.24',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; nl-NL; rv:1.7.5) Gecko/20041202 Firefox/1.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.204.0 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/532.2 (KHTML, like Gecko) Chrome/4.0.222.6 Safari/532.2',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/528.8 (KHTML, like Gecko) Chrome/1.0.156.0 Safari/528.8',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/6.0)',
        'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 2.0.50727; .NET CLR 1.1.4322)',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.7 (KHTML, like Gecko) Chrome/7.0.517.43 Safari/534.7',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.13 (KHTML, like Gecko) Chrome/9.0.597.15 Safari/534.13',
        'Mozilla/5.0 (ipad Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.6 (KHTML, like Gecko) Chrome/7.0.498.0 Safari/534.6',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.43 Safari/530.5',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.208.0 Safari/532.0',
        'Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.66 Safari/535.11',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.19 (KHTML, like Gecko) Chrome/11.0.661.0 Safari/534.19',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-CA) AppleWebKit/534.13 (KHTML like Gecko) Chrome/9.0.597.98 Safari/534.13',
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.211.2 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.201.1 Safari/532.0',
        'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1061.1 Safari/536.3',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/4.0.201.1 Safari/532.0',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/532.1 (KHTML, like Gecko) Chrome/4.0.213.1 Safari/532.1',
        'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/530.6 (KHTML, like Gecko) Chrome/2.0.174.0 Safari/530.6',
        'Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/0.3.154.6 Safari/525.19',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.13 (KHTML, like Gecko) Chrome/9.0.599.0 Safari/534.13',
        'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/534.8 (KHTML, like Gecko) Chrome/7.0.521.0 Safari/534.8',
        'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1b2pre) Gecko/20081015 Fennec/1.0a1',
        'Mozilla/5.0 (iPod; U; CPU iPhone OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5'
    ]

    # Android agent池
    __USER_PHONE_AGENT_LIST = [
        'Mozilla/5.0 (Linux; Android 7.1.1; OPPO R9sk) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.111 Mobile Safari/537.36']

    def __init__(self, sqlFlg=True):
        # self.__cnx_proxy = pymysql.connect(host='114.115.159.144', user='caiji', password='zzsn9988', db='clb_project',
        #                                    charset='utf8mb4')
        # self.__cursor_proxy = self.__cnx_proxy.cursor()
        # 连接到Redis
        self.r = redis.Redis(host="114.116.90.53", port=6380, password='clbzzsn', db=6)
        if sqlFlg:
            self.cnx = pymysql.connect(host='114.115.159.144', user='caiji', password='zzsn9988', db='caiji',
                                       charset='utf8mb4')

            self.cursor = self.cnx.cursor()

            # 11数据库
            self.cnx_ = pymysql.connect(host='114.116.44.11', user='caiji', password='f7s0&7qqtK', db='clb_project',
                                        charset='utf8mb4')
            self.cursor_ = self.cnx_.cursor()
            self.pool_caiji = PooledDB(
                creator=pymysql,
                maxconnections=5,
                mincached=2,
                maxcached=5,
                blocking=True,
                host='114.115.159.144',
                port=3306,
                user='caiji',
                password='zzsn9988',
                database='caiji',
                charset='utf8mb4'
            )

            self.pool_11 = PooledDB(
                creator=pymysql,
                maxconnections=5,
                mincached=2,
                maxcached=5,
                blocking=True,
                host='114.116.44.11',
                port=3306,
                user='caiji',
                password='f7s0&7qqtK',
                database='clb_project',
                charset='utf8mb4'
            )

    def check_mysql_conn(self, conn):
        try:
            conn.ping()
            return True
        except:
            return False

    def close(self):
        try:
            self.cursor.close()
            self.cnx.close()
        except:
            pass

    # 计算耗时
    def getTimeCost(self, start, end):
        seconds = int(end - start)
        m, s = divmod(seconds, 60)
        h, m = divmod(m, 60)
        if (h > 0):
            return "%d小时%d分钟%d秒" % (h, m, s)
        elif (m > 0):
            return "%d分钟%d秒" % (m, s)
        elif (seconds > 0):
            return "%d秒" % (s)
        else:
            ms = int((end - start) * 1000)
            return "%d毫秒" % (ms)

    # 当前时间格式化
    # 1 : 2001-01-01 12:00:00 %Y-%m-%d %H:%M:%S
    # 2 : 010101120000 %y%m%d%H%M%S
    # 时间戳 3:1690179526555  精确到秒
    def getNowTime(self, type):
        now_time = ""
        if type == 1:
            now_time = time.strftime("%Y-%m-%d %H:%M:%S")
        if type == 2:
            now_time = time.strftime("%y%m%d%H%M%S")
        if type == 3:
            now_time = int(time.time() * 1000)
        return now_time

    # 获取流水号
    def getNextSeq(self):
        self.__seq += 1
        if self.__seq > 1000:
            self.__seq = 0
        return self.getNowTime(2) + str(self.__seq).zfill(3)

    # 获取信用代码
    def getNextXydm(self):
        self.__seq += 1
        if self.__seq > 1000:
            self.__seq = 0
        return "ZZSN" + self.getNowTime(2) + str(self.__seq).zfill(3)

    # 日志格式
    def logFormate(self, record, handler):
        formate = "[{date}] [{level}] [{filename}] [{func_name}] [{lineno}] {msg}".format(
            date=record.time,  # 日志时间
            level=record.level_name,  # 日志等级
            filename=os.path.split(record.filename)[-1],  # 文件名
            func_name=record.func_name,  # 函数名
            lineno=record.lineno,  # 行号
            msg=record.message  # 日志内容
        )
        return formate

    # 获取logger
    def getLogger(self, fileLogFlag=True, stdOutFlag=True):
        pid = self.getPID()
        dirname, filename = os.path.split(os.path.abspath(sys.argv[0]))
        dirname = os.path.join(dirname, "logs")
        filename = filename.replace(".py", "") + f"{pid}.log"
        if not os.path.exists(dirname):
            os.mkdir(dirname)

        logbook.set_datetime_format('local')
        logger = logbook.Logger(filename)
        logger.handlers = []

        if fileLogFlag:  # 日志输出到文件
            logFile = logbook.TimedRotatingFileHandler(os.path.join(dirname, filename), date_format='%Y-%m-%d',
                                                       bubble=True, encoding='utf-8')
            logFile.formatter = self.logFormate
            logger.handlers.append(logFile)

        if stdOutFlag:  # 日志打印到屏幕
            logStd = logbook.more.ColorizedStderrHandler(bubble=True)
            logStd.formatter = self.logFormate
            logger.handlers.append(logStd)
        return logger

    # 获取随机的userAgent
    def getRandomUserAgent(self):
        return random.choice(self.__USER_AGENT_LIST)

    # 获取代理
    def get_proxy(self):
        sql = "select proxy from clb_proxy"
        self.cursor.execute(sql)
        proxy_lists = self.cursor.fetchall()
        self.cnx.commit()
        ip_list = []
        for proxy_ in proxy_lists:
            ip_list.append(str(proxy_).replace("('", '').replace("',)", ''))
        proxy_list = []
        for str_ip in ip_list:
            str_ip_list = str_ip.split('-')
            proxyMeta = "http://%(host)s:%(port)s" % {
                "host": str_ip_list[0],
                "port": str_ip_list[1],
            }
            # proxy = {
            #     "HTTP": proxyMeta,
            #     "HTTPS": proxyMeta
            # }
            proxy = {
                "http": proxyMeta,
                "https": proxyMeta
            }
            proxy_list.append(proxy)
        return proxy_list[random.randint(0, 3)]

    # 字符串截取
    def getSubStr(self, str, beginStr, endStr):
        if beginStr == '':
            pass
        else:
            begin = str.rfind(beginStr)
            if begin == -1:
                begin = 0
            str = str[begin:]
        if endStr == '':
            pass
        else:
            end = str.rfind(endStr)
            if end == -1:
                pass
            else:
                str = str[0:end + 1]
        return str

    # 繁体字转简体字
    def hant_2_hans(self, hant_str: str):
        '''
        Function: 将 hant_str 由繁体转化为简体
        '''
        return zhconv.convert(hant_str, 'zh-hans')

    # 判断字符串里是否含数字
    def str_have_num(self, str_num):
        panduan = False

        for str_1 in str_num:
            ppp = str_1.isdigit()
            if ppp:
                panduan = ppp
        return panduan

    # # 从Redis的List中获取并移除一个元素
    # def redicPullData(self,type,key):
    # #1 表示国内 2 表示国外
    #     if type == 1:
    #         gn_item = self.r.lpop(key)
    #         return gn_item.decode() if gn_item else None
    #     if type == 2:
    #         gw_item = self.r.lpop(key)
    #         return gw_item.decode() if gw_item else None

    # 从Redis的List中获取并移除一个元素
    def redicPullData(self, key):
        try:
            self.r.ping()
        except:
            self.r = redis.Redis(host="114.115.236.206", port=6379, password='clbzzsn', db=6)
        item = self.r.lpop(key)
        return item.decode() if item else None

    # 获得脚本进程PID
    def getPID(self):
        PID = os.getpid()
        return PID

    # 获取本机IP
    def getIP(self):
        IP = socket.gethostbyname(socket.gethostname())
        return IP

    def mkPath(self, path):
        folder = os.path.exists(path)
        if not folder:  # 判断是否存在文件夹如果不存在则创建为文件夹
            os.makedirs(path)  # makedirs 创建文件时如果路径不存在会创建这个路径
        else:
            pass

    # 生成google模拟浏览器  必须传入值为googledriver位置信息
    # headless用于决定是否为无头浏览器,初始默认为无头浏览器
    # 正常浏览器可用于开始对页面解析使用或一些网站无头时无法正常采集
    # 无头浏览器用于后续对信息采集时不会有浏览器一直弹出，
    def buildDriver(self, path, headless=True):

        service = Service(path)
        chrome_options = webdriver.ChromeOptions()
        if headless:
            chrome_options.add_argument('--headless')
            chrome_options.add_argument('--disable-gpu')
        chrome_options.add_experimental_option(
            "excludeSwitches", ["enable-automation"])
        chrome_options.add_experimental_option('useAutomationExtension', False)
        chrome_options.add_argument('lang=zh-CN,zh,zh-TW,en-US,en')

        chrome_options.add_argument('user-agent=' + self.getRandomUserAgent())
        # 'user-agent=Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36')
        driver = webdriver.Chrome(options=chrome_options, service=service)
        # with open(r'F:\zzsn\zzsn_spider\base\stealth.min.js') as f:
        #     js = f.read()
        #
        # driver.execute_cdp_cmd("Page.addScriptToEvaluateOnNewDocument", {
        #     "source": js
        # })
        return driver

    # 根据社会信用代码获取企业信息
    def getInfomation(self, social_code):
        data = []
        try:
            sql = f"SELECT * FROM EnterpriseInfo WHERE SocialCode = '{social_code}'"
            # self.cursor.execute(sql)
            # data = self.cursor.fetchone()
            conn = self.pool_caiji.connection()
            cursor = conn.cursor()
            cursor.execute(sql)
            data = cursor.fetchone()
            conn.commit()
            data = list(data)
            cursor.close()
            conn.close()
        except:
            log = self.getLogger()
            log.info('=========数据库操作失败========')
        return data

    # 根据企业名称获取企业信息
    def getBYnameInfomation(self, com_name):
        data = []
        try:
            sql = f"SELECT * FROM EnterpriseInfo WHERE CompanyName = '{com_name}'"
            # self.cursor.execute(sql)
            # data = self.cursor.fetchone()
            conn = self.pool_caiji.connection()
            cursor = conn.cursor()
            cursor.execute(sql)
            data = cursor.fetchone()
            conn.commit()
            data = list(data)
            cursor.close()
            conn.close()
        except:
            log = self.getLogger()
            log.info('=========数据库操作失败========')

        return data

        # 根据企业名称获取企业信息

    def getBYtycidInfomation(self, com_name):
        data = []
        try:
            sql = f"SELECT * FROM EnterpriseInfo WHERE TYCID = '{com_name}'"
            # self.cursor.execute(sql)
            # data = self.cursor.fetchone()
            conn = self.pool_caiji.connection()
            cursor = conn.cursor()
            cursor.execute(sql)
            data = cursor.fetchone()
            conn.commit()
            data = list(data)
            cursor.close()
            conn.close()
        except:
            log = self.getLogger()
            log.info('=========数据库操作失败========')

        return data

    # 更新企业采集次数
    def updateRun(self, social_code, runType, count):
        try:
            sql_update = f"UPDATE EnterpriseInfo SET {runType} = {count} WHERE SocialCode = '{social_code}'"
            # self.cursor.execute(sql_update)
            # self.cnx.commit()
            conn = self.pool_caiji.connection()
            cursor = conn.cursor()
            cursor.execute(sql_update)
            conn.commit()
            cursor.close()
            conn.close()
        except:
            log = self.getLogger()
            log.info('======更新数据库失败======')

    # 保存日志入库
    def recordLog(self, xydm, taskType, state, takeTime, url, e):
        try:
            createTime = self.getNowTime(1)
            ip = self.getIP()
            pid = self.getPID()
            sql = "INSERT INTO LogTable(SocialCode,TaskType,state,TakeTime,url,CreateTime,ProcessIp,PID,Exception) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            values = [xydm, taskType, state, takeTime, url, createTime, ip, pid, e]
            # try:
            #     self.cursor.execute(sql, values)
            # except Exception as e:
            #     print(e)
            # self.cnx.commit()
            cnn = self.pool_caiji.connection()
            cursor = cnn.cursor()
            cursor.execute(sql, values)
            cnn.commit()
            cursor.close()
            cnn.close()
        except:
            log = self.getLogger()
            log.info('======保存日志失败=====')

    # 获取企查查token
    def GetToken(self):

        # 获取企查查token
        query = "select token from QCC_token "
        # token = '67ec7402166df1da84ae83c4b95cefc0'  # 需要隔两个小时左右抓包修改
        self.cursor.execute(query)
        token_list = self.cursor.fetchall()
        self.cnx.commit()
        try:
            token = token_list[random.randint(0, len(token_list) - 1)][0]
        except:
            token = ''
        return token

    # 删除失效的token
    def delete_token(self, token):
        deletesql = f"delete from QCC_token where token='{token}' "
        self.cursor.execute(deletesql)
        self.cnx.commit()

    # 获取天眼查token
    def GetTYCToken(self):
        query = 'select token from TYC_token'
        self.cursor.execute(query)
        token = self.cursor.fetchone()[0]
        self.cnx.commit()
        return token

    # 检测语言
    def detect_language(self, text):
        # 使用langid.py判断文本的语言
        result = langid.classify(text)
        if result == '':
            return 'cn'
        if result[0] == '':
            return 'cn'
        if result[0] == 'ja':
            return 'jp'
        if result[0] == 'fr':
            return 'fra'
        if result[0] == 'es':
            return 'spa'
        if result[0] == 'fi':
            return 'fin'
        if result[0] == 'vi':
            return 'vie'
        if result[0] == 'ko':
            return 'kor'
        if result[0] == 'da':
            return 'dan'
        return result[0]

    # 创建excel文件
    def check_excel_file(self, file_path):
        if os.path.isfile(file_path):
            self.getLogger().info("Excel文件已存在")
            return True
        else:
            self.getLogger().info("Excel文件不存在，正在创建...")
            return False

    # 追加接入excel
    def writerToExcel(self, detailList, filename):
        # filename='baidu搜索.xlsx'
        # 读取已存在的xlsx文件
        existing_data = pd.read_excel(filename, engine='openpyxl', dtype=str)
        # 创建新的数据
        new_data = pd.DataFrame(data=detailList)
        # 将新数据添加到现有数据的末尾
        combined_data = existing_data.append(new_data, ignore_index=True)
        # 将结果写入到xlsx文件
        combined_data.to_excel(filename, index=False)
        # return combined_data

    # 对失败或者断掉的企业 重新放入redis
    def rePutIntoR(self, key, item):
        try:
            self.r.ping()
        except:
            self.r = redis.Redis(host="114.116.90.53", port=6380, password='clbzzsn', db=6)
        self.r.rpush(key, item)

    # 增加计数器的值并返回增加后的值
    def incrSet(self, key):
        # 增加计数器的值并返回增加后的值
        new_value = self.r.incr(key)
        print("增加后的值：", new_value)
        return new_value

    # 获取key剩余的过期时间
    def getttl(self, key):
        # 获取key的剩余过期时间
        ttl = self.r.ttl(key)
        print("剩余过期时间：", ttl)
        # 判断key是否已过期
        if ttl < 0:
            # key已过期，将key的值重置为0
            self.r.set(key, 0)
            self.r.expire(key, 3600)
            time.sleep(2)

    # # 上传至文件服务器,并解析pdf的内容和页数
    # def upLoadToServe(self, pdf_url, type_id, social_code):
    #     headers = {}
    #     retData = {'state': False, 'type_id': type_id, 'item_id': social_code, 'group_name': '', 'path': '',
    #                'full_path': '',
    #                'category': 'pdf', 'file_size': '', 'status': 1, 'create_by': 'XueLingKun',
    #                'create_time': '', 'page_size': '', 'content': ''}
    #     headers['User-Agent'] = self.getRandomUserAgent()
    #     for i in range(0, 3):
    #         try:
    #             resp_content = requests.get(pdf_url, headers=headers, verify=False, timeout=20).content
    #             break
    #         except:
    #             time.sleep(3)
    #             continue
    #     page_size = 0
    #
    #     for i in range(0, 3):
    #         try:
    #             result = client.upload_by_buffer(resp_content, file_ext_name='pdf')
    #             with fitz.open(stream=resp_content, filetype='pdf') as doc:
    #                 page_size = doc.page_count
    #                 for page in doc.pages():
    #                     retData['content'] += page.get_text()
    #             break
    #         except:
    #             time.sleep(3)
    #             continue
    #     if page_size < 1:
    #         # pdf解析失败
    #         print(f'======pdf解析失败=====')
    #         return retData
    #     else:
    #         time_now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    #         retData['state'] = True
    #         retData['path'] = bytes.decode(result['Remote file_id']).replace('group1', '')
    #         retData['full_path'] = bytes.decode(result['Remote file_id'])
    #         retData['file_size'] = result['Uploaded size']
    #         retData['create_time'] = time_now
    #         retData['page_size'] = page_size
    #
    #         return retData

    def deliteATT(self, id):
        delitesql = f"delete from clb_sys_attachment where id = '{id}' "
        self.cursor_.execute(delitesql)
        self.cnx_.commit()

    def secrchATT(self, item_id, year, type_id):
        sel_sql = '''select id from clb_sys_attachment where item_id = %s and year = %s and type_id=%s '''
        self.cursor_.execute(sel_sql, (item_id, year, type_id))
        selects = self.cursor_.fetchone()
        return selects

    # 插入到att表 返回附件id
    def tableUpdate(self, retData, com_name, year, pdf_name, num, pub_time, origin):
        item_id = retData['item_id']
        type_id = retData['type_id']
        group_name = retData['group_name']
        path = retData['path']
        full_path = retData['full_path']
        category = retData['category']
        file_size = retData['file_size']
        status = retData['status']
        create_by = retData['create_by']
        page_size = retData['page_size']
        create_time = retData['create_time']
        order_by = num
        selects = self.secrchATT(item_id, year, type_id)

        if selects:
            self.getLogger().info(f'com_name:{com_name}--{year}已存在')
            id = ''
            return id
        else:
            Upsql = '''insert into clb_sys_attachment(year,name,type_id,item_id,group_name,path,full_path,category,file_size,order_by,status,create_by,create_time,page_size,object_key,bucket_name,publish_time,source) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''

            values = (
                year, pdf_name, type_id, item_id, group_name, path, full_path, category, file_size, order_by,
                status, create_by,
                create_time, page_size, full_path.split('https://zzsn.obs.cn-north-1.myhuaweicloud.com/')[1], 'zzsn',
                pub_time, origin)

            self.cursor_.execute(Upsql, values)  # 插入
            self.cnx_.commit()  # 提交
            self.getLogger().info("更新完成:{}".format(Upsql))
            selects = self.secrchATT(item_id, year, type_id)
            id = selects[0]
            return id

    # 更新企业的CIK
    def updateCIK(self, social_code, cik):
        try:
            sql = f"UPDATE EnterpriseInfo SET CIK = '{cik}' WHERE SocialCode = '{social_code}'"
            cnn = self.pool_caiji.connection()
            cursor = cnn.cursor()
            cursor.execute(sql)
            cnn.commit()
            cursor.close()
            cnn.close()
        except:
            log = self.getLogger()
            log.info('======保存企业CIK失败=====')

    # 上传至obs华为云服务器，并解析破地方的内容和页数
    # 获取文件大小
    def convert_size(self, size_bytes):
        # 定义不同单位的转换值
        units = ['bytes', 'KB', 'MB', 'GB', 'TB']
        i = 0
        while size_bytes >= 1024 and i < len(units) - 1:
            size_bytes /= 1024
            i += 1
        return f"{size_bytes:.2f} {units[i]}"

    # 查看obs文件是否已经上传
    def obsexist(self, file_path):
        # # 文件路径
        # file_path = 'XQWAnnualReport/2023-10/浙江国祥股份有限公司首次公开发行股票并在主板上市暂缓发行公告.doc'

        # 检查文件是否存在
        response = obsClient.getObjectMetadata('zzsn', file_path)

        if response.status >= 300:
            self.getLogger().info('=====文件不存在obs=====')
            return True
        else:
            self.getLogger().info(f'=====文件存在obs========{file_path}')

    # uuid 根据时间戳生成 文件名 上传到obs

    def getuuid(self):
        get_timestamp_uuid = uuid.uuid1()  # 根据 时间戳生成 uuid , 保证全球唯一
        return get_timestamp_uuid

    def uptoOBS(self, pdf_url, name_pdf, type_id, social_code, pathType, taskType, start_time, create_by):
        headers = {}
        retData = {'state': False, 'type_id': type_id, 'item_id': social_code, 'group_name': '', 'path': '',
                   'full_path': '',
                   'category': 'pdf', 'file_size': '', 'status': 1, 'create_by': create_by,
                   'create_time': '', 'page_size': '', 'content': ''}
        headers['User-Agent'] = self.getRandomUserAgent()
        for i in range(0, 3):
            try:
                response = requests.get(pdf_url, headers=headers, verify=False, timeout=20)
                file_size = int(response.headers.get('Content-Length'))
                break
            except:
                time.sleep(3)
                continue
        page_size = 0
        name = str(self.getuuid()) + '.pdf'
        now_time = time.strftime("%Y-%m")
        try:
            result = self.getOBSres(pathType, now_time, name, response)
        except:
            log = self.getLogger()
            log.error(f'OBS发送失败')
            return retData
        try:
            with fitz.open(stream=response.content, filetype='pdf') as doc:
                page_size = doc.page_count
                for page in doc.pages():
                    retData['content'] += page.get_text()

        except:
            log = self.getLogger()
            log.error(f'文件损坏')
            return retData

        if page_size < 1:
            # pdf解析失败
            # print(f'======pdf解析失败=====')
            return retData
        else:
            try:
                time_now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                retData['state'] = True
                retData['path'] = result['body']['objectUrl'].split('.com')[1]
                retData['full_path'] = result['body']['objectUrl']
                retData['file_size'] = self.convert_size(file_size)
                retData['create_time'] = time_now
                retData['page_size'] = page_size
            except Exception as e:
                state = 0
                takeTime = self.getTimeCost(start_time, time.time())
                self.recordLog(social_code, taskType, state, takeTime, pdf_url, f'{e}')
                return retData

            return retData

    @retry(tries=3, delay=1)
    def getOBSres(self, pathType, now_time, name, response):
        result = obsClient.putContent('zzsn', pathType + name, content=response.content)
        # resp = obsClient.putFile('zzsn', pathType + name, file_path='要上传的那个文件的本地路径')
        return result

    def sendEmail(self, file_name):

        file = open(file_name, 'rb').read()
        # 发送邮箱地址
        sender = '1195236739@qq.com'
        # 接收邮箱地址
        receiver = 'fujunxue@ciglobal.cn'
        smtpserver = 'smtp.qq.com'
        # 发送邮箱登录 账户 密码
        username = '1195236739@qq.com'
        password = 'gatvszshadvpgjci'

        maile_title = '企业基本信息采集情况'

        message = MIMEMultipart()
        message['From'] = sender
        message['To'] = receiver
        message['Subject'] = Header(maile_title, 'utf-8')

        message.attach(MIMEText('企业基本信息采集情况', 'plain', 'utf-8'))

        xlsxApart = MIMEApplication(file)
        xlsxApart.add_header('Content-Disposition', 'attachment', filename='企业基本信息采集情况.xlsx')
        message.attach(xlsxApart)
        smtpObj = smtplib.SMTP_SSL(smtpserver)  # 注意：如果遇到发送失败的情况（提示远程主机拒接连接），这里要使用SMTP_SSL方法
        smtpObj.connect(smtpserver, port=465)
        smtpObj.login(username, password)
        smtpObj.sendmail(sender, receiver, message.as_string())
        print("邮件发送成功！！！")
        smtpObj.quit()
