Untitled

import os

import sqlite3

import pandas as pd



os.environ['SQLITE_DB_PATH'] = r'/c01/home/lidtd3o/APP_K/app_db_new.db'



def get_parent_child(program_id, var_name):

    """

    Optimized function to get children and parents of the target variable.

    Returns two lists: one for children and one for parents.

    """
    values = re.split(r' OF ', var_name) if ' OF ' in var_name else [var_name]
    values = [value.strip() for value in values]
    if len(values)>1:
        parent = values[1]
        child = None
    elif len(values)==1:
        # Connect to the database

        conn = sqlite3.connect(os.getenv('SQLITE_DB_PATH'))



        # Fetch all relevant records in a single query

        query = f"""

            SELECT VARIABLE_NAME, VARIABLE_PARENT, PROGRAM_NAME 

            FROM BRE_ATTRIBUTE_TBL

            WHERE PROGRAM_NAME = '{program_id}' 

              AND (VARIABLE_NAME = '{var_name}' OR VARIABLE_PARENT = '{var_name}')

        """

        df = pd.read_sql_query(query, conn)

        conn.close()



        # Separate parents and children

        children = df[df['VARIABLE_PARENT'] == var_name][['VARIABLE_PARENT', 'VARIABLE_NAME', 'PROGRAM_NAME']].values.tolist()

        parents = df[df['VARIABLE_NAME'] == var_name][['VARIABLE_PARENT', 'VARIABLE_NAME', 'PROGRAM_NAME']].values.tolist()
        children = [child for parent, child, program in children if parent == var_name]
        # print(children)
        parents = [parent for parent, child, program in parents if child == var_name]
        # print(parents)
        child = None
        parent = None
        if children!=[]:
            child = children[-1]
        if parents!=[]:
            parent = parents[-1]

    return child, parent