Django Query data optimization

  django, django-orm, python-3.x

Recently, I watched Django and discovered the teaching videos of select_related and prefetch_related.

So I installed debug_toolbar and took a look at my website

enter image description here

I searched the database too many times on one page.

I must convert it to json and send it back to the front end

Can I still optimize?

Is this the only way to go?

Below is my model .

models.py

def get_upload_path(instance, filename):
    return f'register/{instance.owner.job_number}/{filename}'

def image_upload_path(instance, filename):
    date = datetime.datetime.strptime(instance.date, '%Y-%m-%d %H:%M:%S')
    return f'image/{date.year}/{date.month}/{date.day}/{filename}'

class UserProfile(models.Model):
    name = models.CharField(max_length=64)
    department = models.CharField(max_length=32)
    job_number = models.CharField(max_length=32, unique=True)
    card_number = models.CharField(max_length=32, unique=True)
    location = models.CharField(max_length=32)
    date = models.DateTimeField()


class UserRegister(models.Model):
    owner = models.ForeignKey(UserProfile, on_delete=models.CASCADE)
    date = models.DateTimeField()
    feature = ArrayField(models.FloatField(null=True, blank=True), null=True, blank=True, size=512)
    image = models.ImageField(upload_to=get_upload_path)

    class Meta:
        db_table = 'UserRegister'


class UserImage(models.Model):
    owner = models.ForeignKey(UserProfile, on_delete=models.CASCADE)
    heat = models.CharField(max_length=8, blank=True, null=True)
    date = models.DateTimeField()
    image = models.ImageField(upload_to=image_upload_path)
    punch_in = models.CharField(max_length=8, blank=True, null=True)

    class Meta:
        db_table = 'UserImage'

views.py

def get_db_data(db, page, limit, model):
    data = []
    paginator = Paginator(db, limit)
    try:
        page_object = paginator.page(page)
        all_page = paginator.num_pages 

        for db_data in page_object:
            images = []
            for image in db_data.userregister_set.all():
                try:
                    url = f'/static/uploads{image.image.url}'
                except ValueError:
                    url = '/static/imgs/assets/none.jpg'
                images.append({'id': image.id, 'url': url})

            _ = {
                'id': db_data.id,
                'name': db_data.name,
                'department': db_data.department,
                'job_number': db_data.job_number,
                'card_number': db_data.card_number,
                'location': db_data.location,
                'date': db_data.date.strftime('%Y-%m-%d %H:%M:%S'),
                'images': images,
            }

            data.append(_)

        result = {
            'data': data,
            'all_page': all_page,
            'page': int(page)
        }
        return result
    except EmptyPage as error:
        # 該頁數沒資料。
        return {'msg': 'Error'}


class Test(Views):
    def get(self, request):
        name = request.GET.get('name')
        department = request.GET.get('department')
        job_number = request.GET.get('job_number')
        card_number = request.GET.get('card_number')
        location = request.GET.get('location')
        start_date = request.GET.get('start_date')
        end_date = request.GET.get('end_date')
        order = request.GET.get('order', default='DESC')
        page = request.GET.get('page', default=1)
        limit = request.GET.get('limit', default=25)
        user_profile = UserProfile.objects.all().select_related()
        if name:
            user_profile = user_profile.filter(name=name)
        if department:
                user_profile = user_profile.filter(department=department_table.department)
        if job_number:
            user_profile = user_profile.filter(job_number=job_number)
        if card_number:
            user_profile = user_profile.filter(card_number=card_number)
        if location:
                user_profile = user_profile.filter(location=locations_table.location)
        if start_date and end_date:
            user_profile = user_profile.filter(date__range=[start_date, end_date])
        if order == 'DESC':
            data = get_db_data(db=user_profile.order_by('-date'), page=page, limit=limit, model='api')
        else:
            data = get_db_data(db=user_profile.order_by('date'), page=page, limit=limit, model='api')
        response = JsonResponse(data)
        # return response  #  This is the response I want to return, but now I need to use `debug_toolbar`, so I return an empty page to facilitate my reading `debug_toolbar`. 
        return render(request, 'User/test1.html')

return JsonResponse(data) is what I want to return, but now I need to use debug_toolbar, so I return to an empty page to facilitate reading debug_toolbar.

Source: Python-3x Questions

LEAVE A COMMENT