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.

Answer 1

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.

Answer 2

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'