piaoquan_crawler/benshanzhufu/benshanzhufu_recommend/insert.py

# -*- coding: utf-8 -*-
# @Author: wangkun
# @Time: 2023/4/13
import json
import os
import sys
import time
from datetime import date, timedelta
sys.path.append(os.getcwd())
from common.common import Common
from common.feishu import Feishu
from common.scheduling_db import MysqlHelper


class Insert:
    @classmethod
    def get_config(cls, log_type, crawler, text, env):
        select_sql = f"""select * from crawler_config where source="benshanzhufu" """
        contents = MysqlHelper.get_values(log_type, crawler, select_sql, env, action='')
        title_list = []
        filter_list = []
        for content in contents:
            config = content['config']
            config_dict = eval(config)
            for k, v in config_dict.items():
                if k == "title":
                    title_list_config = v.split(",")
                    for title in title_list_config:
                        title_list.append(title)
                if k == "filter":
                    filter_list_config = v.split(",")
                    for filter_word in filter_list_config:
                        filter_list.append(filter_word)
        if text == "title":
            return title_list
        elif text == "filter":
            return filter_list

    @classmethod
    def before_day(cls):
        publish_time_str_rule = (date.today() + timedelta(days=-30)).strftime("%Y-%m-%d %H:%M:%S")
        publish_time_stamp_rule = int(time.mktime(time.strptime(publish_time_str_rule, "%Y-%m-%d %H:%M:%S")))
        print(publish_time_str_rule)
        print(publish_time_stamp_rule)

    @classmethod
    def insert_config(cls, log_type, crawler, env):
        filter_sheet = Feishu.get_values_batch(log_type, crawler, "DjXfqG")
        # title_sheet = Feishu.get_values_batch(log_type, crawler, "bHSW1p")
        filter_list = []
        # title_list = []
        for x in filter_sheet:
            for y in x:
                if y is None:
                    pass
                else:
                    filter_list.append(y)
        # for x in title_sheet:
        #     for y in x:
        #         if y is None:
        #             pass
        #         else:
        #             title_list.append(y)
        # str_title = ','.join(title_list)
        str_filter = ','.join(filter_list)
        config_dict = {
            # "title": str_title,
            "filter": str_filter
        }
        str_config_dict = str(config_dict)
        # print(f"config_dict:{config_dict}")
        # print(f"str_config_dict:{str_config_dict}")
        insert_sql = f""" insert into crawler_config(title, source, config) values("本山祝福小程序", "benshanzhufu", "{str_config_dict}") """
        MysqlHelper.update_values(log_type, crawler, insert_sql, env)

    @classmethod
    def insert_video_from_feishu_to_mysql(cls, log_type, crawler, env):
        benshanzhufu_sheetid = ['440018']
        for sheetid in benshanzhufu_sheetid:
            xiaoniangao_sheet = Feishu.get_values_batch(log_type, crawler, sheetid)
            for i in range(1, len(xiaoniangao_sheet)):
            # for i in range(1, 3):
                if xiaoniangao_sheet[i][5] is None or xiaoniangao_sheet[i][9] is None:
                    continue
                video_id = xiaoniangao_sheet[i][8].replace("https://admin.piaoquantv.com/cms/post-detail/", "").replace(
                    "/info", "")
                if video_id == "None":
                    continue
                video_id = int(video_id)
                out_user_id = str(xiaoniangao_sheet[i][17])
                platform = "本山祝福"
                strategy = "推荐榜爬虫策略"
                out_video_id = str(xiaoniangao_sheet[i][6])
                video_title = str(xiaoniangao_sheet[i][7])
                cover_url = str(xiaoniangao_sheet[i][19])
                video_url = str(xiaoniangao_sheet[i][20])
                duration = int(xiaoniangao_sheet[i][13])
                publish_time = str(xiaoniangao_sheet[i][15]).replace("/", "-")
                play_cnt = int(xiaoniangao_sheet[i][9])
                like_cnt = int(xiaoniangao_sheet[i][11])
                share_cnt = int(xiaoniangao_sheet[i][12])
                # collection_cnt = 0
                comment_cnt = int(xiaoniangao_sheet[i][10])
                user_id = str(xiaoniangao_sheet[i][17])
                crawler_rule = json.dumps({})
                width = int(xiaoniangao_sheet[i][14].split("*")[0])
                height = int(xiaoniangao_sheet[i][14].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"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)
                Common.logger(log_type, crawler).info(f"repeat_video:{repeat_video}")

                if repeat_video is not None and len(repeat_video) != 0:
                    Common.logger(log_type, crawler).info(f"{video_title} 已存在数据库中\n")
                else:
                    # 视频信息保存数据库
                    insert_sql = f""" insert into crawler_video(video_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},
                                        "{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, action='')
                    Common.logger(log_type, crawler).info('视频信息插入数据库成功！\n')



if __name__ == "__main__":
    # Insert.insert_config("insert", "benshanzhufu", "dev")
    # print(Insert.get_config("insert", "benshanzhufu", "filter", "dev"))
    Insert.insert_video_from_feishu_to_mysql("insert-prod", "benshanzhufu", "prod")
    pass