Flask Sqlarchemy实现按日、周、月统计并图表展示

def test():
    """统计用户同步分布情况"""
    from pyecharts.charts import Bar, Line, Pie
    from pyecharts import options as opts
    from pyecharts.globals import ThemeType
    from sqlalchemy.sql import func, extract
    from modules.database.models import ComputerTarget
    # 根据最后同步时间计算每一天的活跃量（2020-05-15是服务给数据的时间加一天）
    target_date = datetime.strptime('2020-05-15', '%Y-%m-%d')
    with session_maker() as session:
        stats_query = session.query(
            func.date_format(ComputerTarget.LastSyncTime, '%Y-%m-%d').label('date'),
            func.count(ComputerTarget.TargetID).label('count')
        ).filter(ComputerTarget.LastSyncTime <= target_date, ComputerTarget.OSVersion == 'CMGE V0-G').order_by('date').group_by('date').all()
        date_list, count_list = [], []
        for item in stats_query:
            date_list.append(item.date)
            count_list.append(item.count)
        # 计算机日活柱状图
        bar1 = (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis(date_list)
            .add_yaxis("", count_list)
            # .set_global_opts(title_opts=opts.TitleOpts(title="计算机日活跃度统计"))
        )
        bar1.render("mycharts.html")
        # 计算机日活折线图
        line1 = (
            Line(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis(date_list)
            .add_yaxis("", count_list)
            # .set_global_opts(title_opts=opts.TitleOpts(title="计算机日活跃度统计"))
        )
        line1.render("mycharts2.html")
        # 计算机周活柱状图
        week_stats = ComputerTarget.query.with_entities(
            func.count(ComputerTarget.TargetID).label('count'),
            extract('week', ComputerTarget.LastSyncTime).label('week')
        ).filter(ComputerTarget.LastSyncTime <= target_date).order_by('week').group_by('week').all()
        week_list, week_count_list = [], []
        for item in week_stats:
            week_list.append(item.week)
            week_count_list.append(item.count)
        bar2 = (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis(week_list)
            .add_yaxis("周活跃数", week_count_list)
            .set_global_opts(title_opts=opts.TitleOpts(title="计算机周活跃度统计"), tooltip_opts=opts.ToolboxOpts(is_show=True))
        )
        bar2.render("mycharts6.html")
        # 计算机月活饼图
        month_stats = session.query(
            func.date_format(ComputerTarget.LastSyncTime, '%Y年%m月').label('month'),
            func.count(ComputerTarget.TargetID).label('count')
        ).filter(ComputerTarget.LastSyncTime <= target_date).order_by('month').group_by('month').all()
        pie1 = (
            Pie(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
        )
        pie1.add(series_name='', data_pair=list(month_stats))
        pie1.render("mycharts4.html")
        # 计算机月活柱状图
        month_list, month_count_list = [], []
        for item in month_stats:
            month_list.append(item.month)
            month_count_list.append(item.count)
        bar3 = (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis(month_list)
            .add_yaxis("月活跃数", month_count_list)
            .set_global_opts(title_opts=opts.TitleOpts(title="计算机月活跃度统计"))
        )
        bar3.render("mycharts5.html")
        # OS饼图统计
        os_stats_query = session.query(
            ComputerTarget.OSVersion.label('os'),
            func.count(ComputerTarget.TargetID).label('count')
        ).filter(ComputerTarget.LastSyncTime < target_date).group_by('os').all()
        pie2 = (
            Pie(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .set_global_opts(title_opts=opts.TitleOpts(title='OS饼图统计'), legend_opts=opts.LegendOpts(orient='vertical', pos_left='0%', pos_top='50%'))
            .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}", position='inside'))
        )
        pie2.add(series_name='', data_pair=list(os_stats_query), center=['50%', '60%'])
        pie2.render("mycharts3.html")
        # OS折线图统计
        os_list, os_count_list = [], []
        for item in os_stats_query:
            os_list.append(item.os)
            os_count_list.append(item.count)
        (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis(os_list)
            .add_yaxis("OS数量统计", os_count_list)
            .set_global_opts(title_opts=opts.TitleOpts(title="OS数量统计"))
            .render("mycharts7.html")
        )
    return http_response()