django restframework

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

django_restframwork

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

The GitHub link for this tutorial

References

1. Django Docs
2. Agiliq

14 Comments

  • Nice one sis, if you are read this I encourage you to feel free to expand your knowledge utilizing free sources like; YouTube.com

  • This is awesome!
    Can I ask a question (not Django related)? Please Oluchi, how do I achieve this; —– I have 12 input fields, one requests for input and when that is filled, it brings up the input for other fields. Now, after all is filled, the whole list is saved as a dictionary taking the first input field as the key and the remaining 11 are stored as a list in the values pair.

    • First of all your question is not clear enough. My assumption is that the user is giving inputs one by one, 12 times.. If that’s the case then you can check the python code below;

      twelve_input_fields = []
      covert_to_dict = dict()

      for i in range(0, 12):
      ele = input()
      twelve_input_fields.append(ele)

      convert_to_dict = {twelve_input_fields[0]: [twelve_input_fields[i] for i in range(1, len(twelve_input_fields))]}
      print(convert_to_dict)

  • Hi there would you mind stating which blog platform you’re working with? I’m going to start my own blog soon but I’m having a difficult time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I’m looking for something completely unique. P.S Apologies for getting off-topic but I had to ask!

    • This is a technical blog and I needed something simple to schedule and publish posts.
      This is a WordPress powered blog and I am using Shamrock theme.

  • you’re really a good webmaster. The site loading speed is amazing. It seems that you’re doing any unique trick. Also, The contents are masterpiece. you have done a excellent job on this topic!

    • Thank you for your kind words, this is a WordPress site, so I am using some extensions for caching and minifying images. Thanks for visiting the blog

  • Hola An impressive share! I have just forwarded this onto a co-worker who had been doing a little homework on this. And he actually ordered me dinner because I stumbled upon it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending the time to talk about this topic here on your website. danke

Leave a Reply

Your email address will not be published. Required fields are marked *