How to create an organizational chart from SQL database with flask?

Question

I have a SQL database like this:

Basically this is a hierarchical database where you have managers (parent) and employees (children).

Currently I am working with flask which allows me to add/remove managers and employees and my idea is to have an html file which automatically reads from my SQL database and creates a tree like strucure (or an organizational chart) like this:

I have figured out a way to create nested unordered lists which can be then turned into the chart by using the following library: https://github.com/caprica/jquery-orgchart

However this works basically only when the database is static and does not change over time. I saw some solutions where they use json files but I have no clue of the middle step that leads from the SQL database to the json file.

Therefore, would you be able to propose an approach to solve this problem starting from the database? Which solution do you recommend? Are there any tutorials / examples that I could follow?

Thanks in advance.

Answer 1

For SQL Server 2016 or later, you can extract data as JSON using T-SQL. An example using your pseudo-table:

SELECT employee, employee_id, manager_id FROM employee_table FOR JSON PATH

The output of that query will be a single column with the table data formatted as JSON. You can read more about it here.