Follow tree structure of sql database with python-CodePudding

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

CodePudding user response：

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

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

CodePudding user response：

Well, I would like you to paraphrase your question in order for me to understand; how could 0x123 has salary of 330? 0x456 has 180 with itself and the child it has, 0xabc, having 120, it makes 180, so it is correct but you either made a mistake while calculating 0x123 or I misunderstood your question

CodePudding user response：

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.