script_database.py

import mysql.connector
from dotenv import dotenv_values

env = dotenv_values(".env")

def get_connection():
    return mysql.connector.connect(
        host=env["host"],
        user=env["user"],
        password=env["password"],
        database=env["database"]
    )

def test_connection():
    print("Testing database connection...")
    print(f"  Host     : {env['host']}")
    print(f"  User     : {env['user']}")
    print(f"  Database : {env['database']}")
    print()

    try:
        conn = get_connection()

        if conn.is_connected():
            info = conn.get_server_info()
            cursor = conn.cursor()
            cursor.execute("SELECT DATABASE();")
            db_name = cursor.fetchone()[0]

            print(f"[OK] Connected successfully!")
            print(f"     MySQL version : {info}")
            print(f"     Database      : {db_name}")
            print()

            # Show row counts for each table
            tables = ["dim_person", "dim_health_behavior", "fact_person_snapshot"]
            print("Table row counts:")
            for table in tables:
                try:
                    cursor.execute(f"SELECT COUNT(*) FROM {table}")
                    count = cursor.fetchone()[0]
                    print(f"  {table:<30} {count:>10} rows")
                except mysql.connector.Error as e:
                    print(f"  {table:<30} ERROR: {e}")

            # Show min/max person_id
            print()
            try:
                cursor.execute("SELECT MIN(person_id), MAX(person_id) FROM dim_person")
                min_id, max_id = cursor.fetchone()
                print(f"dim_person ID range: {min_id} → {max_id}")
            except mysql.connector.Error as e:
                print(f"Could not read dim_person: {e}")

            cursor.close()
            conn.close()
        else:
            print("[FAIL] Connection returned but is not active.")

    except mysql.connector.Error as e:
        print(f"[FAIL] Connection error: {e}")

def lookup_id(person_id: int):
    print(f"\nLooking up person_id = {person_id} ...")
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SELECT * FROM dim_person WHERE person_id = %s", (person_id,))
        row = cursor.fetchone()
        if row:
            print(f"[FOUND in dim_person] {row}")
        else:
            print(f"[NOT FOUND] person_id {person_id} does not exist in dim_person.")

        cursor.close()
        conn.close()
    except mysql.connector.Error as e:
        print(f"[FAIL] {e}")

if __name__ == "__main__":
    test_connection()
    lookup_id(1241308)