How to handle multiple DB in Django?

Hi Friends,

Now I am going to share my experience that clearly tells you how to handle multiple databases in your Django projects.

Why am I coming here to explain this?

We had a client requirement for handling multiple databases in their project. I spent some time for researching and finalized the logic setup.

Let us move to step by step setup.

Here we are going to use three databases in our project named as ‘master’, ‘client1′, ‘client2′. In this case ‘master’  database has a table which is used for storing list of other databases list.

Eg.

1. client1

2. client2

(We will use this in login form select options)

The bellow login page gives you clear pictures about what we are going to discuss. Here, user can select database login page itself and play all database related queries using this selected DB. For that you have to store it in session.

Screenshot from 2013-06-20 15:10:16

Step 1: How to setup/configure multiple Databases in settings.py?

Change DATABASES in your settings.py as

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'master',
        'USER': 'root',
        'PASSWORD': 'secret'
    },
    'client1': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'client1',
        'USER': 'root',
        'PASSWORD': 'secret'
    },
    'client2': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'client2',
        'USER': 'root',
        'PASSWORD': 'secret'
    }
}

Add your own authentication backend if you have any.

# AUTHENTICATION_BACKENDS = ('django.contrib.auth.backends.ModelBackend',) This default authentication backend.
AUTHENTICATION_BACKENDS = ('authentication.file.path.auth_backends.MyBackend',)

This should be a python tuple.  Django keeps all this list for checking authentication. when you call django.contrib.auth.authenticate() Django tries authenticating across all of its authentication backends. If the first authentication method fails, Django tries the second one, and so on, until all backends have been attempted.

Step 2: How to customize authentication backend? 

In auth_backends.py

Write  your own authentication backend to authenticate user with selected database in login page.

from __future__ import unicode_literals
from django.contrib.auth import get_user_model
from django.contrib.auth.models import Permission

class MyBackend(object):
    """
    Authenticates against django.contrib.auth.models.User.
    """

    def authenticate(self, username=None, password=None, db=None, **kwargs):
        UserModel = get_user_model()
        if username is None:
            username = kwargs.get(UserModel.USERNAME_FIELD)
        try:
            user = UserModel.objects.using(db).get(username=username)
            if user.check_password(password):
                return user
        except UserModel.DoesNotExist:
            return None

Step 3: How to design login form to select different Database?

In forms.py

Here, I have designed model called ‘ClientDBInfo’ for storing database names in master DB. See, the sample code that tells you how I am using ‘ClientDBInfo’ in login form.

#from Django
from django import forms
from django.utils.translation import ugettext_lazy as _
from django.contrib.auth import authenticate

#from apps
from models import *

class LoginForm(forms.Form):
    ClientDB = ClientDBInfo.objects.all()
    db_choice_list = []
    for db in ClientDB:
        db_choice_list.append((db.name, db.display_name))

    db = forms.ChoiceField(choices=db_choice_list)
    username = forms.CharField(required=True, label='',)
    password = forms.CharField(required=True, label='', widget=forms.PasswordInput(render_value=False))

    def clean(self):
        if ('username' in self.cleaned_data and 'password' in self.cleaned_data):
            db = self.cleaned_data['db']
            username = self.cleaned_data['username']
            password = self.cleaned_data['password']
            user = authenticate(username=username, password=password, db=db)

            if user is not None:
                if not user.is_active:
                    raise forms.ValidationError(_('Your account is not active, please contact the site admin.'))
            else:
                raise forms.ValidationError(_('Please enter a correct username and password. Note that both fields are case-sensitive'))

        return self.cleaned_data

In models.py

from django.conf import settings
from django.db import models
from django.contrib.auth.models import AbstractUser
from django.utils.translation import ugettext_lazy as _
from django.utils import timezone

from ezerve.utils import gv

AUTH_USER_MODEL = getattr(settings, 'AUTH_USER_MODEL', 'auth.User')

class ClientDBInfo(models.Model):
    name = models.CharField(max_length=255, verbose_name=_('DB Name'), help_text=_('Enter a Database name which is available to client'))
    display_name = models.CharField(max_length=255, verbose_name=_('DB Display Name'), help_text=_('Enter a Database display name which is for lable'))
    logo = models.ImageField(upload_to='img-data/clientlogo', default='img-data/clientlogo/no_clientphoto.png', blank=True)
    contact_address_line1 = models.CharField(max_length=140, blank=True)
    contact_address_line2 = models.CharField(max_length=140, blank=True)
    contact_address_line3 = models.CharField(max_length=140, blank=True)
    city = models.CharField(max_length=140, blank=True)
    country = models.ForeignKey(Country)
    state = models.CharField(max_length=140, blank=True)
    zipcode = models.IntegerField(default=0)
    latitude = models.FloatField(default=0.0)
    longitude = models.FloatField(default=0.0)

    created = models.DateTimeField(default=timezone.now, auto_now_add=True)
    modified = models.DateTimeField(default=timezone.now, auto_now=True)
    created_by = models.ForeignKey(AUTH_USER_MODEL,related_name = "%(class)s_createdby")
    updated_by = models.ForeignKey(AUTH_USER_MODEL, related_name = "%(class)s_updatedby")

    def __unicode__(self):
        return self.name

    class Meta:
        verbose_name = _('ClientDBInfo')
        verbose_name_plural = _('ClientDBInfos')
        ordering = ('name',)

Step 4: Login Methods in views.py

Store DB in session after checking credentials. It can be used in entire project by ‘using()’.

@csrf_protect
@never_cache
def index(request):
    fn = _fn()
    template_name = fn
    logger.info("{0} method is loading...".format(fn))

    title = 'Home'

    if not request.user.is_authenticated():
        title = 'Login'
        form = LoginForm()
        if request.method == "POST":
            pDict =request.POST.copy()
            form = LoginForm(data=request.POST)
            if form.is_valid():
                if not request.POST.get('remember_me', None):
                    request.session.set_expiry(0)
                db = form.cleaned_data['db']
                username = form.cleaned_data['username']
                password = form.cleaned_data['password']
                user = authenticate(username=username, password=password, db=db)
                login(request, user)
                request.session['db'] = db

                if request.session.test_cookie_worked():
                    request.session.delete_test_cookie()

                if request.GET.get('next'):
                    return HttpResponseRedirect(request.GET.get('next'))

                title = 'Home'
                return render_to_response(ub.get_template(request,template_name),locals(),context_instance = RequestContext(request))

        return render_to_response(ub.get_template(request,'login'),locals(),context_instance = RequestContext(request))

    if request.user.is_authenticated():
        logger.info("{0} method is loading... done".format(fn))
        return render_to_response(ub.get_template(request,template_name),locals(),context_instance = RequestContext(request))

Step 5: How to use syncdb for multiple databases?

This is very straight forward. The thing is you have to mention database name while use syncdb command. Run following command one by one.

./manage.py syncdb --database=default
./manage.py syncdb --database=client1
./manage.py syncdb --database=client2

Step 6: How to loaddata for multiple database?

If you have any fixtures like yaml or xml or json to auto load for each database separately, use following commands

./manage.py loaddata --database=default ezerve/data/fixtures/region.yaml
./manage.py loaddata --database=client1 ezerve/data/fixtures/region.yaml 
./manage.py loaddata --database=client2 ezerve/data/fixtures/region.yaml

I hope that I covered very basic steps to handle multiple database.

Cheers!


Django-Python Web based application developer.

Share This Post

Related Articles

Leave a Reply

© 2017 Techy Diary. All rights reserved.
Powered by Charvi Groups