I have a project where I save a lot of data in the sqlite3 database. I want to create a simple application in Flask - displaying appropriate data from the database.
New records are added to the database every few minutes - I want this application to refresh automatically. I prepared some information about it and found information that it can be done somehow using socketio Can flask framework send real-time data from server to client browser? or using AJAX. I tried to do something like this: or Python Flask date update real-time but I didn't manage to do it.
here's the code I'm using right now:
from flask import Flask, request, render_template, session, redirect
import numpy as np
import pandas as pd
import sqlite3
con = sqlite3.connect(r'path')
app = Flask(__name__)
df = pd.read_sql('select * from table1', con)
df1 = pd.read_sql('select * from table2', con)
@app.route('/', methods=("POST", "GET"))
def html_table():
return render_template('app.html',
tables=[df.to_html(classes='data1'),df1.to_html(classes='data2')],
titles=df.columns.values)
if __name__ == '__main__':
app.run()
Template:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Title</title>
</head>
<body>
{% for table in tables %}
{{titles[loop.index]}}
{{ table|safe }}
{% endfor %}
</body>
</html>
in this case, everything is displayed, but it doesn't work in real time.
I'm asking for some instructions or some advice on how to do it? Can I use the links I posted here somehow? This is my first application in Flask so I don't quite know how to achieve such a thing.
pd.read_sql, which grabs the entire query at once. That is OK; it is outside of the route. But you also havedf.to_htmlin your route. This means that it needs to be called every time the page is hit. You should do all of thatto_htmloutside of the route, then maybe have some clever diff to a cache to see when it changes. That might help.