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.
A useful package to make html table from sql query in Django
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.
a simple django project to demo django-query-to-table package
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 :