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.
    """
    # 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()

    return children, parents


# Input
children, parents = get_parent_child('PAJSFN20', 'ERROR-PCB-AREA')

# Output
print("Children:", children)
print("Parents:", parents)