Integrate data from other databases?

Using Django Using the ORM
1

Hello everyone,

I’m now making quite good progress with my first Django application. So far, the data model has worked as intended. But now I have to integrate data from another database. I don’t want to continue working with this data, it should only be displayed in the frontend.

Here is some basic information about the setup.

Django DB: Server01, DB Name = ‘Django’
Other data DB: Server02, DB Name = ‘Data’

Both DB servers are SQL Server from Microsoft. What is the best way to integrate the data of the other DB now?

At the moment, I have simply created a model for my third-party database table in the models.py. I can empty and fill them by ETL. But this is certainly not the cleanest way.

Specifically, this is about linking purchasing positions with responsibilities. The join must be done via the cost_bearer, department, and system fields between the Resposibility and BottleneckPart models.

This is what my models.py file looks like.

from django.db import models


# Create your models here.
class Project(models.Model):
    cost_bearer = models.CharField(max_length=6, primary_key=True, verbose_name='Kostenträger')
    description = models.CharField(max_length=50, unique=True, verbose_name='Bezeichnung')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Projekt'
        verbose_name_plural = 'Projekte'

    def __str__(self):
        return f'{self.cost_bearer} {self.description}'


class System(models.Model):
    number = models.CharField(max_length=4, primary_key=True, verbose_name='Nummer')
    description = models.CharField(max_length=100, verbose_name='Bezeichnung')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Baugruppe'
        verbose_name_plural = 'Baugruppen'

    def __str__(self):
        return f'{self.number} {self.description}'


class Department(models.Model):
    name = models.CharField(max_length=50, unique=True, verbose_name='Name')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Abteilung'
        verbose_name_plural = 'Abteilungen'

    def __str__(self):
        return self.name
    

class Team(models.Model):
    department = models.ForeignKey(Department, on_delete=models.CASCADE, verbose_name='Abteilung')
    name = models.CharField(max_length=50, unique=True, verbose_name='Name')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Team'
        verbose_name_plural = 'Teams'

    def __str__(self):
        return self.name


class Employee(models.Model):
    team = models.ManyToManyField(Team, verbose_name='Team')
    user = models.OneToOneField('auth.User', on_delete=models.CASCADE, verbose_name='Benutzer')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Mitarbeiter'
        verbose_name_plural = 'Mitarbeiter'

    def __str__(self):
        return f'{self.user.first_name} {self.user.last_name}'


class Responsibility(models.Model):
    project = models.ForeignKey(Project, on_delete=models.CASCADE, verbose_name='Projekt')
    system = models.ForeignKey(System, on_delete=models.CASCADE, verbose_name='Baugruppe')
    team = models.ForeignKey(Team, on_delete=models.CASCADE, verbose_name='Team')
    employee = models.ForeignKey(Employee, on_delete=models.SET_NULL, blank=True, null=True, verbose_name='Mitarbeiter')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Verantwortlichkeit'
        verbose_name_plural = 'Verantwortlichkeiten'
        constraints = [
            models.UniqueConstraint(fields=['project', 'system', 'team', 'employee'], name='uq_project_system_team_employee')
        ]

    def __str__(self):
        return f'{self.project} | {self.system} | {self.team} | {self.employee}'


class Problem(models.Model):
    responsibility = models.ForeignKey(Responsibility, on_delete=models.CASCADE, verbose_name='Verantwortlichkeit')
    summary = models.CharField(max_length=150, verbose_name='Zusammenfassung')
    description = models.TextField(verbose_name='Beschreibung')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Problem'
        verbose_name_plural = 'Probleme'

    def __str__(self):
        return f'{self.responsibility} | {self.summary}'


class Solution(models.Model):
    problem = models.OneToOneField(Problem, on_delete=models.CASCADE, verbose_name='Problem')
    summary = models.CharField(max_length=150, verbose_name='Zusammenfassung')
    description = models.TextField(verbose_name='Beschreibung')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Lösung'
        verbose_name_plural = 'Lösungen'

    def __str__(self):
        return f'{self.problem} | {self.summary}'


class Status(models.Model):
    COLORS = (
        ('Rot', 'Rot'),
        ('Gelb', 'Gelb'),
        ('Grün', 'Grün'),
    )   
    responsibility = models.OneToOneField(Responsibility, on_delete=models.CASCADE, unique=True, verbose_name='Verantwortlichkeit')
    traffic_light = models.CharField(max_length=4, choices=COLORS, default='Grün', verbose_name='Ampel')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Status'
        verbose_name_plural = 'Status'

    def __str__(self):
        return f'{self.responsibility} | {self.traffic_light}'


class BottleneckPart(models.Model):
    cost_bearer = models.ForeignKey(Project, on_delete=models.CASCADE, verbose_name='Kostenträger')
    department = models.ForeignKey(Department, on_delete=models.CASCADE, verbose_name='Abteilung')
    system = models.ForeignKey(System, on_delete=models.CASCADE, verbose_name='Baugruppe')
    feedback_number = models.IntegerField(verbose_name='Rückmeldenummer')
    feedback_position_number = models.CharField(max_length=6, verbose_name='Rückmeldepositionsnummer')
    description_head = models.CharField(max_length=150, verbose_name='Kopfbezeichnung')
    description = models.CharField(max_length=150, verbose_name='Bezeichnung')
    po_position_status = models.CharField(max_length=50, blank=True, null=True, verbose_name='Bestellpositionsstatus')
    inquiry_purchase_current = models.DateField(blank=True, null=True, verbose_name='Anfrage an KM IST')
    inquiry_purchase_target = models.DateField(blank=True, null=True, verbose_name='Anfrage an KM SOLL')
    purchase_requisition_planning_current = models.DateField(blank=True, null=True, verbose_name='Bestellanforderung an TP IST')
    purchase_requisition_planning_target = models.DateField(blank=True, null=True, verbose_name='Bestellanforderung an TP SOLL')
    po_finished_current = models.DateField(blank=True, null=True, verbose_name='Bestellung fertig IST')
    po_finished_target = models.DateField(blank=True, null=True, verbose_name='Bestellung fertig SOLL')
    appointment_document = models.CharField(max_length=50, blank=True, null=True, verbose_name='TU')
    appointment_document_date = models.DateField(blank=True, null=True, verbose_name='TU Termin')
    delivery_time_current = models.IntegerField(blank=True, null=True, verbose_name='Lieferzeit AT')
    delivery_date_target = models.DateField(blank=True, null=True, verbose_name='Liefertermin SOLL')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Engpassteil'
        verbose_name_plural = 'Engpassteile'

I hope you can understand what I want to achieve. I would be very grateful for a few tips.

Best regards
gospat

2

Did you read this? Multiple databases | Django documentation | Django

3

So, I have to configure Django to use multiple Databases. I understand. Is the second step to create an unmanaged model for my third-party data?

4

If you don’t want them to have migrations tracked/applied, then yes.

5

Thank you for your advice.

I have now added two more databases in the settings.py file. After that, I ran the following command for each database.

python manage.py check --database <DB-NAME>

The result was successful every time. I can also make queries on the other databases via .using(‘<DB_NAME>’). Afterwards, I watched a video on YouTube about DB Routers and tried to implement what was shown.

My db_router.py file is in the systemcockpit app.

class DbRouter:

    def db_for_read(self, model, **hints):
        if model.meta.model_name == 'BottleneckPart':
            return 'DWH92'
        elif model._meta.model_name == 'Drawing':
            return 'D3P'
        elif model._meta.app_label == 'systemcockpit':
            return 'default'
        return None
    
    def db_for_write(self, model, **hints):
        if model.meta.model_name == 'BottleneckPart':
            return 'DWH92'
        elif model._meta.model_name == 'Drawing':
            return 'D3P'
        elif model._meta.app_label == 'systemcockpit':
            return 'default'
        return None

settings.py

DATABASE_ROUTERS = [
    'systemcockpit.db_router.DbRouter'
]

The code shown doesn’t work that way, but I want to make the distinction by models, since I’m only using one app. How do I do that? I have another question. The tutorial showed how two models from different databases can or must be related. This was solved via a kind of local copy that connected to the “external” database. Does it really have to work that way? Can I not use a FK in a model that is on a different DB?

Specifically, I would like to link these two models.

Model 1:

class Responsibility(models.Model):
    project = models.ForeignKey(Project, on_delete=models.CASCADE, verbose_name='Projekt')
    system = models.ForeignKey(System, on_delete=models.CASCADE, verbose_name='Baugruppe')
    team = models.ForeignKey(Team, on_delete=models.CASCADE, verbose_name='Team')
    employee = models.ForeignKey(Employee, on_delete=models.SET_NULL, blank=True, null=True, verbose_name='Mitarbeiter')
    created_at = models.DateTimeField(auto_now_add=True, editable=False, blank=True, verbose_name='Erstelldatum')
    updated_at = models.DateTimeField(auto_now=True, editable=False, blank=True, verbose_name='Änderungsdatum')

    class Meta:
        verbose_name = 'Verantwortlichkeit'
        verbose_name_plural = 'Verantwortlichkeiten'
        constraints = [
            models.UniqueConstraint(fields=['project', 'system', 'team', 'employee'], name='uq_project_system_team_employee')
        ]

    def __str__(self):
        return f'{self.project} | {self.system} | {self.team} | {self.employee}'

Model 2:

class BottleneckPart(models.Model):
    id = models.IntegerField(primary_key=True, verbose_name='ID')
    project = models.CharField(max_length=6, verbose_name='Kostenträger') #FK model Responsibility
    team = models.CharField(max_length=3, verbose_name='Abteilung') #FK model Responsibility
    system = models.CharField(max_length=4, verbose_name='Baugruppe') #FK model Responsibility
    feedback_number = models.IntegerField(verbose_name='RückmeldeNr')
    description_head = models.CharField(max_length=150, verbose_name='Kopfbezeichnung')
    feedback_position_number = models.IntegerField(verbose_name='RückmeldePosNr')
    description = models.CharField(max_length=150, verbose_name='Bezeichnung')
    po_position_status = models.CharField(max_length=50, blank=True, null=True, verbose_name='BestellPos Status')
    inquiry_purchase_current = models.DateField(blank=True, null=True, verbose_name='Anfrage an KM IST')
    inquiry_purchase_target = models.DateField(blank=True, null=True, verbose_name='Anfrage an KM SOLL')
    purchase_requisition_planning_current = models.DateField(blank=True, null=True, verbose_name='Bestellanforderung an TP IST')
    purchase_requisition_planning_target = models.DateField(blank=True, null=True, verbose_name='Bestellanforderung an TP SOLL')
    po_finished_current = models.DateField(blank=True, null=True, verbose_name='Bestellung fertig IST')
    po_finished_target = models.DateField(blank=True, null=True, verbose_name='Bestellung fertig SOLL')
    appointment_document = models.CharField(max_length=50, blank=True, null=True, verbose_name='TU')
    appointment_document_date = models.DateField(blank=True, null=True, verbose_name='TU Termin')
    delivery_time_current = models.IntegerField(blank=True, null=True, verbose_name='Lieferzeit AT')
    delivery_date_target = models.DateField(blank=True, null=True, verbose_name='Liefertermin SOLL')

    class Meta:
        managed = False
        db_table = 'bottleneck_part'
        verbose_name = 'Engpassteil'
        verbose_name_plural = 'Engpassteile'

Best regards
gospat

6

Yes you “can”, You just need to be aware of the possible outcomes, and that Django will be doing some magic in order for this to work. What you need to do is set on the ForeignKey field the option db_constraint=False, you can read more about this option here.
After that, your Router will need to allow relations between these two models.

7

Test for the model name, not the app name.

Yes, that’s how it works.

No, there is no valid workaround.

Quoting from the docs at Limitations of multiple databases:

Django doesn’t currently provide any support for foreign key or many-to-many relationships spanning multiple databases. If you have used a router to partition models to different databases, any foreign key and many-to-many relationships defined by those models must be internal to a single database.

- and -

However, if you’re using MySQL with MyISAM tables, there is no enforced referential integrity; as a result, you may be able to ‘fake’ cross database foreign keys. However, this configuration is not officially supported by Django.

What this really means in practice is that even if you disable db_constraints, there’s a very high probability that you could end up writing queries that fail or don’t act the way you expect them to. This is, in my opinion, a far worse situation than any other result, especially if it’s a situation where you have the same table name in both databases.

(I’m in the camp where data integrity is the #1 concern, and all other considerations rank below that.)

8

Thanks for your advice, Ken.

What do you mean by “Test for the model name, not the app name”? I tried meta.model_name, but apparently it doesn’t work or I’m still doing something wrong.

As for the other topic, I’m not sure if I need the whole thing at all. I actually want to iterate through the responsibilities and render them in my template, like this:

            {% for responsibility in responsibilities %}
            <tr>
                <td>{{ responsibility.project }}</td>
                <td>{{ responsibility.system }}</td>
                <td>{{ responsibility.department }}</td>
                <td>{{ responsibility.employee }}</td>
                {% if responsibility.status.traffic_light == 'Rot' %}
                    <td class='text-center'>Rot</td>
                {% elif responsibility.status.traffic_light == 'Gelb' %}
                    <td class='text-center'>Gelb</td>
                {% else %}
                    <td class='text-center'>Grün</td>
                {% endif %}
                <td><a href='{% url "responsibility" responsibility.id %}'><p>{{ forloop.counter }} - {{ responsibility.project }}</p></a></td>
            </tr>
            {% empty %}
                <p>Keine Verantwortlichkeiten vorhanden</p>
            {% endfor %}

However, several bottleneck parts can be part of one responsibility. At DB level, I would now join both tables with each other via the fields cost_bearer, team and system.

What is the best way to render the data from the BottlenackPart model? Make another loop within the loop of responsibilities? If so, how? Can I reference the attributes of the Responsibilities model in the filter of the query set for the bottleneck parts? Something like cost_bearer = responsibility.project. That would be almost like a subquery in SQL.

9

Notice the difference in how the model name is being referenced between these two statements. (And you have the same mistake in both methods.)

I’m not sure I understand what “whole thing” you’re referring to here.

What does the view look like? The template itself has no responsibilities over accessing a database. The template engine will render the data passed to it in the context. If the data in the context is correct, the template will render correctly.

Then within your view, or a model method, or a model manager, you could issue the queries based upon these values.

By their value, but not by foreign key.

Seeing the actual view in which you’re trying to do this would be very helpful. This is getting into the realm where the details matter, and reaching the limits of the usefulness of trying to discuss this in the abstract.

10

Sorry, Ken, but I don’t know what I am doing wrong in db_router.py. Could you possibly help me with a short code snippet? I don’t know what I have to change to make it work. I’ll take a closer look at the other points tomorrow, then I’ll send the complete code.

Best regards
Patrick

11

What is the difference between this line:

if model.meta.model_name == 'BottleneckPart':
and
elif model._meta.model_name == 'Drawing':

other than the name of the model?

Or, to be even more succinct, between this:
model.meta.model_name
and
model._meta.model_name

12

Oh man, sorry. Sometimes you can’t see the forest for the trees…

This code works now.

from .models import BottleneckPart

class DbRouter:

    def db_for_read(self, model, **hints):
        if model == BottleneckPart:
            return 'DWH92'
            return 'D3P'
        elif model._meta.app_label == 'systemcockpit':
            return 'default'
        return None
    
    def db_for_write(self, model, **hints):
        if model == BottleneckPart:
            return 'DWH92'
        elif model._meta.app_label == 'systemcockpit':
            return 'default'
        return None

Thank you again for your support, once again.

Best regards
gospat

13

I was also able to solve the other “problem”. I can render the data from the other DB just fine.

It helped me a lot to watch a complete tutorial about Django on Udemy. There someone explains all the relevant things step by step and in great detail. Before that, I just looked at YoutTube and took over something here and there. This does not lead to success in the long term.

Now I know how to test my “commands” in advance on the shell and thus I can debug a little.