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
