video_rewriting/common/sql_help.py

import os
import sys
from datetime import datetime, timedelta



sys.path.append(os.getcwd())
from datetime import datetime
from common import MysqlHelper


class sqlCollect():
    """
    视频信息写入库中
    """
    @classmethod
    def insert_task(cls, task_mark, video_id, mark, channel):
        current_time = datetime.now()
        formatted_time = current_time.strftime("%Y-%m-%d %H:%M:%S")
        insert_sql = f"""INSERT INTO pj_video_data (task_name, used_video_id, mark_name, data_time, channel) values ('{task_mark}' ,'{video_id}','{mark}', '{formatted_time}', '{channel}')"""
        MysqlHelper.update_values(
            sql=insert_sql
        )

    """
    判断该任务id是否用过
    """
    @classmethod
    def is_used(cls, task_mark, video_id, mark_name, channel):
        sql = """
            SELECT used_video_id
            FROM pj_video_data
            WHERE used_video_id = %s AND task_name = %s AND mark_name = %s AND channel = %s 
            ORDER BY data_time DESC
            LIMIT 1
        """
        data = MysqlHelper.get_values(sql, (str(video_id), task_mark, mark_name, channel))
        if len(data) == 0 or data == ():
            return True
        return False

    @classmethod
    def get_history_id(cls, channel, url):
        """
        从数据库表中读取 id
        """
        sql = f"""select name_id from accounts where name = %s and platform = %s and useful = 1 limit 1"""
        data = MysqlHelper.get_values(sql, (url,  channel))
        if data:
            return data[0][0]
        else:
            return False

    @classmethod
    def insert_history_id(cls, account_name, target, channel):
        insert_sql = f"""INSERT INTO accounts (name, name_id, platform, useful) values ("{account_name}", "{target}", "{channel}", 1 )"""
        MysqlHelper.update_values(
            sql=insert_sql
        )


    @classmethod
    def insert_machine_making_data(cls, name: str, task_mark: str, channel_id: str, url: str, v_id: str, piaoquan_id: str, new_title: str, code: str, formatted_time):
        insert_sql = f"""INSERT INTO machine_making_data (name, task_mark, channel, user, v_id, pq_uid, title, pq_vid, data_time) values ("{name}", "{task_mark}", "{channel_id}", "{url}", "{v_id}" , "{piaoquan_id}", "{new_title}", "{code}", "{formatted_time}")"""
        MysqlHelper.update_values(
            sql=insert_sql
        )