{"id":219,"date":"2021-04-12T12:30:10","date_gmt":"2021-04-12T08:00:10","guid":{"rendered":"http:\/\/m-shaeri.ir\/blog\/?p=219"},"modified":"2023-03-26T12:34:39","modified_gmt":"2023-03-26T09:04:39","slug":"generate-html-table-from-sql-query-in-flask","status":"publish","type":"post","link":"https:\/\/mshaeri.com\/blog\/generate-html-table-from-sql-query-in-flask\/","title":{"rendered":"Generate HTML Table From SQL Query In Flask"},"content":{"rendered":"\n<p>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&#8217;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 <strong>generateFromSql <\/strong>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. <\/p>\n\n\n\n<p>I published first release of this package to pip (Python package manager) to be used by other developers with <strong><a rel=\"follow\" href=\"https:\/\/pypi.org\/project\/flask-sqlalchemy-report\/\">flask-sqlalchemy-report<\/a><\/strong> name. Also I put it on <a rel=\"follow\" href=\"https:\/\/github.com\/birddevelper\/Flask_SqlAlchemy_Report\" data-type=\"URL\" data-id=\"https:\/\/github.com\/birddevelper\/Flask_SqlAlchemy_Report\">github<\/a>, maybe a generous python developer would help it become a nice comprehensive package for building reports in flask. <\/p>\n\n\n<div class=\"ebg-br-wrapper ebg-br-wrapper-dark-mode-off\">\n\t<div class=\"ebg-br-background-image\"><\/div>\n\t<div class=\"ebg-br-editmode egb-br-darkmode-status\">\n\t\t<span class=\"egb-br-darkmode-status-img\">Dark Mode<\/span>\n\t<\/div>\n\t<div class=\"ebg-br-avatar\">\n\t\t<img loading=\"lazy\" decoding=\"async\" class=\"ebg-br-header-avatar\" src=\"https:\/\/avatars.githubusercontent.com\/u\/20352967?v=4\" alt=\"\" width=\"150\" height=\"150\" \/>\n\t<\/div>\n\t<div class=\"ebg-br-main\">\n\t\t<p class=\"ebg-br-title\">\n\t\t\t<strong>\n\t\t\t\t<a target=\"_blank\" rel=\"noopener noreferrer\" href=\"https:\/\/github.com\/birddevelper\/Flask_SqlAlchemy_Report\">\n\t\t\t\t\tFlask_SqlAlchemy_Report\n\t\t\t\t\t<span class=\"screen-reader-text\">(this link opens in a new window)<\/span>\n\t\t\t\t<\/a>\n\t\t\t<\/strong>\n\t\t\t<em>\n\t\t\t\tby<a target=\"_blank\" rel=\"noopener noreferrer\" href=\"https:\/\/github.com\/birddevelper\">\n\t\t\t\t\tbirddevelper\n\t\t\t\t\t<span class=\"screen-reader-text\">(this link opens in a new window)<\/span>\n\t\t\t\t<\/a>\n\t\t\t<\/em>\n\t\t<\/p>\n\t\t<p class=\"ebg-br-description\">A useful simple to use utility to turn your sql query into a beautiful report HTML table<\/p>\n\t\t<p class=\"ebg-br-footer\">\n\t\t\t<span class=\"ebg-br-subscribers\">\n\t\t\t\t<span class=\"ebg-br-background-image\"><\/span>\n\t\t\t\t1 Subscriber\t\t\t<\/span>\n\t\t\t<span class=\"ebg-br-watchers\">\n\t\t\t\t<span class=\"ebg-br-background-image\"><\/span>\n\t\t\t\t4 Watchers\t\t\t<\/span>\n\t\t\t<span class=\"ebg-br-forks\">\n\t\t\t\t<span class=\"ebg-br-background-image\"><\/span>\n\t\t\t\t0 Forks\t\t\t<\/span>\n\t\t\t<a target=\"_blank\" rel=\"noopener noreferrer\" class=\"ebg-br-link\" href=\"https:\/\/github.com\/birddevelper\/Flask_SqlAlchemy_Report\">\n\t\t\t\tCheck out this repository on GitHub.com\t\t\t\t<span class=\"screen-reader-text\">(this link opens in a new window)<\/span>\n\t\t\t<\/a>\n\t\t<\/p>\n\t<\/div>\n<\/div>\n\n\n<p>Install it with pip :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">pip install flask-sqlalchemy-report<\/code><\/pre>\n\n\n\n<p>it is an easy to use tool for generating html table from sql query. it contains only one function named &#8220;generateFromSql&#8221; with 11 arguments :<\/p>\n\n\n\n<ul><li>session : SQLAlchemy session<\/li><li>title : the title of the report that will be shown on top of table<\/li><li>sqltext : the sql query<\/li><li>footerCols : a list of columns name that you want to have Sum of values on footer . Example : [&#8216;amount&#8217;,&#8217;price&#8217;]<\/li><li>direction (default = &#8220;ltr&#8221;) : indicates direction of the report page. &#8220;ltr&#8221;- Left to Right , &#8220;rtl&#8221; &#8211; Right to Left<\/li><li>font (default = &#8220;Tahoma&#8221;) : font of title and table contents<\/li><li>totalText (default = &#8220;Total&#8221;) : title of footer row that will be the put below the first column.<\/li><li>rowIndex (default = False) : indicates whether the table should have index column or not.<\/li><li>headerRowColor (default = &#8216;#eeeeee&#8217;) : the header (title) row background color.<\/li><li>evenRowColor (default = &#8216;#ffffff&#8217;) : the even rows background color.<\/li><li>oddRowColor (default = &#8216;#ffffff&#8217;) : the odd rows background color.<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">from flask_sqlalchemy_report import Reporter \n @app.route('\/listOfPersons', methods=['GET'])\n def listOfPersons():\n   reportTitle = \"Employee List\"\n   sqlQuery = \"SELECT FirstName as 'First Name', LastName as 'Last Name', phone as 'Phone Number', salary as 'Salary' FROM persons\"\n   columnsToBeSummarized = ['Salary']\n   fontName = \"Arial\"\n   headerRowBackgroundColor = '#ffeeee'\n   evenRowsBackgroundColor = '#ffeeff'\n   oddRowsBackgroundColor = '#ffffff'\n   return Reporter.generateFromSql(db.session, reportTitle, sqlQuery, columnsToBeSummarized, \n                                   \"ltr\", fontName, \"Total Salary\", True,\n                                   headerRowBackgroundColor, evenRowsBackgroundColor, oddRowsBackgroundColor\n                                   )<\/code><\/pre>\n\n\n\n<p>I have developed same package for Django wich can be reached from my <a href=\"http:\/\/github.com\/birddevelper\/django-query-to-table\">github repository<\/a>. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Reports are essential part of any software , and also it can be most frustrating part of software development. In web application, particularly flask web &hellip; <\/p>\n","protected":false},"author":1,"featured_media":222,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,35,41,110,214],"tags":[42,46,48,39,44,47,45,43],"_links":{"self":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/219"}],"collection":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/comments?post=219"}],"version-history":[{"count":13,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/219\/revisions"}],"predecessor-version":[{"id":1473,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/219\/revisions\/1473"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/media\/222"}],"wp:attachment":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/media?parent=219"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/categories?post=219"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/tags?post=219"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}