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:
CREATE TABLE my_items(id INTEGER, flag INTEGER);
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
Code
Next, create a file in flask_datatables_example/ called app.py, 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__)
app.config.from_object(__name__)
# Basic initial home page
@app.route("/")
@app.route("/index/")
def index():
# Load table from database
conn = sqlite3.connect(DB)
conn.row_factory = sqlite3.Row
df = pd.read_sql("SELECT * FROM my_items", conn)
conn.close()
# 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,
selected_rows=selected_rows)
# Handle selection/deselection events
@app.route("/_handle_selection")
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))
conn.commit()
conn.close()
return render_template("index.html")
if __name__ == '__main__':
app.run(host='0.0.0.0', 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">
<head>
<!-- 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 http://flask.pocoo.org/docs/1.0/patterns/jquery/) -->
<script type=text/javascript>
$SCRIPT_ROOT = {{ request.script_root|tojson|safe }};
</script>
<!-- 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/dataTables.select.min.js') }}"></script>
-->
<!-- Uncomment below if using CDN. Note: Bootstrap is not used since it wasn't working. -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.3.1/dt-1.10.18/sl-1.3.0/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.3.1/dt-1.10.18/sl-1.3.0/datatables.min.js"></script>
<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.
// https://datatables.net/reference/type/row-selector
// https://stackoverflow.com/questions/11178426/how-can-i-pass-data-from-flask-to-javascript-in-a-template
{% if selected_rows %}
var selected = JSON.parse('{{ selected_rows | tojson }}');
table.rows(selected).select();
{% endif %}
// Next, automatically log user selection/delection events.
// http://flask.pocoo.org/docs/1.0/patterns/jquery/
// https://datatables.net/extensions/select/examples/api/events.html
table
.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)}});
} );
} );
</script>
<title>Table Selection Example</title>
</head>
<body>
<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 %}
</div>
</div>
</body>
</html>
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:
python app.py
Then, in your web browser, go to: http://127.0.0.1:8000/ and you should see your app!
Alta Fang 