DataTables logo
≡

Reading data

The DataTable class provides a read only interface to a database table, reading the data from the columns configured and returning it to the client-side for display in response to a DataTables ajax request. It also supports server-side processing for large data sets and advanced filtering options for ColumnControl and SearchBuilder.

Basic initialisation

In this documentation we'll assume a db variable is available with the SQLAlchemy Core connection, per the installing documentation.

Construct a JS DataTable instance using:

import { DataTable, Column } from 'datatables-server';

table = DataTable(db, tableName)

Where:

  • db is the SQLAlchemy Core database connection
  • tableName is the database table name that you want to read data from

Primary key name

It can often be useful to be able to uniquely identify rows in a DataTable (e.g. for row selection, or performing actions on the data). This can be done by telling the DataTable class what your table's primary key is with the optional third parameter (by default it will assume id is the column name):

table = DataTable(db, "staff", "staffId")

An array can also be given for the third parameter if your table has a compound key.

Columns

Once the DataTable instance has been created and configured for what database table it should read from, it needs to be instructed what columns it should read data from and send back to the client. This is done through the DataTable.columns() method which is passed Column class instances. The columns() method can take as many Column instances as you wish to define and can also be called multiple times to add additional columns.

In the example below the DataTable instance is configured with five simple columns.

table = DataTable(db, "staff").columns(
    Column("first_name"),
    Column("last_name"),
    Column("position"),
    Column("salary")
)

Column instances also provide the ability to operate on columns for filtering via ColumnControl and SearchBuilder as needed. Please refer to the documentation for those sections for detailed information.

Parameter names

By default the column name given as the first parameter to the Column instance constructor will also be the name that the column is given in the JSON sent to the client, and the name of the column that the libraries will look for in the data submitted to the server from the Editor form. This name (JSON and submitted data) can be modified using an optional second parameter for the Column constructor - e.g.:

Column( "first_name", "fname" )

This can be useful if you wish to obfuscate the SQL columns names so the client will never see the actual database naming, or simply want to reduce the size of the JSON object transmitted to the client.

SQL functions

While you will usually wish to just obtain data from SQL columns, as shown above, it can also be useful to use SQL functions for certain operations. The Column instance can be specified with an SQL function - this is found in the libraries simply by the use of parenthesis. In this case you will likely wish to use the optional second parameter to name the data in the JSON sent to the client.

In the following example, the MySQL function timestampdiff is used (note that in other SQL dialects, functions can also be used). The method is used to calculate the difference between a field (start_date) and now, in days, then naming the field as ago in the JSON sent to the server.

Column( "timestampdiff(DAY, now(), start_date)", "ago" )

Data processing

When DataTables and Editor make a request to the server to get or update data, they send the data using HTTP parameters (POST is the default for Editor). Exactly how you will be able to access this information in your Python application will depend upon the web-server framework you are using, but typically in Flask and similar frameworks they are in request.values.

The data is processed using the process() method of the DataTable class, with the data sent from the client being passed into it. At this point the DataTable instance will perform whatever action the client-side has asked of it - be it to create a new row, update an existing row, etc. These actions will be automatically detected.

table = DataTable(db, "staff").columns(
    Column("first_name"),
    Column("last_name"),
    Column("position"),
    Column("salary")
)

table.process(request.get_json(silent=True) or request.form)

Return data

The final step is to send the data back to the client for it to process (e.g. redraw the table with the updated data if everything was successful or display an error message if validation failed). The data sent back to the client should be in the JSON data format. The DataTable.data() method will give you an object with the data to be sent to the client. Exactly how you return it to the client will depend upon the server / framework you are using, but in Flask you would use:

return jsonify(editor.data().to_dict())

Complete Flask example

Based on the above, and using a Flask router, we've build the following full example:

from __future__ import annotations
from flask import Blueprint, jsonify, request
from datatables_server import DataTable, Column
from db import get_db

bp = Blueprint("staff", __name__)

@bp.route("/api/staff", methods=["GET", "POST"])
def staff():
    with get_db() as conn:
        table = DataTable(db, "staff").columns(
            Column("first_name"),
            Column("last_name"),
            Column("position"),
            Column("salary")
        )
        editor.process(request.get_json(silent=True) or request.form)
        return jsonify(editor.data().to_dict())

And that's it! This will now read data from the database and send it to the client-side for display.