[PYTHON] 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.

• MySQL only
• Primary key name is always id
• Table names are basically plural (excluding intermediate tables)
• Not compatible with some data types (such as ENUM)

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.

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.