Django create admin list from raw query

Question

I'm willing to create queryset list template in the admin

The queryset is obtained through a cursor.execute() so a fake model might be needed is needed.

Basically I just want to take advantage of the pagination and filter functionality Django admin list provides.

Here's the relevant part of my code

models.py

class Query(object):

   def __init__(self, sql):

       self.sql = sql


   def execute_query(self):

        cursor = connection.cursor()
        start_time = time()

        try:
            cursor.execute(self.sql)
        except DatabaseError as e:
            cursor.close()
            raise e

        return cursor, ((time() - start_time) * 1000)

admin.py

 class QueryAdmin(admin.ModelAdmin):
 ....


admin.site.register(Query, QueryAdmin)

Displant

Answer 1

If you are just trying to use the django admin functionality to make use of it's pagination and filtering features, you will find yourself spending far more time on it than you would if you used django pagination. In the the very first thing you will find is that both pagination and filtering will not work with cursor.execute

If you do need some sort of admin functionality here, read on.

The preferred way to use a custom queryset in django admin is to override the get_queryset method in the admin class.

The get_queryset method on a ModelAdmin returns a QuerySet of all model instances that can be edited by the admin site. One use case for overriding this method is to show objects owned by the logged-in user:

So our code would be like

class QueryAdmin(admin.ModelAdmin):
    def queryset(self, request, queryset):

        return SomeModel.objects.raw('SOME QUERY')

If you wanted to return a custom raw query set, you would do that here by using the raw method call on the object's manager. It's much more preferable to using cursor.execute. You will actually need such a method (objects.raw) instead of execute to make use of some of the django admin functionality.

Even now you will find that pagination doesn't work. Then you will need to do this: django pagination and RawQuerySet

Greenlee

Thanks, I don't think the second option applies in my case since I dont actually have a model. I just need to filter and paginate my query results just like you stated in option one. – Displant

Not having a model to suite this query doesn't matter! Just use any old model and in your select choose a number that can act as a primary key! – Greenlee

Answer 2

I have just implemented this feature. You need new View RawChangeList extending ChangeList View from Django:

from django.contrib.admin.views.main import ChangeList
from django.db import connections

class RawChangeList(ChangeList):
    """
    Extended Django ChangeList to be able show data from RawQueryset.
    """
    def get_count(self):
        connection = connections[self.queryset.db]
        with connection.cursor() as c:
            if connection.vendor == 'microsoft':  # CTE in subquery is not working in SQL Server
                c.execute(self.queryset.raw_query)
                c.execute('SELECT @@ROWCOUNT')
            else:
                query = 'SELECT COUNT(*) FROM ({query}) AS sq'
                c.execute(query.format(query=self.queryset.raw_query))

            return c.fetchone()[0]

    def get_queryset_slice(self):
        connection = connections[self.queryset.db]
        if connection.vendor == 'microsoft':
            # SQL Server needs ordered query for slicing
            if hasattr(self.queryset, 'ordered') and self.queryset.ordered:
                query = '{query}'
            else:
                query = '{query} ORDER BY 1'
            query += ' OFFSET {offset} ROWS FETCH NEXT {limit} ROWS ONLY'
        else:
            query = '{query} LIMIT {limit} OFFSET {offset}'

        return self.queryset.model.objects.raw(
            query.format(
                query=self.queryset.raw_query,
                offset=self.page_num * self.list_per_page,
                limit=(self.page_num + 1) * self.list_per_page - self.page_num * self.list_per_page,
            )
        )

    def get_queryset(self, request):
        """
        Overriding to avoid applying filters in ChangeList because RawQueryset has not filter method.
        So any filters has to be applied manually for now.
        """
        qs = self.root_queryset
        if not hasattr(qs, 'count'):
            qs.count = lambda: self.get_count()
        return qs

    def get_results(self, request):
        if self.show_all:
            qs = self.queryset
        else:
            qs = self.get_queryset_slice()

        paginator = self.model_admin.get_paginator(request, self.queryset, self.list_per_page)

        self.result_count = paginator.count
        self.show_full_result_count = False
        self.show_admin_actions = True
        self.full_result_count = 0
        self.result_list = list(qs)
        self.can_show_all = True
        self.multi_page = True
        self.paginator = paginator

And then use this RawChangeList in admin:

@admin.register(MyModel)
class MyModelAdmin(admin.ModelAdmin):

    def get_changelist(self, request, **kwargs):
        return RawChangeList

    def get_queryset(self, request):
        return MyModel.objects.raw('SELECT * FROM my_app_my_model')

    def get_object(self, request, object_id, from_field=None):
        return MyModel.objects.raw('SELECT * FROM my_app_my_model WHERE id=%s', (object_id, ))[0]

Arroba