Dajango FK constraint complaint, but object is there. What do I miss?-CodePudding

I am at my first Django app, please bear with me...

It's based on some legacy scripts with an existing PostgreSQL database. Hence, the naming of some of the tables doesn't follow the Django naming schema.

Currently I am in the middle of remodeling my data to follow sane data modeling principles. The migration doing one of the steps gives me an foreign key exception I do not understand:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
psycopg2.errors.ForeignKeyViolation: insert or update on table "design_shopify_category" violates foreign key constraint "design_shopify_categ_shopifycollection_id_9f310876_fk_designs_s"
DETAIL:  Key (shopifycollection_id)=([<ShopifyCollection: ShopifyCollection object (gid://shopify/Collection/263596736569)>]) is not present in table "designs_shopifycollection".


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

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/local/lib/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/root/.vscode-server/extensions/ms-python.python-2021.11.1422169775/pythonFiles/lib/python/debugpy/__main__.py", line 45, in <module>
    cli.main()
  File "/root/.vscode-server/extensions/ms-python.python-2021.11.1422169775/pythonFiles/lib/python/debugpy/../debugpy/server/cli.py", line 444, in main
    run()
  File "/root/.vscode-server/extensions/ms-python.python-2021.11.1422169775/pythonFiles/lib/python/debugpy/../debugpy/server/cli.py", line 285, in run_file
    runpy.run_path(target_as_str, run_name=compat.force_str("__main__"))
  File "/usr/local/lib/python3.9/runpy.py", line 268, in run_path
    return _run_module_code(code, init_globals, run_name,
  File "/usr/local/lib/python3.9/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/local/lib/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/workspace/src/otaya_tool/manage.py", line 23, in <module>
    main()
  File "/workspace/src/otaya_tool/manage.py", line 19, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 230, in apply_migration
    migration_recorded = True
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 120, in __exit__
    self.atomic.__exit__(exc_type, exc_value, traceback)
  File "/usr/local/lib/python3.9/site-packages/django/db/transaction.py", line 246, in __exit__
    connection.commit()
  File "/usr/local/lib/python3.9/site-packages/django/utils/asyncio.py", line 33, in inner
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/base.py", line 266, in commit
    self._commit()
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
  File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/base.py", line 242, in _commit
    return self.connection.commit()
django.db.utils.IntegrityError: insert or update on table "design_shopify_category" violates foreign key constraint "design_shopify_categ_shopifycollection_id_9f310876_fk_designs_s"
DETAIL:  Key (shopifycollection_id)=([<ShopifyCollection: ShopifyCollection object (gid://shopify/Collection/263596736569)>]) is not present in table "designs_shopifycollection".

There are two Models, relevant to the error:

class ShopifyCollection(models.Model):
    graph_ql_id = models.CharField(max_length=250, primary_key=True,
                                   validators=[validate_sy_graph_ql_id])
    handle = models.CharField(max_length=250, blank=False, null=False,
                              validators=[validate_slug])
    title = models.CharField(max_length=250, blank=False, null=False)

def pk_uuid_gen() -> str:
    return str(uuid4())

class Design(models.Model):
    uuid = models.CharField(primary_key=True, unique=True, default=pk_uuid_gen,
                            editable=False, max_length=36)
    niche = models.CharField(blank=False, null=True, max_length=50)
    id = models.CharField(blank=False, null=True, max_length=4)
    variant = models.CharField(blank=False, null=True, max_length=1)
    language = models.CharField(max_length=2, default=None)


    # the new many 2 many relation to be filled by the migration.
    shopify_category = models.ManyToManyField(ShopifyCollection, blank=True)

    # the legacy field to get the values from...
    shopify_category_tmp = models.JSONField(blank=True, default=empty_development_default)

    class Meta:
        db_table = 'design'
        unique_together = ((niche', 'id', 'variant'),)

And this is the migration code:

# Generated by Django 3.2.9 on 2021-11-27 18:00

from django.db import migrations
from django.core.exceptions import ObjectDoesNotExist
from designs.models import Design, ShopifyCollection


def convert_shopify_category_json_objects_to_m2m(apps, schema_editor):

    # this doesn't give an error. It's the exact same key the FK exception 
    # complains about. It's also the first object it want's to add.

    x = ShopifyCollection.objects.get(pk='gid://shopify/Collection/263596736569')

    for design in Design.objects.all().iterator():
        if len(design.shopify_category_tmp) == 0:
            continue

        # the legacy field holds a list of strings like: ["archery", "outdoor"]
        for category in design.shopify_category_tmp:
            if len(category) == 0:
                continue

            sy_col_handles = [
                f'niche-de-{category}',
                f'featured-de-{category}']
            sy_collections = []
            for handle in sy_col_handles:
                try:
                    sy_collections.append(ShopifyCollection.objects.get(handle=handle))

                except ObjectDoesNotExist:
                    pass

            if len(sy_collections) < 1:
                raise ValueError('There are no Shopify Collections for the handles '  
                                 f'{sy_col_handles}. We need at least one of them.')

            design.shopify_category.add(sy_collections)
            design.save()


class Migration(migrations.Migration):

    dependencies = [
        ('designs', '0031_shopify_categories_link_them'),
    ]

    operations = [
        migrations.RunPython(convert_shopify_category_json_objects_to_m2m),
    ]

The table designs_shopifycollection holds the row gid://shopify/Collection/263596736569 before the db transaction of migration starts which the exception is complaining about to be missing.

Also the line x = ShopifyCollection.objects.get(pk='gid://shopify/Collection/263596736569') proves the table contains the requested row in the db transaction of the migration.

The table also contains the "missing" row after the migration.

I tried to debug the issue through the Django framework code, but stopped after some time. Couldn't find more than the Exception is triggered by the commit...

Also adding the row into the m2n table by hand worked without any issues and shows as selected in the DesignFrom view.

I run out of ideas. Where can be the problem?

CodePudding user response：

You need to pass the items as individual parameters, so:

#                  asterisk ↓
design.shopify_category.add(*sy_collections)