Generate HTML Table Report From SQL Query In Django

Creating reports is an essential part of software development, but it can also be a challenging task. Django developers often struggle with generating tables using Python and a combination of HTML tags to present data retrieved from a SQL query. Various factors, such as report title, column titles, report orientation, fonts, and more, need to be considered while developing a report. Additionally, most reports include numerical information that should be aggregated in the footer section of the table.

To simplify the process of generating reports, I developed a package for Django framework named django-query-to-table that includes a functions named generate_from_sql and generate_from_queryset. These functions generate reports based on a given SQL query or querset. As a result, Django developers can now use this package to streamline the report generation process for their projects. The package is available on PyPI for other developers to use.

Package Github Repository :

Installation :

pip install django-query-to-table

The package contains two functions named “generate_from_sql” and “generate_from_queryset” . You can customize your table using these parameter:

• title : The title of the report that will be shown on top of table
• sqltext/queryset : The sql select query to retrieve data/ django queryset
• footerCols : A list of columns name that you want to have Sum of values on footer . Example : [‘amount’,’price’]
• htmlClass : Html CSS classes for the table
• direction (default = “ltr”) : Indicates direction of the report page. “ltr”- Left to Right , “rtl” – Right to Left
• font (default = “Tahoma”) : Font of title and table contents
• totalText (default = “Total”) : Title of footer row that will be the put below the first column.
• rowIndex (default = False) : Indicates whether the table should have index column or not.
• headerRowColor (default = ‘#eeeeee’) : The header (title) row background color.
• evenRowColor (default = ‘#ffffff’) : The even rows background color.
• oddRowColor (default = ‘#ffffff’) : The odd rows background color.

Sample project using django-query-to-table on github :

Sample code of using the package in django project :

from django_query_to_table import DjangoQtt
from django.http import HttpResponse

# view function in Django project
def listOfOrders(request):
  reportTitle = "Order List"
  sqlQuery = "SELECT order_number as 'Order No', order_item as 'Item', total_amount 'Price', order_date as 'Date' FROM report_app_order"
  columnsToBeSummarized = ['Price']
  fontName = "Arial"
  cssClasses = "reportTable container"
  headerRowBackgroundColor = '#ffeeee'
  evenRowsBackgroundColor = '#ffeeff'
  oddRowsBackgroundColor = '#ffffff'
  table = DjangoQtt.generate_from_sql(reportTitle, sqlQuery, columnsToBeSummarized, cssClasses, "ltr", fontName, "Total Price", True, headerRowBackgroundColor, evenRowsBackgroundColor, oddRowsBackgroundColor)

  # table is a string variable contianing the html table showing the query result
  return HttpResponse(table)

The output table will be as shown below :

Another query that get number of sold items and their total price

SELECT order_item AS 'Item',
COUNT(*) AS 'Total Sell',
SUM(total_amount) as 'Total Earn'
FROM report_app_order group by order_item

The result :

You can also use generate_from_queryset to generate HTML table from Django queryset, with the release of Django 4.0.4, which implemented a security fix that prevents the use of spaces in aliases, you can use double underscores (__) as an alternative in your aliases. The table generator will automatically render these as spaces in the final output:

 from django_query_to_table import DjangoQtt
 from django.http import HttpResponse
 from .models import Order
 view function in Django project
 def listOfPersons(request):
 order_queryset = Order.objects.annotate(
       **{
          'Order__Number': F('order_number'),
          'Order__Item': F('order_item'),
          'Customer__Name': F('customer_name'),
          'Order__Date': F('order_date'),
          'Total__Amount': F('total_amount'),
       }
    ).values(
       'Order__Number',
       'Order__Item',
       'Customer__Name',
       'Order__Date',
       'Total__Amount'
    )
 table = DjangoQtt.generate_from_queryset(
                                        title = "Summmary Table",
                                        queryset = order_queryset,
                                        htmlClass = "summary",
                                        rowIndex = True,
                                        footerCols=['Total__Amount'],
                                 )
 return HttpResponse(table)

You can find the complete example project using django-query-to-table package to show orders list on github repository.