Beautiful Interactive Tables for your Flask Templates

Posted by

on under

Rendering a table with data in a Flask template is a relatively simple task when the table is short, but can be incredibly hard for larger tables that require features such as sorting, pagination and searching. In this article I'm going to show you how to integrate the dataTables.js library in your templates, which will allow you to create fully featured tables with ease!

How to Get the Code

All the code presented in this article comes from my flask-tables repository on GitHub. I will only be showing the interesting snippets here, 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.

Update: I have now released an update to this article, including support for editing table cells. Check it out here!

How to Render a Table in Flask

I am going to start from the beginning, so the first step is to create a small Flask application that renders a plain table.

The table is going to contain contain information about users. Here is the SQLAlchemy model that I'm going to use for the database:

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

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

@app.route('/')
def index():
    users = User.query
    return render_template('bootstrap_table.html', title='Bootstrap Table',
                           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 rest of the page.

Here 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</th>
        <th>Email</th>
      </tr>
    </thead>
    <tbody>
      {% for user in users %}
        <tr>
          <td>{{ user.name }}</td>
          <td>{{ user.age }}</td>
          <td>{{ user.address }}</td>
          <td>{{ user.phone }}</td>
          <td>{{ user.email }}</td>
        </tr>
      {% endfor %}
    </tbody>
  </table>
{% endblock %}

I hope you agree that there isn't much going on in this template. The <table> element is created with two sections: a header (inside the <thead> element) and a body (inside <tbody>). The contents in both sections are rows of data, either table headers or actual users. The table 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'm using template inheritance. This is because I want 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 below:

<!doctype html>
<html>
  <head>
    <title>{{ title }}</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-+0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7+AMvyTG2x" crossorigin="anonymous">
  </head>
  <body>
    <div class="container">
      <h1>{{ title }}</h1>
      <hr>
      {% block content %}{% endblock %}
    </div>
    {% block scripts %}{% endblock %}
  </body>
</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> element at the top of the page, and creates two blocks content and scripts for the derived template to use.

Running the Bootstrap Table

To be able to test the application, 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 fake users."""
    faker = Faker()
    for i in range(n):
        user = User(name=faker.name(),
                    age=random.randint(20, 80),
                    address=faker.address().replace('\n', ', '),
                    phone=faker.phone_number(),
                    email=faker.email())
        db.session.add(user)
    db.session.commit()
    print(f'Added {n} fake users to the database.')


if __name__ == '__main__':
    if len(sys.argv) <= 1:
        print('Pass the number of users you want to create as an argument.')
        sys.exit(1)
    create_fake_users(int(sys.argv[1]))

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

An interesting little trick that I'm 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 have cloned the flask-tables repository and set up a virtual environment with all the dependencies, you can now create a database with a handful of random users with the following command:

python create_fake_users.py 5

And then you can run the Bootstrap table application:

python bootstrap_table.py

If you navigate to http://localhost:5000 on your web browser, you should see a nice table with five rows.

Adding dataTables.js

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

So here is where the dataTables.js library enters the picture. This library runs in the browser and attaches to a <table> element to enhance it.

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

<!doctype html>
<html>
  <head>
    <title>{{ title }}</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.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/1.10.25/css/dataTables.bootstrap5.css">
  </head>
  <body>
    <div class="container">
      <h1>{{ title }}</h1>
      <hr>
      {% 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.net/1.10.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> section I've added the dataTables.bootstrap5.css stylesheet. The dataTables.js library provides styles that are compatible with several CSS frameworks, so you have to use the correct CSS file here. For Bootstrap, there are styles for versions 3, 4 and 5. If you don't use Bootstrap, it also provides styles for tables built with Foundation, jQuery UI, Bulma and a few other CSS frameworks. If you don't use any CSS frameworks, it also provides a set of standalone styles.

At the bottom of the <body> element I have added a few scripts. First there's the jQuery library, which is a required dependency of dataTables.js. Next is the core dataTables.js library, which is called jquery.dataTables.js. The third and last script is called dataTables.bootstrap5.js, and provides the custom logic that applies to my chosen Bootstrap 5 integration. If you use a different styling framework you will need to change this last script accordingly.

At the very end of the body the base template keeps the scripts block, included there to give derived templates the chance to add their own scripts. This is going to be 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 table is to call the DataTable() function on it. Using the Bootstrap table example as a starting point, the only change that needs to be made is to add a short script in the template to call this function:

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

The $(document).ready() function comes from jQuery and is used to tell the browser to execute the function passed as an argument after the page finished loading. The $('#data') expression is a jQuery selector that retrieves the element with the id attribute set to data, in other words, the table that is rendered in the Jinja template. The DataTable() function from dataTables.js modifies this table in place, as you will see in a moment.

Running the Basic Table

Before you try this example out, it would be a good idea to add a few more users to the database. Let's add 100 more:

python create_fake_users.py 100

There should now be 105 users in the database, which is a nice size to experience the pagination feature. If you have the flask-tables repository cloned, this example is called basic_table.py and you can start it as follows:

python basic_table.py

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

This table implements all the features you would expect, including pagination (bottom right corner of the table), searching (top-right) and sorting (table headers). In addition, in the top-left there is a dropdown where you can select how many rows are displayed per page, and in the bottom-left corner you can 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 do any additional work besides rendering the table!

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

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

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

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

Adding an Ajax Data Source

You may be wondering why I called the above table the "basic" table, given that it has all the features you may wish to have in a table. The problem with this way 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 amount of time rendering it, 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 see the original table rendered on the page for a short moment before the enhancements are applied, just because of the number of rows that need to be hidden as a result of the pagination.

While the basic solution from the previous section is conveniently simple, it only works for tables that are not very long. A better approach would be to render the table without any rows, and then have the browser request the data that goes in the table asynchronously through a separate request.

This obviously requires a more involved integration, but it is still relatively low effort. The first 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 dictionary that can be serialized to JSON:

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

    def to_dict(self):
        return {
            'name': self.name,
            'age': self.age,
            'address': self.address,
            'phone': self.phone,
            'email': self.email
        }

The main endpoint in the application will now render an empty table, so there is no need 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 following format:

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

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

@app.route('/api/data')
def data():
    return {'data': [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 any data rows.

{% block content %}
  <table id="data" class="table table-striped">
    <thead>
      <tr>
        <th>Name</th>
        <th>Age</th>
        <th>Address</th>
        <th>Phone Number</th>
        <th>Email</th>
      </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 data endpoint, and each column needs the data sub-option that indicates which of the keys in each element's dictionary should be used for that column:

{% block scripts %}
  <script>
    $(document).ready(function () {
      $('#data').DataTable({
        ajax: '/api/data',
        columns: [
          {data: 'name'},
          {data: 'age', searchable: false},
          {data: 'address', orderable: false, searchable: false},
          {data: 'phone', orderable: false, searchable: false},
          {data: 'email'}
        ],
      });
    });
  </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 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 table is better than the basic table because the data is downloaded in the background, after the page has been loaded. But like the basic solution, this method has the problem that the data is downloaded all in a single request, so it is still not something you can use for a very large table set because the data would take 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 good performance because the client would be downloading only the few rows it needs to display. 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, but has a big disadvantage. In the basic and ajax tables, the dataTables.js library was able to implement searching and sorting on its own, because it had access to the entire data set. If the library will download the data one page at a time, then 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 be 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 option are actually very simple. 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: 'age'},
          {data: 'address', orderable: false},
          {data: 'phone', orderable: false},
          {data: 'email'}
        ],
      });
    });
  </script>
{% endblock %}

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

Server-Side Pagination

In my first attempt at server-side processing I'm going to show you how to implement pagination. The dataTables.js library will send the start 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.get('start', type=int)
    length = request.args.get('length', type=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('draw', type=int),
    }

I hope this is not too hard to follow. The endpoint takes the start and length arguments from the request object, and it then applies them as offset and limit filters on the SQLAlchemy query object.

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

data: a list of the paginated results. These are obtained by running the query, after it was modified to account for pagination.
recordsFiltered: the total number of rows that match the current search filter. Since searching isn't implemented yet, I'm setting the total_filtered variable to query.count() before the pagination is applied. For now this is going to be the total number of rows in the table.
recordsTotal: the total number of rows in the table, without considering any filters. I'm calculating this simply by running User.query.count() which just returns the total count of rows in the database table.
draw: an opaque value that dataTables.js sends to the server. It needs to be returned exactly as sent, so that the client can match a response to the corresponding request.

This endpoint is functional and can be used, but because 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 bit more involved. The dataTables.js library will send what the user types in the search box in the search[value] query string argument (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 simple pattern. Luckily this is integrated with SQLAlchemy. If you want to search for names that begin with "Chris", the query would be:

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

The % acts as a placeholder, so this query will match users named Chris, Christian and Christina. A % can be added at the start as well:

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

Now the text in between the percent signs can appear anywhere in the name, so the above query will match users named Aaron, Arnold and any others with "ar" anywhere in their names.

In the previous two table implementations the search was not only done in the Name column, the table 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.email.like('%ar%'),
))

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

@app.route('/api/data')
def data():
    query = User.query

    # search filter
    search = request.args.get('search[value]')
    if search:
        query = query.filter(db.or_(
            User.name.like(f'%{search}%'),
            User.email.like(f'%{search}%')
        ))
    total_filtered = query.count()

    # pagination
    start = request.args.get('start', type=int)
    length = request.args.get('length', type=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('draw', type=int),
    }

With this version of the endpoint, the total_filtered variable will be calculated after the search is applied, but before pagination, so it will tell the client how many records match the search. As you recall, this information is shown in the bottom-left corner of the table.

Server-Side Sorting

The last bit of logic that needs to be added to the /api/data endpoint is the sorting. The client will send the sorting requirements in the following query string arguments:

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

The table supports sorting by multiple columns as well. As a user, you can select additional sorting columns by shift-clicking on the sorting headers. The server receives the additional columns with increasing index numbers in the order arguments. For example, a secondary sorting column will be given in the order[1][column] and order[1][dir] arguments.

The client also sends the configuration of the columns to the server and this is actually handy because it can be used to transform the column indexes into column names. For the table configuration used in this project, the following arguments would be sent also as query string arguments:

columns[0][data]: set to name
columns[1][data]: set to age
columns[2][data]: set to address
columns[3][data]: set to phone
columns[4][data]: set to email

Using the above elements from the query string, here is a Python snippet that calculates the first sorting column:

        col_index = request.args.get('order[0][column]')
        col_name = request.args.get(f'columns[{col_index}][data]')
        if col_name not in ['name', 'age', 'email']:
            col_name = 'name'
        descending = request.args.get(f'order[0][dir]') == 'desc'
        col = getattr(User, col_name)
        if descending:
            col = col.desc()

This logic is non-trivial, so 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 as an index to get the column name into the col_name variable.

Just as a matter of security, I make sure that the column name is one of the three columns that has the orderable option set. If the server receives any column name that is not one of these, then the name is reset back to name. This is as a precaution, as it is not a good idea to allow the client to request sorting by an arbitrary column without any validation.

The descending variable is then set to a boolean value of True or False according to the sorting direction.

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 name ascending, the value of col at the end would be User.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 can see how the sorting snippet above is incorporated into the /api/data endpoint. You will notice that there is a little bit more complexity introduced by a while-loop that deals with multiple sorting columns:

@app.route('/api/data')
def data():
    query = User.query

    # search filter
    search = request.args.get('search[value]')
    if search:
        query = query.filter(db.or_(
            User.name.like(f'%{search}%'),
            User.email.like(f'%{search}%')
        ))
    total_filtered = query.count()

    # sorting
    order = []
    i = 0
    while True:
        col_index = request.args.get(f'order[{i}][column]')
        if col_index is None:
            break
        col_name = request.args.get(f'columns[{col_index}][data]')
        if col_name not in ['name', 'age', 'email']:
            col_name = 'name'
        descending = request.args.get(f'order[{i}][dir]') == 'desc'
        col = getattr(User, col_name)
        if descending:
            col = col.desc()
        order.append(col)
        i += 1
    if order:
        query = query.order_by(*order)

    # pagination
    start = request.args.get('start', type=int)
    length = request.args.get('length', type=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('draw', type=int),
    }

Running the Server-Driven Table

If you made it all the way here you can congratulate yourself, because this is the end, the more advanced implementation of the table is now complete.

Before you try this out, it would be a good idea to add more users to the database, because this advanced 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 nice and performant your table is!

If you want to stress-test this implementation, you are welcome to add as many more users as you want to the table. The performance of this solution is now driven by how fast your database 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 offer.

Update: I have now switched to grid.js as my favorite table library to use with Flask. I have written an article about it here.

Have you used a different method to generate your tables? I'd love to know so leave a comment below.

Become a Patron!

Hello, and thank you for visiting my blog! If you enjoyed this article, please consider supporting my work on this blog on Patreon!

96 comments

#51 kurt said 2022-01-18T13:44:49Z

Hi Miguel, and thank you for this great tutorial! It's in deed beautiful!
I've been using both the ajax data source on a small table, and now I wanted to use the server side on a larger table. I manage to get the server side to work using your code, But in my project I'm using BluePrint to separate the view's and I've also got a model per view and it works fine interacting with the small tables. However, when I try to run the server side DataTable in my project, importing the model class in the views file, I get an exception saying function object has no attribute query when I initialize the query structure with query = Projections.query. Projections is my model. You have any idea what I'm doing wrong? Do you have an example using BluePrints?
Kind regards
Kurt
#52 Miguel Grinberg said 2022-01-18T14:15:27Z

@kurt: I don't have enough context about your project to be sure, but the "query" attribute only exists when your models are created through Flask-SQLAlchemy. This is not available on plain SQLAlchemy models. The error suggests that you may be using models that are not from Flask-SQLAlchemy. In that case, you should just create your queries in the way that you normally do, you do not need to use the query attribute as I do.
#53 Ben said 2022-02-04T13:42:10Z

Amazing tutorial! I have one problem I can't seem to fix, when I refresh the web page each row in my table content duplicates. This happens every time I refresh, is there any reason you can imagine for this?

I have checked the database and the content is not duplicating in there. I'm using the Ajax version. When I close the python file and restart the program everything is back to normal again until I hit refresh.
#54 Miguel Grinberg said 2022-02-05T11:04:21Z

@Ben: is this with your code or with my code? Because I do not see the duplication when I refresh the application shown in this article. If this is with your code, I can't really tell you. It has to be a bug that you introduced.
#55 Don Wilkie said 2022-02-16T00:21:10Z

Hey Miguel,

I was wondering if you have implemented a version of this where you could make the data editable?

Thank you for all your great content!
#56 Miguel Grinberg said 2022-02-16T12:04:29Z

@Don: I have not. This is more JavaScript than Python, so I did not believe to be in scope for this article. You can look for DataTables tutorials that show how to edit cells.
#57 Marco said 2022-03-10T16:22:25Z

Hola Miguel.
I started using your helpful indications, with just one difference: no sqlalchemy, no sqlite: I populate the tables from Firebird tables; with no problem.
I'd like to extract (on client side) only the visible rows after filtering, in a usable format (csv, json, xlsx...), but really i'm not able to achieve this result.
I dont find how to write a template from which to extract (with some request.list i suppose) what i'm looking for.
Any help?
Thanks in advance
#58 Miguel Grinberg said 2022-03-10T17:28:31Z

@Marco: And you want to do this in the client? The normal thing to do would be to have the server to filter and then generate the csv, which then the client can download.

#59 Marco Meyerer said 2022-03-17T12:51:33Z

Hi Miguel,

i change something in the last version. Now i use pandas/dataframe. Maybe you like it.

def data():
    qry_new = pd.read_sql_query(
    sql = db.select([User.id,
                     User.name,
                     User.age,
                     User.address,
                     User.phone,
                     User.email]),
    con = db.engine)
    #qry_new = selectwnk()
    total_filtered_total = int(qry_new['name'].count())
    # search filter
    search = request.args.get('search[value]')
    if search:
        qry_new['name'] = qry_new['name'].astype(str)
        qry_new['email'] = qry_new['email'].astype(str)
        qry_new = qry_new[qry_new['name'].str.contains(search) | qry_new['email'].str.contains(search)]
    total_filtered = int(qry_new['name'].count())
    # sorting
    i = 0
    while True:
        col_index = request.args.get(f'order[{i}][column]')
        if col_index is None:
            break
        col_name = request.args.get(f'columns[{col_index}][data]')
        if col_name not in ['name', 'age', 'email']:
            col_name = 'name'
        descending = request.args.get(f'order[{i}][dir]') == 'desc'
        if descending:
            qry_new = qry_new.sort_values(by=col_name, ascending=False)
        else:
            qry_new = qry_new.sort_values(by=col_name, ascending=True)
        i += 1

    # pagination
    start = request.args.get('start', type=int)
    length = request.args.get('length', type=int)
    query = qry_new[start:length]
    # response
    return {
        'data': query.to_dict(orient='records'),
        'recordsFiltered': total_filtered,
        'recordsTotal': total_filtered_total,
        'draw': request.args.get('draw', type=int),
    }

#60 Jorn said 2022-03-18T11:13:58Z

Hi, I'm trying to get records from a database though the second endpoint of the ajax solution. But I keep getting ajax datatable errors. A single record DOES work. Could you explain this a bit more? {'data': [user.to_dict() for user in User.query]} is this a loop? Because I create a dataframe or dictionary with multiple records and then return it to ajax. (I'm a bit new to this :)
#61 Jorn said 2022-03-18T11:47:21Z

after a couple of days I found the problem , so you can ignore my previous question :).
throwtoajax = df.to_dict('records') -> return {'data': throwtoajax} works. It really has to be a dictionary with a very strict type of layout (records). Thanks for the initial tutorial!
#62 Miguel Grinberg said 2022-03-18T11:47:27Z

@Jorn: Yes, that expression contains a loop. This is called a "list comprehension" in Python, you may want to look it up if you are not familiar with it. I suggest you print the working and non-working payloads and compare them to find what's the problem.
#63 Carl Rodgers said 2022-03-26T22:10:12Z

Hi Miguel, this is excellent! Is it possible to configure the server-driven table to read from a pre-existing database? I've tried multiple times to create a database in SQLite to mimic the users database but when I run server_tables.py (configured to my database) I receive the response "No data available in table". Everything else renders fine. Any help with this would be greatly appreciated!
#64 Miguel Grinberg said 2022-03-26T23:24:06Z

@Carl: the example in this article reads users from a sqlite database. How is what you are trying to do different?
#65 Kurt said 2022-04-06T09:45:31Z

Hi Miguel, any chance you can include an example with a check box column and showing the jquery part for extracting the required information from the selected check boxes upon a button click?
#66 Saulo said 2022-04-06T22:07:35Z

Thanks for the tutorial.
I've been using a Bootstrap table, but due to the number of records, I need to change to a server-driven table. But, I need to be able to edit the information in some rows. It is possible to do that with server-driven tables?
#67 Miguel Grinberg said 2022-04-06T23:19:20Z

@Kurt: Sorry, I can't really implement every single variant people ask me about. The datatables library has extensive documentation, you should be able to learn how to do this yourself.
#68 Miguel Grinberg said 2022-04-06T23:26:29Z

@Saulo: There is an editor plugin for datatables, but it is paid.
#69 Alberto said 2022-05-29T04:30:17Z

Miguel thanks a lot! I really learn a lot from all your tutorials, they are amazing! I could implement this in my own web app development. I have my own DataBase with more than 2,000,000 records, so i need to make complex filters using data input from Flask Form.

How could i pass / get the input data form, to the api/data end point in the view function? So the query could be filtered with my own data filter selection, maybe a combination of 6 or more fields (date, state name, source, etc...)

I could make this, with the basic table, i just wait for if form.validate_on_submit(): and then send the data to a function with the query logic, but i can't do it with the api/data end point.
#70 Miguel Grinberg said 2022-05-29T10:13:35Z

@Alberto: You can pass any arguments that you need in the URL that you set in the ajax argument to datatables. You can encode all your form fields as a query string, for example.
#71 Alberto said 2022-05-29T17:06:58Z
Miguel, i'm trying to set the URL, by the ajax arguments data tables, but nothing is happen. I figure out that i'm not doing the relationship between the Flask Form and the Java Script code in the base template, to encode my form fields as a query strings.

Here is my view function:

@app.route('/ajax_form_db_queries', methods=['GET', 'POST'])
def ajax_form_db_queries():
form = Filters() #this is the form declared
title='Data Base Queries'
```
if form.validate_on_submit():    #when submit all the forms field, then render ajax_table.html      
    return render_template('ajax_table.html', title='Ajax Table', form=form)
else:
    return render_template('database_queries.html', title=title, form=form)
```
The las script in my base template:

{#Ajax Table#}

<script> $(document).ready(function () { $('#dataAjax').DataTable({ ajax: '/api/data', serverSide: true, columns: [ {#data from my db.model#} {data: 'created_date'}, {data: 'user_screen_name'}, {data: 'text', orderable: false}, {data: 'url', orderable: false}, {data: 'state_name'}, {data: 'state_classification_method'}, {data: 'search_method'}, {#data from the form input#} {data: 'form.state_name.data'} ], }); }); </script>

Here is my ajax_table-html version, where my table ID = dataAjax and form ID = dataAjax_form:

{% extends "base.html" %}
{% import "bootstrap/wtf.html" as wtf %}

{% block app_content %}

{%if form%}

<div class="container"> <h3>Consulta-Búsqueda en Base de Datos</h3> <div class="row"> <div class='col-sm-3'> {# chang form width#} <form id="dataAjax_form" method="post"> {{ wtf.quick_form(form) }} </form>
</div> </div> </div>

{% endif %}
<table id="dataAjax" class="table table-striped"> <thead> <tr> <th>CreatedDate</th> <th>UserName</th> <th>Text</th> <th>Url</th> <th>State</th> <th>StateClassMethod</th> <th>Source</th> </tr> </thead> <tbody> </tbody> </table>
{% endblock %}
#72 Miguel Grinberg said 2022-05-29T22:09:59Z

@Alberto: I don't understand what you are trying to do here. You will not be submitting the form anymore. You will need to write some JavaScript code that takes the values in all the fields in your form and adds them to the ajax URL. You can't submit the form and at the same time have the datatables library submit a request for data separately, everything needs to go out to the server in the same request.
#73 Alberto said 2022-05-30T02:00:48Z

I really appreciate for the time you took to reply! I was a little confused because i'm a beginer in the WEB development area. So i understand:

1) I don't have to send/submit the form anymore.
2) Instead i have to work with JavaScript code that getvthe values from the form fields.
3) Then this values, must be add them to the ajax URL.
4) And for last, in the 'api/data' end point, i must request this values in the same way as the column values and search box.

When i use the search box, the dataTables.js library send the user input as query string argument in automatic way. But in my case, with the JavaScripts code, i must figure out, how to get the data form and generate new query strins arguments to add them at the ajax URL.

Is that correct? Again, thanks a lot!
#74 Miguel Grinberg said 2022-05-30T23:18:29Z

@Alberto: You are assuming I'm giving you detailed instructions that apply to your case. I really can't do that, as I do not know all the details. I'm just giving you general guidelines that you will need to adapt to your particular situation. The important part if that your Ajax URL needs to have your form fields, because you will need to have these fields in the Ajax endpoint that generates the rows for your table. How do these fields get there really depends. If the form is in the same page as the table, then you can get the field values using JavaScript and add them to the Ajax URL. If the fields are in a form that needs to be submitted before the table is displayed, then maybe you can insert the fields into the Ajax URL when you render the table template. There are several ways this can be done, it depends on what you want to do.
#75 Alberto said 2022-06-01T17:49:06Z

Thanks for your general guidelines, i have to read a bit more, to be able to adapt the Ajax URL with my form fields and my particular needs. Best regards Miguel.