Hi Everyone i am trying to implement raw sql query to create api in django framework and input parameter is team_id, start_date and end_date. like team_id=1 and start=2022-04-25 and end_date=2022-05-01, and temp1 query date like 7 date from 2022-04-25 to 2022-05-01. how will do this,please help me out.
models.py
class Car_dash_daily(models.Model):
created_at = TimestampField(null=True, blank=True, auto_now_add=True)
car_number = models.CharField(max_length=80, null=True, blank=True)
date = models.DateField(null=True, blank=True)
car_status = EnumField(choices=['DRIVEN', 'INSURANCE', 'ND', 'REPAIR', 'SERVICE', 'BREAKDOWN', 'ACTIVE', 'B2B', 'PARKING'], default=None)
trips = models.IntegerField(null=True, blank=True)
online_hours = models.CharField(max_length=10, null=True, blank=True)
revenue = models.FloatField(max_length=10, null=True, blank=True)
status = EnumField(choices=['STICKERING', 'CALL_FOR_GPS','SERVICING'], default=None)
team = models.ForeignKey(
Team,
models.CASCADE,
verbose_name='Team',
null=True,
)
car = models.ForeignKey(
Car,
models.CASCADE,
verbose_name='Car',
null=True,
blank=True
)
city = models.ForeignKey(
City,
models.CASCADE,
verbose_name='City',
null=True,
)
driver = models.ForeignKey(
Driver,
models.CASCADE,
verbose_name='Driver',
null=True,
blank=True
)
views.py
def car_report(request):
start_date = request.GET.get('start_date')
print(start_date,'start_date')
end_date = request.GET.get('end_date')
print(end_date,'end_date')
dates = request.GET.getlist('dates[]')
print(dates,'dates')
team_id = int(request.GET.get('team_id'))
print(team_id,'team_id')
car_report= connection.cursor()
car_report.execute(''' SELECT
temp2.car_number,sum(temp2.trips)as total_trips, temp2.status,
case
when sum(temp2.day1_trips)=-10 then 'BD'
when sum(temp2.day1_trips)=-20 then 'ND'
when sum(temp2.day1_trips)=-30 then 'R'
when sum(temp2.day1_trips)=-40 then 'I'
when sum(temp2.day1_trips)=-50 then 'P'
else sum(temp2.day1_trips) end AS day1_trips,
case
when sum(temp2.day2_trips)=-10 then 'BD'
when sum(temp2.day2_trips)=-20 then 'ND'
when sum(temp2.day2_trips)=-30 then 'R'
when sum(temp2.day2_trips)=-40 then 'I'
when sum(temp2.day2_trips)=-50 then 'P'
else sum(temp2.day2_trips) end AS day2_trips,
case
when sum(temp2.day3_trips)=-10 then 'BD'
when sum(temp2.day3_trips)=-20 then 'ND'
when sum(temp2.day3_trips)=-30 then 'R'
when sum(temp2.day3_trips)=-40 then 'I'
when sum(temp2.day3_trips)=-50 then 'P'
else sum(temp2.day3_trips) end AS day3_trips,
case
when sum(temp2.day4_trips)=-10 then 'BD'
when sum(temp2.day4_trips)=-20 then 'ND'
when sum(temp2.day4_trips)=-30 then 'R'
when sum(temp2.day4_trips)=-40 then 'I'
when sum(temp2.day4_trips)=-50 then 'P'
else sum(temp2.day4_trips) end AS day4_trips,
case
when sum(temp2.day5_trips)=-10 then 'BD'
when sum(temp2.day5_trips)=-20 then 'ND'
when sum(temp2.day5_trips)=-30 then 'R'
when sum(temp2.day5_trips)=-40 then 'I'
when sum(temp2.day5_trips)=-50 then 'P'
else sum(temp2.day5_trips) end AS day5_trips,
case
when sum(temp2.day6_trips)=-10 then 'BD'
when sum(temp2.day6_trips)=-20 then 'ND'
when sum(temp2.day6_trips)=-30 then 'R'
when sum(temp2.day6_trips)=-40 then 'I'
when sum(temp2.day6_trips)=-50 then 'P'
else sum(temp2.day6_trips) end AS day6_trips,
case
when sum(temp2.day7_trips)=-10 then 'BD'
when sum(temp2.day7_trips)=-20 then 'ND'
when sum(temp2.day7_trips)=-30 then 'R'
when sum(temp2.day7_trips)=-40 then 'I'
when sum(temp2.day7_trips)=-50 then 'P'
else sum(temp2.day7_trips) end AS day7_trips
FROM
(
SELECT temp1.car_number,temp1.trips,temp1.status,
case when temp1.date= %s then temp1.trip else 0 end as day1_trips,
case when temp1.date= %s then temp1.trip else 0 end as day2_trips,
case when temp1.date= %s then temp1.trip else 0 end as day3_trips,
case when temp1.date= %s then temp1.trip else 0 end as day4_trips,
case when temp1.date= %s then temp1.trip else 0 end as day5_trips,
case when temp1.date= %s then temp1.trip else 0 end as day6_trips,
case when temp1.date= %s then temp1.trip else 0 end as day7_trips
from
(SELECT date, car_number,driver_id,trips,car_status,status,
case when trips=0
THEN
CASE WHEN
car_status = 'BREAKDOWN'
THEN -10
when car_status='ND'
then -20
when car_status='REPAIR'
then -30
when car_status='INSURANCE'
then -40
when car_status='PARKING'
then -50
ELSE 0
END
else trips end as trip
FROM fleet_car_dash_daily WHERE team_id= %s
and (date BETWEEN %s and %s))as temp1) temp2 GROUP by temp2.car_number''',
[dates[0],dates[1],dates[2],dates[3],dates[4],dates[5],dates[6],team_id,start_date,end_date])
car_report_data = car_report.fetchall()
print(car_report_data,'car_report_data')
json_res=[]
for row in car_report_data:
json_obj=dict(car_number=row[0],total_trips=row[1],status=row[2],day1_trips=row[3],day2_trips=row[4],day3_trips=row[5],day4_trips=row[6],day5_trips=row[7],day6_trips=row[8],day7_trips=row[9])
print(json_obj,'json_obj')
json_res.append(json_obj)
return JsonResponse(json_res, safe=False)
url path-which i have given to get data-
http://127.0.0.1:8000/fleet/car_report?start_date=2022-04-04 &end_date=2022-04-10 &team_id=1&dates[]=2022-04-04 &dates[]=2022-04-05 &dates[]=2022-04-06 &dates[]=2022-04-07 &dates[]=2022-04-08 &dates[]=2022-04-09 &dates[]=2022-04-10
output error-
TypeError: Object of type bytes is not JSON serializable
ERROR "GET /fleet/car_report?start_date=2022-04-04 &end_date=2022-04-10 &team_id=1&dates[]=2022-04-04 &dates[]=2022-04-05 &dates[]=2022-04-06 &dates[]=2022-04-07 &dates[]=2022-04-08 &dates[]=2022-04-09 &dates[]=2022-04-10 HTTP/1.1" 500 126805
CodePudding user response：
You can use the raw() function to add raw SQL queries.
Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
I had a specific issue in regards of this in which I could not use Django's ORM depending on the query complexity. The problem is not due to Django ORM itself, rather the lack of deep knowlege in regards of translating a complex raw SQL query into Django ORM. In a hurry this is the best and quickest way to do.