How to Join two tables from two different databases in Django?

Question

In one of my project I came across the requirement of joining two tables from different databases in Django and couldn't figure out optimal solution yet. The problem is I have two databases with tables that are correlated with each other and I want to make a join between tables of two different databases. SQL query works fine inside MS SQL Server but how to do that in Django is the main issue. I want to run the RawSQL Query inside Django and pass it to ORM. For a single database it is fine but for two databases it is not working.

For example, I have two Databases Like DB1 with table CashAccount and DB2 with table teamAccount.

SQL Query:
SELECT  (t1.unique_name) as unique_name,
        (t1.AccountBalanceAmount) AS AccountBalanceAmount,
        (t2.TeamName) AS TeamName,
        (t2.ProjectDesc) AS ProjectDesc,
FROM (
    SELECT CONCAT(ID,'_',ProjectName) AS unique_name,
    AccountBalanceAmount
    FROM DB1.CashAccount
) t1
INNER JOIN (
    SELECT CONCAT(ID,'_',ProjectName) AS unique_name,
        TeamName
        ProjectDesc
    FROM DB2.TeamAccount
) t2 ON t1.unique_name = t2.unique_name

How do I execute this query using Django ORM or raw SQL Query using raw()?

Answer 1

I think the above answer is a good answer but there is a more complex solution.

• Django wants you to create databases by creating a model and then running the migrate command to create/update the tables in your DB of choice. If you do this, everything will be in the same DB and you will not have this issue.
• I want to organize my databases a little more so I manually create them and a team member created a custom router to indicate which db to use.

Custom Router class:

class DBRouter(object): def db_for_read(self, model, **hints): """ specify what model uses what db to write """ if model in [ Industry, Verticals ]: return 'database-a' ...

Update Settings file to use your custom router DATABASE_ROUTERS = ['your_app.db_routers.DBRouter']

Then you can use code like:

banana = TableB.objects.get(pk=1)

queryset = TableA.objects.using('db-a').all()

queryset = queryset.using('db-b').filter(foreign_key_col_a=banana)

Answer 2

Using Django's Manager.raw execute SQL should work fine across two different in one db server, just make sure result columns contain the primary key of ORM model.