I made a tool to automatically generate a simple ER diagram from the CREATE TABLE statement

The other day, I made a command line tool called Pyagram that automatically generates a state transition diagram, but as an additional function of it, the relationship between tables from the CREATE TABLE statement Implemented a function to automatically generate a simple ER diagram that only can be understood.

Personally, I don't use foreign keys at all, so when using MySQL Workbench, there was a problem that it was not possible to generate an ER diagram with relations between tables. Therefore, I decided to create a tool that can guess the parent and child from the column name and associate them even if there is no foreign key.

There are some restrictions on use, so please understand the following before using.

Also, the verification may still be inadequate, so it may not work. We would appreciate it if you could report it in the comments section or Issue on Github.

The following is the generated figure.

erd.gif

Prepare a file in which the following CREATE TABLE statement is defined as an input file.

CREATE TABLE employees (
    id INT(11) NOT NULL AUTO_INCREMENT,
    birth_date DATE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    gender INT(11) NOT NULL,
    hire_date DATE NOT NULL,
    department_id int(11),
    PRIMARY KEY('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE departments (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE titles (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE employee_title (
    employee_id INT(11) NOT NULL,
    title_id INT(11) NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY('employee_id', 'title_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE salaries (
    id INT(11) NOT NULL AUTO_INCREMENT,
    employee_id INT(11) NOT NULL,
    salary INT(11) NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY('id'),
    KEY('employee_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Run the following command to generate the diagram.

pyagram -t {Image type} -o {Output path} -i {Input file} -f {Font name} -d erd

You can also install it with the following command.

pip3 install pyagram

For this fix, we made a big change to make it a pluggable implementation. By inheriting the class called Diagram, you can create any number of diagram generation classes. In the Diagram class

  1. lexical_analysis
  2. syntactic_analysis
  3. generate_dot (data generation for dot files)
  4. generate_image (image file generation)

There are roughly four processes, but in the class that inherits Diagram, it is made so that 1 to 3 can be implemented.

In the future, I would like to increase the number of output items and the types of RDBMS that support it, but it will be a little painful, so I would like to take time to proceed.

Recommended Posts

I made a tool to automatically generate a simple ER diagram from the CREATE TABLE statement
I made a tool to generate Markdown from the exported Scrapbox JSON file
I made a tool to create a word cloud from wikipedia
I made a plugin to generate Markdown table from csv in Vim
I tried to automatically generate a port management table from Config of L2SW
I made a tool to automatically back up the metadata of the Salesforce organization
I made a command to markdown the table clipboard
I wrote Python code to create a table (view) dependency diagram (PlantUML) from SQL
I made a command to generate a table comment in Django
I made a plug-in from the Japan Meteorological Agency GPV to easily create an animated contour diagram with QGIS.
I made a tool to automatically generate a state transition diagram that can be used for both web development and application development
I made a tool to automatically browse multiple sites with Selenium (Python)
Create a tool to automatically furigana with html using Mecab from Python3
I made a simple timer that can be started from the terminal
I made a tool to compile Hy natively
I made a tool to get new articles
I made a tool to estimate the execution time of cron (+ PyPI debut)
I made a package to create an executable file from Hy source code
I tried to create a table only with Django
I tried to automatically generate a password with Python3
Try to create a battle record table with matplotlib from the data of "Schedule-kun"
[Python] I made a system to introduce "recipes I really want" from the recipe site!
I tried to introduce the block diagram generation tool blockdiag
I made a tool that makes it a little easier to create and install a public key.
I tried to automatically generate OGP of a blog made with Hugo with tcardgen made by Go
I tried to automatically create a report with Markov chain
I made a tool to get the answer links of OpenAI Gym all at once
I want to automatically generate a modern metal band name
I made a function to check the model of DCGAN
A story that made it possible to automatically create anison playlists from your music files
Create a correlation diagram from the conversation history of twitter
[Titan Craft] I made a tool to summon a giant to Minecraft
I made you to execute a command from a web browser
I want to create a Dockerfile for the time being.
I made a program to solve (hint) Saizeriya's spot the difference
I want to automatically find high-quality parts from the videos I shot
I tried to create a simple credit score by logistic regression.
I want to create a system to prevent forgetting to tighten the key 1
Continuation: I tried to introduce the block diagram generation tool blockdiag
I tried to cut out a still image from the video
I made a scaffolding tool for the Python web framework Bottle
I made a command to display a colorful calendar in the terminal
I made a program that automatically calculates the zodiac with tkinter
I made a POST script to create an issue on Github and register it in the Project
I want to send a signal only from the sub thread to the main thread
A story I was addicted to when inserting from Python to a PostgreSQL table
[Django] I made a field to enter the date with 4 digit numbers
I made a kitchen timer to be displayed on the status bar!
I made a CLI tool to convert images in each directory to PDF
I made a kind of simple image processing tool in Go language.
[Outlook] I tried to automatically create a daily report email with Python
I made a library konoha that switches the tokenizer to a nice feeling
I made a tool to convert Jupyter py to ipynb with VS Code
I made a program to check the size of a file in Python
I made a function to see the movement of a two-dimensional array (Python)
I tried to create a linebot (implementation)
How to create a clone from Github
I tried to create a linebot (preparation)
I made a script to display emoji
How to create a repository from media
I made a browser automatic stamping tool.