piaoquan_crawler/analysis/analysis.py

import pandas as pd
import json
import pymysql


class Analysis(object):
    def __init__(self):
        self.platform_list = ["xiaoniangao", "gongzhonghao", "shipinhao", "douyin", "kuaishou", "fuqiwang", "haitunzhufu"]
        self.date_last = "2023-11-01"
        self.out_put = {}

    def analysis_videos(self):
        connection = pymysql.connect(
            host="rm-bp1159bu17li9hi94ro.mysql.rds.aliyuncs.com",  # 数据库IP地址，内网地址
            port=3306,  # 端口号
            user="crawler",  # mysql用户名
            passwd="crawler123456@",  # mysql用户登录密码
            db="piaoquan-crawler",  # 数据库名
            # 如果数据库里面的文本是utf8编码的，charset指定是utf8
            charset="utf8")
        for platform in self.platform_list:
            select_sql = f"""SELECT DATE(create_time) as DATE, count(1) as Total
                    FROM crawler_video 
                    WHERE `platform`  = "{platform}" and create_time > "{self.date_last}"
                    GROUP BY DATE( `create_time` )
                    ORDER BY DATE( `create_time`)  DESC; """
            out_dict = {}
            mysql = connection.cursor()
            mysql.execute(select_sql)
            data_lines = mysql.fetchall()
            for i in data_lines:
                date_info = i[0].strftime('%Y-%m-%d')
                count = i[1]
                out_dict[date_info] = count
            self.out_put[platform] = out_dict
        connection.close()


if __name__ == '__main__':
    A = Analysis()
    A.analysis_videos()
    print(json.dumps(A.out_put, ensure_ascii=False, indent=4))