LongArticlesJob/applications/utils/save_to_db.py

"""
@author: luojunhui
"""

import traceback
from applications.aliyunLogApi import log


def insert_into_single_video_source_table(db_client, video_item):
    """
    insert video into single video source table
    """
    insert_sql = f"""
        INSERT INTO publish_single_video_source
        (content_trace_id, article_title, out_account_id, out_account_name, read_cnt, like_cnt, article_url, cover_url, video_oss_path, publish_timestamp, crawler_timestamp, url_unique_md5, category, tags, platform, source_account)
        values
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
    """
    try:
        db_client.save(
            query=insert_sql,
            params=(
                video_item["content_trace_id"],
                video_item["article_title"],
                video_item["out_account_id"],
                video_item["out_account_name"],
                video_item["read_cnt"],
                video_item["like_cnt"],
                video_item["article_url"],
                video_item["cover_url"],
                video_item["video_oss_path"],
                video_item["publish_timestamp"],
                video_item["crawler_timestamp"],
                video_item["url_unique_md5"],
                video_item["category"],
                video_item["tags"],
                video_item["platform"],
                video_item["source_account"],
            ),
        )
    except Exception as e:
        log(
            task="{}_video_crawler".format(video_item["platform"]),
            function="save_each_video",
            message="save video failed",
            data={
                "error": str(e),
                "traceback": traceback.format_exc(),
                "video_id": video_item["url_unique_md5"],
                "oss_path": video_item["video_oss_path"],
            },
        )

def insert_into_video_meta_accounts_table(db_client, account_item):
    """
    insert account into account meta table
    """
    insert_sql = f"""
        insert into video_meta_accounts
            (platform, account_id, account_name, max_cursor, account_init_date, status, priority)
        values
            (%s, %s, %s, %s, %s, %s, %s);
    """
    try:
        db_client.save(
            query=insert_sql,
            params=(
                account_item["platform"],
                account_item["account_id"],
                account_item["account_name"],
                account_item["max_cursor"],
                account_item["account_init_date"],
                account_item["status"],
                account_item["priority"],
            ),
        )
    except Exception as e:
        log(
            task="{}_account_crawler".format(account_item["platform"]),
            function="save_each_account",
            message="save account failed",
            data={
                "error": str(e),
                "traceback": traceback.format_exc(),
                "account_id": account_item["account_id"],
            },
        )

def insert_into_associated_recommendation_table(db_client, associated_recommendation_item):
    """
    insert recommendation into recommendation table
    """
    # check whether duplicate video
    fetch_query = f"""
        select id from video_association
        where account_id = %s and platform = %s and recommend_video_id = %s;
    """
    duplicate_id = db_client.fetch(
        query=fetch_query, params=(
            associated_recommendation_item["account_id"],
            associated_recommendation_item["platform"],
            associated_recommendation_item["recommend_video_id"]
        )
    )
    if duplicate_id:
        return

    # insert into table
    insert_query = f"""
        insert into video_association
            (account_name, account_id, recommend_video_id, title, read_cnt, duration, seed_account, seed_title, recommend_date, platform)
            values
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
    """
    try:
        db_client.save(
            query=insert_query,
            params=(
                associated_recommendation_item["account_name"],
                associated_recommendation_item["account_id"],
                associated_recommendation_item["recommend_video_id"],
                associated_recommendation_item["title"],
                associated_recommendation_item["read_cnt"],
                associated_recommendation_item["duration"],
                associated_recommendation_item["seed_account"],
                associated_recommendation_item["seed_title"],
                associated_recommendation_item["recommend_date"],
                associated_recommendation_item["platform"]
            )
        )
    except Exception as e:
        log(
            task="{}_recommendation_crawler".format(associated_recommendation_item["platform"]),
            function="save_each_recommendation",
            message="save recommendation failed",
            data={
                "error": str(e),
                "traceback": traceback.format_exc(),
                "item": associated_recommendation_item
            }
        )