Adding field to Django model when the column already exists in the database-CodePudding

I have a model in Django which represents a MySQL table that has some extra columns. I want to add a field to the model for one of these extra columns but I'm not sure how best to do it.

Let's say the person table has an age column. My model looks like:

class Person(models.Model):
    name = models.CharField(min_length=200)

If I add an age field like:

    age = models.IntegerField(db_column="age")

then when I migrate I get an error about "Duplicate column name 'age'" because it tries to create it. Is there a way around this?

What I've tried:

Add the field with a new column and make a migration for that:
```
    age = models.IntegerField(db_column="age_2")
```
Create a manual data migration to copy data from original column to new one:
UPDATE person SET age_2 = age;
Create a manual migration to drop the original column:
ALTER TABLE person DROP COLUMN age;
Create a manual migration to rename the new column:
ALTER TABLE person CHANGE COLUMN age_2 age INT(11) NOT NULL;
On the model change it to use the age column (db_column="age") and make an automatic migration.

This works on my existing database, but when I run my tests, and it applies all the migrations to create a test database, it complains about Unknown column 'age' in 'field list' (with no indication which migration is causing this).

Traceback from trying to run tests:

Traceback (most recent call last):
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/django/base.py", line 149, in execute
    return self.cursor.execute(query, new_args)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/cursor.py", line 572, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/connection.py", line 922, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/connection.py", line 732, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'age' in 'field list'

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

Traceback (most recent call last):
  File "manage.py", line 24, in <module>
    main()
  File "manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/commands/test.py", line 24, in run_from_argv
    super().run_from_argv(argv)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/base.py", line 414, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/commands/test.py", line 68, in handle
    failures = test_runner.run_tests(test_labels)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/test/runner.py", line 1000, in run_tests
    old_config = self.setup_databases(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/test/runner.py", line 898, in setup_databases
    return _setup_databases(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/test/utils.py", line 220, in setup_databases
    connection.creation.create_test_db(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/backends/base/creation.py", line 79, in create_test_db
    call_command(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/__init__.py", line 198, in call_command
    return command.execute(*args, **defaults)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/base.py", line 98, in wrapped
    res = handle_func(*args, **kwargs)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 290, in handle
    post_migrate_state = executor.migrate(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/migrations/executor.py", line 131, in migrate
    state = self._migrate_all_forwards(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/migrations/executor.py", line 163, in _migrate_all_forwards
    state = self.apply_migration(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/migrations/executor.py", line 248, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/migrations/migration.py", line 131, in apply
    operation.database_forwards(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/migrations/operations/special.py", line 106, in database_forwards
    self._run_sql(schema_editor, self.sql)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/migrations/operations/special.py", line 129, in _run_sql
    schema_editor.execute(sql, params=params)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 192, in execute
    cursor.execute(sql, params)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/django/base.py", line 149, in execute
    return self.cursor.execute(query, new_args)
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/cursor.py", line 572, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/connection.py", line 922, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/root/.local/share/virtualenvs/code-_Py8Si6I/lib/python3.8/site-packages/mysql/connector/connection.py", line 732, in _handle_result
    raise errors.get_exception(packet)
django.db.utils.ProgrammingError: (1054, "1054 (42S22): Unknown column 'age' in 'field list'", '42S22')

CodePudding user response：

I found a solution but it seems so laborious and hacky that I really hope there's a better way. But, this works for both the code with the existing legacy database (that already has an age column) and when running the tests that start by creating a fresh database.

Add the field to the model, with the column name we want to end up with (age):

class Person(models.Model):
    age = models.IntegerField(db_column="age", null=True)

Do ./manage.py makemigrations and edit the migration that was generated, changing the db_column from age to age_2:

# ...

operations = [
    migrations.AddField(
        model_name="person",
        name="age",
        field=models.IntegerField(
            db_column="age_2",  # Changed this from age to age_2
            null=True,
        ),
    ),
]

Create a manual migration (./manage.py makemigrations --empty appname) and edit it to create a migration that will copy data from age to age_2 only if the age column already exists:

from django.db import migrations

def copy_age(apps, schema_editor):
    with schema_editor.connection.cursor() as cursor:
        cursor.execute("SHOW COLUMNS FROM `person` LIKE 'age';")

        if cursor.fetchone() is not None:
            cursor.execute("UPDATE person SET age_2 = age")


class Migration(migrations.Migration):

    dependencies = [
        ("api", "0017_person_age_with_new_column"),
    ]

    operations = [migrations.RunPython(copy_age)]

Create another manual migration and edit it to create a migration that will drop the original age column only if it already exists:

from django.db import migrations

def drop_age(apps, schema_editor):
    with schema_editor.connection.cursor() as cursor:
        cursor.execute("SHOW COLUMNS FROM `person` LIKE 'age';")

        if cursor.fetchone() is not None:
            cursor.execute("ALTER TABLE person DROP COLUMN age")

def add_age(apps, schema_editor):
    "For reverse migrations"
    with schema_editor.connection.cursor() as cursor:
        cursor.execute("ALTER TABLE person ADD COLUMN age INT(11) DEFAULT NULL")


class Migration(migrations.Migration):

    dependencies = [
        ("api", "0018_copy_age_column_to_age_2"),
    ]

    operations = [migrations.RunPython(drop_age, add_age)]

Create one more manual migration to change age_2 to age, and tell Django that we really are using the age column for this field:

from django.db import migrations, models

class Migration(migrations.Migration):

    dependencies = [
        ("api", "0019_drop_age_column"),
    ]

    operations = [
        migrations.RunSQL(
            "ALTER TABLE person CHANGE COLUMN age_2 age INT(11) DEFAULT NULL",
            state_operations=[
                migrations.AlterField(
                    model_name="person",
                    name="age",
                    field=models.IntegerField(
                        db_column="age",
                        null=True,
                    ),
                ),
            ],
        )
    ]

CodePudding user response：

I think the better way you're after is:

Add the age field to the Django model;
Generate the migration with makemigrations;
Instead of running the migration normally, run migrate with the --fake parameter.

This tells Django that the migration has already been applied to the database (as the column already exists) and so it should just mark the migration as having been applied.