如何使用参数在DRF中实现原始sql查询

Question

大家好，我正在尝试实现原始sql查询，以便在django框架中创建api，输入参数是team_id、start_date和end_date。如team_id=1和start=2022-04-25和end_ date = 2022-05-01，temp1查询日期类似于7日期，从2022-04-25到2022-05-01。怎么做，请帮帮我。

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路径-我为获取数据而给出的路径-

http://127.0.0.1:8000/fleet/car_report?start_date=2022-04-04+&end_date=2022-04-10+&team_id=1&dates%5B%5D=2022-04-04+&dates%5B%5D=2022-04-05+&dates%5B%5D=2022-04-06+&dates%5B%5D=2022-04-07+&dates%5B%5D=2022-04-08+&dates%5B%5D=2022-04-09+&dates%5B%5D=2022-04-10+

输出误差-

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%5B%5D=2022-04-04+&dates%5B%5D=2022-04-05+&dates%5B%5D=2022-04-06+&dates%5B%5D=2022-04-07+&dates%5B%5D=2022-04-08+&dates%5B%5D=2022-04-09+&dates%5B%5D=2022-04-10+ HTTP/1.1" 500 126805

Answer 1

您可以使用raw()函数添加原始SQL查询。

Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')

文件编码

在这方面，我有一个特定的问题，根据查询的复杂性，我不能使用Django的ORM。问题不在于Django ORM本身，而在于缺乏将复杂的原始SQL查询转换为Django ORM的深层知识。匆忙中，这是最好也是最快的方法。