Django - No such column: rowid when migrating Spatialite database

Question

I am trying to create a database using GeoDjango, but when I try to migrate the database I get "error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid". I have added the right database engine and included 'django.contrib.gis', in settings.py. Any ide what the problem might be?

from django.contrib.gis.db import models

# Create your models here.
class Bus(models.Model):
    busID = models.IntegerField(primary_key=True)
    route = models.CharField(max_length=20)
    operator = models.SmallIntegerField()
    latitude = models.DecimalField( max_digits=8, decimal_places=6)
    longitude = models.DecimalField( max_digits=8, decimal_places=6)
    location = models.PointField(srid=4326, blank=True, null=True)
    updated = models.DateTimeField(blank=True, null=True)
    lastStop = models.IntegerField(blank=True, null=True)

    def __str__(self):
        return self.busID

settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.spatialite',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}

Error:

python3 ./manage.py migrate
Operations to perform:
  Apply all migrations: admin, api, auth, contenttypes, sessions
Running migrations:
  Applying admin.0002_logentry_remove_auto_add...Traceback (most recent call last):
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
    return Database.Cursor.execute(self, query, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid

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

Traceback (most recent call last):
  File "/Users/vvv/Documents/Prosjekter/mqtt/./manage.py", line 22, in <module>
    main()
  File "/Users/vvv/Documents/Prosjekter/mqtt/./manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/base.py", line 96, in wrapped
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/migration.py", line 130, in apply
    operation.database_forwards(
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/operations/fields.py", line 235, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 173, in alter_field
    super().alter_field(model, old_field, new_field, strict=strict)
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 788, in alter_field
    self._alter_field(
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 460, in _alter_field
    self._remake_table(model, alter_field=(old_field, new_field))
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 347, in _remake_table
    self.alter_db_table(
  File "/opt/homebrew/lib/python3.11/site-packages/django/contrib/gis/db/backends/spatialite/schema.py", line 153, in alter_db_table
    super().alter_db_table(model, old_db_table, new_db_table, disable_constraints)
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 120, in alter_db_table
    super().alter_db_table(model, old_db_table, new_db_table)
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 606, in alter_db_table
    self.execute(
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 199, in execute
    cursor.execute(sql, params)
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
    return Database.Cursor.execute(self, query, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.OperationalError: error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid

I have tried deleting old migrations, updating to the newest versions of Spatialite-tools. If I delete all the fields in my model, I still get the error.

Answer 1

Hello i had the same problem soon. The solution to me was to change two line in the two triggers named ISO_metadata_reference_row_id_value_update and ISO_metadata_reference_row_id_value_insert

AND NOT EXISTS
(SELECT eval('SELECT rowid FROM ' || NEW.table_name ||
' WHERE rowid = ' || NEW.row_id_value));

the lines were second and third line from bottom to top.

If you want more information for the problem, read next article: https://groups.google.com/g/spatialite-users/c/SnNZt4AGm_o

Answer 2

Recently, while working with a Django SQLite database, I encountered the "django.db.utils.OperationalError: no such column: rowid" error

After some research, I found a helpful solution here (https://code.djangoproject.com/ticket/32935)

before migrations and migrate,run the following:

python ./manage.py shell -c "import django;django.db.connection.cursor().execute('SELECT InitSpatialMetaData(1);')";

I hope this solution works.

Answer 3

To fix the error, you need to downgrade to Spatialite 4.2.0. You can download Spatialite 4.2.0 from the Spatialite website: https://doc.rasdaman.org/05_geo-services-guide.html

Once you have downgraded Spatialite, you can recreate your migrations and run the migrate command again. The migrations should run without any errors.

    # Create your models here.
    class Bus(models.Model):
        busID = models.IntegerField(primary_key=True)
        route = models.CharField(max_length=20)
        operator = models.SmallIntegerField()
        latitude = models.DecimalField( max_digits=8, decimal_places=6)
        longitude = models.DecimalField( max_digits=8, decimal_places=6)
        location = models.PointField(srid=4326)
        updated = models.DateTimeField(blank=True, null=True)
        lastStop = models.IntegerField(blank=True, null=True)

        def __str__(self):
            return self.busID

    settings.py:
    DATABASES = {
        'default': {
            'ENGINE': 'django.contrib.gis.db.backends.spatialite',
            'NAME': BASE_DIR / 'db.sqlite3',
         }
    }