piaoquan_crawler/kuaishou/kuaishou_follow/insert_videos.py

# -*- coding: utf-8 -*-
# @Author: wangkun
# @Time: 2023/2/27
import json
import os
import sys

sys.path.append(os.getcwd())
from common.common import Common
from common.db import MysqlHelper
from common.feishu import Feishu


class Insert:
    @classmethod
    def insert_video_from_feishu_to_mysql(cls, log_type, crawler, env, machine):
        kuaishou_sheetid_list = ["fYdA8F", "3cd128", "31kOdu"]
        for sheetid in kuaishou_sheetid_list:
            kuaishou_sheet = Feishu.get_values_batch(log_type, crawler, sheetid)
            # Common.logger(log_type, crawler).info(f"kuaishou_sheet:{kuaishou_sheet}")
            for i in range(1, len(kuaishou_sheet)):
            # for i in range(1, 3):
                if kuaishou_sheet[i][5] is None:
                    continue
                if kuaishou_sheet[i][9] is None:
                    video_id = 0
                else:
                    video_id = kuaishou_sheet[i][9].replace("https://admin.piaoquantv.com/cms/post-detail/", "").replace("/info", "")
                if video_id == "None":
                    continue
                video_id = int(video_id)
                user_id = 0
                out_user_id = str(kuaishou_sheet[i][18])
                platform = "快手"
                strategy = "定向爬虫策略"
                out_video_id = str(kuaishou_sheet[i][7])
                video_title = str(kuaishou_sheet[i][8])
                cover_url = str(kuaishou_sheet[i][20])
                video_url = str(kuaishou_sheet[i][21])
                duration = int(kuaishou_sheet[i][14])
                publish_time = str(kuaishou_sheet[i][16].replace("/", "-"))
                play_cnt = int(kuaishou_sheet[i][10])
                like_cnt = int(kuaishou_sheet[i][12])
                share_cnt = int(kuaishou_sheet[i][13])
                # collection_cnt = 0
                comment_cnt = int(kuaishou_sheet[i][11])
                crawler_rule = json.dumps({"play_cnt": 5000, "comment_cnt": 0, "like_cnt": 5000, "share_cnt": 1000, "duration": 40, "publish_time": 7, "video_width": 0, "video_height": 0})
                width = int(kuaishou_sheet[i][15].split("*")[0])
                height = int(kuaishou_sheet[i][15].split("*")[1])

                # print(f"video_id:{video_id}, type:{type(video_id)}")
                # print(f"user_id:{user_id}, type:{type(user_id)}")
                # print(f"out_user_id:{out_user_id}, type:{type(out_user_id)}")
                # print(f"platform:{platform}, type:{type(platform)}")
                # print(f"strategy:{strategy}, type:{type(strategy)}")
                # print(f"out_video_id:{out_video_id}, type:{type(out_video_id)}")
                # print(f"video_title:{video_title}, type:{type(video_title)}")
                # print(f"cover_url:{cover_url}, type:{type(cover_url)}")
                # print(f"video_url:{video_url}, type:{type(video_url)}")
                # print(f"duration:{duration}, type:{type(duration)}")
                # print(f"publish_time:{publish_time}, type:{type(publish_time)}")
                # print(f"play_cnt:{play_cnt}, type:{type(play_cnt)}")
                # print(f"like_cnt:{like_cnt}, type:{type(like_cnt)}")
                # print(f"share_cnt:{share_cnt}, type:{type(share_cnt)}")
                # print(f"collection_cnt:{collection_cnt}, type:{type(collection_cnt)}")
                # print(f"comment_cnt:{comment_cnt}, type:{type(comment_cnt)}")
                # print(f"crawler_rule:{crawler_rule}, type:{type(crawler_rule)}")
                # print(f"width:{width}, type:{type(width)}")
                # print(f"height:{height}, type:{type(height)}\n")

                select_sql = f""" select * from crawler_video where platform="{platform}" and out_video_id="{out_video_id}" """
                # Common.logger(log_type, crawler).info(f"select_sql:{select_sql}")
                repeat_video = MysqlHelper.get_values(log_type, crawler, select_sql, env, machine)
                Common.logger(log_type, crawler).info(f"repeat_video:{repeat_video}")

                repeat_video_id_sql = f""" select * from crawler_video where out_video_id="{out_video_id}" """
                repeat_video_id = MysqlHelper.get_values(log_type, crawler, repeat_video_id_sql, env, machine)
                Common.logger(log_type, crawler).info(f"repeat_video_id:{repeat_video_id}")

                if repeat_video is not None and len(repeat_video) != 0:
                    Common.logger(log_type, crawler).info(f"{video_title} 已存在数据库中\n")
                elif repeat_video_id is not None and len(repeat_video_id) != 0:
                    Common.logger(log_type, crawler).info(f"开始更新视频信息\n")
                    update_sql = f""" UPDATE crawler_video SET
                                    user_id={user_id},
                                    out_user_id="{out_user_id}",
                                    platform="{platform}",
                                    strategy="{strategy}",
                                    out_video_id="{out_video_id}",
                                    video_title="{video_title}",
                                    cover_url="{cover_url}",
                                    video_url="{video_url}",
                                    duration={duration},
                                    publish_time="{publish_time}",
                                    play_cnt={play_cnt},
                                    like_cnt={like_cnt},
                                    share_cnt={share_cnt},
                                    comment_cnt={comment_cnt},
                                    crawler_rule='{crawler_rule}',
                                    width={width},
                                    height={height}
                                    WHERE video_id={video_id}
                                    """
                    Common.logger(log_type, crawler).info(f"update_sql:{update_sql}")
                    MysqlHelper.update_values(log_type, crawler, update_sql, env, machine)
                    Common.logger(log_type, crawler).info('视频信息更新成功！\n')
                else:
                    # 视频信息保存数据库
                    insert_sql = f""" insert into crawler_video(video_id,
                                    user_id,
                                    out_user_id,
                                    platform,
                                    strategy,
                                    out_video_id,
                                    video_title,
                                    cover_url,
                                    video_url,
                                    duration,
                                    publish_time,
                                    play_cnt,
                                    like_cnt,
                                    share_cnt,
                                    comment_cnt,
                                    crawler_rule,
                                    width,
                                    height)
                                    values({video_id},
                                    {user_id},
                                    "{out_user_id}",
                                    "{platform}",
                                    "{strategy}",
                                    "{out_video_id}",
                                    "{video_title}",
                                    "{cover_url}",
                                    "{video_url}",
                                    {duration},
                                    "{publish_time}",
                                    {play_cnt},
                                    {like_cnt},
                                    {share_cnt},
                                    {comment_cnt},
                                    '{crawler_rule}',
                                    {width},
                                    {height}) """
                    Common.logger(log_type, crawler).info(f"insert_sql:{insert_sql}")
                    MysqlHelper.update_values(log_type, crawler, insert_sql, env, machine)
                    Common.logger(log_type, crawler).info('视频信息插入数据库成功！\n')

    @classmethod
    def get_sheet(cls):
        sheet = Feishu.get_values_batch("insert", "kuaishou", "fYdA8F")
        print(sheet)


if __name__ == "__main__":
    Insert.insert_video_from_feishu_to_mysql("insert-prod", "kuaishou", "prod", "local")
    # Insert.get_sheet()
    pass