How to implement server side pagination with Flask and DataTables

My application is written with the Flask framework. I also employed jQuery DataTables to draw tables. However, how can I do a server side pagination with Flask and jQuery DataTables? I found a cgi solution but doesn't seem to fit my application well. I'd like DataTables to use Flask HTTP APIs via Ajax.
condor888Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott FellDeveloper & EE ModeratorCommented:
I do not know python, but for datatables you want to use ajax https://datatables.net/examples/ajax/index.html
condor888Author Commented:
Yes, I understand, but I'd like to do server side pagination with Ajax, any idea? Thanks!
Scott FellDeveloper & EE ModeratorCommented:
The way it works is if your server side outputs 500 records, then that 500 gets sent to the client and the pagination is based on the 500 records. You can use the datatables api to create the increments of how many records to show at a time (10, 20, 50, 100, all etc)

If you have a very large amounts of data, then you will still use ajax, but set serverside processing to true.  http://datatables.net/release-datatables/examples/data_sources/server_side.html
With server-side processing enabled, all paging, searching, ordering actions that DataTables performs are handed off to a server where an SQL engine (or similar) can perform these actions on the large data set (after all, that's what the database engine is designed for!). As such, each draw of the table will result in a new Ajax request being made to get the required data.

Another option is to have a query that determines total amount of records (or cache that).  let's say you have 100,000 records and you only want to push 500 out at a time.  Then create server side generated pagination for the 200 pages.

I think the built in server side function for datatables is the way to go.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

condor888Author Commented:
Hi Scott, I went to the doc page that you pointed to. But it seems that the PHP code is missing... Is there any code samples to demo how it works?

In my case, I have a huge dataset, so only part of the data should be returned each time. Thanks!
Scott FellDeveloper & EE ModeratorCommented:
This is a better url http://datatables.net/examples/data_sources/server_side.html  Now you can click on the serverside link to reveal the below code.

<?php
 
/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */
 
// DB table to use
$table = 'datatables_demo';
 
// Table's primary key
$primaryKey = 'id';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'first_name', 'dt' => 0 ),
    array( 'db' => 'last_name',  'dt' => 1 ),
    array( 'db' => 'position',   'dt' => 2 ),
    array( 'db' => 'office',     'dt' => 3 ),
    array(
        'db'        => 'start_date',
        'dt'        => 4,
        'formatter' => function( $d, $row ) {
            return date( 'jS M y', strtotime($d));
        }
    ),
    array(
        'db'        => 'salary',
        'dt'        => 5,
        'formatter' => function( $d, $row ) {
            return '$'.number_format($d);
        }
    )
);
 
// SQL server connection information
$sql_details = array(
    'user' => '',
    'pass' => '',
    'db'   => '',
    'host' => ''
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
condor888Author Commented:
Hi Scott, sorry about the late reply. I checked the code that you sent. It's rather a server-side processing than a server-side pagination. So I am afraid this is not a proper example for the pagination...
Scott FellDeveloper & EE ModeratorCommented:
Actually, it is both.

Datatables will take in a specified number of records when you use server side processing/ajax. (See "Draw" in the api https://www.datatables.net/manual/server-side) and as needed will grab more data.

If you have 500 records and you tell datatables to takin all 500, then the pagination is built into datatables and his handled by the client. You can even select the number of rows to show at a time (10, 20, 50, all etc) and that creates your pages.

If you have 10,000 records and set 100 to come in at a time, datatables will automatically cache 100 at a time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
jQuery

From novice to tech pro — start learning today.