A Simple Python Class for Formatting Table Data to Display in HTML or CSV

Displaying content from a database table on a web page is a common requirement, however often the way the data is stored in the back-end isn’t quite how you’d like it to look on the front-end. For example, column names are often short-hand and not very informative, values may be abbreviated to save space, and timestamps are often standardized to UTC.

Here’s a simple class I made for one of my projects which helps to translate table headers, perform value lookups for particular fields, and handle timestamp timezone conversion and formatting.

First, we’ll look at an example of some raw data selected from a database, and how we might want to display it to an end user:

Here the field headers have been translated to provide more detail, lookup-style values for Gender and State of Residence have been replaced, and the Last Logged In timestamps have been translated from UTC to local time and presented in a more human-friendly format.

Initialization

Here’s the start of the TableFormatter class:

The key attributes are:

  • table_data (list/tuple of lists/tuples/dicts) — Raw data, selected directly from a database or similar. The length of the rows must be equal to the length of field_names, and also same order if lists/tuples
  • field_names (list/tuple of str )— Original field names as in database, ordered as desired for output
  • header_mapping (dict) — Mapping of original field names to desired output column labels
  • value_lookups (dict of dicts) — Value lookups for field data. Keys are original field names, values are mapping dictionaries relevant to that field
  • title (str) — Title to show above table in HTML rendering
  • source_tz (dateutil tz object) — Timezone of timestamps in source data, for example UTC if coming from a database
  • display_tz (dateutil tz object) — Desired output display timezone (perhaps local timezone of user)
  • timestamp_format (str) — Format string used to display any timestamps

The list of translated table header strings are produced here using list comprehension.

Table Processing

Here’s the build_table_data method called during initialization:

It performs two functions:

  • Validates format of table and transforms it into labelled fields (list of dicts). If you are using pyodbc as your database driver, add pyodbc.Row where indicated so that you can pass in the results directly from cursor.fetchall()
  • Performs field value lookups and translations where necessary using nested list comprehension. If neither source_tz or display_tz are provided, no timezone translation is performed. If a field name or value lookup key aren’t present in self.value_lookups, the original value is used

Generating HTML

This method generates a HTML table, with a configurable maximum number of rows and classes for styling. It returns a Markup string which avoids HTML escaping when rendering in your template with Jinja2 or Flask. Otherwise you could use another method, such as {% autoescape off %} in Django.

CSV Generation

This method is quite straightforward; provide a filepath and the formatted table will be dumped into a file there.

Defining Attributes

There are two ways to define the field formatting attributes for a particular table. You can choose which is most appropriate for your application:

  1. Create a new subclass for a particular table and define class attributes, resulting in a nicely contained table definition. It allows a cleaner approach if the field mappings can be statically defined before code execution:

2. Dynamically assign the lookup dictionaries upon object initialization (may be useful for dynamic scenarios like if the field mappings are stored elsewhere and subject to change)

Instantiation

Here’s a simple example using a database connection to retrieve data and demonstrating the two methods of instantiation.

In practice, you may use a combination of the two methods, perhaps with table-specific attributes defined in a subclass, and display timezone selected by the user and defined upon instantiation.

HTML Rendering

Just pass the table object to a Jinja2, Flask or Django template and either reference it directly to invoke the __html__() method to call as_html() with default arguments, or override defaults by calling the object with keyword parameters:

If you’re not using a templating system then you can just call the object in your script similar to above and do whatever you like with the output.

Extra Feature Ideas

This class currently has pretty limited functionality but there’s a few things you could do to expand on depending on your application:

  • More generalized data transformations — Instead of just doing a key-value lookup and timezone conversion for data values, you could define custom transformation functions for specific fields in the table.The options here are pretty limitless!
  • Integrate with ORM models — Often web applications using Flask or Django will define model classes which are represented by database tables. Since each model instance is the equivalent of a single row in the database, you could define all class attributes and methods related to data translation in the model class. The table headers and title may need to be handled independently, perhaps with some other kind of wrapper class.
  • Make the HTML table more interactive — There are various Javascript libraries that enable filtering, sorting or hiding of table columns. Just tweak the rendering of the table in this class to enable these features.

Conclusion

I hope you can get some use out of this simple too for your own projects, or at least get a few ideas from it! Here’s the full thing on GitHub.

Feel free to add suggestions or let me know if you run into any problems.