piaoquan_crawler/analysis/analysis.py

import json
import pymysql
import pyecharts.options as opts
from pyecharts.charts import Line


class Analysis(object):
    def __init__(self):
        self.platform_list = ["xiaoniangao", "gongzhonghao", "shipinhao", "douyin", "kuaishou", "fuqiwang",
                              "haitunzhufu", "haokanshipin", "benshanzhufu", "zhongmiaoyinxin"]
        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()


class Drawer(object):
    def __init__(self, json_obj):
        self.ori_data = json_obj
        self.x_list = ["2023-11-{:02}".format(i) for i in range(1, 22)]

    def draw_line(self):
        line = Line()
        line.add_xaxis(xaxis_data=self.x_list)
        for key in self.ori_data:
            # print(key, self.ori_data[key])
            each_obj = self.ori_data[key]
            line.add_yaxis(
                series_name=key,
                stack="Total",
                y_axis=[each_obj.get(i, 0) for i in self.x_list],
                label_opts=opts.LabelOpts(is_show=False),
                is_smooth=True
            )
        line.set_global_opts(
            title_opts=opts.TitleOpts(title="入库量折线图", item_gap=10, padding=10, pos_top="10"),
            legend_opts=opts.LegendOpts(pos_right=True),
            tooltip_opts=opts.TooltipOpts(trigger="axis", padding=100),
            yaxis_opts=opts.AxisOpts(
                type_="value",
                axistick_opts=opts.AxisTickOpts(is_show=True),
                splitline_opts=opts.SplitLineOpts(is_show=True),
                offset=10
            ),
            xaxis_opts=opts.AxisOpts(type_="category", boundary_gap=True, offset=10),

        )
        line.render("test.html")


if __name__ == '__main__':
    A = Analysis()
    A.analysis_videos()
    result_data = A.out_put
    D = Drawer(result_data)
    D.draw_line()