How Do I Display Json Data With Flask And Jinja Templates

Rendering a table with data in a Flask template is a relatively elementary chore when the table is brusk, merely can be incredibly difficult for larger tables that require features such as sorting, pagination and searching. In this article I'm going to prove yous how to integrate the dataTables.js library in your templates, which volition allow y'all to create fully featured tables with ease!

How to Get the Code

All the code presented in this commodity comes from my flask-tables repository on GitHub. I will only be showing the interesting snippets hither, and so if you intend to run the code locally you should clone this repository, create a virtual environment and install the requirements.txt file in it.

How to Return a Tabular array in Flask

I am going to commencement from the beginning, then the beginning step is to create a small Flask application that renders a plain tabular array.

The table is going to contain contain information nearly users. Here is the SQLAlchemy model that I'grand going to utilise for the database:

          class User(db.Model):     id = db.Column(db.Integer, primary_key=True)     name = db.Cavalcade(db.String(64), alphabetize=True)     age = db.Column(db.Integer, alphabetize=True)     address = db.Cavalcade(db.String(256))     phone = db.Column(db.Cord(20))     email = db.Cavalcade(db.String(120), index=True)

The application is going to take a unmarried route, which passes the query with all the users stored in the database to a Jinja template for rendering:

          @app.road('/') def alphabetize():     users = User.query     render render_template('bootstrap_table.html', title='Bootstrap Tabular array',                            users=users)

The template name is bootstrap_table.html because I will be using the Bootstrap CSS framework to provide the basic table styling. This is entirely optional, but in my case it makes sense because I use Bootstrap in most of my projects, and I want my tables to have a look that is consistent with the balance of the page.

Hither is the bootstrap_table.html template:

          {% extends "base.html" %}  {% block content %}   <table id="data" class="table table-striped">     <thead>       <tr>         <th>Name</th>         <th>Age</th>         <th>Address</th>         <th>Phone Number</thursday>         <th>E-mail</th>       </tr>     </thead>     <tbody>       {% for user in users %}         <tr>           <td>{{ user.name }}</td>           <td>{{ user.age }}</td>           <td>{{ user.address }}</td>           <td>{{ user.telephone }}</td>           <td>{{ user.email }}</td>         </tr>       {% endfor %}     </tbody>   </tabular array> {% endblock %}

I hope you hold that at that place isn't much going on in this template. The <table> element is created with two sections: a header (inside the <thead> chemical element) and a torso (inside <tbody>). The contents in both sections are rows of data, either table headers or actual users. The tabular array body is generated with a Jinja for-loop that iterates over the query object that was passed in as an argument in the render_template() call.

From looking at the first line in the template, you know that I'1000 using template inheritance. This is because I desire to keep the boilerplate of the page from complicating the template file. The base.html template from which bootstrap_table.html inherits from is copied beneath:

          <!doctype html> <html>   <head>     <title>{{ championship }}</championship>     <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-+0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7+AMvyTG2x" crossorigin="bearding">   </caput>   <body>     <div class="container">       <h1>{{ title }}</h1>       <hr>       {% cake content %}{% endblock %}     </div>     {% block scripts %}{% endblock %}   </torso> </html>

This base template includes the Bootstrap CSS file from a CDN, accepts a title argument that is inserted both in the <head> section and as a <h1> chemical element at the tiptop of the page, and creates 2 blocks content and scripts for the derived template to use.

Running the Bootstrap Table

To be able to exam the awarding, I needed to generate some random content for the database table, The create_fake_users.py script shown below achieves that:

          import random import sys from faker import Faker from bootstrap_table import db, User   def create_fake_users(n):     """Generate imitation users."""     faker = Faker()     for i in range(n):         user = User(proper noun=faker.name(),                     age=random.randint(20, lxxx),                     address=faker.address().supplant('\n', ', '),                     phone=faker.phone_number(),                     email=faker.e-mail())         db.session.add(user)     db.session.commit()     print(f'Added {n} fake users to the database.')   if __name__ == '__main__':     if len(sys.argv) <= i:         impress('Pass the number of users yous desire to create as an statement.')         sys.go out(1)     create_fake_users(int(sys.argv[1]))

This script uses the Faker parcel to generate fake (yet realistic) information.

An interesting little play tricks that I'g using here is to "steal" the db object and the User model from the Flask application. This actually works quite well and eliminates the need to duplicate the database and model definitions for use outside of the Flask application.

If you take cloned the flask-tables repository and ready a virtual environment with all the dependencies, you tin now create a database with a scattering of random users with the following command:

          python create_fake_users.py 5

And so y'all tin run the Bootstrap table application:

          python bootstrap_table.py

If yous navigate to http://localhost:5000 on your spider web browser, you should see a squeamish tabular array with five rows.

Calculation dataTables.js

While the table above looks nice, information technology is but practical to utilize it when the number of rows is very small. In general you will find that users look larger tables to have interactive features, such as pagination, searching and sorting, and the Bootstrap table has none of that.

And then hither is where the dataTables.js library enters the film. This library runs in the browser and attaches to a <table> element to raise it.

Before I show you lot how to apply dataTables.js to the Bootstrap table above, the library needs to be included in the base.html template, and so that information technology is available to use. Below you tin can find the updated base template that includes dataTables.js:

          <!doctype html> <html>   <head>     <championship>{{ title }}</title>     <link href="https://cdn.jsdelivr.net/npm/bootstrap@v.0.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-+0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7+AMvyTG2x" crossorigin="anonymous">     <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/i.10.25/css/dataTables.bootstrap5.css">   </caput>   <body>     <div class="container">       <h1>{{ title }}</h1>       <60 minutes>       {% block content %}{% endblock %}     </div>     <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.6.0.min.js"></script>     <script type="text/javascript" charset="utf8" src="https://cdn.datatables.cyberspace/one.x.25/js/jquery.dataTables.js"></script>     <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap5.js"></script>     {% block scripts %}{% endblock %}   </body> </html>

In the <head> department I've added the dataTables.bootstrap5.css stylesheet. The dataTables.js library provides styles that are compatible with several CSS frameworks, so you take to apply the right CSS file here. For Bootstrap, there are styles for versions 3, 4 and 5. If you don't apply Bootstrap, it as well provides styles for tables built with Foundation, jQuery UI, Bulma and a few other CSS frameworks. If you don't employ any CSS frameworks, information technology likewise provides a set of standalone styles.

At the lesser of the <torso> chemical element I accept added a few scripts. First at that place's the jQuery library, which is a required dependency of dataTables.js. Next is the cadre dataTables.js library, which is called jquery.dataTables.js. The tertiary and concluding script is called dataTables.bootstrap5.js, and provides the custom logic that applies to my called Bootstrap 5 integration. If you use a different styling framework you will need to change this terminal script accordingly.

At the very end of the body the base template keeps the scripts cake, included there to give derived templates the chance to add their ain scripts. This is going to exist how the templates can initialize and configure the dataTables.js library.

A Basic Table

In its most basic implementation, all that is needed to enhance a tabular array is to telephone call the DataTable() office on it. Using the Bootstrap tabular array example as a starting indicate, the only change that needs to be made is to add together a brusk script in the template to call this part:

          {% block scripts %}   <script>     $(document).ready(role () {       $('#data').DataTable();     });   </script> {% endblock %}

The $(document).gear up() function comes from jQuery and is used to tell the browser to execute the role passed as an statement after the page finished loading. The $('#data') expression is a jQuery selector that retrieves the element with the id attribute set up to data, in other words, the tabular array that is rendered in the Jinja template. The DataTable() function from dataTables.js modifies this tabular array in place, every bit you lot will encounter in a moment.

Running the Basic Table

Before you effort this example out, it would exist a skillful idea to add together a few more users to the database. Allow's add 100 more than:

          python create_fake_users.py 100

There should at present be 105 users in the database, which is a squeamish size to feel the pagination feature. If y'all have the flask-tables repository cloned, this example is called basic_table.py and yous can start it as follows:

          python basic_table.py

If you now navigate to http://localhost:5000 on your browser, you should come across a much nicer table:

This table implements all the features yous would expect, including pagination (lesser correct corner of the table), searching (top-right) and sorting (table headers). In add-on, in the pinnacle-left there is a dropdown where y'all can select how many rows are displayed per page, and in the lesser-left corner y'all tin see the range of rows that are currently displayed, and how many rows there are in total. This is all managed by dataTables.js, without having to practise whatever additional work besides rendering the table!

The DataTable() function accepts an options object that the application can utilize to customize the way the table is enhanced. The number of options that are available covers a broad range of customizations.

To demonstrate how this customization works, I'thousand going to change the way the sorting and searching works using the columns option:

          {% cake scripts %}   <script>     $(document).ready(role () {       $('#data').DataTable({         columns: [           null,           {searchable: simulated},           {orderable: fake, searchable: false},           {orderable: fake, searchable: simulated},           nothing],       });     });   </script> {% endblock %}

The columns option accepts an assortment of sub-options for each cavalcade in the table. For the columns that practise not need customization the null value is given. I've made two customizations, first I fix the searchable column choice to false for the Age, Address and Phone Number columns. This volition remove these columns when the library looks for matches to a search string given in the search box. The 2d change is to set the orderable option to imitation for the Accost and Telephone Number columns, which removes the clickable sorting headers on these two columns.

Adding an Ajax Data Source

Y'all may be wondering why I called the above tabular array the "basic" tabular array, given that it has all the features you may wish to have in a table. The problem with this style of using dataTables.js is that you have to render the entire table to the page before the library takes over and applies its enhancements. If the table is large, Jinja may take a considerable corporeality of time rendering information technology, and then the browser may spend some more time downloading all that HTML content, and all of this will happen while the user is waiting for the page to display. For long tables, when the page completes to load in the browser you may run into the original table rendered on the page for a short moment before the enhancements are practical, just considering of the number of rows that need to exist hidden every bit a consequence of the pagination.

While the basic solution from the previous department is conveniently simple, it but works for tables that are not very long. A meliorate arroyo would exist to render the table without any rows, and then have the browser request the data that goes in the table asynchronously through a split request.

This obviously requires a more involved integration, but information technology is still relatively low endeavor. The kickoff change I'm going to make is to expand the User model with a to_dict() method that can return a user as a Python lexicon that can be serialized to JSON:

          class User(db.Model):     id = db.Cavalcade(db.Integer, primary_key=Truthful)     proper name = db.Column(db.Cord(64), index=True)     historic period = db.Column(db.Integer, index=True)     address = db.Cavalcade(db.Cord(256))     telephone = db.Column(db.String(20))     email = db.Column(db.String(120))      def to_dict(self):         render {             'name': self.name,             'age': self.historic period,             'address': self.address,             'phone': self.telephone,             'email': self.email         }

The main endpoint in the application will now render an empty table, so in that location is no demand to pass the user query to the template anymore:

          @app.route('/') def index():     return render_template('ajax_table.html', title='Ajax Table')

A second endpoint needs to be added for the table data. This endpoint will return a JSON payload in the post-obit format:

          {     "information": [         { ... user dict ... },         { ... user dict ... },         ...     ] }

I've decided to put the second endpoint on the /api/data URL. The implementation of this 2nd endpoint is shown beneath:

          @app.road('/api/information') def data():     return {'information': [user.to_dict() for user in User.query]}

The template that renders the table does not need the for-loop that renders all the users anymore, the table is now rendered without whatever information rows.

          {% block content %}   <table id="data" class="table tabular array-striped">     <thead>       <tr>         <th>Proper name</th>         <th>Historic period</th>         <th>Address</th>         <th>Phone Number</th>         <th>Email</thursday>       </tr>     </thead>     <tbody>     </tbody>   </table> {% endblock %}

And finally, the script that attaches dataTables.js to the table needs to pass the ajax option with the URL of the information endpoint, and each column needs the information sub-choice that indicates which of the keys in each element'due south lexicon should be used for that column:

          {% cake scripts %}   <script>     $(certificate).set(function () {       $('#information').DataTable({         ajax: '/api/information',         columns: [           {data: 'name'},           {data: 'age', searchable: false},           {data: 'accost', orderable: faux, searchable: false},           {data: 'phone', orderable: simulated, searchable: false},           {data: 'electronic mail'}         ],       });     });   </script> {% endblock %}

Running the Ajax Table

In the flask-tables repository, the ajax solution described above is defined in the ajax_table.py and templates/ajax_table.html files. You lot tin can run this version of the table as follows:

          python ajax_table.py

As before, you can view the table by navigating to http://localhost:5000 on your browser.

Server-Driven Tables

The ajax tabular array is improve than the basic table because the data is downloaded in the background, subsequently the folio has been loaded. But like the basic solution, this method has the problem that the data is downloaded all in a unmarried request, so information technology is withal not something you can use for a very large table gear up because the information would have too long to download and nothing will display until all the data is downloaded. For really large tables the data may not even fit in the memory of the browser all at once.

The perfect solution, which would work for tables of any size, is for the browser to download rows of the table on demand, as they are needed. With a solution of this type, a table with thousands, or even millions of rows would still work with skilful operation considering the client would be downloading only the few rows information technology needs to brandish. And when the user navigates to a different page, a new request would download the new rows that become visible.

It is a great solution, simply has a large disadvantage. In the bones and ajax tables, the dataTables.js library was able to implement searching and sorting on its ain, because it had access to the entire data fix. If the library will download the data ane folio at a time, and so it won't be able to manage the search filter or the clickable sorting headers. This solution is the hardest of the three to implement, because searching and sorting needs to exist moved to the server on the /api/data endpoint.

The dataTables.js calls this method server-side processing, because it passes the control of the pagination, searching and sorting to the server.

Starting from the ajax solution, the changes in the template to enable the server-side pick are actually very unproblematic. All that needs to be done is add the serverSide: true option to the table:

          {% block scripts %}   <script>     $(document).ready(function () {       $('#data').DataTable({         ajax: '/api/data',         serverSide: true,         columns: [           {data: 'name'},           {data: 'historic period'},           {data: 'address', orderable: false},           {data: 'phone', orderable: false},           {data: 'email'}         ],       });     });   </script> {% endblock %}

When the serverSide choice is enabled, the library volition disable its own processing of the data and volition instead transport the pagination, searching and sorting requirements as query string arguments to the ajax endpoint.

Server-Side Pagination

In my showtime effort at server-side processing I'grand going to show you how to implement pagination. The dataTables.js library will send the showtime and length query string arguments indicating the range of rows that it needs.

Here is the paginated endpoint:

          @app.route('/api/data') def data():     query = User.query      total_filtered = query.count()      # pagination     start = request.args.become('start', type=int)     length = request.args.become('length', blazon=int)     query = query.offset(start).limit(length)      # response     return {         'data': [user.to_dict() for user in query],         'recordsFiltered': total_filtered,         'recordsTotal': User.query.count(),         'draw': request.args.get('depict', type=int),     }

I hope this is non too difficult to follow. The endpoint takes the starting time and length arguments from the request object, and information technology so applies them equally offset and limit filters on the SQLAlchemy query object.

The response that is sent back to dataTables.js consists of a JSON object with 4 keys:

data: a listing of the paginated results. These are obtained by running the query, after it was modified to business relationship for pagination.
recordsFiltered: the total number of rows that match the current search filter. Since searching isn't implemented yet, I'thou setting the total_filtered variable to query.count() before the pagination is practical. For now this is going to be the total number of rows in the tabular array.
recordsTotal: the total number of rows in the table, without because whatsoever filters. I'm calculating this simply by running User.query.count() which but returns the total count of rows in the database table.
draw: an opaque value that dataTables.js sends to the server. It needs to exist returned exactly equally sent, so that the customer tin match a response to the respective request.

This endpoint is functional and can be used, but considering searching and sorting aren't implemented, those options are going appear as if they are not working.

Server-Side Searching

The implementation for search is a flake more involved. The dataTables.js library will send what the user types in the search box in the search[value] query string statement (the brackets are part of the argument name).

In a relational database, a good option to perform searches is the Like operator, which searches using a uncomplicated pattern. Luckily this is integrated with SQLAlchemy. If you desire to search for names that begin with "Chris", the query would be:

          User.query.filter(User.name.like('Chris%'))

The % acts every bit a placeholder, so this query will match users named Chris, Christian and Christina. A % can exist added at the outset likewise:

          User.query.filter(User.proper name.like('%ar%'))

Now the text in between the pct signs can announced anywhere in the name, and so the to a higher place query volition match users named Aaron, Arnold and any others with "ar" anywhere in their names.

In the previous two table implementations the search was non only done in the Name column, the tabular array configuration had both the Name and Email columns as searchable. This can be implemented in SQLAlchemy using the or_ operator:

          User.query.filter(db.or_(     User.name.like('%ar%'),     User.electronic mail.like('%ar%'), ))

This query can be added to the /api/information endpoint right above the total_filtered variable:

          @app.route('/api/data') def data():     query = User.query      # search filter     search = asking.args.go('search[value]')     if search:         query = query.filter(db.or_(             User.proper name.like(f'%{search}%'),             User.email.like(f'%{search}%')         ))     total_filtered = query.count()      # pagination     start = asking.args.get('start', type=int)     length = asking.args.become('length', type=int)     query = query.offset(start).limit(length)      # response     render {         'information': [user.to_dict() for user in query],         'recordsFiltered': total_filtered,         'recordsTotal': User.query.count(),         'draw': request.args.go('depict', type=int),     }

With this version of the endpoint, the total_filtered variable volition be calculated later the search is practical, but earlier pagination, so it will tell the customer how many records match the search. As yous recall, this information is shown in the bottom-left corner of the table.

Server-Side Sorting

The final flake of logic that needs to be added to the /api/information endpoint is the sorting. The client will send the sorting requirements in the following query cord arguments:

society[0][column]: the column index to sort by, naught based.
order[0][dir]: the string asc if sorting in ascending social club, or desc if sorting in descending order.

The table supports sorting by multiple columns as well. Equally a user, you tin select boosted sorting columns by shift-clicking on the sorting headers. The server receives the additional columns with increasing index numbers in the social club arguments. For case, a secondary sorting column will be given in the order[ane][column] and order[i][dir] arguments.

The client too sends the configuration of the columns to the server and this is really handy because information technology can be used to transform the column indexes into column names. For the table configuration used in this project, the post-obit arguments would be sent also every bit query string arguments:

columns[0][data]: set to name
columns[one][information]: fix to age
columns[2][data]: gear up to address
columns[iii][data]: set to phone
columns[iv][data]: set to electronic mail

Using the in a higher place elements from the query cord, hither is a Python snippet that calculates the starting time sorting column:

                      col_index = asking.args.get('order[0][cavalcade]')         col_name = asking.args.become(f'columns[{col_index}][data]')         if col_name not in ['name', 'historic period', 'email']:             col_name = 'proper noun'         descending = request.args.get(f'lodge[0][dir]') == 'desc'         col = getattr(User, col_name)         if descending:             col = col.desc()

This logic is not-trivial, then you may need to read it carefully to understand everything that goes on. The col_index variable is imported from the query string, and then used every bit an index to go the column name into the col_name variable.

But as a matter of security, I brand sure that the column name is one of the three columns that has the orderable selection set. If the server receives whatsoever column proper name that is non one of these, then the name is reset back to proper name. This is as a precaution, as it is not a skilful idea to permit the customer to request sorting by an arbitrary column without whatsoever validation.

The descending variable is so set to a boolean value of True or False co-ordinate to the sorting management.

The last three lines in the snippet obtain the selected column of the User model using getattr, and apply the desc() sorting qualifier if the descending direction was requested. When sorting by proper name ascending, the value of col at the end would be User.proper name. If sorting by age descending, the value of col would be User.age.desc(). This is exactly what the order_by() filter from SQLAlchemy requires as an argument.

Below you lot can see how the sorting snippet above is incorporated into the /api/data endpoint. You will notice that in that location is a little bit more complexity introduced past a while-loop that deals with multiple sorting columns:

          @app.road('/api/data') def information():     query = User.query      # search filter     search = asking.args.get('search[value]')     if search:         query = query.filter(db.or_(             User.proper noun.like(f'%{search}%'),             User.email.like(f'%{search}%')         ))     total_filtered = query.count()      # sorting     order = []     i = 0     while True:         col_index = request.args.get(f'lodge[{i}][cavalcade]')         if col_index is None:             intermission         col_name = asking.args.get(f'columns[{col_index}][data]')         if col_name non in ['proper name', 'age', 'email']:             col_name = 'name'         descending = request.args.become(f'order[{i}][dir]') == 'desc'         col = getattr(User, col_name)         if descending:             col = col.desc()         order.suspend(col)         i += 1     if order:         query = query.order_by(*order)      # pagination     start = asking.args.get('commencement', type=int)     length = request.args.become('length', type=int)     query = query.beginning(beginning).limit(length)      # response     render {         'data': [user.to_dict() for user in query],         'recordsFiltered': total_filtered,         'recordsTotal': User.query.count(),         'draw': request.args.go('describe', type=int),     }

Running the Server-Driven Table

If yous made it all the way here you can congratulate yourself, considering this is the finish, the more advanced implementation of the table is now consummate.

Before y'all endeavour this out, it would be a good idea to add more users to the database, considering this avant-garde implementation really shines when there is a lot of data:

          python create_fake_users.py 1000

Using the flask-tables repository, you can run this version of the table as follows:

          python server_table.py

And once again navigate to http://localhost:5000 on your browser and appreciate how dainty and performant your table is!

If you want to stress-test this implementation, you are welcome to add equally many more than users as you want to the table. The performance of this solution is now driven past how fast your database tin can perform the required queries.

Conclusion

I hope this article was as fun to read as it was for me to write it!

The dataTables.js library has an extensive list of features, many of which I have not covered, so you should read their documentation to learn about everything this library has to offering.

Have you lot used a different method to generate your tables? I'd dear to know so exit a comment beneath.