ScheduledTasks/develop/update_info.py

"""
@author: luojunhui
"""
import json
import requests
import pymysql

from tqdm import tqdm


class UpdateInfo(object):
    """
    updateInfo
    """
    connection = pymysql.connect(
        host='rm-bp1159bu17li9hi94.mysql.rds.aliyuncs.com',
        port=3306,
        user='crawler',
        password='crawler123456@',
        db='piaoquan-crawler',
        charset='utf8mb4')

    @classmethod
    def request_for_info(cls, video_id):
        """
        request for info
        :param video_id:
        :return:
        """
        url = "https://longvideoapi.piaoquantv.com/longvideoapi/openapi/video/batchSelectVideoInfo"
        data = {
            "videoIdList": [video_id]
        }
        header = {
            "Content-Type": "application/json",
        }
        response = requests.post(url, headers=header, data=json.dumps(data))
        return response.json()

    @classmethod
    def updateInfoToMysql(cls, video_id, uid, title):
        """

        :param video_id:
        :param uid:
        :param title:
        :return:
        """
        update_sql = f"""
            update article_match_videos
            set video_title = %s, uid = %s, oss_status = %s
            where video_id = %s;"""
        cursor = cls.connection.cursor()
        cursor.execute(
            update_sql,
            (
                title, uid, 1, video_id
            )
        )
        cls.connection.commit()

    @classmethod
    def select_video_info(cls):
        """
        :return:
        """
        sql = f"""
        select video_id from article_match_videos where video_path is not NULL and uid is NULL;
        """
        cursor = cls.connection.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        for line in tqdm(data):
            video_id = line[0]
            detail = cls.request_for_info(video_id)
            uid = detail['data'][0]['user']['uid']
            title = detail['data'][0]['title']
            if "search" not in title:
                cls.updateInfoToMysql(video_id, uid, title)


U = UpdateInfo()
U.select_video_info()