Building a Flask web app with MongoDB and DataTables

Table of Contents

In modern web applications, handling and visualizing large datasets is essential. This guide walks through creating a Flask-based web application that uses MongoDB for backend data storage and the JavaScript library DataTables to present, filter, and export data. Each section explains the functionality of code snippets that come together to create a robust, user-friendly interface for managing data.

Why Use Flask, MongoDB, and DataTables?
#

Combining Flask, MongoDB, and DataTables provides an efficient and scalable solution for handling large datasets:

Flask is a lightweight Python web framework perfect for building APIs quickly and with minimal setup.
MongoDB is a NoSQL database with a flexible schema design that adapts to growing and changing data needs, making it easy to store and retrieve complex datasets.
DataTables is an interactive JavaScript library that enhances HTML tables by adding features like pagination, sorting, and search. It provides a seamless experience for users to navigate large datasets.

This tutorial will cover the setup of each component, the code for displaying and filtering data and how to implement data export.

Setting Up the Flask App and MongoDB Connection
#

The first step is to create the main application file, app.py, which connects Flask to MongoDB and defines routes for displaying and exporting data.

Code Breakdown: `app.py`
#

The main application, app.py, initializes Flask, connects to MongoDB, and defines routes for viewing and exporting data. Here’s a look at each section of the code:

Imports and MongoDB Connection: We start by importing required libraries. Then we connect to a MongoDB instance and specify the testdb database.

from flask import Flask, render_template, send_file, request
from pymongo import MongoClient
import pandas as pd
from io import BytesIO
from datatable_helpers import (
    get_paginated_filtered_sorted_data,
    get_filtered_and_sorted_data_for_export,
    generate_datatables_script
)

app = Flask(__name__)

# MongoDB connection
client = MongoClient('mongodb://root:example@localhost:27017/')
db = client['testdb']

Route for Displaying Data: The home route (/) renders an HTML page with an interactive table. The table’s columns and the JavaScript needed to initialize DataTables are passed to the template.

@app.route('/')
def mac_db_table():
    columns = ["mac", "name", "switch", "port"]
    table_id = "macTable"
    script = generate_datatables_script("/data/mac_db", columns, table_id=table_id)
    return render_template("index.html", table_title="MAC Database Table", columns=columns, table_id=table_id, script=script)

Data Loading with AJAX: To enable real-time data updates, we use an AJAX endpoint (/data/mac_db). This route responds with filtered and paginated data from MongoDB to keep the DataTables interface responsive.

@app.route('/data/mac_db', methods=['GET'])
def load_mac_db_data():
    columns = ["mac", "name", "switch", "port"]
    search_columns = ["mac", "port"]
    return get_paginated_filtered_sorted_data(db['mac_db'], columns, search_columns=search_columns)

Data Export to Excel: This route exports data to Excel in response to the current filter and sort options applied by the user. The data is sent as an Excel file download.

@app.route('/export/mac_db', methods=['GET'])
def export_mac_db_data():
    columns = ["mac", "name", "switch", "port"]
    search_columns = ["mac", "port"]

    data = get_filtered_and_sorted_data_for_export(db['mac_db'], columns, search_columns=search_columns)
    df = pd.DataFrame(data['data'])
    output = BytesIO()

    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        df.to_excel(writer, index=False, sheet_name="MAC Data")

    output.seek(0)
    return send_file(output, as_attachment=True, download_name="mac_db_filtered.xlsx")

Running the App: Finally, we include a conditional to run the app in debug mode when executed directly.
```
if __name__ == '__main__':
    app.run(debug=True)
```

Handling Data in `datatable_helpers.py`
#

To manage data pagination, filtering, and sorting, we define helper functions in datatable_helpers.py. These functions streamline the main application code and enhance reusability.

Fetching and Filtering Data: get_paginated_filtered_sorted_data handles pagination, filtering, and sorting. It uses DataTables request parameters to fetch a specific subset of data from MongoDB based on the user’s search input and column sorting choices.

import re
import json
from bson.json_util import dumps
from flask import request

def get_paginated_filtered_sorted_data(collection, columns, search_columns=None):
    start = int(request.args.get('start', 0))
    length = int(request.args.get('length', 10))
    draw = int(request.args.get('draw', 1))
    search_value = request.args.get('search[value]', '')

    order_column = int(request.args.get('order[0][column]', 0))
    order_dir = request.args.get('order[0][dir]', 'asc')

    column_map = {i: col for i, col in enumerate(columns)}
    sort_field = column_map.get(order_column, columns[0])
    sort_direction = 1 if order_dir == "asc" else -1

    search_fields = search_columns if search_columns else columns

    query = {}
    if search_value:
        regex = re.compile(search_value, re.IGNORECASE)
        query = {"$or": [{col: regex} for col in search_fields]}

    data_cursor = collection.find(query).sort(sort_field, sort_direction).skip(start).limit(length)
    data = list(data_cursor)

    records_filtered = collection.count_documents(query)
    records_total = collection.count_documents({})

    return {
        "draw": draw,
        "recordsTotal": records_total,
        "recordsFiltered": records_filtered,
        "data": json.loads(dumps(data))
    }

Exporting Data: The get_filtered_and_sorted_data_for_export function applies the current filters and sorting, preparing the data for export as an Excel file.

def get_filtered_and_sorted_data_for_export(collection, columns, search_columns=None):
    search_value = request.args.get('search[value]', '')

    order_column = int(request.args.get('order[0][column]', 0))
    order_dir = request.args.get('order[0][dir]', 'asc')

    column_map = {i: col for i, col in enumerate(columns)}
    sort_field = column_map.get(order_column, columns[0])
    sort_direction = 1 if order_dir == "asc" else -1

    search_fields = search_columns if search_columns else columns

    query = {}
    if search_value:
        regex = re.compile(search_value, re.IGNORECASE)
        query = {"$or": [{col: regex} for col in search_fields]}

    data_cursor = collection.find(query).sort(sort_field, sort_direction)
    data = list(data_cursor)

    return {
        "data": json.loads(dumps(data))
    }

Generating JavaScript for DataTables: The generate_datatables_script function dynamically creates JavaScript to initialize the DataTables component.

def generate_datatables_script(url_path, columns, table_id="dataTable"):
    columns_js = ",\n                ".join([f'{{ "data": "{col}" }}' for col in columns])

    script = f"""
    <script>
        $(document).ready(function() {{
            $('#{table_id}').DataTable({{
                "processing": true,
                "serverSide": true,
                "ajax": {{
                    "url": "{url_path}",
                    "type": "GET"
                }},
                "columns": [
                    {columns_js}
                ],
                "language": {{
                    "paginate": {{
                        "first": "First",
                        "last": "Last",
                        "next": "Next",
                        "previous": "Previous"
                    }},
                    "search": "Search:",
                    "processing": "Loading..."
                }},
                "order": [[0, "asc"]]
            }});
        }});
    </script>
    """
    return script

Frontend Interface in `index.html`
#

The HTML template, index.html, displays the data table with a user-friendly interface. It uses AJAX to load data from the server in real-time and includes a button for data export.

<!DOCTYPE html>
<html lang="en">
<head>
   <meta charset="UTF-8">
   <title>DataTables with Flask and MongoDB</title>
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
   <link rel="stylesheet" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css">
</head>
<body>

<div class="container mt-5">
   <div class="card">
      <div class="card-header">
         <h3 class="card-title">{{ table_title }}</h3>
      </div>
      <div class="card-body">
         <table id="{{ table_id }}" class="table table-striped table-bordered" style="width:100%">
            <thead>
            <tr>
               {% for column in columns %}
               <th>{{ column|capitalize }}</th>
               {% endfor %}
            </tr>
            </thead>
            <tbody></tbody>
         </table>
         <button id="exportButton" class="btn btn-dark">Export to Excel</button>
      </div>
   </div>
</div>

<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>

{{ script|safe }}
<script>
   $('#exportButton').on('click', function() {
      var table = $('#{{ table_id }}').DataTable();
      var searchValue = table.search();
      var order = table.order();

      var params = $.param({
         'search[value]': searchValue,
         'order[0][column]': order[0][0],
         'order[0][dir]': order[0][1]
      });

      window.location.href = "/export/mac_db?" + params;
   });
</script>
</body>
</html>

Generate test data
#

To fill the collection with test records you can use Faker and some generators to populate data.

from pymongo import MongoClient
from faker import Faker
import random

def generate_switchname():
    number = random.randint(10, 99)
    return f"deeeh-swt{number}"


def generate_hostname():
    number = random.randint(10000, 99999)
    return f"srv{number}"


def generate_interface():
    switchunit = random.randint(1, 4)
    port = random.randint(1, 48)
    interf = f"Gi{switchunit}/0/{port}"
    return interf


client = MongoClient('mongodb://root:example@localhost:27017/')
db = client['testdb']
collection = db['mac_db']

fake = Faker()


for _ in range(5000):
    dataset = {
        "mac": fake.mac_address(),
        "name": generate_hostname(),
        "switch": generate_switchname(),
        "port": generate_interface()
    }
    collection.insert_one(dataset)
    print(dataset)

Useful links
#

Conclusion
#

With this setup, we achieve an efficient and interactive web application that allows users to view, filter, sort, and export data seamlessly. Using Flask, MongoDB, and DataTables together offers an optimal solution for applications that require large-scale data management and an intuitive user experience. This framework can easily be extended and customized to support additional features and scale with your application’s data needs.

LiveLab: Automating Excel Reports with Ansible, Flask, Mongodb, TextFSM and Pandas

15 June 2024·2081 words·10 mins