pythondjangodjango-migrations

psycopg2.errors.UndefinedTable: relation "mydjangoapp_mymodel" does not exist


I am managing a django app built by third parts.

I have configured in settings.py the connection to a new db

'default': {  # changed
    'ENGINE': 'django.contrib.gis.db.backends.postgis',
    'NAME': 'waterwatch',
    'USER': 'waterwatch_main',
    'PASSWORD': '****',
    'HOST': 'localhost',
}

Now I want to populate the new database with the table in my models.py, that is

class WaterConsumption(models.Model):
    Id = models.IntegerField(primary_key=True)
    Suburb = models.CharField(max_length=100)
    NoOfSingleResProp = models.IntegerField()
    AvgMonthlyKL = models.IntegerField()
    AvgMonthlyKLPredicted = models.IntegerField()
    PredictionAccuracy = models.IntegerField()
    Month = models.CharField(max_length=50)
    Year = models.IntegerField()
    DateTime = models.DateTimeField()
    geom = models.PointField()

    def __str__(self):
        return self.Suburb

    class Meta:
        verbose_name_plural = 'WaterConsumption'

so I run

python manage.py makemigrations

but I get

django.db.utils.ProgrammingError: relation "waterwatchapp_waterconsumption" does not exist

well... I guess that is obvious, I am actually trying to create new tables in my new database.

I guess something is wrong with the migrations.

so I have dropped and recreated the database, deleted all files in

But as I run again python manage.py makemigrations or python manage.py migrate, I still get the same error.

Why does django does not creates the tables anew?

It seems to me that django is still somehow following a sort of track of what was in the database before.

How can I remove all the migration history done before, and make django apply the migrations to a new database? (I don't care about the stored data)

Complete traceback.

Watching for file changes with StatReloader
Exception in thread django-main-thread:
Traceback (most recent call last):
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: relation "waterwatchapp_waterconsumption" does not exist
LINE 1: UPDATE "waterwatchapp_waterconsumption" SET "Suburb" = 'ATHL...
               ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/lib/python3.10/threading.py", line 1016, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.10/threading.py", line 953, in run
    self._target(*self._args, **self._kwargs)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/utils/autoreload.py", line 64, in wrapper
    fn(*args, **kwargs)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/core/management/commands/runserver.py", line 115, in inner_run
    autoreload.raise_last_exception()
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/utils/autoreload.py", line 87, in raise_last_exception
    raise _exception[1]
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 381, in execute
    autoreload.check_errors(django.setup)()
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/utils/autoreload.py", line 64, in wrapper
    fn(*args, **kwargs)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/__init__.py", line 24, in setup
    apps.populate(settings.INSTALLED_APPS)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/apps/registry.py", line 122, in populate
    app_config.ready()
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/contrib/admin/apps.py", line 27, in ready
    self.module.autodiscover()
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/contrib/admin/__init__.py", line 24, in autodiscover
    autodiscover_modules('admin', register_to=site)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/utils/module_loading.py", line 57, in autodiscover_modules
    import_module('%s.%s' % (app_config.name, module_to_search))
  File "/usr/lib/python3.10/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1050, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1027, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1006, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 688, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/waterwatchapp/admin.py", line 44, in <module>
    ).save()
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/models/base.py", line 743, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/models/base.py", line 780, in save_base
    updated = self._save_table(
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/models/base.py", line 862, in _save_table
    updated = self._do_update(base_qs, using, pk_val, values, update_fields,
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/models/base.py", line 916, in _do_update
    return filtered._update(values) > 0
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/models/query.py", line 809, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1591, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1202, in execute_sql
    cursor.execute(sql, params)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute
    with self.db.wrap_database_errors:
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: relation "waterwatchapp_waterconsumption" does not exist
LINE 1: UPDATE "waterwatchapp_waterconsumption" SET "Suburb" = 'ATHL...

Solution

  • By looking deeper into the Traceback, I noticed this snippet

    File "/home/tommaso/tommaso03/coding_projects/corsi_udemy/create-smart-maps-in-python-and-leaflet/waterwatch/venv/lib/python3.10/site-packages/django/contrib/admin/__init__.py", line 24, in autodiscover
        autodiscover_modules('admin', register_to=site)
    

    This made me think django tryed to access the table before it was created, raising the error.

    This was actually done by waterwatchapp/admin.py.

    # Register your models here.
    class WaterConsumptionAdmin(LeafletGeoAdmin):
        pass
    
    admin.site.register(WaterConsumption, WaterConsumptionAdmin)
    
    df_excelReader = pd.read_excel(BASE_DIR / 'tabledata_source/waterwatch_clean2.xlsx')
    
    for index, row in df_excelReader.iterrows():
        Id = index
        Suburb = row['Suburb']
        NoOfSingleResProp = row['Number of single-residential properties_number']
        AvgMonthlyKL = row['Oct 2017\nkl/month']
        AvgMonthlyKLPredicted = 0
        PredictionAccuracy = 0
        Month = row ['Month']
        Year = row['Year']
        DateTime = datetime.now()
        Longitude = row['Longitude']
        Latitude = row['Latitude']
    
        WaterConsumption(Id=Id,
                        Suburb=Suburb,
                        NoOfSingleResProp=NoOfSingleResProp,
                        AvgMonthlyKL=AvgMonthlyKL,
                        AvgMonthlyKLPredicted=AvgMonthlyKLPredicted,
                        PredictionAccuracy=PredictionAccuracy,
                        Month=Month,
                        Year=Year,
                        DateTime=DateTime,
                        geom=Point(Longitude, Latitude)
                        ).save()
    

    I commented all the lines of code of waterwatchapp/admin.py involving the table quoted by the traceback (WaterConsumption, in the traceback it is like djangoapp_djangomodel, all lowercase), then I run

    python manage.py makemigrations
    

    and it worked!

    Then I decommented the lines.


    Also, a simple try-except structure could do the job.

    try:
    
        # Register your models here.
        class WaterConsumptionAdmin(LeafletGeoAdmin):
            pass
    
        admin.site.register(WaterConsumption, WaterConsumptionAdmin)
    
        df_excelReader = pd.read_excel(BASE_DIR / 'tabledata_source/waterwatch_clean2.xlsx')
    
        for index, row in df_excelReader.iterrows():
            Id = index
            Suburb = row['Suburb']
            NoOfSingleResProp = row['Number of single-residential properties_number']
            AvgMonthlyKL = row['Oct 2017\nkl/month']
            AvgMonthlyKLPredicted = 0
            PredictionAccuracy = 0
            Month = row ['Month']
            Year = row['Year']
            DateTime = datetime.now()
            Longitude = row['Longitude']
            Latitude = row['Latitude']
    
    
            WaterConsumption(Id=Id,
                            Suburb=Suburb,
                            NoOfSingleResProp=NoOfSingleResProp,
                            AvgMonthlyKL=AvgMonthlyKL,
                            AvgMonthlyKLPredicted=AvgMonthlyKLPredicted,
                            PredictionAccuracy=PredictionAccuracy,
                            Month=Month,
                            Year=Year,
                            DateTime=DateTime,
                            geom=Point(Longitude, Latitude)
                            ).save()
            
    except Exception:
        print(traceback.format_exc())
        print("Cannot access table WaterConsumption.")
    

    furthermore, I have found a quite unelegant way to avoid the error to be raised:
    I have added a snippet that accesses the table only if the table already exists.

    # introducing a condition, to avoid django access the table when it is not created yet
    
    from django.db import connection
    all_tables = connection.introspection.table_names()
    table_name =  'waterwatchapp' + '_' + 'WaterConsumption'.lower()
    
    if table_name in all_tables:
        
        # Register your models here.
        class WaterConsumptionAdmin(LeafletGeoAdmin):
            pass
    
        admin.site.register(WaterConsumption, WaterConsumptionAdmin)
    
        for index, row in df_excelReader.iterrows():
            Id = index
            Suburb = row['Suburb']
            NoOfSingleResProp = row['Number of single-residential properties_number']
            AvgMonthlyKL = row['Oct 2017\nkl/month']
            AvgMonthlyKLPredicted = 0
            PredictionAccuracy = 0
            Month = row ['Month']
            Year = row['Year']
            DateTime = datetime.now()
            Longitude = row['Longitude']
            Latitude = row['Latitude']
        
        
            WaterConsumption(Id=Id,
                            Suburb=Suburb,
                            NoOfSingleResProp=NoOfSingleResProp,
                            AvgMonthlyKL=AvgMonthlyKL,
                            AvgMonthlyKLPredicted=AvgMonthlyKLPredicted,
                            PredictionAccuracy=PredictionAccuracy,
                            Month=Month,
                            Year=Year,
                            DateTime=DateTime,
                            geom=Point(Longitude, Latitude)
                            ).save()