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 fetch the child and parent of a variable.
    """

    # Parse the variable name if it contains "OF"
    if ' OF ' in var_name:
        parts = [part.strip() for part in var_name.split(' OF ')]
        return parts[0], parts[1]

    # Connect to the database (single connection)
    conn = sqlite3.connect(os.getenv('SQLITE_DB_PATH'))
    
    # Use parameterized query for safety and efficiency
    query = """
        SELECT VARIABLE_NAME, VARIABLE_PARENT 
        FROM BRE_ATTRIBUTE_TBL
        WHERE PROGRAM_NAME = ? 
          AND (VARIABLE_NAME = ? OR VARIABLE_PARENT = ?)
    """
    df = pd.read_sql_query(query, conn, params=(program_id, var_name, var_name))
    conn.close()  # Close the connection immediately after fetching data

    # Filter and extract parent and child directly
    child = df.loc[df['VARIABLE_PARENT'] == var_name, 'VARIABLE_NAME'].last(valid_only=True)
    parent = df.loc[df['VARIABLE_NAME'] == var_name, 'VARIABLE_PARENT'].last(valid_only=True)

    return child, parent