scripts/build_interactions_db.py

#!/usr/bin/env python3
import sqlite3, csv, argparse
import sys, os
sys.path.append(os.getcwd())
from config import DB_PATH, INTERACTIONS_CSV

def build_db(db_path: str, csv_path: str):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute("DROP TABLE IF EXISTS interactions")
    c.execute("CREATE TABLE interactions(cui1 TEXT, cui2 TEXT, severity TEXT, advice TEXT, UNIQUE(cui1, cui2))")
    
    with open(csv_path, newline='', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for row in reader:
            # Ensure consistent order to avoid duplicate entries like (A,B) and (B,A)
            cui1, cui2 = sorted((row['cui1'], row['cui2']))
            c.execute(
                "INSERT OR IGNORE INTO interactions(cui1, cui2, severity, advice) VALUES(?,?,?,?)",
                (cui1, cui2, row['severity'], row['advice'])
            )
    conn.commit()
    conn.close()

if __name__ == "__main__":
    build_db(DB_PATH, INTERACTIONS_CSV)
    print(f"✅ Interactions DB built at {DB_PATH}")