Introduction

Django is convenient because it automatically issues a Create Table statement from the Model written in models.py to create a table ... but it doesn't support commenting on tables and columns. If you want to reverse-generate the ER diagram from the created table, I would appreciate it if you could add a comment ...

So, I tried and errored whether to make it myself if it wasn't there.

Table comment / column comment syntax

The syntax for adding table comments or column comments is as follows.

comment on table table_name is 'comment'
comment on column table_name.column_name is 'comment'

However, this is an proprietary specification of Oracle and PostgreSQL (and DB2), and it seems that it is not a SQL standard. I wish I could make sqlite3 with the same syntax if possible, but I gave up this time because it seems impossible. At the very least, I'd like to comment on Oracle, which I usually use.

Create Django custom commands

You can create it as a normal Python module, but since it's a big deal, I'll create my own Django command so that it can be executed quickly from the command line. For Django commands, by inheriting the AppCommand class, you can receive the Django application's AppConfig object as an argument, so it's easy to get a list of Models to create comments.

For creating Django custom commands, I referred to the following article.

In addition, I referred to the source of django.core.manegement.commands.sqlsequencereset.

Command format

The command name is "create table comment". The format should look like ↓.

manage.py createtablecomment [options] <app_label app_label ...>

Module placement for commands

Since the command created this time is not for a specific application, place the module in the project folder. (The following is a reference for placement. The project name is "myproject")

myproject
　└ myproject
　　　└ management
　　　　　└ commands
　　　　　　　└ createtablecomment.py

Command class source

Except for the contents of handle_app_config, I copy sqlsequencereset.py and use it almost as it is.

`createtablecomment.py`


from __future__ import unicode_literals

from optparse import make_option

from django.core.management.base import AppCommand
from django.core.management.sql import check_for_migrations
from django.db import connections, DEFAULT_DB_ALIAS
from django.db.models.fields.related import ForeignKey

class Command(AppCommand):
    help = 'Prints the SQL statements for create comment of tables and columns for the given app name(s).'

    option_list = AppCommand.option_list + (
        make_option('--database', action='store', dest='database',
            default=DEFAULT_DB_ALIAS, help='Nominates a database to print the '
                'SQL for.  Defaults to the "default" database.'),
    )

    output_transaction = True

    def handle_app_config(self, app_config, **options):
        if app_config.models_module is None:
            return
        connection = connections[options.get('database')]
        check_for_migrations(app_config, connection)
        models = app_config.get_models(include_auto_created=True)
        cursor = connection.cursor()
        statements = []

        for model in models :
            statement = "COMMENT ON TABLE %s IS '%s'" %
                      (model._meta.db_table, model._meta.verbose_name)
            statements.append(statement)
            cursor.execute(statement)
            for field in model._meta.fields :
                if isinstance(field, ForeignKey) :
                    column  = '%s_id' % field.name
                    comment = '%s(FK:%s)' %
                            (field.verbose_name, field.related.parent_model._meta.db_table)
                else :
                    column  = '%s' % field.name
                    comment = field.verbose_name
                statement = "COMMENT ON COLUMN %s.%s IS '%s'" %
                          (model._meta.db_table, column, comment)
                statements.append(statement)
                cursor.execute(statement)
        return '\n'.join(statements)

The processing content is like ↓.

Get the model class list under the app with ʻapp_config.get_models ()` and turn it with for
Create & execute create comment statement for table (get cursor with connection.cursorl () andcursor.execute (statement))
Get the list of fields that model has with model._meta.fields and turn it with for
Create and execute create comment statement for the column (In the case of the ForeignKey field, it is completed because" _id "is added to the column name. Also, the information" Which table's FK? "Is added to the comment)
Make the list of executed create comment statements into line break delimited strings and return (when the command is executed from the console, the executed SQL statement is output to the console)

Execution result

I tried to execute it for myapp application that has models.py of ↓.

`models.py`


from django.db import models

class Author(models.Model):
    class Meta :
        db_table = 'AUTHORS'
        verbose_name = 'Author'
        verbose_name_plural = verbose_name

    name = models.CharField('name', max_length=50)
    birthday = models.DateField('Birthday')

class BooK(models.Model):
    class Meta:
        verbose_name = 'Book'
        verbose_name_plural = verbose_name

    name = models.CharField('Book title', max_length=100)
    price = models.IntegerField('price')
    author = models.ForeignKey(Author, verbose_name='Author')

Command execution

> python manage.py createtablecomment myapp
BEGIN;
COMMENT ON TABLE AUTHORS IS 'Author'
COMMENT ON COLUMN AUTHORS.id IS 'ID'
COMMENT ON COLUMN AUTHORS.name IS 'name'
COMMENT ON COLUMN AUTHORS.birthday IS 'Birthday'
COMMENT ON TABLE myapp_book IS 'Book'
COMMENT ON COLUMN myapp_book.id IS 'ID'
COMMENT ON COLUMN myapp_book.name IS 'Book title'
COMMENT ON COLUMN myapp_book.price IS 'price'
COMMENT ON COLUMN myapp_book.author_id IS 'Author(FK:AUTHORS)'

COMMIT;

The comments have been successfully created for the tables and columns.

[PYTHON] I made a command to generate a table comment in Django