Metadata SQL database relationship visualizations (ERDs)

General Discussion(Q&A)
1

Hi,

Intro: Ashim here, working with Fraunhofer IWES and working on a metadata database currently

Question:
I am working on a metadata database in Python language and using SQLalchemy and SQLite libraries to generate the database structure. I was wondering if some one has experience in making the Entity Relationship Diagrams here from the SQLalchemy data classes / tables?

Also, I would be happy to hear more about the implementation workflows as well. At the moment, I have the following structure:

  • db_tables.py (Skeleton structure of the database)
  • db_enumerations.py (list of declared input variables to limit user input errors)
  • db_helper_functions.py (some helper methods in python to help in automating the functions)
  • script.py (This is the main script, where I insert, execute, query commands)
  • model.db (This is my SQLite database)

PS:
This question was initially posted on the IEA Wind Digitalization thread on Slack, but redirected here due to it’s suitability here.

1 Like
2

Hi @ashimgiyanani ,

Thanks for posting this here. My answer on Slack was:

I used diagrams.net or draw.io (same thing) by importing the create table sql statements. It then needed cleaning up but you can get the screenshot below.
There are other paid online Apps and database work benches. There must be something in Python but I’m haven’t come across it yet.

image
image1668×961 280 KB

But @christianj had a better answer which I’m sure he’ll post here.

1 Like
3

Hi @ashimgiyanani and @stephenholleran, thanks for sharing the topic in this forum. Below is my original response on the Slack channel.

I have worked with the erdantic (erdantic) package in python for drawing entity relationship diagrams (ERDs) from data models. This however requires that you have the data models implemented either as python data classes or pydantic (https://docs.pydantic.dev/) models. If you are working in python I suppose you might have something like that. The pydantic package has nice plugins for converting other models to that framework. For example, there is a tool to generate pydantic models from SQLAlchemy models, but I have never tested that myself. The erdantic package can generate Graphviz DOT format files, so if you do not like the visual rendering, you can implement your own one from the DOT files.

2 Likes
4

@ashimgiyanani I am happy to exchange ideas if you want and would be interested to hear how you get on with it.