Django后台无法向非默认数据库添加新条目问题求助

使用双数据库开发Django应用，修改、删除非默认数据库（dbcost）中的现有条目功能正常，但无法在Admin后台向dbcost.tbItemDetail添加新条目，系统尝试将数据存入默认数据库userauth.tbItemDetail，并报错：Table 'userauth.tbItemDetail' doesn't exist。

admin.py #

from unicodedata import name
from django.contrib import admin
from .models import *

# 创建MultiDBModelAdmin以支持Admin站点操作多数据库
class MultiDBModelAdmin(admin.ModelAdmin): 
    
    def save_model(self, request, obj, form, change):
         # 指定保存对象到目标数据库
        obj.save(using=self.using)
        
    def delete_model(self, request, obj):
        # 指定从目标数据库删除对象
        obj.delete(using=self.using)

    def get_queryset(self, request):
        # 指定从目标数据库查询对象
        return super().get_queryset(request).using(self.using)

    def formfield_for_foreignkey(self, db_field, request, **kwargs):
        # 指定外键字段从目标数据库获取数据
        return super().formfield_for_foreignkey(db_field, request, using=self.using, **kwargs)

    def formfield_for_manytomany(self, db_field, request, **kwargs):
        # 指定多对多字段从目标数据库获取数据
        return super().formfield_for_manytomany(db_field, request, using=self.using, **kwargs)

# 注册目标数据库模型
class tb_companyinfo(MultiDBModelAdmin):
    using = 'dbcost' # 指定数据库
    list_display = ('name','emailaddress',) # Admin列表显示字段

class tb_itemdetail(MultiDBModelAdmin):
    using = 'dbcost' # 指定数据库
    list_display = ('sku','item_name',) # Admin列表显示字段 

# 注册模型
admin.site.register(companyinfo, tb_companyinfo)
admin.site.register(itemdetail, tb_itemdetail)

models.py #

from django.db import models

class companyinfo(models.Model):
    id = models.CharField(db_column='ID', primary_key=True, max_length=100)  # 字段名转小写
    name = models.CharField(db_column='Name', max_length=200)  # 字段名转小写
    address1 = models.CharField(db_column='Address1', max_length=200)  # 字段名转小写
    address2 = models.CharField(db_column='Address2', max_length=200)  # 字段名转小写
    phonenumber = models.CharField(db_column='PhoneNumber', max_length=100, verbose_name='Phone Number')  # 字段名转小写
    emailaddress = models.CharField(db_column='EmailAddress', max_length=100, verbose_name='Email Address')  # 字段名转小写
    country = models.CharField(db_column='Country', max_length=100)  # 字段名转小写

    class Meta:        
        verbose_name_plural = 'Company Info' # Admin显示复数名称
        managed = False
        db_table = 'tbCompanyInfo'

class invoice(models.Model):
    id = models.OneToOneField('itemInvoice', models.DO_NOTHING, db_column='ID', primary_key=True)  # 字段名转小写
    companyid = models.ForeignKey(companyinfo, models.DO_NOTHING, db_column='Company ID')  # 字段名转小写
    invoicedate = models.DateField(db_column='InvoiceDate')  # 字段名转小写

    class Meta:
        verbose_name_plural = 'Invoice'
        managed = False
        db_table = 'tbInvoice'

class itemdetail(models.Model):
    sku = models.CharField(db_column='SKU', primary_key=True, max_length=50)  # 字段名转小写
    item_name = models.CharField(db_column='Item Name', max_length=100)  # 字段名转小写，重命名去除特殊字符
    shortdescription = models.TextField(db_column='ShortDescription', verbose_name='Short Description')  # 字段名转小写
    detaildescription = models.TextField(db_column='DetailDescription', verbose_name='Detail Description')  # 字段名转小写
    
    class Meta:
        verbose_name_plural = 'Product Details'
        managed = False
        db_table = 'tbItemDetail'

class itemInvoice(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True)  # 字段名转小写
    invoice_no = models.CharField(db_column='Invoice_No', max_length=50)  # 字段名转小写
    sku = models.ForeignKey(itemdetail, models.DO_NOTHING, db_column='SKU')  # 字段名转小写
    quantity = models.IntegerField(db_column='Quantity')  # 字段名转小写
    cost = models.DecimalField(db_column='Cost', max_digits=5, decimal_places=2)  # 字段名转小写

    class Meta:
        verbose_name_plural = 'Invoicing Items'
        managed = False
        db_table = 'tbItem_Invoice'

settings.py 数据库配置 #

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'userauth',
        'USER':'sql-conn',
        'HOST':'myhosting',
        'PORT':3307,
        'PASSWORD':'mypassword',
    },

    'dbcost': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'dbCosts',
        'USER':'sql-conn',
        'HOST':'myhosting',
        'PORT':3307,
        'PASSWORD':'mypassword',
    }
}

报错信息 #

ProgrammingError at /admin/invoicing/itemdetail/add/
(1146, "Table 'userauth.tbItemDetail' doesn't exist")
Request Method: POST
Request URL:    http://127.0.0.1:8000/admin/invoicing/itemdetail/add/
Django Version: 4.1
Exception Type: ProgrammingError
Exception Value:    
(1146, "Table 'userauth.tbItemDetail' doesn't exist")
Exception Location: C:\Python\.venv\lib\site-packages\MySQLdb\connections.py, line 254, in query
Raised during:  django.contrib.admin.options.add_view
Python Executable:  C:\Python\.venv\Scripts\python.exe
Python Version: 3.10.5
Python Path:    
['C:\Python\costcalculate\apps',
 'C:\Python\costcalculate',
 'C:\Users\VM1\AppData\Local\Programs\Python\Python310\python310.zip',
 'C:\Users\VM1\AppData\Local\Programs\Python\Python310\DLLs',
 'C:\Users\VM1\AppData\Local\Programs\Python\Python310\lib',
 'C:\Users\VM1\AppData\Local\Programs\Python\Python310',
 'C:\Python\.venv',
 'C:\Python\.venv\lib\site-packages']
Server time:    Wed, 24 Aug 2022 02:24:14 -0700

问题根源 #

新增条目时，Django表单会验证主键sku的唯一性，此时默认使用模型的默认管理器（连接默认数据库userauth），而该数据库中不存在tbItemDetail表，因此触发报错。修改/删除操作正常是因为这些逻辑直接指定了目标数据库，但验证步骤未覆盖。

方案一：给模型添加自定义数据库管理器（推荐） #

在models.py中定义一个绑定dbcost数据库的管理器，让模型的所有操作默认使用该数据库：

from django.db import models

# 定义自定义管理器，指定默认使用dbcost数据库
class DBCostManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().using('dbcost')

然后在需要操作dbcost数据库的模型（如companyinfo、itemdetail）中添加该管理器：

class companyinfo(models.Model):
    # ... 原有字段 ...
    objects = DBCostManager()  # 添加自定义管理器

    class Meta:
        # ... 原有元信息 ...

class itemdetail(models.Model):
    # ... 原有字段 ...
    objects = DBCostManager()  # 添加自定义管理器

    class Meta:
        # ... 原有元信息 ...

方案二：完善MultiDBModelAdmin，覆盖验证逻辑 #

在MultiDBModelAdmin中添加validate_unique方法，指定验证时使用目标数据库：

class MultiDBModelAdmin(admin.ModelAdmin): 
    # ... 原有方法 ...

    def validate_unique(self, request, form, obj=None):
        # 指定验证唯一性时使用目标数据库
        try:
            obj.validate_unique(using=self.using)
        except ValidationError as e:
            form._update_errors(e)

这样可以确保表单验证步骤使用指定数据库，避免查询默认数据库。

内容的提问来源于stack exchange，提问作者David L