3
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

[Django][MySQL] モデルの verbose_name をテーブルコメントに

Django のモデルやそのフィールドの verbose_name をテーブルのコメントに付与するコマンドを作りました.

DB は MySQL のみ対応 です.

環境

  • Django 2.1
  • Mysql 5.7

実装

任意の app に 以下のコマンドを配置します. ここではコマンドの名前は add_mysql_comments にしました.

<app dir>/management/commands/add_mysql_comments.py

import re

from django.core.management.base import AppCommand
from django.db import connections


class Command(AppCommand):
    help = 'add mysql comments'

    cursor = None

    def alter_table_comment(self, model):
        meta = model._meta
        statement = f"ALTER TABLE {meta.db_table} COMMENT '{meta.verbose_name}';"
        # statements.append(statement)
        self.cursor.execute(statement)
        return statement

    def _get_alter_column_statements_without_comment(self, table_name) -> dict:

        self.cursor.execute(f'SHOW CREATE TABLE {table_name}')
        f = self.cursor.fetchone()
        sql = f[1]
        lines = re.split(r'[\r\n]+', sql)
        columns = {}

        for line in lines:
            line = line.strip()
            m = re.match(r'^`(.*?)`.*', line)
            if not m: continue
            column_name = m.group(1)

            # 既存コメント除外
            s = re.sub(r"COMMENT +'([^']|'')+'[ ,]?", '', line)
            columns[column_name] = s[:-1]  # trim last `,`

        res = {}
        for k, v in columns.items():
            res[k] = f'alter table `{table_name}` modify {v} '
        return res

    def alter_columns_comment(self, model):
        meta = model._meta
        table_name = meta.db_table
        alter_statements = self._get_alter_column_statements_without_comment(table_name)

        statements = []

        for field in model._meta.fields:
            column = field.db_column or field.column
            statement = alter_statements.get(column, None)
            if not statement: continue

            comment = field.verbose_name
            if comment:
                statement += f" COMMENT '{comment}'"
                self.cursor.execute(statement)
                statements.append(statement)

        return statements

    def handle_app_config(self, app_config, **options):
        """
        # TODO: database を指定(今 default 固定)
        # TODO: 除外モデルを指定
        """

        if app_config.models_module is None:
            return

        connection = connections['default']
        self.cursor = connection.cursor()
        models = app_config.get_models(include_auto_created=True)

        statements = []
        for model in models:
            statements.append(self.alter_table_comment(model))
            statements += self.alter_columns_comment(model)

        connection.close()  # 必要?

        return '\n'.join(statements)

つかいかた

python manage.py add_mysql_comments <app1> <app2>

python manage.py add_mysql_comments のあとに対象とする app 名を渡します.
正しく実行されるとテーブルにコメントが付与されています. 実行したSQLは以下のようにコンソールに出力されます.

ALTER TABLE accounts_user COMMENT 'ユーザー';
alter table `accounts_user` modify `password` varchar(128) NOT NULL  COMMENT 'パスワード'
alter table `accounts_user` modify `last_login` datetime(6) DEFAULT NULL  COMMENT '最終ログイン'
alter table `accounts_user` modify `is_superuser` tinyint(1) NOT NULL  COMMENT 'スーパーユーザー権限'

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
3
Help us understand the problem. What are the problem?