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 function named “generateFromSql.” This function can generate reports based on a given SQL query. 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 one function named “generateFromSql” accepting 12 arguments :

• cursor : DB cursor
• title : The title of the report that will be shown on top of table
• sqltext : The sql select query to retrieve data
• 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.db import connection
from django_query_to_table import DjangoQtt
from django.http import HttpResponse

# view function in Django project
def listOfOrders(request):
  cursor = connection.cursor()
  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.generateFromSql(cursor, 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 find the complete example project using django-query-to-table package to show orders list on github repository.