python - 如何将 Django 查询集转换为字典以用作模板上下文

Question

我正在 Django 中重新创建一份 gem 库存状态报告，该报告最初是在 Microsoft Access 中构建的。报告结构如下:

• 交易(“ABC” - 表示自有、委托(delegate)、合伙等)
  • 库存状态(库存、已售出、仅成本、历史 FYI 等)
    • 明细行(StoneID、克拉、采购成本等)
      • 小计(总成本、总克拉数 - 能够将其插入正确的位置是我遇到的问题...)

以下是模型的相关部分:

class Deal(models.Model):
    deal_name = models.TextField()

class Stone(models.Model):
    stoneid = models.TextField(verbose_name='StoneID', unique=True)
    dealid = models.ForeignKey(Deal, on_delete=models.PROTECT)
    ct_in = models.DecimalField(verbose_name='Carats', max_digits=7, decimal_places=3)
    cost_purchase = models.DecimalField(verbose_name='Purchase Cost', max_digits=14, decimal_places=2, null=True, blank=True)

我通过两个查询获取数据 - 一个用于明细行，另一个用于小计。以下是查询:

def dump_stone(request):
    query = Stone.objects.filter(Q(dealid_id__deal_name='ABC') | \
                                    Q(dealid_id__deal_name='DEF') | \
                                    Q(dealid_id__deal_name='GHI')).select_related().order_by('dealid_id__deal_name', 'inventory_status', 'stoneid')
    totals = Stone.objects.values('dealid', 'inventory_status').annotate(sum_by_deal=Sum('cost_purchase'), sum_ct_in_by_deal=Sum('ct_in'))

按状态、按交易打印库存明细表的模板是:

    {% block content %}
    REPORT:
    </br>
    {% regroup context by dealid as deal_list %}
        {% for dealid in deal_list %}
        {{dealid.grouper}}
            {% regroup dealid.list by inventory_status as stone_list%}
            {% for inventory_status in stone_list %}
                {{inventory_status.grouper}}
                <table>
                    <thead>
                        <tr>
                        <th>StoneID</th>
                        <th>Ct</th>
                        <th>Cost</th>
                        </tr>
                    </thead>
                    <tbody>
                        {% for stone in inventory_status.list %}
                        <tr>
                        <td>{{ stone.stoneid }}</td>
                        <td>{{ stone.ct_in|floatformat:2 }}</td> 
                        <td>{{ stone.cost_purchase|prepend_dollars }}</td>
                        </tr>
                        {% endfor %}
                    {% endfor %}
                </tbody>
                </table>
        {% endfor %}
    {% endblock content %}

总计查询产生以下输出:

    {'dealid': 1, 'inventory_status': 'HistoricFYI', 'sum_by_deal': Decimal('1287750'), 'sum_ct_in_by_deal': Decimal('15.1500000000000')}
    {'dealid': 1, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('209138.7100000'), 'sum_ct_in_by_deal': Decimal('327.810000000000')}
    {'dealid': 2, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('338726.99000000'), 'sum_ct_in_by_deal': Decimal('56.2000000000000')}
    {'dealid': 3, 'inventory_status': 'Inventory', 'sum_by_deal': Decimal('296754.5900000'), 'sum_ct_in_by_deal': Decimal('294.970000000000')}
    {'dealid': 3, 'inventory_status': 'Memo In', 'sum_by_deal': Decimal('192948.340000000'), 'sum_ct_in_by_deal': Decimal('9.47000000000000')}
    {'dealid': 3, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('154384.57000000'), 'sum_ct_in_by_deal': Decimal('88.1200000000000')}
    {'dealid': 5, 'inventory_status': 'Inventory', 'sum_by_deal': Decimal('187000'), 'sum_ct_in_by_deal': Decimal('26.75')}
    {'dealid': 5, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('20000'), 'sum_ct_in_by_deal': Decimal('2')}
    {'dealid': 5, 'inventory_status': 'Test', 'sum_by_deal': Decimal('13700'), 'sum_ct_in_by_deal': Decimal('19')}

我想做的是将总计查询转换为字典字典，以便我可以按交易、按状态访问各个小计，并将它们插入模板中的正确位置带有标签(不会如图所示进行硬编码，但接下来我会处理):

    {{deal_dict.1.Sold.sum_by_deal}}

我正在尝试生成一个如下所示的字典:

    {   1:
            {
                ‘HistoricFYI’:{’sum_by_deal': Decimal('1287750'), 'sum_ct_in_by_deal': Decimal('15.1500000000000’)},
                'Sold:{'sum_by_deal': Decimal('209138.7100000'), 'sum_ct_in_by_deal': Decimal('327.810000000000’)}
            },
        2:
            {
                ’Sold’:{‘sum_by_deal': Decimal('338726.99000000'), 'sum_ct_in_by_deal': Decimal('56.2000000000000’)},
            },
        3:
            {
                'Inventory’:{‘sum_by_deal': Decimal('296754.5900000'), 'sum_ct_in_by_deal': Decimal('294.970000000000’)},
                'Memo In’:{‘sum_by_deal': Decimal('192948.340000000'), 'sum_ct_in_by_deal': Decimal('9.47000000000000’)},
                'Sold’: {‘sum_by_deal': Decimal('154384.57000000'), 'sum_ct_in_by_deal': Decimal('88.1200000000000')}
            },
        5:  {
                'Inventory’:{‘sum_by_deal': Decimal('187000'), 'sum_ct_in_by_deal': Decimal('26.75’)},
                'Sold’:  {‘sum_by_deal': Decimal('20000'), 'sum_ct_in_by_deal': Decimal(‘2’)},
                'Test’:      {‘sum_by_deal': Decimal('13700'), 'sum_ct_in_by_deal': Decimal('19')}
            }
    }

我已经尝试了一些方法来获取总计查询集并将其放入嵌套字典中:

deal_dict = {}
status_dict = {}
numbers_dict = {}
for things in totals:
    print(things)
    numbers_dict['sum_by_deal']=things['sum_by_deal']
    numbers_dict['sum_ct_in_by_deal']=things['sum_ct_in_by_deal']
    status_dict[things['inventory_status']]=dict(numbers_dict)
    deal_dict[things['dealid']]=dict(status_dict)

上述代码的问题在于，每笔交易的嵌套字典都包含之前交易的状态，除非交易本身有自己的状态数据覆盖之前的数据。换句话说，例如，对于交易 2，我得到了

    {   2:
            {
                ‘HistoricFYI’:{’sum_by_deal': Decimal('1287750'), 'sum_ct_in_by_deal': Decimal('15.1500000000000’)},
                'Sold:{'sum_by_deal': Decimal('338726.99000000'), 'sum_ct_in_by_deal': Decimal('56.2000000000000’)}
            },

即使它自己没有任何“HistoricFYI”数据，因为字典仍然包含交易 1 数据。

我也试过像这样清除字典

    status_dict.clear()

在每个循环的末尾，但我最终得到的字典只有每笔交易(销售或测试)按字母顺序排列的最后状态。

我也试过

    deal_dict = {}
    for things in totals:
        deal_dict.update({things['dealid']:{things['inventory_status']:{'sum_by_deal': things['sum_by_deal'], 'sum_ct_in_by_deal': things['sum_ct_in_by_deal']}}})

但这在字典中只留下了每笔交易的最后状态，就像我尝试 clear() 方法时一样。

我不知道如何调整它 --> Totals/Subtotals in Django template或者这个 --> Django: how to process flat queryset to nested dictionary?

我怎样才能生成这个字典的字典，以便我可以将小计插入模板，或者以某种方式将小计放到正确的位置？如果有任何帮助，我将不胜感激!

Answer 1

这似乎实现了你想要的嵌套字典:

def regroup_inventory(totals_qset):
    for dealid, row_group in groupby(totals_qset, key=itemgetter('dealid')):
        yield dealid, {
            row['inventory_status']: {
                key: val
                for key, val in row.items()
                if key not in ('dealid', 'inventory_status')
            }
            for row in row_group
        }

注意:这是一个生成器，因此您需要像遍历 dict.items() 一样遍历它，或者对结果调用 dict()。在您的示例中尝试一下，我得到:

> from decimal import Decimal
> from pprint import pprint
> foo = [
    {'dealid': 1, 'inventory_status': 'HistoricFYI', 'sum_by_deal': Decimal('1287750'), 'sum_ct_in_by_deal': Decimal('15.1500000000000')},
    {'dealid': 1, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('209138.7100000'), 'sum_ct_in_by_deal': Decimal('327.810000000000')},
    {'dealid': 2, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('338726.99000000'), 'sum_ct_in_by_deal': Decimal('56.2000000000000')},
    {'dealid': 3, 'inventory_status': 'Inventory', 'sum_by_deal': Decimal('296754.5900000'), 'sum_ct_in_by_deal': Decimal('294.970000000000')},
    {'dealid': 3, 'inventory_status': 'Memo In', 'sum_by_deal': Decimal('192948.340000000'), 'sum_ct_in_by_deal': Decimal('9.47000000000000')},
    {'dealid': 3, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('154384.57000000'), 'sum_ct_in_by_deal': Decimal('88.1200000000000')},
    {'dealid': 5, 'inventory_status': 'Inventory', 'sum_by_deal': Decimal('187000'), 'sum_ct_in_by_deal': Decimal('26.75')},
    {'dealid': 5, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('20000'), 'sum_ct_in_by_deal': Decimal('2')},
    {'dealid': 5, 'inventory_status': 'Test', 'sum_by_deal': Decimal('13700'), 'sum_ct_in_by_deal': Decimal('19')},
]
> pprint(dict(regroup_inventory(foo)))
{1: {'HistoricFYI': {'sum_by_deal': Decimal('1287750'),
                     'sum_ct_in_by_deal': Decimal('15.1500000000000')},
     'Sold': {'sum_by_deal': Decimal('209138.7100000'),
              'sum_ct_in_by_deal': Decimal('327.810000000000')}},
 2: {'Sold': {'sum_by_deal': Decimal('338726.99000000'),
              'sum_ct_in_by_deal': Decimal('56.2000000000000')}},
 3: {'Inventory': {'sum_by_deal': Decimal('296754.5900000'),
                   'sum_ct_in_by_deal': Decimal('294.970000000000')},
     'Memo In': {'sum_by_deal': Decimal('192948.340000000'),
                 'sum_ct_in_by_deal': Decimal('9.47000000000000')},
     'Sold': {'sum_by_deal': Decimal('154384.57000000'),
              'sum_ct_in_by_deal': Decimal('88.1200000000000')}},
 5: {'Inventory': {'sum_by_deal': Decimal('187000'),
                   'sum_ct_in_by_deal': Decimal('26.75')},
     'Sold': {'sum_by_deal': Decimal('20000'),
              'sum_ct_in_by_deal': Decimal('2')},
     'Test': {'sum_by_deal': Decimal('13700'),
              'sum_ct_in_by_deal': Decimal('19')}}}