How to Use Multiple database in django and performer query with multiple database

Spread the love

Step 1:- Install Django: If you haven’t already done so, you will need to install Django. You can do this by running the following command in your terminal or command prompt:

Command : – pip install django

Step2:- Create a new Django project: Once you have installed Django, you can create a new project by running the following command in your terminal or command prompt:

Command:- django-admin startproject projectname

Replace “projectname” with the name you want to give your project.

Step3:- Create a new app: Once you are in the project directory, run the following command to create a new app:

Command:- python manage.py startapp Product

Replace “appname” with the name you want to give your app. This command will create a new directory with the same name as your app inside your project directory, and it will contain the basic files and folders needed to run a Django app.

Step4:- Register the app open the settings.py file in your project
directory, and find the INSTALLED_APPS list. Add the name of your app to this list, like so:

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

INSTALLED_APPS = [

'django.contrib.admin',

'django.contrib.auth',

'django.contrib.contenttypes',

'django.contrib.sessions',

'django.contrib.messages',

'django.contrib.staticfiles',

'Product'

]

Step 5:- Choose your database: Django supports many different databases, including PostgreSQL, MySQL, SQLite, and Oracle. You will need to choose the database you want to use and make sure it is installed on your computer.

Update your project’s settings: Open your project’s settings.py file and find the DATABASES dictionary. This dictionary defines the settings for your project’s database connection. Here’s an example using SQLite:

Here we are using sqllite3 and postgress and mysql

DATABASE_ROUTERS = ['Product.database_route.ProductRouter',
                    'Product.database_route1.ProductRouter1']
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    },
    'database1': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydatabase',
        'USER': 'root',
        'PASSWORD': 'root',
    },
    'database2': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydatabase2',
        'USER': 'postgres',  
        'PASSWORD': 'root',
        'HOST': 'localhost',
        'PORT':'5432'
    }
}

DATABASE_ROUTERS = ['Product.database_route.ProductRouter',

'Product.database_route1.ProductRouter1']

DATABASES = {

'default': {

'ENGINE': 'django.db.backends.sqlite3',

'NAME': BASE_DIR / 'db.sqlite3',

'database1': {

'ENGINE': 'django.db.backends.mysql',

'NAME': 'mydatabase',

'USER': 'root',

'PASSWORD': 'root',

'database2': {

'ENGINE': 'django.db.backends.postgresql',

'NAME': 'mydatabase2',

'USER': 'postgres',

'PASSWORD': 'root',

'HOST': 'localhost',

'PORT':'5432'

}

Step6:- After that we will create the model class in model.py.

from django.db import models
from django.contrib.auth.models import User
# Create your models here.
class  Customers(models.Model):
    first_name = models.CharField(max_length=255,blank=True)
    last_name = models.CharField(max_length=255,blank=True)
    email = models.EmailField(max_length=255)
    phone_number = models.CharField(max_length=12)
    def __str__(self):
        return f"{self.first_name} {self.last_name}"
    class Meta:
        app_label = 'Product'   
class Orders(models.Model):
    customer = models.ForeignKey(Customers, on_delete=models.CASCADE)
    order_date =  models.DateField(auto_now=False, auto_now_add=False)
    total_Amount = models.DecimalField(max_digits=10, decimal_places=2)  
    class Meta:
        app_label = 'Product'

from django.db import models

from django.contrib.auth.models import User

# Create your models here.

class Customers(models.Model):

first_name = models.CharField(max_length=255,blank=True)

last_name = models.CharField(max_length=255,blank=True)

email = models.EmailField(max_length=255)

phone_number = models.CharField(max_length=12)

def __str__(self):

return f"{self.first_name} {self.last_name}"

class Meta:

app_label = 'Product'

class Orders(models.Model):

customer = models.ForeignKey(Customers, on_delete=models.CASCADE)

order_date = models.DateField(auto_now=False, auto_now_add=False)

total_Amount = models.DecimalField(max_digits=10, decimal_places=2)

class Meta:

app_label = 'Product'

Step 7: -You can then add this router to your project’s settings file under the DATABASE_ROUTERS setting to:

DATABASE_ROUTERS = ['Product.database_route.ProductRouter',
                    'Product.database_route1.ProductRouter1']

1 2	DATABASE_ROUTERS = ['Product.database_route.ProductRouter', 'Product.database_route1.ProductRouter1']

Step 8: -After that specify the database to use for a model or a query using the using() method. If you want to define read and write methods for each model separately, you can create a database_route.py file, database_route1.py in your app. database_route.py file:

class ProductRouter:
    app_label = 'Product'

    def db_for_read(self, model, **hints):
        if model._meta.app_label == self.app_label:
            if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':
                return 'database1'
        return 'default'

    def db_for_write(self, model, **hints):
        if model._meta.app_label == self.app_label:
            if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':
                return 'database1'
        return 'default'

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the user app is involved.
        """
        if obj1._meta.app_label == 'Product' or \
           obj2._meta.app_label == 'Product':
           return True
        elif 'Product' not in [obj1._meta.app_label, obj2._meta.app_label]:
            return True
    return False

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label == self.app_label:
            if model_name == 'Customers' and model_name == 'Orders':
                return db == 'database1'
        return None

class ProductRouter:

app_label = 'Product'

def db_for_read(self, model, **hints):

if model._meta.app_label == self.app_label:

if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':

return 'database1'

return 'default'

def db_for_write(self, model, **hints):

if model._meta.app_label == self.app_label:

if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':

return 'database1'

return 'default'

def allow_relation(self, obj1, obj2, **hints):

"""

Allow relations if a model in the user app is involved.

"""

if obj1._meta.app_label == 'Product' or \

obj2._meta.app_label == 'Product':

return True

elif 'Product' not in [obj1._meta.app_label, obj2._meta.app_label]:

return True

return False

def allow_migrate(self, db, app_label, model_name=None, **hints):

if app_label == self.app_label:

if model_name == 'Customers' and model_name == 'Orders':

return db == 'database1'

return None

database_route1.py file:

class ProductRouter1:
    app_label = 'Product'

    def db_for_read(self, model, **hints):
        if model._meta.app_label == self.app_label:
            if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':
                return 'database2'
        return 'default'

    def db_for_write(self, model, **hints):
        if model._meta.app_label == self.app_label:
            if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':
                return 'database2'
        return 'default'

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the user app is involved.
        """
        if obj1._meta.app_label == 'Product' or \
           obj2._meta.app_label == 'Product':
           return True
        elif 'Product' not in [obj1._meta.app_label, obj2._meta.app_label]:
            return True

        return False
        
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label == self.app_label:
            if model_name == 'Customers' and model_name == 'Orders':
                return db == 'database2'
        return None

class ProductRouter1:

app_label = 'Product'

def db_for_read(self, model, **hints):

if model._meta.app_label == self.app_label:

if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':

return 'database2'

return 'default'

def db_for_write(self, model, **hints):

if model._meta.app_label == self.app_label:

if model._meta.object_name == 'Customers' and model._meta.object_name == 'Orders':

return 'database2'

return 'default'

def allow_relation(self, obj1, obj2, **hints):

"""

Allow relations if a model in the user app is involved.

"""

if obj1._meta.app_label == 'Product' or \

obj2._meta.app_label == 'Product':

return True

elif 'Product' not in [obj1._meta.app_label, obj2._meta.app_label]:

return True

return False

def allow_migrate(self, db, app_label, model_name=None, **hints):

if app_label == self.app_label:

if model_name == 'Customers' and model_name == 'Orders':

return db == 'database2'

return None

Step 9: To migrate a table for a specific database in Django, you can use the following commands

Run the makemigrations command with the –database option to create a migration file for the specific database:

Command:-python manage.py makemigrations –database database1

Command:-python manage.py makemigrations –database database2

Step 10:- Once your database and table are set up, you can begin querying them for data in your views.py file.

from django.shortcuts import render
from .models import Customers, Orders
        
def customers(request):
        customers = Customers.objects.using('database2').create(first_name='manisha',last_name='singh')
        orders = Orders.objects.using('database2').create(customer=customers, total_Amount=100.00,order_date=date(2023,4,2))

        customers = Customers.objects.using('database2').all()
        orders = Orders.objects.using('database2').all()

        customers1 = Customers.objects.using('database1').create(first_name='nisha',last_name='singh')
        orders1 = Orders.objects.using('database1').create(customer=customers1, total_Amount=200.00,order_date=date(2022,3,1))

        customers1 = Customers.objects.using('database1').all()
        orders1 = Orders.objects.using('database1').all()
        
        print(customers,'________________________________________customers')
        print(orders,'________________________________________orders')
        return render(request, 'customer_orders.html', {'customers': customers,'orders':orders, 'customers1': customers1,'orders1':orders1 })