Django Model.objects.all() returns unexpected empty QuerySet

Question

I have some problem while trying to use a MySQL database with Django. The database contains a table named items. This database is imported from a development system to a production system. This database is the second database in addition to an existing SQLite databased used by other teams. There had been an error message about no such table, but I was able to solve it with this question. I then assume that there are no migration errors.

The MySQL database works fine in the development system (tested using python manage.py shell), but it doesn't work in the production system (tested the same way.)

I tested the import with:

$ python manage.py shell    

>> from project.models import Item

>> Item.objects.all()
<QuerySet []>

I created the models.py file with:

$ python manage.py inspectdb > models.py

I verified that the database and the table items actually contains records. (Although some fields are stored as unicode text, if that makes any difference to the situation.)

Here is part of the project/settings.py file:

...
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'new_project': {
        'NAME': 'new_project',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'xxxxxxxx',
        'PASSWORD': 'xxxxxxxx',
    }
}
...

Here is part of the generated app/models.py:

...
class Item(models.Model):
    itemid = models.AutoField(db_column='ItemID', primary_key=True)  # Field name made lowercase.
    name = models.TextField(blank=True, null=True)
    category = models.TextField(blank=True, null=True)
    kind = models.TextField(blank=True, null=True)

    class Meta:
        managed = True
        db_table = 'items'
...

I am using Django 2.0.2 with Python 3.6.3.

It should give you at least `]>` for the entries if you haven't defined a `__str__` method; can you simple try `x = Item("fields")` `x.save()` and then `Item.objects.all()` in `python manage.py shell` — Işık Kaplan, Feb 27 '18 at 03:33
@JerinPeterGeorge I have added the details of my `project/settings.py` file — krismath, Feb 27 '18 at 03:41
try to route your DBs, https://docs.djangoproject.com/en/2.0/topics/db/multi-db/ — JPG, Feb 27 '18 at 03:47
@IşıkKaplan I have executed your code; `Item.objects.all()` shows that there is one Item object in the resulting QuerySet. However, when I go to look at the database via MySQL CLI, the new item could not be found. — krismath, Feb 27 '18 at 03:56
@JerinPeterGeorge Routing my DBs solved the problem! Please make it into an answer and I'll accept it. Thanks! — krismath, Feb 27 '18 at 04:42

endless · Answer 1 · 2018-02-27T03:44:48.807

1

Django try to find a table <app_name>_<model_name> which matching to its model, but you can add this Meta class below to Item class to change how Django works (or change your table name to project_items):

class Item(models.Model):
    ...
    class Meta:
        db_table = 'items'

edited Feb 27 '18 at 03:44

answered Feb 27 '18 at 03:38

endless

114
1
2
5

The generated `app/models.py` is pretty similar to yours. I have updated my question to reflect this. – krismath Feb 27 '18 at 03:51

score 1 · Accepted Answer · answered Feb 27 '18 at 05:04

Django has support for interacting with multiple databases. That's what you are doing here. In order to establish the connection, you must set up DATABASE_ROUTERS for all of your apps’ models.
exampe for DBRouter

class AuthRouter:
    """
    A router to control all database operations on models in the
    auth application.
    """
    def db_for_read(self, model, **hints):
        """
        Attempts to read auth models go to auth_db.
        """
        if model._meta.app_label == 'auth':
            return 'auth_db'
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write auth models go to auth_db.
        """
        if model._meta.app_label == 'auth':
            return 'auth_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the auth app is involved.
        """
        if obj1._meta.app_label == 'auth' or \
           obj2._meta.app_label == 'auth':
           return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the auth app only appears in the 'auth_db'
        database.
        """
        if app_label == 'auth':
            return db == 'auth_db'
        return None

and in your settings.py, DATABASE_ROUTERS = ['path.to.AuthRouter']

You can find more details and info from django's official doc.

Django Model.objects.all() returns unexpected empty QuerySet

2 Answers2