Using query to fetch large data and display in HTML table with flask in Python

user_14603892 0
Viewed 2393

I have a table with 229,000 rows in sql server and I try to use select command and also Flask (in python) to show recorded data in HTML. I must show all the records value of the table in my HTML table, so that relative team can see all of it's data. My code seems as below: The problem is that however I use pagination in this regard, but it takes so much time to load the table and some times the browser would freeze and stop working. I appreciate if anyone can guide me in this regards?

sql.py

from datetime import datetime 
from flask import Flask , render_template, request
import pyodbc   
import pypyodbc 
import os
from waitress import serve
from flask import render_template, redirect, request    

app = Flask(__name__)


@app.route('/index', methods=['GET', 'POST'])
def ShowResult():
    # creating connection Object which will contain SQL Server Connection    
    connection = pypyodbc.connect('Driver={SQL Server}; Server=Server; Database=DB; UID=UserID; PWD= {Password};')# Creating Cursor    
    
    cursor = connection.cursor()    
    cursor.execute("""select A,B,C,D from TABLE""")    
    Result=cursor.fetchall()
    return render_template('index.html', Result=Result)


if __name__ == '__main__':
    serve(app,port=5009)

index.html

<body oncontextmenu='return false' class='snippet-body'>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.2/css/jquery.dataTables.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js"></script>
    <div class="container">
        <div class="row header" style="text-align:center;color:green">
            <h3>Bootstrap table with pagination</h3>
        </div>
        <table id="example" class="table table-striped table-bordered" style="width:100%;font-family: tahoma !important;">
          <thead>
                    <tr>
                    <th>A</th>
                    <th>B</th>
                    <th>C</th>
                    <th>D</th>
                    </tr>
                    </thead>
                     
                    <tbody>
                    {% for row in Result %}  
                    <tr>
                    <td>{{row[0]}}</td>
                    <td>{{row[1]}}</td>
                    <td>{{row[2]}}</td>
                    <td>{{row[3]}}</td>
                    <td>{{row[4]}}</td>
                    <td style="word-break:break-all; width: 200px;"><p style="word-wrap: break-word;  word-wrap: break-word; width: 200px;">{{row[5]}}</p></td>
                    </tr>
                    {% endfor %}
                    </tbody>
        </table>
    </div>
                    <script type='text/javascript' src='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js'></script>
                    <script type='text/javascript'>
                    //$(document).ready(function() {
    //$('#example').DataTable();
    $(document).ready(function() {
        $('#example').DataTable( {
            serverSide: true,
            ordering: false,
            searching: false,
            ajax: function ( Result, callback, settings ) {
                var out = [];
     
                for ( var i=Result.start, ien=Result.start+Result.length ; i<ien ; i++ ) {
                    out.push( [ i+'-1', i+'-2', i+'-3', i+'-4', i+'-5', i+'-6' ] );
                }
     
                setTimeout( function () {
                    callback( {
                        draw: data.draw,
                        data: out,
                        recordsTotal: 5000000,
                        recordsFiltered: 5000000
                    } );
                }, 50 );
            },
            scrollY: 200,
            scroller: {
                loadingIndicator: true
            },
        } );
    } );</script>
</body>
flask pagination sql-server python
1 Answers

Browser will freeze when you pull that much data.

To avoid that use server-side pagination and provide queries with page number to backend.

PAGE_SIZE = 10

def get_paginated_result(page):
    # creating connection Object which will contain SQL Server Connection    
    connection = pypyodbc.connect('Driver={SQL Server}; Server=Server; Database=DB; UID=UserID; PWD= {Password};')  # Creating Cursor    
    cursor = connection.cursor()    

    order_by = 'id'  # should always be validated (if you get from args)
    offset = (page - 1) * PAGE_SIZE
    cursor.execute(
        "select A, B, C, D from TABLE ORDER BY %s OFFSET %d ROWS FETCH NEXT %d ROWS ONLY"
        % (order_by, offset, PAGE_SIZE)
    )    
    Result=cursor.fetchall()
    return result


@app.route('/index', methods=['GET', 'POST'])
def view_index():
    page = abs(int(request.args.get('page')))
    Result = get_paginated_result(page)
    return render_template('index.html', Result=Result)


@app.route('/ajax_table', methods=['GET'])
def ajax_table():
    page = abs(int(request.args.get('page')))
    Result = get_paginated_result(page)
    return render_template('ajax_table.html', Result=Result)

After that you'll need to extract table to separate HTML file ajax_table.html. So you'll have main page and table that can be paginated. For pagination recommend to use Bootstrap Pagination and add JS code to load from /ajax_table and replace table content with another page.

user_37226350
Related