Django导出数据库模型类说明文档_django导出oracle数据库表模型

1.工具类

import os

from django.db import models
from pandas import DataFrame

from django.conf import settings

# # 获取 模型类 unique_together数据
# model_name.PositionKind._meta.original_attrs.get('unique_together')
# # 获取 模型类 verbose_name
# model_name.PositionKind._meta.original_attrs.get('verbose_name')
# # 获取模型类


class HandleModelInfoExport(object):
    def __init__(self, *model_py_list):
        """
        初始化
        :param model_py_list: models.py的列表
        """
        self.model_py_list = list(model_py_list)
        self.columns = ['变量', '变量名称', '类型', 'null/not null', '备注']

    def get_model_excel(self, excel_name):
        """
        获取数据库模型excel
        :param excel_name:  要导出的excel名字
        :return: 格式:  xxx.xlsx
        """
        model_info = self.get_model_data()
        df = DataFrame(model_info, index=None)
        path = os.path.join(settings.BASE_DIR, f'{excel_name}.xlsx')
        df.to_excel(path, index=False)
        return '导出数据成功'

    def get_model_data(self):
        """
        获取模型导出数据
        :return:
        """
        model_info = []
        for model_py in self.model_py_list:
            model_info.extend(self.get_model_py_info(model_py))
        return model_info

    def get_model_py_info(self, model_py):
        """
        获取每一个models.py下的信息, 便利每个models.py的模型类获取数据
        :param model_py:  模型文件
        :return:
        """
        un_except_list = ['AbstractUser']            # 不希望获取信息的类名
        data_info_list = []
        model_name_keys = model_py.__dict__.keys()
        for key in model_name_keys:
            # 排除系统变量, 获取模型类名
            # 判断__dict__.keys() 中的key数据是否是模型类
            if model_py.__dict__.get(key).__class__ is models.base.ModelBase and key not in un_except_list:
                model_info = model_py.__dict__.get(key)    # 通过模型类名获取模型类
                if model_info:
                    original_attrs = model_info._meta.original_attrs    # 获取模型类 class Meta中的数据
                    table_name = original_attrs.get('verbose_name')     # 获取模型类的verbose_name
                    unique_together = original_attrs.get('unique_together')     # 获取联合唯一数据unique_together
                    data_info_list.append(self.add_blank_info(f'{model_info.__name__}({table_name})'))      # 添加模型的verbose_name到表格数据中    用f进行占位需python3.6+

                    data_info_list.append(self.columns)                             # 添加每个表格的表头

                    data_info_list.extend(self.get_every_model_info(model_info))    # 添加每个表格数据

                    if unique_together:                                             # 添加联合唯一备注信息
                        unique_together = f'{unique_together}联合唯一'
                        data_info_list.append(self.add_blank_info(unique_together))
                    data_info_list.append(self.add_blank_info(''))                  # 添加空白行
                    data_info_list.append(self.add_blank_info(''))                  # 添加空白行

        return data_info_list

    def get_every_model_info(self, model):
        """
        获取每一个模型类中的变量数据
        :param model:
        :return: 二位列表, 每个变量都是一维列表 存储'变量', '变量名称', '类型', 'null/not null', '备注'五个字段信息
        """
        fields_info_list = []
        un_except_list = ['id']      # 不希望获取的属性字段
        fields = model._meta.fields
        for field in fields:
            if field.name not in un_except_list:
                db_info = self.get_db_info(field)
                type_info = db_info[0]
                is_null = db_info[1]
                remark = db_info[2]
                fields_info_list.append([field.name, field.verbose_name, type_info, is_null, remark])

        return fields_info_list

    @staticmethod
    def add_blank_info(data_info):
        """
        添加空白数据, 凑数据
        :param data_info: 要凑数据的值
        :return:
        """
        return [data_info, '', '', '', '']

    def get_db_info(self, field):
        """
        获取该字段对象相关信息
        :param field: 字段对象
        :return:  [数据类型, 空值信息, 备注信息]
        """
        is_null = 'not null' if not field.null else ''    # 是否为空
        remark = '唯一' if field._unique else ''          # 是否唯一
        if field.choices:                                # 是否有选择项目
            remark += f', {field.choices}'
        if field.default is not None and field.default is not models.fields.NOT_PROVIDED:  # 是否有默认值
            remark += f', 默认值为: {field.default}'
        if field.remote_field:                          # 是否是外键引用  获取外键数据
            remark += f', {self.get_relation_info(field)}'
        if remark.startswith(','):
            remark = remark[2:]
        return [self.get_type_info(field), is_null, remark]

    @staticmethod
    def get_type_info(field):
        """
        获取数据类型
        :param field:
        :return:
        """
        if isinstance(field, models.fields.CharField):
            max_length = field.max_length
            return f'varchar({max_length})'
        elif isinstance(field, models.fields.TextField):
            return 'longtext'
        elif isinstance(field, models.fields.BooleanField):
            return 'bool'
        elif isinstance(field, models.fields.DateTimeField):
            return 'datetime'
        elif isinstance(field, models.fields.DateField):
            return 'date'
        elif isinstance(field, models.fields.TimeField):
            return 'time'
        elif isinstance(field, models.fields.IntegerField):
            return 'int'
        elif isinstance(field, models.fields.DecimalField):
            return 'decimal'
        elif isinstance(field, models.fields.FloatField):
            return 'double'
        elif isinstance(field, models.fields.IntegerField):
            return 'int'
        else:
            return ''

    @staticmethod
    def get_relation_info(field):
        """
        获取外键信息
        :param field:
        :return:
        """
        field_type = type(field)
        relation_class_name = field.remote_field.model.__name__                     # 获取关联模型名
        relation_model_py = field.remote_field.model.__module__.split('.')[0]       # 获取关联模型所在的包名
        db_relation_list = ['many_to_one', 'many_to_one', 'one_to_many', 'one_to_one']      # 判断关系
        relation_name = ''
        for db_relation in db_relation_list:
            relation_name = db_relation
            if field_type.__dict__.get(db_relation):
                break
        return f'外键, 和{relation_model_py}包下的{relation_class_name}是{relation_name}关系'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172

2.调用函数

from utils.db_info_export import HandleModelInfoExport
import companies.models as com_model
import employees.models as em_model
import performance.models as per_model
import users.models as user_model
model_list = [user_model, com_model, em_model, per_model]
HandleModelInfoExport(*model_list).get_model_excel('数据库信息')
1
2
3
4
5
6
7

3. 导出示例

4. 说明

本例中使用的是pandas进行excel导出, 插件较重, 简单点可以使用xlwt/xlrd进行导出, 对应地方进行修改即可
1