Map dynamically generated tables in Django by another application

Question

I will develop an application with Django in which it will be necessary to query data in a database that is powered by an ERP (desktop).

This ERP generates some drive tables by adding at the end of the table name the month and the year, as a kind of partitioning, example: financeiro0718, financeiro0818, financeiro0918, etc..

These tables have the same column structure and data types, the difference is only the name. My intention is to have only one model mapping these tables so as not to get a huge amount of classes with repeated code in my project.

How can I map these tables in Django in a way that I can have a model Financeiro and the ORM can query the data in the corresponding tables?

Answer 1

You can specify the table name in the SQL database by filling in the field db_table in the metadata of a model. In a conventional declaration, the metadata is inside the nested class with the name "Meta" -

So one way to create multiple models dynamically, each pointing to a table, is to create these models dynamically with calls to type, changing the necessary data - you can store these templates in a dictionary, and so you can access them in Runtime.

If it’s done in code top level - that is, executed when the file models.py It’s almost certain to work. It may work in Azy way, with models being dynamically created as needed as well - but it may not, if Django performs some ORM action when loading the tables.

from django.db import model

class BaseFinanceiro(models.Model):
     # declare os campos normalmente
     class Meta:
          abstract = True

tabelas_extras = "financeiro0718 financeiro0818 financeiro0918 ...".split()
# acima, é só uma forma com menos digitação de criar uma lista

modelos = {}
for nome in tabelas_extras:
     meta = type('Meta', (), {'db_table': nome})
     modelos[nome] = type(nome, (BaseFinanceiro,), {'Meta': meta})

Of course I only used "name" as a string because it was the example you gave - the access can be even more direct if you use as keys of the path in "models" a pair of values "month, year" (it is a tuple in Python: models[9, 18] - or even a datetime object.date) - but then, of course, it depends on how you will access these templates in the rest of the code.