Django with tastypie how to refresh queryset?

Question

Some tables in my database are updated from some background python scripts that are constantly running in the background (doing data mining). Django had a hard time knowing the data was updated outside of Django and was always showing the old data. After some long research, most cache disabling methods online do not work for me, but this workaround works perfectly:

from django.db import connection
connection.close()

It's not a perfect solution still but it does work in Django's views. If you know how to fix that, please comment here Django: how to refresh or reload models from database. However, the data is also accessed by REST. In Tastypie, the resource is still not being updated.

The JSON structure always returns the outdated timestamp and I can see the latest value in database is different. I already tried the following, didn't work.

from tastypie.cache import NoCache
cache = NoCache()

The only way to refresh it now is to restart the uwsgi service manually, which is obviously not a solution. This problem is driving me nuts. Any comment will be appreciated. Thank you.

---

UPDATE

I have simplified the code and model to only contain the timestamp column. Here is the full code and logs.

Tastypie's resources.py

from lp.models import LatestPrices
from tastypie.resources import ModelResource
from tastypie.serializers import Serializer
from django.db import connection

class LpResource(ModelResource):
    class Meta:

        # forces Django to make a new connection, fixes OperationalError: (2006, 'MySQL server has gone away') after a long time with no access
        connection.close() 

        queryset = LatestPrices.objects.all()

        include_resource_uri = False
        resource_name = 'lp'
        allowed_methods = ['get']
        excludes = ['slug']
        serializer = Serializer(formats=['json'])
        connection.close()

models.py:

from django.db import models

class LatestPrices(models.Model):
    id = models.AutoField(primary_key=True)
    slug = models.SlugField(unique=True)

    # timestamp is updated every 10 seconds
    timestamp = models.DateTimeField(null=True, blank=True)

    # Removed all other columns for debugging.

    # The data to show in admin
    def __unicode__(self):
        return str(self.timestamp)

    class Meta:
        db_table = 'latest_prices'

The DB injector script outside of Django

while True:
    try:
        # Get date & time
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        logger.info("------" + timestamp)

        # Inject to local database
        logger.debug("Injecting...")
        con = None
        con = db.connect('localhost', 'user', 'pw', 'tablename')
        con.autocommit(True)
        cursor = con.cursor(MySQLdb.cursors.DictCursor)
        update_query = "UPDATE latest_prices SET slug='latest', timestamp='" + timestamp \
                             + "' WHERE id=1"
        rows_affected = cursor.execute(update_query)
        con.commit()
        if rows_affected != 1:
            logger.critical("Couldn't inject! Rows affected: " + str(rows_affected))
            cursor.close()
            time.sleep(WAIT_TIME)
            continue

        cursor.close()

    except Exception as e:
        # Code removed. Email admin the error string...
        pass
    finally:    
        pass

    time.sleep(WAIT_TIME)

mysql.log

          124 Query UPDATE latest_prices SET slug='latest', timestamp='2013-04-04 18:38:16' WHERE id=1
          124 Query commit
130404 18:38:26   125 Connect   dev@localhost on gs
          125 Query set autocommit=0
          125 Query set autocommit=1
          124 Quit  
          125 Query UPDATE latest_prices SET slug='latest', timestamp='2013-04-04 18:38:26' WHERE id=1
          125 Query commit
130404 18:38:36   126 Connect   dev@localhost on gs
          126 Query set autocommit=0
          126 Query set autocommit=1
          125 Quit  
          126 Query UPDATE latest_prices SET slug='latest', timestamp='2013-04-04 18:38:36' WHERE id=1
          126 Query commit
130404 18:38:46   127 Connect   dev@localhost on gs
          127 Query set autocommit=0
          127 Query set autocommit=1
          126 Quit  
          127 Query UPDATE latest_prices SET slug='latest', timestamp='2013-04-04 18:38:46' WHERE id=1
          127 Query commit

# Click the browser refresh button one time here
130404 18:38:53    73 Query SELECT `latest_prices`.`id`, `latest_prices`.`slug`, `latest_prices`.`timestamp` FROM `latest_prices` WHERE (`latest_prices`.`id` = 1  AND `latest_prices`.`id` = 1 )

130404 18:38:56   128 Connect   dev@localhost on gs
          128 Query set autocommit=0
          128 Query set autocommit=1
          127 Quit  
          128 Query UPDATE latest_prices SET slug='latest', timestamp='2013-04-04 18:38:56' WHERE id=1
          128 Query commit

# Click the browser refresh button a few times here
130404 18:38:58    70 Query SELECT `latest_prices`.`id`, `latest_prices`.`slug`, `latest_prices`.`timestamp` FROM `latest_prices` WHERE (`latest_prices`.`id` = 1  AND `latest_prices`.`id` = 1 )
130404 18:39:02    70 Query SELECT `latest_prices`.`id`, `latest_prices`.`slug`, `latest_prices`.`timestamp` FROM `latest_prices` WHERE (`latest_prices`.`id` = 1  AND `latest_prices`.`id` = 1 )
130404 18:39:04    73 Query SELECT `latest_prices`.`id`, `latest_prices`.`slug`, `latest_prices`.`timestamp` FROM `latest_prices` WHERE (`latest_prices`.`id` = 1  AND `latest_prices`.`id` = 1 )

130404 18:39:06   129 Connect   dev@localhost on gs
          129 Query set autocommit=0
          129 Query set autocommit=1
          128 Quit  
          129 Query UPDATE latest_prices SET slug='latest', timestamp='2013-04-04 18:39:06' WHERE id=1
          129 Query commit
130404 18:39:16   130 Connect   dev@localhost on gs
          130 Query set autocommit=0
          130 Query set autocommit=1
          130 Query UPDATE latest_prices SET slug='latest', timestamp='2013-04-04 18:39:16' WHERE id=1
          129 Quit  
          130 Query commit

Browser output (always the same thing)

{"id": 1, "timestamp": "2013-04-04T18:29:45"}

HTTP header

Cache-Control →no-cache
Connection →keep-alive
Content-Type →application/json
Date →Fri, 05 Apr 2013 00:27:48 GMT
Server →nginx
Transfer-Encoding →
Transfer-Encoding
The form of encoding used to safely transfer the entity to the user. Currently defined methods are: chunked, compress, deflate, gzip, identity.

chunked
Vary →Accept

I also added an explicit commit in the injector script. It didn't make much different it says commit in mysql.log, together with the existing "set autocommit=1" that I was using.

Now that I'm looking at the mysql.log, I see that everytime I hit the browser's refresh button, I do see a new SELECT statement sent by Django. But the objects are still old. That I can't figure, why?

Answer 1

Add "transaction-isolation = READ-COMMITTED" to my.cnf. More details here: Django: how to refresh or reload models from database

Answer 2

Django will cache the results within a queryset, but not the results within unrelated querysets.

The following code will result in two database queries:

list(SomeTable.objects.all())  # First db query.
list(SomeTable.objects.all())  # Second db query unrelated to first.

The symptoms and your connection.close() solution are consistent with a MySQL transaction issue. A new connection on each query will result in a different transaction.

It seems as though a transaction is being started in your Django code, but never committed or rolled back. When the transaction starts, further reads/writes in that transaction will only see the state of the database at the start of the transaction, plus and updates made in that transaction. If another process starts a session (eg your data mining script) and inserts data, it will not be seen by the Django transaction until the existing transaction is closed.

Without seeing your code it's impossible to tell why there would be a transaction started but not completed. To verify this assumption, turn on the MySQL query log and examine its contents. You should see a start transaction query without a corresponding commit or rollback.