Django can't add new item to another database on admin site-CodePudding

I'm using two databases to create an app and everything looks fine, I can change, delete the existing item but can't add new item to other database table on the admin site. The data should be stored in dbcost.tbItemDetail instead of userauth.tbItemDetail. Does anyone know how to correct this? Thanks

Here is the code: code on admin.py:

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

#Create MultiDBModelAdmin to expose multiple databases for admin site
class MultiDBModelAdmin(admin.ModelAdmin): 
    
    def save_model(self, request, obj, form, change):
         # Tell Django to save objects to the other database.
        obj.save(using=self.using)
        
    def delete_model(self, request, obj):
        # Tell Django to delete objects from the other database
        obj.delete(using=self.using)

    def get_queryset(self, request):
        # Tell Django to look for objects on the other database.
        return super().get_queryset(request).using(self.using)

    def formfield_for_foreignkey(self, db_field, request, **kwargs):
        # Tell Django to populate ForeignKey widgets using a query
        # on the other database.
        return super().formfield_for_foreignkey(db_field, request, using=self.using, **kwargs)

    def formfield_for_manytomany(self, db_field, request, **kwargs):
        # Tell Django to populate ManyToMany widgets using a query
        # on the other database.
        return super().formfield_for_manytomany(db_field, request, using=self.using, **kwargs)
# Register your other database here:
class tb_companyinfo(MultiDBModelAdmin):
    using = 'dbcost' #select database name    
    list_display = ('name','emailaddress',) #add different fields into admin site

class tb_itemdetail(MultiDBModelAdmin):
    using = 'dbcost' #select database name    
    list_display = ('sku','item_name',) #add different fields into admin site 

#class tb_invoice(MultiDBModelAdmin):
    #using = 'dbcost' #select database name    
    #list_display = ('companyid','invoicedate',) #add different fields into admin site 

# Register your models here.

admin.site.register(companyinfo, tb_companyinfo)
admin.site.register(itemdetail, tb_itemdetail)
#admin.site.register(invoice, tb_invoice)

mode.py:

from django.db import models
class companyinfo(models.Model):
    id = models.CharField(db_column='ID', primary_key=True, max_length=100)  # Field name made lowercase.
    name = models.CharField(db_column='Name', max_length=200)  # Field name made lowercase.
    address1 = models.CharField(db_column='Address1', max_length=200)  # Field name made lowercase.
    address2 = models.CharField(db_column='Address2', max_length=200)  # Field name made lowercase.
    phonenumber = models.CharField(db_column='PhoneNumber', max_length=100, verbose_name='Phone Number')  # Field name made lowercase.
    emailaddress = models.CharField(db_column='EmailAddress', max_length=100, verbose_name='Email Address')  # Field name made lowercase.
    country = models.CharField(db_column='Country', max_length=100)  # Field name made lowercase.

    class Meta:        
        verbose_name_plural = 'Company Info' #show the name as 'Company Info' in admin instead of companyinfo
        managed = False
        db_table = 'tbCompanyInfo'
class invoice(models.Model):
    id = models.OneToOneField('itemInvoice', models.DO_NOTHING, db_column='ID', primary_key=True)  # Field name made lowercase.
    companyid = models.ForeignKey(companyinfo, models.DO_NOTHING, db_column='Company ID')  # Field name made lowercase.
    invoicedate = models.DateField(db_column='InvoiceDate')  # Field name made lowercase.

    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)  # Field name made lowercase.
    item_name = models.CharField(db_column='Item Name', max_length=100)  # Field name made lowercase. Field renamed to remove unsuitable characters.
    shortdescription = models.TextField(db_column='ShortDescription', verbose_name='Short Description')  # Field name made lowercase.
    detaildescription = models.TextField(db_column='DetailDescription', verbose_name='Detail Description')  # Field name made lowercase.
    
    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)  # Field name made lowercase.
    invoice_no = models.CharField(db_column='Invoice_No', max_length=50)  # Field name made lowercase.
    sku = models.ForeignKey(itemdetail, models.DO_NOTHING, db_column='SKU')  # Field name made lowercase.
    quantity = models.IntegerField(db_column='Quantity')  # Field name made lowercase.
    cost = models.DecimalField(db_column='Cost', max_digits=5, decimal_places=2)  # Field name made lowercase.

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

database in setting.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',
    }
}

The error show up when I filled the form and hit save to add the new item on admin site: error showed when hit save

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

CodePudding user response：

You can use Database Router to force writing in the right db.

class CustomDbRouter:
    def db_for_read(self, model, **hints):
        if model == itemdetail:
            return "dbcost"
        return None

    def db_for_write(self, model, **hints):
        if model == itemdetail:
            return "dbcost"
        return None

add in settings.py

DATABASE_ROUTERS = ["app_name.CustomDbRouter"]