Connecting To Multiple Databases Dynamically with Django and Sqlalchemy

Photo by Joshua Aragon on Unsplash

When we come across certain problems we might need some unconventional hacks. Here I’m going to talk about using sqlalchemy as a substitute for the django ORM for connecting and creating tables inside multiple databases in the same database server.

The problem

The problem is very simple the database architecture was that there is a mysql database server with multiple databases .

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dummy              |
| mysql              |
| mytest             |
| mytest1            |
| mytest2            |
| mytest3            |
| performance_schema |
| sys                |
| test               |
+--------------------+
10 rows in set (0.00 sec)

here mytest , mytest1 , mytest2 , mytest3 are the databases.

in django you cannot connect with multiple databases dynamically as your connection goes in the settings.py file.

# https://docs.djangoproject.com/en/2.0/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'read_default_file': '/etc/mysql/my.cnf',
        },
    }
}

my.cnf file

[client]
database = db_name
user = db_user
password = db_password
default-character-set = utf8

here we can only connect to a fixed number of databases and they are static.

So we are going to fix this by using a popular library sqlalchemy.

first install sqlalchemy.

pip install sqlalchemy

we use sqlalchemy to connect with databases .Create connect inside views.py or create a new file and import that in views.

def connect(database_name):
    connection_string = "mysql://{}:    {}@{}/{}".format(db_user,db_password,db_url,database_name)
    engine = create_engine(connection_string,pool_recycle=3600)
    connection = engine.connect()
    return connection

next Ihave created alchemymodel.py file which acts as the model.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String ,Text
Base = declarative_base()
class Post(Base):

    __tablename__ = 'post'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    description = Column(Text())

import this file in the views.py

from dash.alchemymodel import Base,Post
from sqlalchemy.orm import sessionmaker
from rest_framework import status

@api_view(['POST'])
def PostView():

    data = request.data
    engine = connect(req_data['database_name'])
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    if request.method == 'POST':
        post = Post(name = "My post" , description = "This is a   sample post")
        session.add(post)
        session.commit()
    Response({'status':'created'}, status = status.HTTP_200_OK)

Base.metadata.creat_all() creates the table inside each of the databases.

You can insert and query data using the sqlalchemy ORM for more information please see the official documentation of sqlalchemy.

https://docs.sqlalchemy.org/en/13/orm/

I have also used marshmallow for serializing and deserializing the database objects but they are not explained here.