pythonsql-serverdjangodjango-mssql

Django and django-mssql - problem with rendering ForeignKey field in ModelForm


In my Django application I would like to use 2 databases. First (default one) - sqlite3 and second MS SQL. Since Django don't support MsSQL I'm forced to use 3rd party django-mssql package.

I have model with ForeignKey and ModelForm based on this model. The problem is that I get an error "no such table: TLC_OWDP_InstructionParameters" while trying to render ForeignKey form field. It looks like django tries to find the table in wrong database.

I tried use PostgreSQL instead of MS SQL and everything works fine.

Could someone please help me with this issue? My code below:

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    
    'logistyka': {
        'ENGINE': 'mssql',
        'NAME': 'LOGISTYKA',
        'HOST': 'WPLRZD0A',
        'PORT': '',
        'USER'      : '***',
        'PASSWORD'  : '***',

        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    },
}

models.py

class InstructionParameter(models.Model):
    id = models.AutoField(db_column='id', primary_key=True)
    instr_number = models.CharField(max_length=30, verbose_name='Numer instrukcji')
    tab_number = models.SmallIntegerField(verbose_name='Numer tabeli', 
                                     validators=[MinValueValidator(1), MaxValueValidator(99)])
    point_number = models.SmallIntegerField(verbose_name='Numer punktu tabeli', 
                                       validators=[MinValueValidator(1), MaxValueValidator(99)])

    def __str__(self):
        return f'{self.instr_number} tab.{self.tab_number} p.{self.point_number}'

    class Meta:
        verbose_name_plural = 'Instrukcje'
        managed = False
        db_table = 'TLC_OWDP_InstructionParameters'
    

class PartParameter(models.Model):
    id = models.AutoField(primary_key=True)
    part_number = models.CharField(max_length=30, verbose_name='Numer części')
    op_number = models.CharField(max_length=4, verbose_name='Numer operacji')
    instruction_id = models.ForeignKey('InstructionParameter', to_field='id', db_column='instruction_id', blank=True, null=True,
                                    on_delete=models.SET_NULL, 
                                    verbose_name='Odniesienie do instrukcji')
    
    class Meta:
        managed = False
        db_table = 'TLC_OWDP_PartParameters'
        verbose_name_plural = 'Części'

    def __str__(self):
        return f'{self.part_number} op: {self.op_number}'

forms.py

class PartModelForm(forms.ModelForm):

    class Meta:
        model = models.PartParameter
        fields = '__all__'
        
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

        self.fields = add_bootstrap_classes(self.fields)

views.py

...
'PartForm': forms.PartModelForm,
...

Template .html - line wchich causes the problem

{{ PartForm.instruction_id }}

Error

OperationalError at /params/
no such table: TLC_OWDP_InstructionParameters
Request Method: GET
Request URL:    http://localhost:5200/params/
Django Version: 4.2.9
Exception Type: OperationalError
Exception Value:    
no such table: TLC_OWDP_InstructionParameters
Exception Location: C:\Users\p535112\source\repos\WsadyDoPiecow\WsadyDoPiecow\env\lib\site-packages\django\db\backends\sqlite3\base.py, line 328, in execute
Raised during:  params.views.ParameterView
Python Executable:  C:\Users\p535112\source\repos\WsadyDoPiecow\WsadyDoPiecow\env\Scripts\python.exe
Python Version: 3.10.7
Python Path:    
['C:\\Users\\p535112\\source\\repos\\WsadyDoPiecow\\WsadyDoPiecow',
 'C:\\Users\\p535112\\source\\repos\\WsadyDoPiecow\\WsadyDoPiecow',
 'C:\\Users\\p535112\\AppData\\Local\\Programs\\Python\\Python310\\python310.zip',
 'C:\\Users\\p535112\\AppData\\Local\\Programs\\Python\\Python310\\DLLs',
 'C:\\Users\\p535112\\AppData\\Local\\Programs\\Python\\Python310\\lib',
 'C:\\Users\\p535112\\AppData\\Local\\Programs\\Python\\Python310',
 'C:\\Users\\p535112\\source\\repos\\WsadyDoPiecow\\WsadyDoPiecow\\env',
 'C:\\Users\\p535112\\source\\repos\\WsadyDoPiecow\\WsadyDoPiecow\\env\\lib\\site-packages']
Server time:    Wed, 21 Feb 2024 14:38:19 +0100

Screenshot of the error


Solution

  • Finally I've found the solution.

    This article of the official documentation is very helpful.

    Is is necessary to create custom router with db_for_read and db_for_write methods. These methods should return connection name (name of database in the settings.py) relevant to the model.