python - 如何将 django 查询集转换为字典以用作模板上下文
问题描述
我正在 Django 中重新创建一个宝石库存状态报告,该报告最初是在 Microsoft Access 中构建的。报告组织如下:
- 交易(“ABC” - 表示自有、委托、合伙等)
- 库存状态(库存、已售、仅成本、历史 FYI 等)
- 详细信息行(StoneID、克拉、采购成本等)
- 小计(总成本,总克拉 -能够将其插入正确的位置是我卡住的地方......)
- 详细信息行(StoneID、克拉、采购成本等)
- 库存状态(库存、已售、仅成本、历史 FYI 等)
以下是模型的相关部分:
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?
我怎样才能产生这个字典词典,以便我可以将小计插入模板,或者以某种其他方式将小计放到正确的位置?我将不胜感激任何帮助!
解决方案
这似乎实现了你想要的嵌套字典:
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')}}}
推荐阅读
- php - PHP表单找不到空字段
- android - Android WebView 中的 Botframework Speak 功能
- shell - awk 不打印新行
- java - 将两个数据库 DATA 与 liquibase 进行比较
- julia - 如何在 MacOs 上更新到 Julia 0.7 而无需再次安装包
- apache - 迁移到 PHP7 后的错误
- node.js - tslint exclude 不适用于 tsoa 路线
- laravel - 无法在 PHP 脚本迭代、Laravel、Postgres 中保存 UUID 字段
- visual-c++ - if condition with _bstr_t variable
- angular - 如何在 Angular 2+ 中使用 ngmodel 为输入设置一个值