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
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.