首页 > 解决方案 > 如何将 django 查询集转换为字典以用作模板上下文

问题描述

我正在 Django 中重新创建一个宝石库存状态报告,该报告最初是在 Microsoft Access 中构建的。报告组织如下:

以下是模型的相关部分:

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() 方法时一样。

我不知道如何调整这个 --> Django 模板中的 Totals/Subtotals或这个 --> Django: how to process flat queryset to nested dictionary?

我怎样才能产生这个字典词典,以便我可以将小计插入模板,或者以某种其他方式将小计放到正确的位置?我将不胜感激任何帮助!

标签: pythondjangodictionarydjango-queryset

解决方案


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

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')}}}

推荐阅读