Untitled

import os
os.environ['SQLITE_DB_PATH']  = r'/c01/home/lidtd3o/APP_K/app_db_new.db'
def get_parent_child(program_id, var_name):
    """
    Function to get parent and child of the target variable
    """
    #Connect db & set base dict
    conn             = sqlite3.connect(os.getenv('SQLITE_DB_PATH'))
    parent_child_var = {(var_name, var_name, program_id, 'paraname')}
    master_set       = set()
    #While loop to find parent/child
    while parent_child_var:
        current_var  = parent_child_var.pop()
        data         = pd.read_sql_query(
                            sql=f"""
                                 SELECT VARIABLE_PARENT, PROGRAM_NAME FROM BRE_ATTRIBUTE_TBL
                                 WHERE VARIABLE_NAME='{current_var[0]}' AND VARIABLE_PARENT<>'N/A' AND PROGRAM_NAME='{program_id}'
                                 """,
                            con=conn
                        ).drop_duplicates()
        work_set     = set(
                        [
                            (d['VARIABLE_PARENT'], current_var[0], d['PROGRAM_NAME']) for i,d in data.iterrows() if (d['VARIABLE_PARENT'], current_var[0], d['PROGRAM_NAME']) not in master_set
                        ]
                    )

        master_set       = master_set.union(work_set)
        parent_child_var = work_set.union(parent_child_var)

        data = pd.read_sql_query(
                    sql=f"""
                        SELECT VARIABLE_NAME, PROGRAM_NAME FROM BRE_ATTRIBUTE_TBL
                            WHERE VARIABLE_PARENT='{current_var[0]}' AND VARIABLE_NAME<>'N/A' AND PROGRAM_NAME='{program_id}'
                        """,
                    con=conn
                ).drop_duplicates()
        work_set = set(
            [
                (current_var[0], d['VARIABLE_NAME'], d['PROGRAM_NAME']) for i,d in data.iterrows() if (current_var[0], d['VARIABLE_NAME'], d['PROGRAM_NAME']) not in master_set
            ]
        )

        master_set = master_set.union(work_set)
        parent_child_var = work_set.union(parent_child_var)

    conn.close()
    return master_set




# input
res = get_parent_child('PAJSFN20','ERROR-PCB-AREA')

# output
{('ERROR-PCB-AREA', 'ERROR-PCB', 'PAJSFN20'),
 ('ERROR-PCB-AREA', 'FILLER', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-CCT-ACCESS', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-CCT-FUNC', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-CCT-MODE', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-CCT-RTN', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-CNTL-DATA-FIELD', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-CNTL-LGTH', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-CNTL-NAME', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-DBVAL-FIELD', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-DBVAL-LGTH', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-DLPS-CODE', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-DLPS-PROG', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-FLD-NAME', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-ID', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-MESSAGE', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-PARM-6', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-PARM-7', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-PARM-8', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-PARM-9', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-PCB-AREA', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-PCB-ERR-DATA', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-PROGRAM-ID', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-SEARCH-ARG-LENGTH', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-SEARCH-ARGUMENTS', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-SQL-AREA', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-TABLE-OVERNAME', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-TABLE-OVEROCC', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-TYPE', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-VIR-STOR-NAME', 'PAJSFN20'),
 ('WS-SCOPE-YCERR0E1', 'ERROR-VIR-STOR-SIZE', 'PAJSFN20')}