database_functions.py

from mysql.connector import Error
from typing import Dict, Any, List

def insert_trial_data(trial: Dict[str, Any], connection, cursor) -> str:
    """Insert trial and all related objects into database"""
    try:
        # Insert trial base data
        trial_sql = """
        INSERT INTO trials (
            nct_id, trial_type, brief_title, official_title,
            conditions, conditions_mesh_terms, keywords, study_type, 
            intervention_mesh_terms, eligibility_summary,
            trial_source, annotation_status, ai_summary, annotation_time_seconds
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(trial_sql, (
            trial['nct_id'],
            trial['type'],
            trial['brief_title'],
            trial['official_title'], 
            ','.join(trial['conditions']),
            ','.join(trial['condition_mesh_terms']),
            ','.join(trial['keywords']),
            trial['study_type'],
            ','.join(trial['intervention_mesh_terms']),
            trial.get('eligibility_summary'),
            trial.get('trial_source'),
            trial.get('annotation_status'),
            trial.get('ai_summary'),
            trial.get('annotation_time_seconds')
        ))
        trial_id = cursor.lastrowid


        # Insert arm groups
        for arm in trial['arm_groups']:
            arm_sql = """
            INSERT INTO arm_groups (
                trial_id_arm, arm_group_label, arm_group_type, arm_group_description, intervention_names
            ) VALUES (%s, %s, %s, %s, %s)
            """
            cursor.execute(arm_sql, (
                trial_id,
                arm['label'],
                arm['type'],
                arm['description'],
                ','.join(arm['intervention_names'])
            ))

        # Insert interventions
        for intervention in trial['interventions']:
            intervention_sql = """
            INSERT INTO interventions (
                trial_id_int, intervention_type_int, intervention_name, intervention_description, arm_group_labels, other_names
            ) VALUES (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(intervention_sql, (
                trial_id,
                intervention['type'],
                intervention['name'],
                intervention['description'],
                ','.join(intervention['arm_group_labels']),
                ','.join(intervention['other_names'])
            ))

        # Insert diseases
        if 'diseases' in trial and trial['diseases']:
            for disease in trial['diseases']:
                disease_sql = """
                INSERT INTO diseases (
                    trial_id_dis, disease_type, disease_name, tissue_type,
                    disease_mesh_term, oncotree_name, oncotree_main_type
                ) VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
            cursor.execute(disease_sql, (
                trial_id,
                disease['type'],
                disease['disease_name'],
                disease['tissue_type'],
                disease.get('disease_mesh_term'),
                disease.get('Oncotree_name'),
                disease.get('Oncotree_mainType')
            ))

        # Insert drugs and their targets
        if 'drugs' in trial and trial['drugs']:
            for drug in trial['drugs']:
                drug_sql = """
                INSERT INTO drugs (
                    trial_id_dru, drug_name, intervention_type, drug_description,
                    drug_type, nci_thesaurus_concept_id, nci_thesaurus_preferred_term,
                    nci_thesaurus_definition, drug_mesh_term, drug_category,
                    drug_class, drug_delivery_route, drug_mechanism_of_action
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                cursor.execute(drug_sql, (
                    trial_id,
                    drug['drug_name'],
                    drug['intervention_type'],
                    drug['drug_description'],
                    drug['type'],
                    drug.get('NCI_thesaurus_concept_id'),
                    drug.get('NCI_thesaurus_preferred_term'),
                    drug.get('NCI_thesaurus_definition'),
                    drug.get('drug_mesh_term'),
                    drug.get('drug_category'),
                    drug.get('drug_class'),
                    drug.get('drug_delivery_route'),
                    drug.get('drug_mechanism_of_action')
                ))
                drug_id = cursor.lastrowid

                # Insert targets
                if 'targets' in drug and drug['targets']:
                    for target in drug['targets']:
                        target_sql = """
                        INSERT INTO targets (
                            drug_id_tar, gene, gene_symbol, hgnc_id, antigen_ligand,
                            binding_type, cytotoxicity, cytotoxicity_type, cytotoxicity_mechanism,
                            enzyme_product, epitope, variant, isoform,
                            hla_specific, gated, other_modifier
                        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        """
                        cursor.execute(target_sql, (
                            drug_id,
                            target['gene'],
                            target['gene_symbol'],
                            target['HGNC_id'],
                            target['antigen_ligand'],
                            target['binding_type'],
                            target.get('cytotoxicity'),
                            target.get('cytotoxicity_type'),
                            target.get('cytotoxicity_mechanism'),
                            target['enzyme_product'],
                            target['epitope'],
                            target['variant'],
                            target['isoform'],
                            target['HLA_specific'],
                            target['gated'],
                            target.get('other_modifier')
                        ))


        connection.commit()
        print(f"Successfully inserted trial {trial['nct_id']}")

    except Error as e:
        print(f"Error inserting trial data: {e}")
        nct_error = trial['nct_id']
        connection.rollback()
        return nct_error
    return ""