Dynamically create tables in schema with Django, dynamically generate models

Overview

Django's ORM feature is great, but when you try to create a Model for a slightly different table, such as a dynamically created table or a table in a specific postgres schema, there isn't much information.

Also, if you try to do something a little complicated, such as diverting the created table and creating a table with the same definition in another schema, it takes time to investigate and the development process stops for a few days.

This article is a record of struggling to achieve the following in the face of these challenges.

** **

--I want to create a Model that has the same definition as an existing Model but only the schema is different, by diverting the existing Model ** dynamically **. --I want to use the created Model to ** dynamically ** create a table in a schema different from the created Model. --I want to use the created Model to search and register using ** Django's ORM function **.

In short, it's a bit complicated requirement, but the ORM function using Django's Model class is excellent, so I want to maximize the benefits of table creation, record registration, search, etc.

Motivation in the first place

Why you want to use a schema other than the Postgres standard schema

--One of the schema uses described in PostgreSQL 10.5 document "One database is used by many users. This is because we want to realize "so that they can be used without interfering with each other." This time, we are aiming to realize an analysis service that can be used by various users, but in order to eliminate interference between user data, we want to prepare a schema with the same table structure for each user.

Why do you want to use existing tables and existing models?

--When a user is registered, I want to dynamically create a schema for that user and a table in that schema. At this time, I want to create from the already defined Model without having to prepare the Create statement. Because the Create statement and the Model source will have double management of DDL.

environment

The environment this time is as follows.

manner

In conclusion, it was possible by applying the references. Details are described below.

Definition of Model that is the source of diversion

First, for the following explanation, an example of a model that is a diversion source is as follows.

app/models/model.py


class Compound(models.Model):
    name = models.CharField(max_length=1024)
    smiles = models.CharField(max_length=4000, null=True)
    standard_inchi = models.CharField(max_length=4000, default=None, null=True)
    mol = models.TextField(null=True)
    image = models.BinaryField(null=True)

Since the model that is the source of the diversion is under the control of Django's migration, you should be able to create a table for the Model by make migration and migrate. And unless you make a special setting, the table will be created in the public schema.

Create a function that dynamically creates a Model

Next, the function that is the key to this time will be explained. Normally, in Django, it is necessary to create a model class in the source in advance, but this time, refer to Dynamic models and create the desired Model. I prepared a function that dynamically creates a class. Specifically, it is now possible to create a new Model by specifying the schema, table name, and Model class of the diversion source. The source is as follows. It should be noted that, where may be created for the function, but are defined in the same module and the current diversion source.

app/models/model.py


def create_model_by_prototype(model_name, schema_name, table_name, prototype):
    import copy

    class Meta:
        pass

    setattr(Meta, "db_table", schema_name + "\".\"" + table_name)
    fields = {}
    for field in prototype._meta.fields:
        fields[field.name] = copy.deepcopy(field)

    attrs = {'__module__': "app.models.models", 'Meta': Meta}
    attrs.update(fields)
    model = type(model_name, (models.Model,), attrs)

    return model

Commentary

We will look at specific usage examples later, and briefly explain the source.

--As arguments, specify the schema to create the table in schema_name, the table name in table_name, and the Model class as a template in prototype. --Set attributes for generating a model in the class `Meta``` --In setattr```, the table name specified by the argument is set for the attribute" db_table "that represents the table name managed by Model. --In the loop `` for field in prototype._meta.fields: ``, we are preparing to set the field of the model to be diverted to the field of the model to be created this time. --Finally, with ``` type (model_name, (models.Model,), attrs) ``, a new Model class is generated and returned based on the prepared attribute information.

Hands-on

Now, let's see the usage image of the function using the created function by hands-on with django shell.

Creation of model and table to be diverted

This is omitted because it only executes Django's makemigrations and migrate.

Creating a new schema

The schema for storing the new table was troublesome this time, so I created it by connecting directly to Postgres. The schema name is user01.

create schema user01;

Launch Django shell

$ python manage.py shell
Python 3.7.6 | packaged by conda-forge | (default, Jun  1 2020, 18:11:50) [MSC v.1916 64 bit (AMD64)]
Type 'copyright', 'credits' or 'license' for more information
IPython 7.16.1 -- An enhanced Interactive Python. Type '?' for help.

Module import

Next, import the modules required to use the function.

from chempre.models.models import Compound
from chempre.models import models 

Dynamic creation of new model

Now, let's create a Model dynamically using the created function. Here, a Model called Compound is diverted, and a table called my_compound with the same definition is created in the user01 schema created earlier.

model= models.create_model_by_prototype("MyCompound", "user01", "my_compound", Compound)

Creating a table

Next, create a table corresponding to Model. Since this Model is not managed by migration, it will be created differently than usual. The procedure is as follows.

from django.db import connection
with connection.schema_editor() as schema_editor:
     schema_editor.create_model(model)

This will create a table in schema user01. When I actually connect to Postgres with psql, it is certainly created.

Registration of data in Model

Next, let's register the data. Since it was troublesome to specify the field, here I am creating a record with only the name field set.

model.objects.create(name='compound name')

Search for Model

Let's search and check if the table has been created.

compound=model.objects.get(id=1)[
print(compound.name)

If registered, you should see the value'compund name'. Also, when I actually connect to Postgres with psql, a record is certainly created.

in conclusion

I was able to safely achieve all the things I originally wanted to achieve. I want to mass-produce the Gangan Model in this way and enjoy the Django life. Django has a lot of tactics and a lot to remember, but it doesn't have the ruggedness and inflexibility that is typical of this kind of framework, and it seems like it can be customized flexibly.

References

Recommended Posts

Dynamically create tables in schema with Django, dynamically generate models
Save multiple models in one form with Django
Create an API with Django
Create a homepage with django
Create and list Django models
Create an authentication feature with django-allauth and CustomUser in Django
Dynamically generate graphs with matplotlib and embed in PDF with reporlab
Dynamically add form fields in Django
GraphQL API with graphene_django in Django
Create a file uploader with Django
Create a LINE Bot in Django
[Can be done in 10 minutes] Create a local website quickly with Django
Create RESTful APIs with Django Rest Framework
Dynamically specify a ModelChoiceField queryset in Django
Create an update screen with Django Updateview
Create your first app with Django startproject
Dynamically add fields to Form objects in Django
Start Django in a virtual environment with Pipenv
Create a virtual environment with conda in Python
Build a Django environment with Vagrant in 5 minutes
Create a dashboard for Network devices with Django!
Create an image with characters in python (Japanese)
Create a new page in confluence with Python
Create a one-file hello world application with django
Create initial settings and staff apps in Django
Configure a module with multiple files in Django
How to create a Rest Api in Django
Until you create a new app in Django
Implementing authentication in Django REST Framework with djoser