How to write python-django queires and import them?

78 views Asked by At

Data :

{
  "Fruit": "Pomegranate",
  "District": "Nasik",
  "Taluka": "Nasik",
  "Revenue circle": "Nasik",
  "Sum Insured": 28000,
  "Area": 1200,
  "Farmer": 183
}

{
  "Fruit": "Pomegranate",
  "District": "Jalna",
  "Taluka": "Jalna",
  "Revenue circle": "Jalna",
  "Sum Insured": 28000,
  "Area": 120,
  "Farmer": 13
}

{
  "Fruit": "Guava",
  "District": "Pune",
  "Taluka": "Haveli",
  "Revenue circle": "Uralikanchan",
  "Sum Insured": 50000,
  "Area": 10,
  "Farmer": 100
}

{
  "Fruit": "Guava",
  "District": "Nasik",
  "Taluka": "Girnare",
  "Revenue circle": "Girnare",
  "Sum Insured": 50000,
  "Area": 75,
  "Farmer": 90
}

{
  "Fruit": "Banana",
  "District": "Nanded",
  "Taluka": "Nandurbar",
  "Revenue circle": "NandedBK",
  "Sum Insured": 5000,
  "Area": 2260,
  "Farmer": 342
}

{
  "Fruit": "Banana",
  "District": "Jalgaon",
  "Taluka": "Bhadgaon",
  "Revenue circle": "Bhadgaon",
  "Sum Insured": 5000,
  "Area": 220,
  "Farmer": 265
}

I want to write all types of combination queries, if someone wants information only for Fruit which is Guava then the output will be exact data for Guava only.

also if someone wants information only for Fruit which is Banana & Guava then the output will be exact data for Banana and Guava.

If fruit is equal to Banana

output will be data for Banana

If fruit is equal to Guava

output will be data for Guava

If fruit is equal to Banana and Guava

output will be data for Banana and Guava

Also, if someone wants information only for District which is Nasik then the output will be exact data for Nasik district only. Query for "District"

If District is equal to Nasik

output will be data for Nasik District

If District is equal to Nanded

output will be data for Nanded District

likewise, there is query for "Revenue_circle, Farmer etc.

I know how to write this queries in mongoshell using find

db.Wbcis.find({"Fruit":"Banana"})

db.Wbcis.find({"District":"Nasik"}) etc...

but I want to writing the queries in the python script so I am confuse in models.py and views.py file.

I tired and type query using Q objects in models.py

models.py

from django.contrib.auth.models import User

from django.db import models

from django.db.models import Q

class Wbcis(models.Model):  
  Fruit = models.CharField(max_length=50)  
  District = models.CharField(max_length=50)   
  Taluka = models.CharField(max_length=50)    
  Revenue_circle = models.CharField(max_length=50)   
  Sum_Insured = models.FloatField()   
  Area = models.FloatField()
  Farmer = models.IntegerField()


def __str__(self):

    return self.Fruit

def save(self, *args, **kwargs):

    super().save(*args, **kwargs)

class Meta:

  verbose_name_plural = 'wbcis'

from models import Wbcis
Guava =Wbcis.objects.filter(Q(Fruit='Guava'))
print Guava
Banana= Wbcis.objects.filter(Q(Fruit='Banana'))
print Banana  
Pomegranate= Wbcis.objects.filter(Q(Fruit='Pomegranate'))   
print Pomegranate
Guava_Banana=Wbcis.objects.filter(Q(Fruit='Guava')&Q(Fruit='Banana'))   
print Guava_Banana

But, I know this is not correct way for query. I need to write this in for loop or while loop. can you please help me for how to writing this query using for loop ?

1

There are 1 answers

15
bigblind On BEST ANSWER

I'm not sure what you want, but your queries seem correct.

I think what you mean with your for loop, is that you can iterate over a query (technically, a QuerySet) to get the results.

For example:

Guava = Wbcis.objects.filter(Q(Fruit='Guava'))
for guava_entry in Guava:
    # Do something with the returned element here:
    print guava_entry

If you want to filter by multiple things, you can just have multiple conditions in your filter. For example, to get Bananas in Nanded

Guava = Wbcis.objects.filter(Fruit="Banana", District="Nanded")

You don't need to use a for loop for that. By default, filter combines your conditions using and. That means that in the example above, Fruit must be "Banana", and District must be "Nanded".

Looking at the examples you have in your question, you have this query:

Guava_Banana=Wbcis.objects.filter(Q(Fruit='Guava')&Q(Fruit='Banana'))   

This query will never return any results. It is asking for an object that has both a Fruit of "Guava" and a Fruit of "Banana". What you want instead is this:

Guava_Banana=Wbcis.objects.filter(Q(Fruit='Guava') | Q(Fruit='Banana'))

This new query returns objects where fruit is either "Guava" or "Banana".

I understand this can be confusing, because you want to return Guavas and Bananas, but think of it as a boolean expression.

You can wrap this in a function like this:

def get_wbcis(fruit=None, district=None, talkua=None, min_farmer=None, max_farmer=None, limit=100):
    query = Wbcis.objects.all()
    if fuit is not None:
        query = query.filter(Fruit=fruit)

    if district is not None:
        query = query.filter(District=district)

    if taluka is not None:
        query = query.filter(Taluka=taluka)

    if min_farmer is not None:
        query = query.filter(Farmer__gte=min_farmer)

    if max_farmer is not None:
        query = query.filter(Farmer__lt=max_farmer)

    return query[:limit]

The limit parameter ensures that at most that many results are returned.

min_farmer in this example uses the __gte query operator, meaning that results with a Farmer greather than or equal to min_farmer will be returned.

max_farmer uses the __lt operator, so results with a farmer lower than, but not equal to max_farmer will be returned.

Similarly, you could use __gt or __lte if you want different different inequality filters.

Your views.py could do something like this:

import json
from django.forms.models import model_to_dict
from django.http import JsonResponse
from models import get_wbcis

def wbcis_view(request):
    fruit = request.GET.get("fruit")
    district = request.GET.get("district")
    taluka = request.GET.get("taluka")
    min_farmer = request.GET.get("min_farmer")
    max_farmer = request.GET.get("max_farmer")

    wbcis = get_wbcis(fruit, district, taluka, min_farmer, max_ffarmer)

    #convert them to JSON:
    dicts = []
    for wbci in wbcis:
        dicts.append(model_to_dict(wbci))

    return JsonResponse(dicts)