GeoDjango迁移Spatialite数据库报错：no such column: rowid

尝试使用GeoDjango创建数据库，执行migrate命令时触发错误：error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid。已配置正确的数据库引擎，并在settings.py中添加了'django.contrib.gis'。尝试过删除旧迁移文件、更新至最新版Spatialite-tools，即使清空模型中的所有字段，仍会出现该错误。

模型代码 #

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',
    }
}

完整错误栈 #

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

1. 彻底重置数据库 #

当前数据库文件可能已被损坏的触发器污染，最简单的修复方式是完全重置：

• 删除项目根目录下的db.sqlite3文件
• 删除所有应用migrations目录下的迁移文件（保留__init__.py）
• 依次运行python manage.py makemigrations和python manage.py migrate重新初始化

2. 调整模型主键设置 #

SQLite中，显式定义INTEGER PRIMARY KEY类型主键时，该字段会映射到rowid，但表重建时Spatialite触发器可能出现异常：

• 暂时移除自定义主键，让Django自动生成id主键，测试迁移是否正常
• 若必须保留自定义主键，确保字段类型为models.IntegerField(primary_key=True)，完成基础迁移后再添加空间字段

3. 检查版本兼容性 #

确保Spatialite-tools版本与Django版本匹配：

• Django 4.x及以上建议使用Spatialite 5.x版本
• 通过spatialite --version查看当前版本，版本过低则升级

4. 手动修复触发器（进阶） #

若不想重置数据库，可通过SQLite命令行手动删除异常触发器：

1. 打开数据库：sqlite3 db.sqlite3
2. 查看所有触发器：.triggers
3. 删除出错的触发器：DROP TRIGGER IF EXISTS ISO_metadata_reference_row_id_value_insert;
4. 退出后重新运行python manage.py migrate

内容的提问来源于stack exchange，提问作者Viljan