Django Model 与 E-R 图

在需求逐步确认的过程中，系统的 DB 模型也逐步确认。有时使用，MySQL Workbench 这类工具绘制 E-R 图，然后生成 MySQL 数据库表，这时就需要反向生成 Django Models；有时直接写 Django Models ，但是又需要查看 E-R 图。本文主要解决上面两个需求，实现 Django Models 与 E-R 图之间的转换。

1. 生成 model 的关系图

Django 提供第三方包 django-extensions，可以用来将 Django 中的 Models 生成 E-R 图。

1.1 安装包

1
pip install django-extensions

1.2 配置

在 Django settings.py 文件， INSTALLED_APPS 中添加 django_extensions。

1
2
3
INSTALLED_APPS = (
    'django_extensions',
)

1.3 生成 dot 文件和 png 图片

生成全部 model 的 E-R 图

1
python manage.py graph_models -a > all.dot

单独某个 Django App （以 django_view_permission 为例）生成 dot 文件。

1
python manage.py graph_models django_view_permission > django_view_permission.dot

导出的 dot 内容如下：
django_view_permission.dot

digraph model_graph {
  // Dotfile by Django-Extensions graph_models
  // Created: 2018-03-03 14:55
  // Cli Options: django_view_permission

  fontname = "Helvetica"
  fontsize = 8
  splines  = true

  node [
    fontname = "Helvetica"
    fontsize = 8
    shape = "plaintext"
  ]

  edge [
    fontname = "Helvetica"
    fontsize = 8
  ]

  // Labels

  django_view_permission_models_CommonElement [label=<
    <TABLE BGCOLOR="palegoldenrod" BORDER="0" CELLBORDER="0" CELLSPACING="0">
    <TR><TD COLSPAN="2" CELLPADDING="4" ALIGN="CENTER" BGCOLOR="olivedrab4">
    <FONT FACE="Helvetica Bold" COLOR="white">
    CommonElement
    </FONT></TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">create_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">DateTimeField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">doc</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">TextField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">update_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">DateTimeField</FONT>
    </TD></TR>
  
    </TABLE>
    >]

  django_view_permission_models_View [label=<
    <TABLE BGCOLOR="palegoldenrod" BORDER="0" CELLBORDER="0" CELLSPACING="0">
    <TR><TD COLSPAN="2" CELLPADDING="4" ALIGN="CENTER" BGCOLOR="olivedrab4">
    <FONT FACE="Helvetica Bold" COLOR="white">
    View<BR/>&lt;<FONT FACE="Helvetica Italic">CommonElement</FONT>&gt;
    </FONT></TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT FACE="Helvetica Bold">id</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT FACE="Helvetica Bold">AutoField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">create_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">DateTimeField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">doc</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">TextField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">func</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">CharField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">module</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">CharField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">name</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica ">CharField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">update_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">DateTimeField</FONT>
    </TD></TR>
  
    </TABLE>
    >]

  django_view_permission_models_ViewSet [label=<
    <TABLE BGCOLOR="palegoldenrod" BORDER="0" CELLBORDER="0" CELLSPACING="0">
    <TR><TD COLSPAN="2" CELLPADDING="4" ALIGN="CENTER" BGCOLOR="olivedrab4">
    <FONT FACE="Helvetica Bold" COLOR="white">
    ViewSet<BR/>&lt;<FONT FACE="Helvetica Italic">CommonElement</FONT>&gt;
    </FONT></TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT FACE="Helvetica Bold">id</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT FACE="Helvetica Bold">AutoField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">create_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">DateTimeField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">doc</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">TextField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT FACE="Helvetica ">name</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT FACE="Helvetica ">CharField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">update_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">DateTimeField</FONT>
    </TD></TR>
  
    </TABLE>
    >]

  django_view_permission_models_ViewPermission [label=<
    <TABLE BGCOLOR="palegoldenrod" BORDER="0" CELLBORDER="0" CELLSPACING="0">
    <TR><TD COLSPAN="2" CELLPADDING="4" ALIGN="CENTER" BGCOLOR="olivedrab4">
    <FONT FACE="Helvetica Bold" COLOR="white">
    ViewPermission<BR/>&lt;<FONT FACE="Helvetica Italic">CommonElement</FONT>&gt;
    </FONT></TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT FACE="Helvetica Bold">id</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT FACE="Helvetica Bold">AutoField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">create_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">DateTimeField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">doc</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">TextField</FONT>
    </TD></TR>
  
    <TR><TD ALIGN="LEFT" BORDER="0">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">update_time</FONT>
    </TD><TD ALIGN="LEFT">
    <FONT COLOR="#7B7B7B" FACE="Helvetica Italic">DateTimeField</FONT>
    </TD></TR>
  
    </TABLE>
    >]
  // Relations

  django_view_permission_models_View -> django_view_permission_models_CommonElement
  [label="abstract\ninheritance"] [arrowhead=empty, arrowtail=none, dir=both];

  django_view_permission_models_ViewSet -> django_view_permission_models_View
  [label="views (viewset)"] [arrowhead=dot arrowtail=dot, dir=both];

  django_view_permission_models_ViewSet -> django_view_permission_models_CommonElement
  [label="abstract\ninheritance"] [arrowhead=empty, arrowtail=none, dir=both];
  account_models_BkUser [label=<
  <TABLE BGCOLOR="palegoldenrod" BORDER="0" CELLBORDER="0" CELLSPACING="0">
  <TR><TD COLSPAN="2" CELLPADDING="4" ALIGN="CENTER" BGCOLOR="olivedrab4">
  <FONT FACE="Helvetica Bold" COLOR="white">BkUser</FONT>
  </TD></TR>
  </TABLE>
  >]
  django_view_permission_models_ViewPermission -> account_models_BkUser
  [label="users (viewpermission)"] [arrowhead=dot arrowtail=dot, dir=both];

  django_view_permission_models_ViewPermission -> django_view_permission_models_View
  [label="views (viewpermission)"] [arrowhead=dot arrowtail=dot, dir=both];

  django_view_permission_models_ViewPermission -> django_view_permission_models_ViewSet
  [label="views_set (viewpermission)"] [arrowhead=dot arrowtail=dot, dir=both];

  django_view_permission_models_ViewPermission -> django_view_permission_models_CommonElement
  [label="abstract\ninheritance"] [arrowhead=empty, arrowtail=none, dir=both];
}

生成图片

由于生成的是 dot 格式的图片描述文件，需要使用工具进行转换成常用的 PNG 格式图片。可以下载 graphviz ，并将 /bin/dot.exe 加入系统 PATH 中。

1
dot -Tpng django_view_permission.dot > django_view_permission.png 

还可以使用在线 Dot 预览工具 GraphvizOnline 查看，下载 PNG 图片。

2. 生成数据库的 models.py

Django 内置了 inspectdb 命令，可以利用数据库对应的表关系和字段，生成 Django Models。

1
python manage.py inspectdb export_models.py

导出的文件内容如下：
export_models.py

 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
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
#
# Also note: You'll have to insert the output of 'django-admin sqlcustom [app_label]'
# into your database.
from __future__ import unicode_literals

from django.db import models

class DjangoViewPermissionView(models.Model):
    doc = models.TextField(blank=True, null=True)
    create_time = models.DateTimeField()
    update_time = models.DateTimeField()
    module = models.CharField(max_length=255, blank=True, null=True)
    func = models.CharField(max_length=255, blank=True, null=True)
    name = models.CharField(max_length=255, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'django_view_permission_view'
        unique_together = (('func', 'module', 'name'),)


class DjangoViewPermissionViewPermission(models.Model):
    doc = models.TextField(blank=True, null=True)
    create_time = models.DateTimeField()
    update_time = models.DateTimeField()

    class Meta:
        managed = False
        db_table = 'django_view_permission_view_permission'


class DjangoViewPermissionViewPermissionUsers(models.Model):
    viewpermission = models.ForeignKey(DjangoViewPermissionViewPermission)
    bkuser = models.ForeignKey(AuthUser)

    class Meta:
        managed = False
        db_table = 'django_view_permission_view_permission_users'
        unique_together = (('viewpermission_id', 'bkuser_id'),)


class DjangoViewPermissionViewPermissionViews(models.Model):
    viewpermission = models.ForeignKey(DjangoViewPermissionViewPermission)
    view = models.ForeignKey(DjangoViewPermissionView)

    class Meta:
        managed = False
        db_table = 'django_view_permission_view_permission_views'
        unique_together = (('viewpermission_id', 'view_id'),)


class DjangoViewPermissionViewPermissionViewsSet(models.Model):
    viewpermission = models.ForeignKey(DjangoViewPermissionViewPermission)
    viewset = models.ForeignKey('DjangoViewPermissionViewSet')

    class Meta:
        managed = False
        db_table = 'django_view_permission_view_permission_views_set'
        unique_together = (('viewpermission_id', 'viewset_id'),)


class DjangoViewPermissionViewSet(models.Model):
    doc = models.TextField(blank=True, null=True)
    create_time = models.DateTimeField()
    update_time = models.DateTimeField()
    name = models.CharField(unique=True, max_length=255)

    class Meta:
        managed = False
        db_table = 'django_view_permission_view_set'


class DjangoViewPermissionViewSetViews(models.Model):
    viewset = models.ForeignKey(DjangoViewPermissionViewSet)
    view = models.ForeignKey(DjangoViewPermissionView)

    class Meta:
        managed = False
        db_table = 'django_view_permission_view_set_views'
        unique_together = (('viewset_id', 'view_id'),)

需要注意的是，使用 migrate 命令创建数据库表时，managed = False 的 Models 会被忽略。

3. 参考

https://segmentfault.com/a/1190000002978794