Django utilizing/pulling data from other databases

Question

My question below was answered somewhat in past back in 2013 here. But I am looking for more robust and latest solution if there is any.

I have a Django app app1 and I want to tie up data in Django from other databases who are constantly updating on daily basis. For example: In app1, I have model model where user has to input sales order from sap and corresponding opportunity number from salesforce. I have tools who dump data from sap into sapdb and salesforce into salesforcedb.

My model in app1 looks like:

class SalesOrderMapping(models.Model):
    sales_order = models.CharField("Sales Order #", max_length=10, primary_key=True)
    opportunity_number = models.CharField("Opportunity Number", max_length=30)

    class Meta:
        verbose_name_plural = "Sales Order / Opportunity Mapping"
        ordering = ('sales_order', 'opportunity_number')

    def __str__(self):
        return self.sales_order

So as you can see that in above model, I have opportunity_number, I want user to type in sales_order and instead of typing in opportunity_number it can be autocomplete text box(ideally) or a drop down populated by a table in salesforcedb. So far I have been able to describe the databases in settings.py. Can somebody point me in right direction?

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'djangoportal',
        'USER': 'myuser',
        'PASSWORD': 'mypass',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    },

    'sap': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'sapdb',
        'USER': 'myuser',
        'PASSWORD': 'mypass',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    },

    'sfdc': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'salesforcedb',
        'USER': 'myuser',
        'PASSWORD': 'mypass',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    }
}

Possible duplicate of [multiple databases and multiple models in django](http://stackoverflow.com/questions/18547468/multiple-databases-and-multiple-models-in-django) — Marat, Mar 23 '17 at 20:53

Marcelo Elizeche Landó · Answer 1 · 2017-03-30T11:06:57.830

4

First you will need a database router for your apps, I'll try to do something similar to what you need with the information given

app1/routers.py

class App1Router(object):
    def db_for_read(self, model, **hints):

        if model._meta.app_label == 'app1':
            return 'default'
        return None
    def db_for_write(self, model, **hints):
        """
        Attempts to write auth models go to app1.
        """
        if model._meta.app_label == 'app1':
            return 'default'
        return None
    def allow_relation(self, obj1, obj2, **hints):
         db_list = ('default', 'sap', 'sfdc')
        if obj1._state.db in db_list and obj2._state.db in db_list:
            return True
        return None
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return True

and in your settings.py file add the following

DATABASE_ROUTERS = ['app1.routers.App1Router',]

You can use the inspectdb command to create the models of the existing databases, e.g.:

./manage.py inspectdb --database "sap"

I hope it helps

Source: Personal experience and the Django Documentation https://docs.djangoproject.com/en/1.10/topics/db/multi-db/

edited Mar 30 '17 at 11:06

answered Mar 30 '17 at 10:57

Marcelo Elizeche Landó

66
3

Let me try to implement this. Will the above solution make a local copy of the other database in django and copy all records or is it just a reference and each time an update is made in other database, it will be reflected in Django? – nomad Mar 30 '17 at 17:20
It's a reference, you're accessing to the other database in real time, but you have to make the models(the inspectdb command will help) so Django can understand how to manage the data. If the structure of the other database changes(e.g. field added to a table) you should update your models – Marcelo Elizeche Landó Mar 31 '17 at 09:27
OK. What are the next steps? How do I reference this data from other database in my views? – nomad Apr 03 '17 at 15:55
Something like this `from app1.models import TableModel1 from app2.models import TableModel2 print TableModel1.objetcts.all() print TableModel2.objects.all() ` – Marcelo Elizeche Landó Apr 03 '17 at 22:18

score 1 · Answer 2 · answered Mar 23 '17 at 20:53

Django has support for multiple databases as described here:

https://docs.djangoproject.com/en/1.10/topics/db/multi-db/

Assuming you set your primary database as the main database, you will have to make sure to specify which database you want to access whenever you are reading from another database.

score 1 · Answer 3 · edited Jun 20 '17 at 22:04

The other answers have already mentioned the multi-DB nature of this and linked to the docs.

You may want to use something like the Django REST Frame to run access to the other tables as a mini-service for the rest of the app, rather than trying to context switch DB calls in your Views code.

E.g. if you need to lookup data in the SAP or SalesForce tables you make an Ajax request to your backend service with that ID. It will send you a JSON answer with whatever you need.

That way you can abstract the relationship and change implementation details in the future without having to rewrite a lot of code.

Django utilizing/pulling data from other databases

3 Answers3