Queryset to build "pivot table" for the dataset using Django ORM?

Question

I have CurrencyHistory model along with the database table which is populated on every Currency model update for the historical data.

class CurrencyHistory(models.Model):
    id = models.AutoField(primary_key=True)
    change_rate_date = models.DateTimeField(_("Change Rate Date"), 
                                        auto_now=False, auto_now_add=True, 
                                        db_column='change_rate_date')

    code = models.ForeignKey("core.Currency", verbose_name=_("Code"), 
                         on_delete=models.CASCADE,
                         related_name='history',
                         db_column='code')

    to_usd_rate = models.DecimalField(_("To USD Rate"), 
                                  max_digits=20, 
                                  decimal_places=6, 
                                  null=True, 
                                  db_column='to_usd_rate')

Database structure looks like

id | change_rate_date | code | to_usd_rate
 1 | 2021-01-01       | EUR  | 0.123456
 2 | 2021-01-01       | CAD  | 0.987654
 3 | 2021-01-02       | EUR  | 0.123459
 4 | 2021-01-02       | CAD  | 0.987651

I need to fetch data using Djnago ORM to have a dictionary to display single row per date with the every currency as columns, like this

Date	EUR	CAD
2021-01-01	0.123456	0.987654
2021-01-02	0.123459	0.987651

But I have no idea how to correctly do it using Django ORM to make it fast.

I suppose for loop over the all unique database dates to get dict for each data will work in this case but it looks very slow solution that will generate thousands of requests.

enjoi4life411 · Answer 1 · 2022-09-03T15:14:09.147

You want to use serailizers to turn a model instance into a dictionary. Even if your not using a RESTFUL api, serailizers are the best way to get show dictionaries.

All types of ways to serialize data Convert Django Model object to dict with all of the fields intact

For a quick summary of my top favorite methods..

Method 1.

Model to dict

from django.forms import model_to_dict

instance = CurrencyHistory(...)
dict_instance = model_to_dict(instance)

Method 2:

Serailizers (this will show the most detail)

of course this implies that you'll need to install DRF

pip install rest_framework

Serializers.py

# import your model
from rest_framework import serializers


class CurrencySerialzier(serializers.ModelSerializer):
    code = serializers.StringRelatedField()  # returns the string repr of the model inst
    class Meta:
        model = CurrencyHistory
        fields = "__all__"

views.py

from .serializers import CurrencySerializer
...inside your view

currency_inst = CurrencyHistory.objects.get()
serializer = CurrencySerializer(currency_inst)
serializer.data # this returns a mapping of the instance (dictionary)

# here is where you either return a context object and template, or return a DRF Response object

Did you mean `from django.forms.models import model_to_dict`? — Gathide, Sep 03 '22 at 04:08

score 0 · Answer 2 · answered Sep 21 '22 at 14:33

You can use the django-pivot module. After you pip install django-pivot:

from django_pivot.pivot import pivot

pivot_table_dictionary = pivot(CurrencyHistory,
                               'change_rate_date',
                               'code',
                               'to_usd_rate')

The default aggregation is Sum which will work fine if you only have one entry per date per currency. If the same currency shows up multiple times on a single date, you'll need to choose what number you want to display. The average of to_usd_rate? The max? You can pass the aggregation function to the pivot call.

Alternatively, put unique_together = [('change_rate_date', 'code')] in the Meta class of your model to ensure there really is only one value for each date, code pair.

Queryset to build "pivot table" for the dataset using Django ORM?

2 Answers2