Django: Refresh data on webpage after update in SQLite

Question

I'm preparing a dashboard web application (in Django 2.1.7) that is supposed to monitor status of different processes.

To do so, I've created a class ReportEntry in models.py

class ReportEntry(models.Model):
    process = models.CharField(max_length=30)
    received = models.DateTimeField(auto_now_add=True)
    status = models.SmallIntegerField(default=1)

    def refresh(self):
        self.refresh_from_db()
        self.save()

To view the dashboard, I'm using django-tables2. The view.py script comprises

from .tables import SimpleTable
from django_tables2 import SingleTableView
from .models import ReportEntry

class TableView(SingleTableView):
    table_class = SimpleTable
    processes = ReportEntry.objects.values('process').distinct()
    queryset = [ReportEntry.objects.filter(**k).latest('received') for k in processes]
    refresh = [a.refresh() for a in ReportEntry.objects.all()]
    template_name = "simple_list.html"

This web application works correctly.

Now, I'd like to insert a new entry to the SQLite DB (suppose I'd like to update the status of the process) using a Python script below

from sqlite3 import connect

def create_connection(db_file):
    try:
        conn = connect(db_file)
        return conn
    except Exception as e:
       print(e)
    return None

if __name__ == '__main__':
    from datetime import datetime, timedelta
    database = r'C:\Apps\Python3702\my_venv\web\mysite\db.sqlite3'
    conn = create_connection(database)
    cur = conn.cursor()
    sql = '''INSERT INTO main.monitor_reportentry(process,received,status)
         VALUES(?,?,?)'''
    cur.execute(sql, ['test', datetime.now(), 1])
    conn.commit()
    conn.close()

When I execute the script and insert the data into SQLite DB, I try to refresh the webpage with my dashboard, but the contents are not updated. The only thing that works for me is a restart of the server which is not a way to do the reloading of data.

Is there an "easy" way to somehow reload the data from the database periodically without using redis/celery and similar applications?

Answer 1

The problem is that you explicitly do the query at class level, here:

queryset = [ReportEntry.objects.filter(**k).latest('received') for k in processes]

Despite the name, here you are not defining a queryset - which would be lazy, and update as required - but a concrete list of items, which is only evaluated once per process. Don't do this.

Instead, define the get_queryset() method:

class TableView(SingleTableView):
    table_class = SimpleTable
    template_name = "simple_list.html"

    def get_queryset(self, *args, **kwargs):
        processes = ReportEntry.objects.values('process').distinct()
        return [ReportEntry.objects.filter(**k).latest('received') for k in processes]