spider_work/weikajia_spider/fill_data.py

# -*- coding: utf-8 -*-
# Author  : Charley
# Python  : 3.8.10
# Date: 2024-09-24 10:50
import requests
from loguru import logger
from retrying import retry
from mysq_pool import MySQLConnectionPool

base_url = "https://api.weikajia.com"
def save_data(sql_pool,info):
    logger.debug("正在写入数据库............")
    sql = """
            UPDATE weikajia_bidding
            SET nickName=%s,
                avatarOss=%s,
                following=%s,
                following=%s,
                voteRate=%s,
                level=%s,
                introduceSign=%s,
                certifyStatus=%s,
                ipRegion=%s,
                blueVflag=%s,
                shopVflag=%s,
                credit=%s,
                agentLevel=%s,
                agentId=%s
            WHERE id=%s
               """
    sql_pool.update_one(sql, info)


@retry(stop_max_attempt_number=3, wait_fixed=1000)
def get_action(auctionId,headers):
    """
    获取auction信息
    :param auctionId:
    :return: agentUserInfo
    """
    logger.debug(f'正在查询auctionId为: {auctionId}的信息..............')
    url = f"{base_url}/api/v2/auction/detail"
    params = {
        "auctionId": auctionId
    }
    response = requests.get(url, headers=headers, params=params, timeout=5)
    # print(f'get_action: {response.json()}')
    if response.json()["resultCode"] == 200:
        agentUserInfo = response.json()["data"].get("agentUserInfo")
        agentId = response.json()["data"].get("agentId")
        agentUserInfo["agentId"] = agentId
        return agentUserInfo
    else:
        logger.debug("get_action 请求失败,重试中...........")
        raise Exception("请求失败")


@retry(stop_max_attempt_number=3, wait_fixed=1000)
def get_cabinet(cabinetId,headers):
    """
    获取cabinet信息
    :param cabinetId:
    :return: cab_dict
    """
    logger.debug(f'正在查询cabinetId为: {cabinetId}的信息..............')
    url = f"{base_url}/api/v2/cabinet/detail"
    params = {
        "cabinetId": cabinetId
    }
    response = requests.get(url, headers=headers, params=params, timeout=5)
    # print(f'get_cabinet: {response.json()}')
    if response.json()["resultCode"] == 200:
        data = response.json()["data"]
        cab_dict = {"rmbPrice": data.get("rmbPrice"), "brand": data.get("brand"), "status": data.get("status"),
                    "switchSt": data.get("switchSt"), "cardNo": data.get("cardNo"),
                    "barcodeId": data.get("barcodeId"), "year": data.get("year"), "grade": data.get("grade"),
                    "setName": data.get("setName"), "player": data.get("player"),
                    "onSaleExpireTs": data.get("onSaleExpireTs"), "authenticNumber": data.get("authenticNumber")
                    }
        return cab_dict
    else:
        logger.debug("get_cabinet 请求失败,重试中...........")
        raise Exception("请求失败")

def fill_main():
    try:
        sql_pool = MySQLConnectionPool(log=logger)
        if not sql_pool:
            logger.error("数据库连接失败")
            raise Exception("数据库连接失败")
        token = sql_pool.select_one("select token from wkj_token")
        headers = {
            "appVersion": "1.6.5",
            "osVersion": "9",
            "deviceModel": "M2007J22C",
            "appVersionCode": "168",
            "deviceBrand": "xiaomi",
            "platform": "android",
            "token": token[0],
            "user-agent": "Mozilla/5.0 (Linux; Android 9; M2007J22C Build/QP1A.190711.020; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/92.0.4515.131 Mobile Safari/537.36",
            "Content-Type": "application/json",
            "Connection": "Keep-Alive"
        }

        sql = """
            SELECT id,cabinetId, auctionItemId 
            FROM weikajia_bidding 
            WHERE nickName IS NULL 
              AND avatarOss IS NULL 
              AND following IS NULL 
              AND voteRate IS NULL 
              AND level IS NULL 
              AND introduceSign IS NULL 
              AND certifyStatus IS NULL 
              AND ipRegion IS NULL 
              AND blueVflag IS NULL 
              AND shopVflag IS NULL 
              AND credit IS NULL 
              AND agentLevel IS NULL 
              AND agentId IS NULL 
              AND rmbPrice IS NULL 
              AND brand IS NULL 
              AND status IS NULL 
              AND switchSt IS NULL 
              AND cardNo IS NULL 
              AND printNo IS NULL 
              AND printNoS IS NULL 
              AND sportId IS NULL 
              AND barcodeId IS NULL 
              AND year IS NULL 
              AND grade IS NULL 
              AND setName IS NULL 
              AND playerIds IS NULL 
              AND player IS NULL 
              AND onSaleExpireTs IS NULL 
              AND authenticNumber IS NULL;
        """
        res = sql_pool.select_all(sql)
        for ii in res:
            sql_id = ii[0]
            cabinetId = ii[1]
            auctionId = ii[2]
            try:
                act_dict = get_action(auctionId, headers)
                cab_dict = get_cabinet(cabinetId, headers)
                info = (act_dict, cab_dict,sql_id)
                save_data(sql_pool,info)
                # time.sleep(random.randint(3, 5))
            except Exception as e:
                logger.error(f'出错, {e}')
    except Exception as e:
        logger.error(e)
    finally:
        logger.info("爬虫程序运行结束,等待下一轮的采集任务.............")