python - Django-Cities-Light，在 postgresql 中将字段更改为 ForeignKey 会引发错误

Question

我的目标是使用 Django-Cities-Light 并将我的模型与外键链接到 django-cities-light 中的城市和国家模型。

当我运行 python3 manage.py migrate 时，出现以下错误:

Operations to perform:
  Synchronize unmigrated apps: gis, crispy_forms, geoposition, messages, staticfiles
  Apply all migrations: amenities, sites, images, venues, sessions, contenttypes, admin, auth, newsletter, registration, easy_thumbnails, cities_light
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  Applying venues.0012_auto_20160514_2024...Traceback (most recent call last):
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: column "venue_city_id" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.


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

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 354, in execute_from_command_line
    utility.execute()
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 346, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 394, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 445, in execute
    output = self.handle(*args, **options)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/commands/migrate.py", line 222, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/executor.py", line 110, in migrate
    self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/executor.py", line 148, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/migration.py", line 115, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/operations/fields.py", line 201, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 484, in alter_field
    old_db_params, new_db_params, strict)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 636, in _alter_field
    params,
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 111, in execute
    cursor.execute(sql, params)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/utils.py", line 98, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "venue_city_id" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

当我运行 python3 manage.py sqlmigrate venues 0012 时，我得到以下回溯:

Traceback (most recent call last):
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/apps/registry.py", line 148, in get_app_config
    return self.app_configs[app_label]
KeyError: 'cities_light'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/state.py", line 238, in __init__
    model = self.get_model(lookup_model[0], lookup_model[1])
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/apps/registry.py", line 202, in get_model
    return self.get_app_config(app_label).get_model(model_name.lower())
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/apps/registry.py", line 150, in get_app_config
    raise LookupError("No installed app with label '%s'." % app_label)
LookupError: No installed app with label 'cities_light'.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 354, in execute_from_command_line
    utility.execute()
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/__init__.py", line 346, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 394, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/commands/sqlmigrate.py", line 31, in execute
    return super(Command, self).execute(*args, **options)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/base.py", line 445, in execute
    output = self.handle(*args, **options)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/core/management/commands/sqlmigrate.py", line 57, in handle
    sql_statements = executor.collect_sql(plan)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/executor.py", line 127, in collect_sql
    state = migration.apply(state, schema_editor, collect_sql=True)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/migration.py", line 115, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/operations/fields.py", line 186, in database_forwards
    to_model = to_state.apps.get_model(app_label, self.model_name)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/utils/functional.py", line 59, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/state.py", line 166, in apps
    return StateApps(self.real_apps, self.models)
  File "/Users/iam-tony/.envs/venuepark/lib/python3.4/site-packages/django/db/migrations/state.py", line 248, in __init__
    raise ValueError(msg.format(field=operations[0][1], model=lookup_model))
ValueError: Lookup failed for model referenced by field venues.Venue.venue_city: cities_light.City

这是 0012 迁移文件:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('venues', '0011_venue_map_activation'),
    ]

    operations = [
        migrations.AlterField(
            model_name='venue',
            name='venue_city',
            field=models.ForeignKey(to='cities_light.City', blank=True),
        ),
    ]

我的模型如下:

class Venue(models.Model):
    venue_owner = models.ForeignKey(User, null=True)
    venue_name = models.CharField(max_length=100)
    venue_address = models.CharField(max_length=250)
    venue_city = models.CharField(max_length=250)
    venue_zipcode = models.CharField(max_length=20, blank=True)
    venue_seated_capacity = models.PositiveIntegerField()
    venue_standing_capacity = models.PositiveIntegerField()
    venue_type = models.CharField(max_length=20, choices=VENUE_TYPES)
    venue_sqf = models.PositiveIntegerField(default=0)
    venue_description = models.TextField(blank=False, null=True)
    featurete = models.PositiveIntegerField(default=0)
    carousel = models.PositiveIntegerField(default=0)
    gallery = models.PositiveIntegerField(default=0)
    #map_activation is for activating the map for the venue
    map_activation = models.BooleanField(default=False)
    position = GeopositionField(blank=True)

我的设置如下:

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.sites',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    #GEOdjango
    'django.contrib.gis',
    #Django cities Light
    'cities_light',
    #Custom Apps
    'venues',
    'images',
    'amenities',
    #Third party apps
    'registration',
    'crispy_forms',
    'easy_thumbnails',
    'geoposition',
)


CITIES_LIGHT_TRANSLATION_LANGUAGES = ['nl', 'en']
CITIES_LIGHT_INCLUDE_COUNTRIES = ['NL']

这导致了错误。将 venue_city 从 charfield 更改为 foreignkey。我尝试改回模型，但仍然出现相同的错误。

如何修复此错误并创建与 Django-cities-models 的连接？

Answer 1

假设此时您的 venue_city 列包含与城市模型中的主键对应的整数值。您仍然可以通过编辑 0012_auto_20160514_2024 迁移文件来执行迁移。先做

./manage.py sqlmigrate venues 0012 

密切注意生成的 SQl 中与 venue_city 列对应的位。现在您需要编辑迁移文件并将默认生成的迁移替换为 migrations.RunSQL。进入其中的查询本质上是您使用 sqlmigration 找到的查询。只需将 USING venue_city::integer 添加到末尾即可。

更新:根据您更新的答案，您将替换此部分

    migrations.AlterField(
        model_name='venue',
        name='venue_city',
        field=models.ForeignKey(to='cities_light.City', blank=True),
    ),

使用修改列的自定义 SQL。

更新 2:由于您无法运行 sqlmigrate，我将尝试向您展示该 SQL 应该是什么。

migrations.RunSQL(''' ALTER TABLE venues_venue ALTER venue_city TYPE integer USING  venue_city::integer '''),

migrations.RunSQL(''' ALTER TABLE venues_venue ALTER venue_city DROP NOT NULL '''),

migrations.RunSQL(''' ALTER TABLE venues_venue ALTER venue_city RENAME COLUMN venue_city TO venue_city_id '''),

migrations.RunSQL(''' ALTER TABLE venues_venue ADD CONSTRAINT venues_venus_somefk FOREIGN KEY (venue_city_id) REFERENCES  cities_light (id) DEFERRABLE INITIALLY DEFERRED'''),

完整查询本质上就是 sqlmigrate 显示的内容。