ScheduledTasks/applications/mysql.py

"""
@author: luojunhui
"""
import pymysql


class MySQL(object):
    """
    MySQL 方法
    """

    @classmethod
    def get_pq_top_return_videos(cls, video_id):
        """
        获取票圈视频
        :return:
        """
        connection = pymysql.connect(
            host="rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com",  # 数据库IP地址，内网地址
            port=3306,  # 端口号
            user="wx2016_longvideo",  # mysql用户名
            passwd="wx2016_longvideoP@assword1234",  # mysql用户登录密码
            db="incentive",  # 数据库名
            charset="utf8mb4"  # 如果数据库里面的文本是utf8编码的，charset指定是utf8
        )
        sql = f"""select * from video_content where video_id = {video_id};"""
        cursor = connection.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        return data[0]

    @classmethod
    def migrate_data_to_mysql(cls, obj):
        """
        把 data_works 数据迁移到数据库
        :param obj:
        :return:
        """
        video_id = obj['videoid']
        title = obj['title']
        return_ = obj['当日回流']
        view_ = obj['曝光量']
        video_url = obj['视频原地址']
        dt = obj['dt']
        rov = int(return_) / int(view_) if int(view_) > 0 else 0
        insert_sql = f"""
            INSERT INTO top_return_daily
                (video_id, title, view_, return_, video_url, dt, rov)
            VALUES 
                ({video_id}, '{title}', {view_}, {return_}, '{video_url}', '{dt}', {rov});
        """
        # print(insert_sql)
        connection = pymysql.connect(
            host='rm-bp1159bu17li9hi94.mysql.rds.aliyuncs.com',
            port=3306,
            user='crawler',
            password='crawler123456@',
            db='piaoquan-crawler',
            charset='utf8mb4'
        )
        cursor = connection.cursor()
        cursor.execute(insert_sql)
        connection.commit()