Generate HTML Table From SQL Query In Flask

Reports are essential part of any software , and also it can be most frustrating part of software development. In web application, particularly flask web application, developers struggle with python and bunch of HTML tags to generate a suitable table for data fetched from SQL query. Report’s title, title of columns, report direction, fonts and etc. are things that emerge in developers mind when it comes to report. Moreover, most of reports contain numerical information that should be aggregated in the footer section of the table. In my last project that I coded in Flask framework, that was a big project with lots of reports, I decided not to write the code from the scratch for each report. So, I wrote a package with a function named generateFromSql that generates report by given SQL query. It can be developed and get matured more and more, However by now, it only works with sqlalchemy and sql query.

I published first release of this package to pip (Python package manager) to be used by other developers with flask-sqlalchemy-report name. Also I put it on github, maybe a generous python developer would help it become a nice comprehensive package for building reports in flask.

Dark Mode

Flask_SqlAlchemy_Report (this link opens in a new window) by birddevelper (this link opens in a new window)

A useful simple to use utility to turn your sql query into a beautiful report HTML table

1 Subscriber 4 Watchers 0 Forks Check out this repository on GitHub.com (this link opens in a new window)

Install it with pip :

pip install flask-sqlalchemy-report

it is an easy to use tool for generating html table from sql query. it contains only one function named “generateFromSql” with 11 arguments :

• session : SQLAlchemy session
• title : the title of the report that will be shown on top of table
• sqltext : the sql query
• footerCols : a list of columns name that you want to have Sum of values on footer . Example : [‘amount’,’price’]
• 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.

from flask_sqlalchemy_report import Reporter 
 @app.route('/listOfPersons', methods=['GET'])
 def listOfPersons():
   reportTitle = "Employee List"
   sqlQuery = "SELECT FirstName as 'First Name', LastName as 'Last Name', phone as 'Phone Number', salary as 'Salary' FROM persons"
   columnsToBeSummarized = ['Salary']
   fontName = "Arial"
   headerRowBackgroundColor = '#ffeeee'
   evenRowsBackgroundColor = '#ffeeff'
   oddRowsBackgroundColor = '#ffffff'
   return Reporter.generateFromSql(db.session, reportTitle, sqlQuery, columnsToBeSummarized, 
                                   "ltr", fontName, "Total Salary", True,
                                   headerRowBackgroundColor, evenRowsBackgroundColor, oddRowsBackgroundColor
                                   )

I have developed same package for Django wich can be reached from my github repository.