Django – 迁移 Spatialite 数据库时出现 “没有此列：rowid” 的错误

huangapple
go评论161阅读模式
英文:

Django - No such column: rowid when migrating Spatialite database

问题

我正在尝试使用GeoDjango创建一个数据库，但当我尝试迁移数据库时，我收到错误信息"error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid"。我已经添加了正确的数据库引擎并在settings.py中包含了'django.contrib.gis'。您有什么想法，问题可能出在哪里？

错误：

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

我尝试删除旧的迁移，更新到最新版本的Spatialite-tools。如果我删除模型中的所有字段，仍然会收到错误消息。

英文:

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?

  1. from django.contrib.gis.db import models
  3. # Create your models here.
  4. class Bus(models.Model):
  5.     busID = models.IntegerField(primary_key=True)
  6.     route = models.CharField(max_length=20)
  7.     operator = models.SmallIntegerField()
  8.     latitude = models.DecimalField( max_digits=8, decimal_places=6)
  9.     longitude = models.DecimalField( max_digits=8, decimal_places=6)
  10.     location = models.PointField(srid=4326, blank=True, null=True)
  11.     updated = models.DateTimeField(blank=True, null=True)
  12.     lastStop = models.IntegerField(blank=True, null=True)
  14.     def __str__(self):
  15.         return self.busID

settings.py:

  1. DATABASES = {
  2.     'default': {
  3.         'ENGINE': 'django.contrib.gis.db.backends.spatialite',
  4.         'NAME': BASE_DIR / 'db.sqlite3',
  5.     }
  6. }

Error:

  1. python3 ./manage.py migrate
  2. Operations to perform:
  3.   Apply all migrations: admin, api, auth, contenttypes, sessions
  4. Running migrations:
  5.   Applying admin.0002_logentry_remove_auto_add...Traceback (most recent call last):
  6.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
  7.     return self.cursor.execute(sql, params)
  8.            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  9.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
  10.     return Database.Cursor.execute(self, query, params)
  11.            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  12. sqlite3.OperationalError: error in trigger ISO_metadata_reference_row_id_value_insert: no such column: rowid
  14. The above exception was the direct cause of the following exception:
  16. Traceback (most recent call last):
  17.   File "/Users/vvv/Documents/Prosjekter/mqtt/./manage.py", line 22, in <module>
  18.     main()
  19.   File "/Users/vvv/Documents/Prosjekter/mqtt/./manage.py", line 18, in main
  20.     execute_from_command_line(sys.argv)
  21.   File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
  22.     utility.execute()
  23.   File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/__init__.py", line 440, in execute
  24.     self.fetch_command(subcommand).run_from_argv(self.argv)
  25.   File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/base.py", line 402, in run_from_argv
  26.     self.execute(*args, **cmd_options)
  27.   File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/base.py", line 448, in execute
  28.     output = self.handle(*args, **options)
  29.              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  30.   File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/base.py", line 96, in wrapped
  31.     res = handle_func(*args, **kwargs)
  32.           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  33.   File "/opt/homebrew/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 349, in handle
  34.     post_migrate_state = executor.migrate(
  35.                          ^^^^^^^^^^^^^^^^^
  36.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/executor.py", line 135, in migrate
  37.     state = self._migrate_all_forwards(
  38.             ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  39.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
  40.     state = self.apply_migration(
  41.             ^^^^^^^^^^^^^^^^^^^^^
  42.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
  43.     state = migration.apply(state, schema_editor)
  44.             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  45.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/migration.py", line 130, in apply
  46.     operation.database_forwards(
  47.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/migrations/operations/fields.py", line 235, in database_forwards
  48.     schema_editor.alter_field(from_model, from_field, to_field)
  49.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 173, in alter_field
  50.     super().alter_field(model, old_field, new_field, strict=strict)
  51.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 788, in alter_field
  52.     self._alter_field(
  53.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 460, in _alter_field
  54.     self._remake_table(model, alter_field=(old_field, new_field))
  55.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 347, in _remake_table
  56.     self.alter_db_table(
  57.   File "/opt/homebrew/lib/python3.11/site-packages/django/contrib/gis/db/backends/spatialite/schema.py", line 153, in alter_db_table
  58.     super().alter_db_table(model, old_db_table, new_db_table, disable_constraints)
  59.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/schema.py", line 120, in alter_db_table
  60.     super().alter_db_table(model, old_db_table, new_db_table)
  61.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 606, in alter_db_table
  62.     self.execute(
  63.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 199, in execute
  64.     cursor.execute(sql, params)
  65.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 102, in execute
  66.     return super().execute(sql, params)
  67.            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  68.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
  69.     return self._execute_with_wrappers(
  70.            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  71.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
  72.     return executor(sql, params, many, context)
  73.            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  74.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
  75.     with self.db.wrap_database_errors:
  76.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
  77.     raise dj_exc_value.with_traceback(traceback) from exc_value
  78.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
  79.     return self.cursor.execute(sql, params)
  80.            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  81.   File "/opt/homebrew/lib/python3.11/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
  82.     return Database.Cursor.execute(self, query, params)
  83.            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  84. 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.

答案1

得分: 5

最近，在使用 Django SQLite 数据库时，我遇到了 "django.db.utils.OperationalError: no such column: rowid" 错误。

经过一些研究，我在这里找到了一个有用的解决方案（https://code.djangoproject.com/ticket/32935）

在迁移和执行 migrate 之前，请运行以下命令：

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

我希望这个解决方案有效。

英文:

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:

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

I hope this solution works.

答案2

得分: 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

  1. AND NOT EXISTS
  2. (SELECT eval('SELECT rowid FROM ' || NEW.table_name ||
  3. ' 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

英文:

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

  1. AND NOT EXISTS
  2. (SELECT eval('SELECT rowid FROM ' || NEW.table_name ||
  3. ' 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

huangapple
  • 本文由 发表于 2023年3月7日 05:55:07
  • 转载请务必保留本文链接：https://go.coder-hub.com/75656184.html
  • django
  • geodjango
  • spatialite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定