Connecting Django to Multiple Legacy Database

While Django is best suited for developing new applications, it’s quite possible to integrate it into legacy databases. Django includes a couple of utilities to automate as much of this process as possible.

Django comes with a tool that can generate model code by reading your database table layouts. This tool is called inspectdb, and you can call it by executing the command
python manage.py inspectdb.

This tutorial assumes that you know Django and Django RestFramework basics.

1.Set up virtual env and install Django and other database connectors.
Create a Django project by running django-admin.py startproject legacydband application within your project by running python mysite/manage.py startapp myapp and then add the app and rest_framework to your INSTALLED_APPSin settings.py file.

mkdir new_project && cd new_project
python3 -m venv env
source env/bin/activate
pip install Django djangorestframework mysqlclient psycopg2 
django-admin startproject legacydb .
python manage.py startapp app

2. Databases can have any alias you choose. However, the alias default has special significance. Django uses the database with the alias of default when no other database has been selected.
Edit the settings file in that project, mysite/settings.py, to tell Django what your database connection parameters are and what the name of the database is. Specifically, provide the DATABASE_NAME, D  ATABASE_ENGINE, DATABASE_USER,DATABASE_PASSWORD,DATABASE_HOST,DATABASE_PORT settings.

legacydb/settings.py


INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'rest_framework',
    'app',
]


REST_FRAMEWORK= {
    'DEFAULT_PERMISSION_CLASSES':
          ['rest_framework.permissions.AllowAny',]}

DATABASES = {
    'default': {},
    'school': {
        'NAME': 'school',
        'ENGINE': 'django.db.backends.postgresql',
        'USER': '****',
        'PASSWORD': '****'
        'HOST': '****',
        'PORT': '****'
    },
    'bank': {
        'NAME': 'bank',
        'ENGINE': 'django.db.backends.mysql',
        'USER': '****',
        'PASSWORD': '****'
    }
}

3. The easiest way to use multiple databases is to set up a database routing scheme. The default routing scheme ensures that objects remain ‘sticky’ to their original database (i.e., an object retrieved from the foo database will be saved on the same database). The default routing scheme ensures that if a database isn’t specified, all queries fall back to the default database, however, we didn’t specify a default database

Now we’ll need to handle routing. First, we want a router that knows to send queries for the auth and contenttypes apps to school (auth models are linked to ContentType, so they must be stored in the same database):

class AuthRouter:
    """
    A router to control all database operations on models in the
    auth and contenttypes applications.
    """
    route_app_labels = {'auth', 'contenttypes'}

    def db_for_read(self, model, **hints):
        """
        Attempts to read auth and contenttypes models go to school.
        """
        if model._meta.app_label in self.route_app_labels:
            return 'school'
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write auth and contenttypes models go to school.
        """
        if model._meta.app_label in self.route_app_labels:
            return 'school'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the auth or contenttypes apps is
        involved.
        """
        if (
            obj1._meta.app_label in self.route_app_labels or
            obj2._meta.app_label in self.route_app_labels
        ):
           return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the auth and contenttypes apps only appear in the
        'school' database.
        """
        if app_label in self.route_app_labels:
            return db == 'school'
     return None

And we also want a router that sends all other apps to the bank configuration.

class BankRouter:
    def db_for_read(self, model, **hints):
        """
        Reads go to bank db
        """
        return 'bank'

    def db_for_write(self, model, **hints):
        """
        Writes always go to bank.
        """
        return 'bank'

    def allow_relation(self, obj1, obj2, **hints):
        """
        Relations between objects are allowed if both objects are
        in bank
        """
        
        if obj1._state.db =='bank' and obj2._state.db  =='bank':
            return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        All non-auth models end up in this pool.
        """
        return True

Finally, in the settings file, we add the where the routers are defined

DATABASE_ROUTERS = ['legacydb.settings.AuthRouter', 'legacydb.settings.BankRouter']

The order in which routers are processed is significant. Routers will be queried in the order they are listed in the DATABASE_ROUTERS setting. In this example, the AuthRouter is processed before the BankRouterRouter, and as a result, decisions concerning the models in auth are processed before any other decision is made.

Synchronizing your databases

python manage.py migrate --database=bank
python manage.py migrate --database=school

Generating and adapting model classes from the legacy database schema

python manage.py inspectdb --database=school > app/school_models.py
python manage.py inspectdb --database=bank >app/bank_models.py

After generating the models.py file, we need to make a few changes.
By default, inspectdb creates unmanaged models. That is, managed = False in the model’s Meta class tells Django not to manage each table’s creation, modification, and deletion, you’ll need to change the managed option to True.

from django.db import models


class Account(models.Model):
    account_id = models.AutoField(primary_key=True)
    product_cd = models.ForeignKey('Product', models.DO_NOTHING,  
    db_column='product_cd')
    cust = models.ForeignKey('Customer', models.DO_NOTHING)
    open_date = models.DateField()
    close_date = models.DateField(blank=True, null=True)
    last_activity_date = models.DateField(blank=True, null=True)
    status = models.CharField(max_length=6, blank=True, null=True)
    open_branch = models.ForeignKey('Branch', models.DO_NOTHING, blank=True, 
    null=True)
    open_emp = models.ForeignKey('Employee', models.DO_NOTHING, blank=True, 
    null=True)
    avail_balance = models.FloatField(blank=True, null=True)
    pending_balance = models.FloatField(blank=True, null=True)

    class Meta:
        managed = True    #change to True
        db_table = 'account'

With this setup installed, Let’s create a simple API to test our setup:

touch app/urls.py
touch app/serializers.py

Configuring our URLs

legacydb/urls.py

from django.contrib import admin
from django.urls import include,path

urlpatterns=[
    path('admin/', admin.site.urls),
    path('api/', include('app.urls')),
    ]

app/urls.py

from django.urls import path
from.views import ListEmployee, DetailEmployee

urlpatterns = [
    path("", ListEmployee.as_view(), name="employee_list"),
    path("<int:pk>/", DetailEmployee.as_view(), name="employee_detail")
]

Serialization and Deserialization

app/serializers.py.

from rest_framework import serializers
from .bank_models import Employee


class EmployeeSerializer(serializers.ModelSerializer):
    class Meta:
        model = Employee
        fields = '__all__'

Create Views

app/views.py

from rest_framework import generics 
from.bank_models import Employee
from.serializers import EmployeeSerializer

class ListEmployee(generics.ListAPIView):
    queryset= Employee.objects.all()
    serializer_class= EmployeeSerializer

class DetailEmployee(generics.RetrieveAPIView):
    queryset= Employee.objects.all()
    serializer_class= EmployeeSerializer

Run this on your terminal python manage.py runserver, DRF comes with a browsable api, so you can directly open http://localhost:8000/api/ in the browser.
It looks like this below

Viola, that is the end of our guide, thank you for reading 👏

The GitHub link for this tutorial

References

1. Django Docs
2. Agiliq

Share this post: on Twitter on Facebook on Google+ on LinkedIn