Wednesday, 30 June 2021

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

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>
                    
                    </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>


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

No comments:

Post a Comment