June 8, 2024
django query to report table

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 :

Generated table by django-query-to-table
Generated table by django-query-to-table

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.

One thought on “Generate HTML Table Report From SQL Query In Django

  1. Hi there, thank you for the package.
    Is it possible to add some sort of pagination to the generated table?

Leave a Reply

Your email address will not be published. Required fields are marked *