ScheduledTasks/auto_getoff_videos.py

"""
@author: luojunhui
"""
import time

import requests
import pymysql
import pandas as pd


class AutoGetOffVideos(object):
    """
    自动下架视频
    """

    @classmethod
    def get_long_articles_video_list(cls):
        """
        获取长文视频list
        :return:
        """
        spider_connection = pymysql.connect(
            host="rm-bp1159bu17li9hi94.mysql.rds.aliyuncs.com",
            port=3306,
            user="crawler",
            passwd="crawler123456@",
            db="piaoquan-crawler",
            charset="utf8mb4"
        )
        now_time_stamp = int(time.time())
        three_days_stamp = now_time_stamp - 3 * 24 * 60 * 60
        seven_days_stamp = now_time_stamp - 7 * 24 * 60 * 60
        select_sql = f"""
        SELECT recall_video_id1, recall_video_id2, recall_video_id3 
        FROM long_articles_video 
        WHERE request_time_stamp > %s;
        """
        cursor = spider_connection.cursor()
        cursor.execute(select_sql, seven_days_stamp)
        data = cursor.fetchall()
        vid_set = set()
        for item in data:
            for vid in item:
                if vid:
                    vid_set.add(vid)
        return tuple(vid_set)

    @classmethod
    def find_video_play_count(cls, vid_tuple):
        """
        获取视频的播放次数
        :return:
        """
        wx_video_connection = pymysql.connect(
            host="rr-bp1x9785e8h5452bi157.mysql.rds.aliyuncs.com",
            port=3306,
            user="wx2016_longvideo",
            passwd="wx2016_longvideoP@assword1234",
            db="longvideo",
            charset="utf8mb4"
        )
        sql = f"""select id, title, play_count, play_count_total from wx_video where id in {vid_tuple};"""
        cursor = wx_video_connection.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        result = [
            {
                "video_id": item[0],
                "title": item[1],
                "play_count": item[2],
                "play_total_count": item[3]
            }
            for item in data
            ]
        return result

    @classmethod
    def change_status(cls, video_id):
        """
        修改视频规则
        :return:
        """
        url = "https://admin.piaoquantv.com/manager/video/audit/v2/updateAuditStatus"
        params = {
            "videoId": video_id,
            "auditStatus": 2,
            "updateReasonJson": "",
            "rejectReasonJson": [
                {
                    "reason": "测试自动下架",
                    "reasonId": -1
                }
            ],
            "adminUid": 206

        }
        headers = {
            'accept': 'application/json',
            'accept-language': 'en,zh;q=0.9,zh-CN;q=0.8',
            'content-type': 'application/x-www-form-urlencoded;charset=UTF-8',
            'cookie': 'SESSION=NTljNTg2YjktMTU0MC00YWQ5LWE4ZTktNDFhODY0NzM3NTcx',
            'origin': 'https://admin.piaoquantv.com',
            'priority': 'u=1, i',
            'sec-ch-ua': '"Not/A)Brand";v="8", "Chromium";v="126", "Google Chrome";v="126"',
            'sec-ch-ua-mobile': '?0',
            'sec-ch-ua-platform': '"macOS"',
            'sec-fetch-dest': 'empty',
            'sec-fetch-mode': 'cors',
            'sec-fetch-site': 'same-origin',
            'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36'
        }
        response = requests.request(
            "POST",
            url,
            headers=headers,
            params=params
        )
        return response.json()


if __name__ == '__main__':
    AGOV = AutoGetOffVideos()
    vid_set = AGOV.get_long_articles_video_list()
    result = AGOV.find_video_play_count(vid_set)
    for i in result:
        print(i)