如何在Django中计算数据库每日数据总和并在页面表格列下展示？

我有一个用于每日记录数值的Day模型，希望每次添加数据后，自动计算各字段的总和并展示在表格对应列的下方。例如部分日期lift_frame处于使用状态（值非0），月末需要统计它投入使用的总天数。求解决方法，或者指引我了解Django中数值求和与展示的相关实现方式。

当前代码 #

models.py #

class Day(models.Model):
    day = models.DateField(auto_now=False)
    well_name = models.ForeignKey("Well", on_delete=models.SET_NULL, null=True)
    lift_frame = models.IntegerField(default=0)
    mpd_manifold_building = models.IntegerField(default=0)
    rcd_housing = models.IntegerField(default=0)
    pipework = models.IntegerField(default=0)
    mpd_supervisor = models.IntegerField(default=2)
    mpd_operator  = models.IntegerField(default=2)

    def __str__(self):
        return self.day.strftime("%d %B")

views.py #

class Dayboard(LoginRequiredMixin, View):
    def get(self, request):
        days = Day.objects.all().order_by('day')
        return render(request, 'days/dayboard.html', {'days': days})

dayboard.html #

{% extends 'base/base.html' %}
{% block content %}
<div class="container">
  <div class="d-flex justify-content-end m-4">
    {% if user.is_superuser %}
    <a href="{% url 'add-day' %}" class="btn btn-outline-info rounded">+</a>
    {% endif %}
  </div>
<table class="table table-hover">
    <thead>
      <tr>
        <th scope="col">Date</th>
        <th scope="col">Well</th>
        <th scope="col">MPD</th>
        <th scope="col">RCD</th>
        <th scope="col">Pipe</th>
        <th scope="col">LF</th>
        <th scope="col">Sup</th>
        <th scope="col">Op</th>
        <th scope="col"></th>
      </tr>
    </thead>
    <tbody>
        {% for day in days %}
      <tr>
        <th scope="row">{{ day.day}} </th>
        <th>{{ day.well_name}} </th>
        <td>{{ day.mpd_manifold_building }}</td>
        <td>{{ day.rcd_housing }}</td>
        <td>{{ day.pipework }}</td>
        <td>{{ day.lift_frame }}</td>
        <td>{{ day.mpd_supervisor }}</td>
        <td>{{ day.mpd_operator }}</td>
        <td><a class="nav-link text-info" href="{% url 'edit-day' day.id %}">Update</a></td> 
      </tr>
      
      {% endfor %} 
     
    </tbody>
  </table>
</div>
{% endblock content %}

main.py #

import sqlite3
import pandas as pd

cnx = sqlite3.connect('db.sqlite3')

df = pd.read_sql_query("SELECT * FROM project_day", cnx)

lift_frame_sum = df['lift_frame'].sum()
mpd_manifold_building_sum = df['mpd_manifold_building'].sum()
rcd_housing_sum = df['pipework'].sum()
pipework_sum = df['mpd_supervisor'].sum()
mpd_supervisor_sum = df['mpd_operator'].sum()
mpd_operator_sum = df['rcd_housing'].sum()

cnx.commit()
cnx.close()

解决方案 #

方法一：使用Django聚合查询（推荐） #

这种方式可以实时自动计算总和，无需手动运行额外脚本，步骤如下：

1. 修改views.py，添加聚合计算

from django.db.models import Sum

class Dayboard(LoginRequiredMixin, View):
    def get(self, request):
        days = Day.objects.all().order_by('day')
        # 计算各字段总和及lift_frame使用天数
        totals = Day.objects.aggregate(
            lift_frame_total=Sum('lift_frame'),
            mpd_manifold_total=Sum('mpd_manifold_building'),
            rcd_housing_total=Sum('rcd_housing'),
            pipework_total=Sum('pipework'),
            mpd_supervisor_total=Sum('mpd_supervisor'),
            mpd_operator_total=Sum('mpd_operator'),
            lift_frame_used_days=Day.objects.filter(lift_frame__gt=0).count()
        )
        # 处理无数据时的None值
        for key, value in totals.items():
            totals[key] = value or 0
        return render(request, 'days/dayboard.html', {'days': days, 'totals': totals})

2. 修改dayboard.html，添加总计行
   在</tbody>标签后添加表格脚注，展示总计数据：

<tfoot>
      <tr class="table-info">
        <th scope="row">总计</th>
        <th></th>
        <td>{{ totals.mpd_manifold_total }}</td>
        <td>{{ totals.rcd_housing_total }}</td>
        <td>{{ totals.pipework_total }}</td>
        <td>
          {{ totals.lift_frame_total }}<br>
          <small>使用天数: {{ totals.lift_frame_used_days }}</small>
        </td>
        <td>{{ totals.mpd_supervisor_total }}</td>
        <td>{{ totals.mpd_operator_total }}</td>
        <td></td>
      </tr>
    </tfoot>

方法二：修正main.py脚本（手动运行方式） #

如果坚持使用pandas脚本，首先修正字段对应错误：

import sqlite3
import pandas as pd

cnx = sqlite3.connect('db.sqlite3')

df = pd.read_sql_query("SELECT * FROM project_day", cnx)

# 修正字段对应关系
lift_frame_sum = df['lift_frame'].sum()
mpd_manifold_building_sum = df['mpd_manifold_building'].sum()
rcd_housing_sum = df['rcd_housing'].sum()
pipework_sum = df['pipework'].sum()
mpd_supervisor_sum = df['mpd_supervisor'].sum()
mpd_operator_sum = df['mpd_operator'].sum()
# 统计lift_frame使用天数
lift_frame_used_days = df[df['lift_frame'] > 0].shape[0]

# 打印结果（如需存入数据库或展示需额外处理）
print(f"Lift Frame总和: {lift_frame_sum}, 使用天数: {lift_frame_used_days}")

cnx.close()

但这种方式需要手动触发脚本，无法实时更新页面展示，不如Django聚合查询便捷。

内容的提问来源于stack exchange，提问作者LoudEye