CODEWITHSUNDEEP
pythondjangodjango-tables2
Premysl Vorac
Premysl Vorac

Reputation: 473

Django: Refresh data on webpage after update in SQLite

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?

Upvotes: 0

Views: 1567

Answers (2)

Daniel Roseman
Daniel Roseman

Reputation: 599630

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]

Upvotes: 2

GwynBleidD
GwynBleidD

Reputation: 20559

Data is not refreshing because of those two lines:

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

Each of them will evaluate once when the code of your view is parsed. This happens because you are converting querysets to lists. I don't exactly know how your refresh field works, because you didn't provide any code that uses it, but for the queryset there are at least two possible solutions.

The first solution will be to rewrite your queryset, so it will be still queryset, not list. Django knows how to handle querysets in views so they behave "lazy" (evaluate on each view instead of once), but it cannot do that with lists. Your new queryset can look like this:

    queryset = ReportEntry.objects.filter(**k).order_by('received').distinct('process')

Note that distinct with fields doesn't work on every database backend.

The second solution is to move your queryset to get_queryset method:

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

Upvotes: 1

Related Questions