Django/sqlite3 "OperationalError: no such table" on threaded operation

Question

By everything I read in the docs, both Django and py-sqlite3 should be fine with threaded access. (Right?) But this code snippet fails for me. The operations in the main thread work, but not in the thread(s) I create. There I get:

File "C:\Python27\lib\site-packages\django-1.9-py2.7.egg\django\db\backends\sq lite3\base.py", line 323, in execute return Database.Cursor.execute(self, query, params)

OperationalError: no such table: thrtest_mymodel

What's the problem?

How do I go about tracking down exactly what's happening to patch Django or whatever's necessary to fix it? The point of failure in Django is pretty indimidating. I can't tell how to see what tables it DOES see, or what differences to look for between main and other threads.

from django.db import models

# Super-simple model
class MyModel(models.Model):
    message       = models.CharField('Message', max_length=200, blank=True)

#Test
from django.test import TestCase

import time
import threading
import random


done = threading.Event()
nThreads = 1


def InsertRec(msg):
    rec = MyModel.objects.create(message=msg)
    rec.save()


def InsertThread():
    try:
        msgNum = 1
        thrName = threading.currentThread().name
        print 'Starting %s' % thrName
        while not done.wait(random.random() * 0.1):
            msgNum += 1
            msg = '%s: %d' % (thrName, msgNum)
            print msg
            InsertRec(msg)
    finally:
        done.set()
    pass


class ThreadTestRun(TestCase):

    def testRunIt(self):
        nThisThread = 10
        msgSet = set()
        for x in xrange(nThisThread):
            msg = 'Some message %d' % x
            InsertRec(msg) # From main thread: works!
            msgSet.add(msg)
        self.assertEqual(MyModel.objects.count(), nThisThread)
        # We use sets because .all() doesn't preserve the original order.
        self.assertEqual(msgSet, set([r.message for r in MyModel.objects.all()]))
        thrSet = set()
        for thrNum in xrange(nThreads):
            t = threading.Thread(name='Thread %d' % thrNum, target=InsertThread)
            t.start()
            thrSet.add(t)

        done.wait(10.)
        done.set()
        for t in thrSet:
            t.join()

Update: Here is DATABASES from settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': ':memory:', # os.path.join(BASE_DIR, 'db.sqlite3'),
        'TEST_NAME' : ':memory:',
    },
}

Update: With respect to Django's ticket #12118, I get the same symptoms using ':memory:' or a disk file (for TEST_NAME).

Django 1.9, Python 2.7.11. (Same symptoms in Django 1.6.)

Answer 1

Change your DATABASES like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': ':memory:',
        'TEST' : 
            {
                'NAME': 'test_db',
            }
    },
}

This will force django to create a real sqlite db on the disk, instead of creating it in memory.

Also be sure to inherit your test cases related to threading from django.test.testcases.TransactionTestCase. If you don't do so, the threads won't see database changes made from another threads.