Django Rest API – Part 8 – Models Depth

Till now we are seen basics of models, views, serializes everything. But the most important aspect of any project is the database operations.

In this blog post we will some db operations which were used a in a live project and get more in-depth knowledge of things.

Basics

In this blog post we will work 3 entities or db tables. Our project is related to a mutual fund platform i.e to analyze data of mutual funds. At basic level what we need is to setup this

AMC = This is fund house (e.g Aditya birla mutual fund, ICICI mutual fund)

Scheme = This is the actual mutual fund scheme which the AMC sells e.g ELSS fund, Equity fund, Debt fund etc

NAV = This is the daily “nav” value published by fund houses for every scheme.

So relationship is such that

AMC has many schemes

Scheme has many navs

Relationships

First things is relationship between model’s. In most cases we have one-to-many relationship which is done simply via “foreign key”. Our models look like this

class AMC(models.Model):
    name = models.CharField(max_length=255, unique=True)

class Scheme(models.Model):
    amc = models.ForeignKey(
        'AMC',
        on_delete=models.CASCADE,
    )
    scheme_category = models.CharField(max_length=255, null=False)
    scheme_type = models.CharField(max_length=255, null=False)
    scheme_sub_type = models.CharField(max_length=255, null=False)
    fund_code = models.CharField(max_length=255, null=False, unique=True)
    fund_name = models.CharField(max_length=255, null=False)
    fund_option = models.CharField(max_length=255, null=False)
    fund_type = models.CharField(max_length=255, null=False)

class Nav(models.Model):
    scheme = models.ForeignKey(
        'Scheme',
        on_delete=models.CASCADE,
    )
    nav = models.FloatField(null=False)
    date = models.DateField(null=False)

This is quite simple and should be clear. Other types of relationship can be read about here https://docs.djangoproject.com/en/2.2/topics/db/examples/

Indexes

Next, most of our tables need index to optimize data access. In our case we need unique indexes. Basically in the scheme table “fund_code” should be unique. And similar in nav table “nav” for every date should be unique. So updated code looks like this

class AMC(models.Model):
    name = models.CharField(max_length=255, unique=True)

class Scheme(models.Model):
    amc = models.ForeignKey(
        'AMC',
        on_delete=models.CASCADE,
    )
    scheme_category = models.CharField(max_length=255, null=False)
    scheme_type = models.CharField(max_length=255, null=False)
    scheme_sub_type = models.CharField(max_length=255, null=False)
    fund_code = models.CharField(max_length=255, null=False, unique=True)
    fund_name = models.CharField(max_length=255, null=False)
    fund_option = models.CharField(max_length=255, null=False)
    fund_type = models.CharField(max_length=255, null=False)

    class Meta:
        unique_together = ("amc", "fund_code")


class Nav(models.Model):
    scheme = models.ForeignKey(
        'Scheme',
        on_delete=models.CASCADE,
    )
    nav = models.FloatField(null=False)
    date = models.DateField(null=False)

    class Meta:
        unique_together = ("scheme", "date")

simple index can also be done without unique see here https://docs.djangoproject.com/en/2.2/ref/models/options/#index-together

GET Model Data

try:
    nav = Nav.objects.get(
        date=date_time_obj, scheme=scheme)
except Nav.DoesNotExist:
    pass

Get will fetch a single data i.e row based on where condition you define. This will throw exception if object doesn’t exist. So it’s always better to enclose it in try/expect

Filter Date

Filter is used to get for selecting multiple rows of data i.e select * or which is called as “query set” in django

# filter and order by and get the first data
mfdownload = MFDownload.objects.filter(
            amc_id=amc_id).order_by('end_date').first()

# filter and update
MFDownload.objects.filter(pk=mfdownload.id).update(
            end_time=datetime.datetime.now(), has_data=False)

There are many things which can be done with filter

https://docs.djangoproject.com/en/2.2/ref/models/querysets/#queryset-api

Q object

Django also provides us with an object Q for making queries with filter. See how to use it here https://books.agiliq.com/projects/django-orm-cookbook/en/latest/query_relatedtool.html

example code would be

scheme = Scheme.objects.get(fund_code=amfi)

    f = Q(scheme=scheme)
    # navs = Nav.objects.filter(scheme=scheme)

    if "start_date" in request.query_params:
        start_date = datetime.datetime.strptime(
            request.query_params["start_date"], '%d-%m-%Y')
        # navs.filter(date__gt=start_date)
        f &= Q(date__gt=start_date)

    if "end_date" in request.query_params:
        end_date = datetime.datetime.strptime(
            request.query_params["end_date"], '%d-%m-%Y')
        # navs.filter(date__lt=end_date)
        f &= Q(date__lt=end_date)

Print Query

It’s always most useful when debugging to print queries doing that is simple

navs = Nav.objects.filter(f)
print(navs.query)