Making a web app that updates a database based on table row selections

Here I will describe a simple example of how to create a web app that uses Flask with DataTables to automatically update a Sqlite database when rows in a displayed table are selected/deselected in the web app.

I am quite new to Flask, DataTables, and Javascript, so one objective of this post is to help me learn how to use these tools and document the process for future reference. The code for this project can also be found on github.

Setup and dependencies

I am using Anaconda python for easy management of environments, so to install flask, I ran the following on the command line.

conda install flask

DataTables (and Bootstrap and jQuery) can be either locally downloaded or used from the DataTables Content Delivery Network (CDN). On the download page, in addition to the default selections, select “Bootstrap 4”, “jQuery3”, “Bootstrap 4”, and “Select”. To download the files locally, select the “Download” tab and click on the link to download the files. Or, the code in the CDN tab (note: for some reason Bootstrap wasn’t working for me when using the CDN so I had to uncheck Bootstrap 4) can be used in the HTML file to be created later.

Next, I set up the file directory structure. Here, I put the flask app in a directory called flask_datatables_example/ and created subdirectories called templates/ and static/.

mkdir flask_datatables_example
cd flask_datatables_example
mkdir templates
mkdir static

If using DataTables/Bootstrap/jQuery locally, copy the downloaded DataTables/ directory into the static/ directory.

Example table of data

For this example, create some simple dummy data in a sqlite database (in the flask_datatables_example/ directory). Create a file setup_table.sql containing:

INSERT INTO my_items values(0, 1);
INSERT INTO my_items values(1, 1);
INSERT INTO my_items values(2, 0);
INSERT INTO my_items values(3, 1);
INSERT INTO my_items values(4, 0);

and then at the command line execute:

sqlite3 data.sqlite < setup_table.sql


Next, create a file in flask_datatables_example/ called, which contains the following python code:

from flask import Flask, render_template, request
import sqlite3
import pandas as pd

# Database file
DB = "data.sqlite"

# Create app
app = Flask(__name__)

# Basic initial home page
def index():
    # Load table from database
    conn = sqlite3.connect(DB)
    conn.row_factory = sqlite3.Row
    df = pd.read_sql("SELECT * FROM my_items", conn)
    # Add empty column for checkboxes
    df[" "] = ""
    # Only display the empty column and the id column
    html_table = df.to_html(escape=False, index=False, justify="left",
                            columns=[" ", "id"])
    # Manually change table id and class.
    # (built-in pandas way doesn't seem to work)
    split_html = html_table.split("\n")
    split_html[0] = """<table id="output" class="display">"""
    html_table = "\n".join(split_html)
    # Determine indices of rows with flag == 1, which will be selected
    selected_rows = df[df["flag"] == 1].index.tolist()
    # Pass html_table and selected_rows to template
    return render_template("index.html", table=html_table,

# Handle selection/deselection events
def update_db_from_app():
    # Get data from request
    selected = int(request.args.get('selected', 0))
    row_data = request.args.get('row_data', 0) # This will be a string
    # Get from row_data string the value in the "id" column.
    # Thus this parsing will be table-dependent.
    id = int(row_data[2:-3].split(",")[1].replace('"', ''))
    # Now update database with the selection/deselection information
    conn = sqlite3.connect(DB)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute("UPDATE my_items SET flag=? WHERE id=?", (selected, id))
    return render_template("index.html")

if __name__ == '__main__':'', port=8000, debug=True)

Next, create a file in flask_datatables_example/templates/ called index.html, which contains the following. Depending on whether you’re using the locally downloaded DataTables/jQuery/Bootstrap or using the CDN, comment or uncomment the appropriate stylesheet and javascript lines.

<!doctype html>
<html lang="en">
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Set SCRIPT_ROOT (see -->
    <script type=text/javascript>
      $SCRIPT_ROOT = {{ request.script_root|tojson|safe }};

    <!-- DataTables, JQuery, Bootstrap -->

    <!-- Uncomment below if using locally downloaded files -->
    <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='DataTables/Bootstrap-4-4.1.1/css/bootstrap.min.css') }}">
    <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='DataTables/DataTables-1.10.18/css/jquery.dataTables.min.css') }}">
    <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='DataTables/Select-1.3.0/css/select.dataTables.min.css') }}">
    <script type="text/javascript" src="{{ url_for('static', filename='DataTables/jQuery-3.3.1/jquery-3.3.1.js') }}"></script>
    <script type="text/javascript" src="{{ url_for('static', filename='DataTables/DataTables-1.10.18/js/jquery.datatables.min.js') }}"></script>
    <script type="text/javascript" src="{{ url_for('static', filename='DataTables/Select-1.3.0/js/') }}"></script>

    <!-- Uncomment below if using CDN. Note: Bootstrap is not used since it wasn't working. -->
    <link rel="stylesheet" type="text/css" href=""/>
    <script type="text/javascript" src=""></script>
    $(document).ready(function() {
      var table = $('#output').DataTable( {
        columnDefs: [ {
            orderable: false,
            className: 'select-checkbox',
            targets:   0
        } ],
        select: {
            style:    'multi',
            selector: 'td:first-child'
        order: [[ 1, 'asc' ]]
      } );
      // Select rows that were specified by flask app.
      // ''selected'' is a javascript array containing indices of selected rows,
      // parsed from selected_rows variable input from flask.
      {% if selected_rows %}
        var selected = JSON.parse('{{ selected_rows | tojson }}');
      {% endif %}

      // Next, automatically log user selection/delection events.
        .on( 'select', function ( e, dt, type, indexes ) {
            var rowData = table.rows( indexes ).data().toArray();
            $.ajax($SCRIPT_ROOT + '/_handle_selection', 
              {data : {selected : 1, row_data : JSON.stringify(rowData)}});
        } )
        .on( 'deselect', function ( e, dt, type, indexes ) {
            var rowData = table.rows( indexes ).data().toArray();
            $.ajax($SCRIPT_ROOT + '/_handle_selection', 
              {data : {selected : 0, row_data : JSON.stringify(rowData)}});
        } );
    } );

    <title>Table Selection Example</title>
    <div class="container">
      <div id="content" class="col-sm">
      Selecting or deselecting items in the table will automatically modify the "flag" column of the data.sqlite database.<br><br>
      {% if table %} 
        {{ table|safe }}
      {% endif %}

Here, the variables table and selected_rows are passed from the python backend to the javascript and HTML frontend. The DataTable javascript event handling functions pass the variables selected and row_data to the python backend.

Testing the web app

Now, to run the app, in the flask_datatables_example/ directory, run the following command:


Then, in your web browser, go to: and you should see your app!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s