Follow tree structure of sql database with python

Question

I have a database with structure like this:

Mother	Child1	Child2	Salary
0x123	0x456	0x789	80
0x456	0xabc	null	60
0x789	null	null	50
0xabc	0xdef	0xghi	120

And this database can extend upto thousands of records Is there a way to give some name like "0x123" to a Python function and get sum of all his/her children's salary?

Like 0x123 => 230

0x456 => 180

0x789 => 50

The answers [here](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) may be helpful. — snakecharmerb, Jul 18 '22 at 09:03

score 0 · Answer 1 · answered Jul 18 '22 at 08:28

I have no idea which database you are using and on which libraries you have knowledge about. However, I will share my rough solution (this function will only give you some hints about the solution, structure depends on libraries you use).

I believe the function you need has to be a recursive function.

def your_func(node):
    child_1 = node.child_1
    child_2 = node.child_2
    return your_func(child_1).salary + your_func(child_2).salary

You will need a stop condition for this function. That condition will stop the function when it reaches a leaf node (node with no child). Like I said, you have to modify it for your own purposes and with respect to the libraries you use, it is like a pseudocode more than a Python script.

Thomas Kimber · Accepted Answer · 2022-07-18T10:04:51.970

You could extract the contents of your database as a flat table like this, then cycle over the records to build a graph in a module like NetworkX

In the below, I setup a pandas dataframe to host the data, but for you, you'd need to deal with whatever database api you've chosen:

import pandas as pd
import networkx as nx


df = pd.DataFrame([{"Mother" : '0x123', 
                   "Child1" : '0x456', 
                   "Child2" : '0x789', 
                   "Salary" : 80},
                  
                  {"Mother" : '0x456', 
                   "Child1" : '0xabc', 
                   "Child2" : None, 
                   "Salary" : 60},
                   
                   {"Mother" : '0x789', 
                   "Child1" : None, 
                   "Child2" : None, 
                   "Salary" : 50},
                   
                   {"Mother" : '0xabc', 
                   "Child1" : '0xdef', 
                   "Child2" : '0xghi', 
                   "Salary" : 120},
                  ], 
                 
                 )

graph = nx.DiGraph()

for e, r in df.iterrows():
    graph.add_node(r.Mother, data={"salary" : r.Salary})
    if r.Child1 is not None:
        graph.add_edge(r.Mother, r.Child1)
    if r.Child2 is not None:
        graph.add_edge(r.Mother, r.Child2)

This will instantiate your graph, for the above data, the graph looks like this:

Once instantiated, you can then extract details such as all the descendants of a given node.

e.g.

summary=[]
start_node = '0x456'
node_set = nx.descendants(graph, start_node)
node_set.add(start_node)
for node,data in graph.nodes(data=True):
    print(data)
    if node in node_set:
        summary.append(data.get('data',{}).get('salary',0))

Which you can finally use to calculate your tree-based figure:

summary

Which returns:

>>> [60, 120, 0, 0]

So summing this:

sum(summary)

Finally returns:

>>> 180

Follow tree structure of sql database with python

2 Answers2