python - django.db.utils.ProgrammingError : (1146, "Table ' med_portal.Custparent'不存在”)

Question

我正在跨两个数据库使用 ManyToMany Through 模型。

'default': {},
    'portaldb': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'med_portal',
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"
        },
        'USER': '****',
        'PASSWORD': '',
        'HOST': '***.***.**.*',
        'PORT': '****',
    },
    'smgpr': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'supplies',
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"
        },
        'USER': '****',
        'PASSWORD': '',
        'HOST': '***.***.
    **.*',
        'PORT': '****',
    },

路由器文件1(LocalRouter.py):

#LocalRouter.py
class DBLocalRouter(object):


    def db_for_read(self, model, **hints):

        app_list = ('auth', 'admin', 'contenttypes', 'sessions', 'messages', 'staticfiles', 'Test', 'Admin', 'Charges', 'Products', 'Patients', 'Relationships', 'apis', 'corsheaders', 'djoser', 'django_filters', 'users', 'rest_framework', 'debug_toolbar')

        if model._meta.app_label in app_list:
            return 'smgpr'
        return None

    def db_for_write(self, model, **hints):

        app_list = ('auth', 'admin', 'contenttypes', 'sessions', 'messages', 'staticfiles', 'Test', 'Admin', 'Charges', 'Products', 'Patients', 'Relationships', 'apis', 'corsheaders', 'djoser', 'django_filters', 'users', 'rest_framework', 'debug_toolbar')
        if model._meta.app_label in app_list:
            return 'smgpr'
        return None

    def allow_relation(self, obj1, obj2, **hints):

        app_list = ('auth', 'admin', 'contenttypes', 'sessions', 'messages', 'staticfiles', 'Test', 'Admin', 'Charges', 'Products', 'Patients', 'Relationships', 'apis', 'corsheaders', 'djoser', 'django_filters', 'users', 'rest_framework', 'debug_toolbar')
        if obj1._meta.app_label in app_list and obj2._meta.app_label in app_list:
            return True
        return None

    def allow_migrate(self, db, app_label, model=None, **hints):

        app_list = ('auth', 'admin', 'contenttypes', 'sessions', 'messages', 'staticfiles', 'Test', 'Admin', 'Charges', 'Products', 'Patients', 'Relationships', 'apis', 'corsheaders', 'djoser', 'django_filters', 'users', 'rest_framework', 'debug_toolbar')

        if app_label in app_list:
            return db == 'smgpr'
        return None

路由器文件2(GlobalRouter.py):

#GlobalRouter.py
class DBGlobalRouter(object):
    def db_for_read(self, model, **hints):
        """
        Attempts to read portal models go to portal.
        """
        app_list = ('portal', 'Test',)

        if model._meta.app_label in app_list:
            return 'portaldb'
        return None

    def db_for_write(self, model, **hints):
        app_list = ('portal', 'Test',)
        if model._meta.app_label in app_list:
            return 'portaldb'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        app_list = ('portal', 'Test',)
        if obj1._meta.app_label in app_list and obj2._meta.app_label in app_list:
            return True
        return True

    def allow_migrate(self, db, app_label, model=None, **hints):        
        app_list = ('portal', 'Test',)

        if app_label in app_list:
            return db == 'portaldb'
        return None

app1 的模型:

    class User(AbstractBaseUser):
        username            = models.CharField(max_length=255, unique=True)
        email               = models.EmailField(max_length=255, unique=True)
        full_name           = models.CharField(max_length=255, blank=True, null=True)
        custparent          = models.ManyToManyField('portal.Custparent', related_name='facility', through='Relation')
        active              = models.BooleanField(default=True)
        staff               = models.BooleanField(default=False)
        admin               = models.BooleanField(default=False)
        jwt_secret          = models.UUIDField(default=uuid.uuid4)
        created_at          = models.DateTimeField(auto_now_add=True)

    class Relation(models.Model):
        user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.DO_NOTHING)
        custparent = models.ForeignKey('portal.Custparent', related_name='profile', on_delete=models.DO_NOTHING)

        class Meta:
            db_table= 'relation'
            unique_together = ('user', 'custparent')

app2 的模型:

    pbd_name = settings.DATABASES['portaldb']['NAME']


    class Custparent(models.Model):
        custparent_id = models.BigAutoField(primary_key=True)
        name = models.CharField(max_length=255, blank=True, null=True)
        comm_queue = models.CharField(max_length=128, blank=True, null=True)
        email = models.CharField(db_column='EMail', max_length=255, blank=True, null=True)  # Field name made lowercase.
        invoice_email = models.CharField(db_column='Invoice_EMail', max_length=128, blank=True, null=True)  # Field name made lowercase.
        portal_id = models.IntegerField(blank=True, null=True)
        sentupdate = models.DateTimeField(db_column='SentUpdate', blank=True, null=True)  # Field name made lowercase.
        webaddress = models.CharField(max_length=255, blank=True, null=True)
        webext = models.CharField(max_length=3, blank=True, null=True)
        passcode = models.CharField(db_column='PassCode', max_length=128, blank=True, null=True)  # Field name made lowercase.
        autoapprove = models.IntegerField(db_column='AutoApprove', blank=True, null=True)  # Field name made lowercase.
        testaccount = models.IntegerField(db_column='TestAccount', blank=True, null=True)  # Field name made lowercase.
        privateemail = models.IntegerField(db_column='PrivateEmail', blank=True, null=True)  # Field name made lowercase.
        forcemanponum = models.IntegerField(db_column='ForceManPONum', blank=True, null=True)  # Field name made lowercase.
        procuretopay = models.IntegerField(db_column='ProcureToPay', blank=True, null=True)  # Field name made lowercase.
        medassets_member = models.IntegerField(db_column='MedAssets_Member', blank=True, null=True)  # Field name made lowercase.
        medassets_facnum = models.IntegerField(db_column='MedAssets_FacNum', blank=True, null=True)  # Field name made lowercase.
        hideglorder = models.IntegerField(db_column='HideGLOrder', blank=True, null=True)  # Field name made lowercase.
        searchall = models.IntegerField(db_column='SearchAll', blank=True, null=True)  # Field name made lowercase.
        accountlabel = models.CharField(db_column='AccountLabel', max_length=50, blank=True, null=True)  # Field name made lowercase.
        inhouselabel = models.CharField(db_column='InHouseLabel', max_length=50, blank=True, null=True)  # Field name made lowercase.
        privatelabellogo = models.CharField(db_column='PrivateLabelLogo', max_length=120, blank=True, null=True)  # Field name made lowercase.
        hin = models.CharField(db_column='HIN', max_length=9, blank=True, null=True)  # Field name made lowercase.
        gln = models.CharField(db_column='GLN', max_length=13, blank=True, null=True)  # Field name made lowercase.
        shiptoattn = models.CharField(db_column='ShiptoAttn', max_length=64, blank=True, null=True)  # Field name made lowercase.
        shiptoaddress = models.CharField(db_column='ShiptoAddress', max_length=64, blank=True, null=True)  # Field name made lowercase.
        shiptocity = models.CharField(db_column='ShiptoCity', max_length=64, blank=True, null=True)  # Field name made lowercase.
        shiptostateprov = models.CharField(db_column='ShiptoStateProv', max_length=24, blank=True, null=True)  # Field name made lowercase.
        shiptopostalcode = models.CharField(db_column='ShiptoPostalCode', max_length=24, blank=True, null=True)  # Field name made lowercase.
        shiptophone = models.CharField(db_column='ShiptoPhone', max_length=24, blank=True, null=True)  # Field name made lowercase.
        shiptofax = models.CharField(db_column='ShiptoFax', max_length=24, blank=True, null=True)  # Field name made lowercase.
        multivendor = models.IntegerField(db_column='MultiVendor', blank=True, null=True)  # Field name made lowercase.
        lockformulary = models.IntegerField(db_column='LockFormulary', blank=True, null=True)  # Field name made lowercase.
        active = models.IntegerField(db_column='Active', blank=True, null=True)  # Field name made lowercase.
        autoadded = models.IntegerField(db_column='AutoAdded', blank=True, null=True)  # Field name made lowercase.
        allowpatientedit = models.IntegerField(db_column='AllowPatientEdit', blank=True, null=True)  # Field name made lowercase.
        displaymaxonorders = models.IntegerField(db_column='DisplayMaxOnOrders', blank=True, null=True)  # Field name made lowercase.

        class Meta:
            managed = False
            # db_table = 'custparent'
            db_table = '`%s`.`custparent`' % pbd_name

        def __str__(self):
            return f'{str(self.custparent_id)} - {self.name}'

使用管理中的关系表创建对象时，一切正常。然而，每当我尝试使用 DjangoRestFramework 查看可浏览 api 中的 User 对象时。我收到此错误:django.db.utils.ProgrammingError:(1146，“表'med_portal.relation'不存在”)

完整堆栈跟踪:

Traceback (most recent call last):
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/core/handlers/base.py", line 117, in _get_response
    response = middleware_method(request, callback, callback_args, callback_kwargs)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/debug_toolbar/middleware.py", line 85, in process_view
    response = panel.process_view(request, view_func, view_args, view_kwargs)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/debug_toolbar/panels/profiling.py", line 160, in process_view
    return self.profiler.runcall(view_func, *args, **view_kwargs)
  File "/usr/lib/python3.6/cProfile.py", line 109, in runcall
    return func(*args, **kw)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/viewsets.py", line 116, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/views.py", line 495, in dispatch
    response = self.handle_exception(exc)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/views.py", line 455, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/views.py", line 492, in dispatch
    response = handler(request, *args, **kwargs)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/mixins.py", line 45, in list
    return self.get_paginated_response(serializer.data)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/serializers.py", line 765, in data
    ret = super(ListSerializer, self).data
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/serializers.py", line 262, in data
    self._data = self.to_representation(self.instance)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/serializers.py", line 683, in to_representation
    self.child.to_representation(item) for item in iterable
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/serializers.py", line 683, in <listcomp>
    self.child.to_representation(item) for item in iterable
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/serializers.py", line 527, in to_representation
    ret[field.field_name] = field.to_representation(attribute)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/rest_framework/relations.py", line 542, in to_representation
    for value in iterable
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/models/query.py", line 268, in __iter__
    self._fetch_all()
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/models/query.py", line 1186, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/models/query.py", line 54, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1065, in execute_sql
    cursor.execute(sql, params)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/debug_toolbar/panels/sql/tracking.py", line 186, in execute
    return self._record(self.cursor.execute, sql, params)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/debug_toolbar/panels/sql/tracking.py", line 124, in _record
    return method(sql, params)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/home/iclark/.local/share/virtualenvs/backend-2BnCgv4_/lib/python3.6/site-packages/MySQLdb/connections.py", line 277, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table 'med_portal.relation' doesn't exist")

任何帮助将不胜感激!

Answer 1

假设您有这些过于简单的结构:

app1.models

from django.db import models

class A(models.Model):
    z = models.TextField()

app2.models

from django.db import models

class B(models.Model):
    aref = models.ForeignKey(to='app1.A', on_delete=models.CASCADE)

app1.app1_router

class MyAppRouter:
    def db_for_read(self, model, **hints):
        if model._meta.app_label in ('app1', 'test'):
            return 'app1_db'
        return 'default'

    def db_for_write(self, model, **hints):
        if model._meta.app_label in ('app1', 'test'):
            return 'app1_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        if obj1._meta.app_label in ('app1', 'test') or obj2._meta.app_label in ('app1', 'test'):
            return True

        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in ('app1', 'test'):
            return db == 'app1_db'
        return None

数据库的这些设置:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },

    'app1_db': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'app1.sqlite3'),
    }
}

DATABASE_ROUTERS = ["app1.app1_router.MyAppRouter",]

现在显然我们有两个数据库

• 主数据库:默认
• app1 数据库:app1_db

显然，我们有一个从 app2.B (位于 default 数据库中)到 app1 的 aref =foreignKey。 A(位于 app1_db 数据库中)。

我们显然会将数据库迁移为:​​

./manage.py migrate
./manage.py migrate --database=app1_db

覆盖两个数据库。

现在假设我们要创建两个对象并将它们链接到这些模型:

a1 = A.objects.create()
b1 = B.objects.create(aref=a1)

将会发生的是，a1 实例将很容易创建，因为我们的 MyRouter 路由器将处理数据库写入，并将指导 SQL INSERT根据需要将 code> 添加到 app1_db 中，但是当我们尝试编写 B 实例时，尤其是当我们尝试链接到 时，问题就出现了app1_a 模型，该模型在 default 数据库中不存在(由于 aref=a1 导致发生)。

我猜当您尝试通过 rest_framework 访问它时发生的情况基本上是相同的。

事情是 Django 一次在一个数据库上运行，就我而言，有两个选项:

• 要么使用单个数据库(让您免去进行定制的痛苦，这里的问题是，为什么您首先需要多个数据库？)
• 迁移两个数据库中的表并在保存时在两个数据库之间同步(大量自定义 -> 取决于您的架构决策)。解决外键问题的示例使用了一些自定义 ForeignKey 实现 like in this article ，与 this implementation