[Django]-Missing Table When Running Django Unittest with Sqlite3

In Django 1.4, 1.5, 1.6, 1.7, or 1.8 it should be sufficient to use:

if 'test' in sys.argv:
    DATABASES['default']['ENGINE'] = 'django.db.backends.sqlite3'

It should not be necessary to override TEST_NAME¹, nor to call syncdb in order to run tests. As @osa points out, the default with the SQLite engine is to create the test database in memory (TEST_NAME=':memory:'). Calling syncdb should not be necessary because Django’s test framework will do this automatically via a call to syncdb or migrate depending on the Django version.² You can observe this with manage.py test -v [2|3].

Very loosely speaking Django sets up the test environment by:

1. Loading the regular database NAME from your settings.py
2. Discovering and constructing your test classes (__init__() is called)
3. Setting the database NAME to the value of TEST_NAME
4. Running the tests against the database NAME

Here’s the rub: At step 2, NAME is still pointing at your regular (non-test) database. If your tests contain class-level queries or queries in __init__(), they will be run against the regular database which is likely not what you are expecting. This is identified in bug #21143.

Don’t do:

class BadFooTests(TestCase):
    Foo.objects.all().delete()     # <-- class level queries, and

    def __init__(self):
        f = Foo.objects.create()   # <-- queries in constructor
        f.save()                   #     will run against the production DB

    def test_foo(self):
        # assert stuff

since these will be run against the database specified in NAME. If NAME at this stage points to a valid database (e.g. your production database), the query will run, but may have unintended consequences. If you have overridden ENGINE and/or NAME such that it does not point to a pre-existing database, an exception will be thrown because the test database has yet to be created:

django.db.utils.DatabaseError: no such table: yourapp_foo  # Django 1.4
DatabaseError: no such table: yourapp_foo                  # Django 1.5
OperationalError: no such table: yourapp_foo               # Django 1.6+

Instead do:

class GoodFooTests(TestCase):

    def setUp(self):
        f = Foo.objects.create()   # <-- will run against the test DB
        f.save()                   #

    def test_foo(self):
        # assert stuff

So, if you are seeing errors, check to see that your tests do not include any queries that might hit the database outside of your test class method definitions.

[1] In Django >= 1.7, DATABASES[alias]['TEST_NAME'] is deprecated in favour of DATABASES[alias]['TEST']['NAME']
[2] See the create_test_db() method in db/backends/creation.py

Having tried all of the above I eventually discovered another reason this can happen:-

If any of your models are not created by one of your migrations.

I did some debugging and it seems that Django testing sets up the database by applying all your migrations in order, starting with 001_initial.py, before trying to SELECT from the tables based on your models.py

In my case a table had somehow not been added to the migrations, but added manually, so the full migration set couldn’t be properly applied. When I manually fixed the 001_initial.py migration to create this table the OperationalError went away.

I had this problem, too. Turned out that I had to add a TEST_NAME property in the settings.py file to identify the test database properly. It solved the problem for me:

if 'test' in sys.argv:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join(os.path.dirname(__file__), 'test.db'),
            'TEST_NAME': os.path.join(os.path.dirname(__file__), 'test.db'),
       }
    }

Just to add another case to this:

If you are trying to upgrade to 1.8 from 1.6 (or from a non-migration setup to a migration setup), you might hit this error if you haven’t run created migrations.

I had the same problem and had to create migrations so the test runner could use them, which was not intuitive because pre-migrations, the tests would just make a new DB based on doing syncdb, which always worked.

For future reference, this also happens if your application is not added to your INSTALLED_APPS, for example:

INSTALLED_APPS = (
   ...
   'myapp'
)

Otherwise you get;

OperationalError: no such table: myapp_mytable

Your table didn’t find in database by doing unittest, because unittest inherited unittest.TestCase. Wherein don’t apply migrations to your database.

If you want to use database with changes from migrations, then you should inherited test case class (for example) from django.test.TestCase.

Run python manage.py test -v 3 and you will see applaying migrations to default testing database.

More information to: https://docs.djangoproject.com/en/dev/topics/testing/overview/#the-test-database

Your database is probably empty, it must be setup with all the tables corresponding to your models. Normally, this would be done by running python manage.py syncdb first, to create all your database tables. The problem is that in your case, when you run syncdb, python will not see that you are running a test so it will try to setup tables in your MySQL database instead.

To get around this, temporarily change

if 'test' in sys.argv:

to

if True:

Then run python manage.py syncdb to setup the sqlite database tables. Now that everything is setup, you can put back in if 'test'... and everything should run smoothly. However you probably want to move your database out of the /tmp directory: django needs to re-use the same database every time you run your tests, otherwise you’ll have to create database tables before every test.

Note that if you add new models, you will need to repeat this procedure to create the new tables in sqlite. If you add new fields to an existing model, you will need to manually add columns to your sqlite database using the sqlite interface and ALTER TABLE..., or do it automatically using a tool like South.

I had to add the follwoing lines after test database definition:

from django.core.management import call_command
call_command('syncdb', migrate=True)

For those who tried all possible ways but still stuck in this:

Since our test code is still running in other machine but not mine, I tried to:

• Create a new virtual-env. (so isolate the affect of apps)
• Clone a new repository. (isolate the affect of ignored files)
• Set in settings to use sqlite3 instead of psql (isolate the affect of database, and database settings)
• Check env variables, and .env file (since I used foreman)

None of those helped. Until I:

1. Create a new account on my machine. (So start clean)
2. Clone the repository.
3. Run tests -> Success 😑
4. Go back to my main account.
5. Open a new terminal (kill current tmux server if you’re using them).
6. Run tests -> Success 😯

So this is not a real answer for your question, since I don’t know what was wrong and how it fixed, just another suggestion that you may try.

For anyone that will get here, searching for why Django keeps creating a database regardless of the --keepdb option. Why it says Using existing test database for alias 'default', but then runs a bunch of CREATE TABLE statements.

If you don’t set a DATABASES > default > TEST > NAME setting, Django will try to use in memory database, and it wont be kept, so set this and override a defaults.

You can make it like this:


DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(ROOT_DIR, 'data', 'db.dev.sqlite3'),
'TEST': {
'NAME': os.path.join(ROOT_DIR, 'data', 'db.test.sqlite3'),
}
}
}


I had a similar problem and I was caused by a previous branch code, so I fixed it removing the pyc files in my projects:

find -regex .*pyc | xargs sudo rm

There are a lot of reasons why this error can occur, for me it had to do with hackish data-migrations.

These data-migrations did some operations on a model that required a field that was not created yet.

E.g.

0001_accounts.py {create the model User}
0002_accounts.py {{for user in User.objects.all(): user.save() }
0003_accounts.py {add a new column to User object}

It fails on 0002 as it tries to save the user object but doesn’t have the new column yet.

To debug:

switch to an empty database
run python manage.py migrate

See where it stops, that is probably the hackish datamigration you’d want to uncomment.