Tree aggregation in python

Question

I have 3 tables in Postgres.

table -> ledger

ledger is a tree structure.

table -> leaf

value of the tree leaf

table -> result

result of the tree aggregation

I want a python code to connect to postgresql database and aggregate the tree from leaf and ledger tables and insert the result into result table.

but in aggregation result name and code are not correct.

from typing import Optional
from sqlmodel import Field, Session, SQLModel, select  ,func
from sqlalchemy import create_engine

class Ledger(SQLModel, table=True):
    ledger_code: Optional[int] = Field(default=None, primary_key=True)
    name: str
    parent: int
    depth: Optional[int] = None
    ledger_code: Optional[int] = Field(
        default=None, foreign_key="Leaf.ledger_code", primary_key=True)
    ledger_code: Optional[int] = Field(
        default=None, foreign_key="Result.ledger_code", primary_key=True)


class Leaf(SQLModel, table=True):
    ledger_code: Optional[int] = Field(default=None, primary_key=True)
    value: int
    parent: int
    depth: Optional[int] = None
    ledger_code: Optional[int] = Field(
        default=None, foreign_key="Ledger.ledger_code", primary_key=True)


class Result(SQLModel, table=True):
    ledger_code: Optional[int] = Field(default=None, primary_key=True)
    depth: int
    parent: int
    value: int
    name: str
    creator: str

dbschema='public' # Searches left-to-right

engine = create_engine(
    'postgresql+psycopg2://test:test@192.168.21.79:5232/test',
    connect_args={'options': '-csearch_path={}'.format(dbschema)})

session = Session(engine)

def create_db_and_tables():
       SQLModel.metadata.create_all(engine)  

def SqlModelAggregation():
        statement = select(func.max(Ledger.depth))
        result = session.exec(statement).first()
        print("This is result >>", result)

        if result > 0:
            dele = Result.__table__.delete().where(Result.creator == "navid")
            engine.execute(dele)
         
            select_leafs = select(Ledger, Leaf).where(
                Ledger.ledger_code == Leaf.ledger_code)
            result_select_leafs = session.exec(select_leafs).all()
            for leaf in result_select_leafs:
                session.add(Result(depth=leaf[0].depth, ledger_code=leaf[0].ledger_code,
                            name=leaf[0].name, parent=leaf[0].parent, value=leaf[1].value, creator='navid'))
                session.commit()
              
            for i in range(result, 0, -1):
                print("This is   >>", i)
                statement_insert = (select( (func.max(Ledger.depth)-1).label("max_depth"), Ledger.parent, func.sum(
                    Result.value).label("sum_value") )
                    .join(Result, Ledger.ledger_code == Result.ledger_code)
                    .where(Ledger.depth == i, Result.creator == "navid")
                    .group_by(Ledger.parent)

                )
                print("this is query2 >>", statement_insert)

                result_insert = session.exec(statement_insert)
                print("I am here", result_insert)
                for row in result_insert:
                    print("This is result_insert >>", row.parent,
                          row.max_depth, row.sum_value)

                    session.add(Result(depth=row.max_depth, parent=row.parent,
                                value=row.sum_value, ledger_code=row.parent, creator='navid'))
                    session.commit()
                    session.close()

        return 1
    

def main():
    print("This is test 1 >>")
    create_db_and_tables()
    print("This is test 2 >>")
    SqlModelAggregation()  


if __name__ == "__main__":
    main()