create_sample.py

import sqlite3
import pandas as pd
import os

source_db = r"c:\Files\VibeCoding\ReportalRMS\financial_data.db"
output_csv = r"c:\Files\VibeCoding\ReportalRMS\sample_100.csv"
output_db = r"c:\Files\VibeCoding\ReportalRMS\sample_100.db"

def create_sample():
    if not os.path.exists(source_db):
        print("Source database not found yet.")
        return

    conn = sqlite3.connect(source_db)
    
    # Check what table to sample from
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = [t[0] for t in cursor.fetchall()]
    
    target_table = "financial_records" if "financial_records" in tables else "raw_data"
    print(f"Sampling 100 random rows from '{target_table}'...")
    
    # Select 100 random rows
    query = f"SELECT * FROM {target_table} ORDER BY RANDOM() LIMIT 100"
    df = pd.read_sql_query(query, conn)
    conn.close()
    
    # Save to CSV
    df.to_csv(output_csv, index=False)
    print(f"Sample saved to {output_csv}")
    
    # Save to a tiny SQLite DB as requested ("as db")
    if os.path.exists(output_db):
        os.remove(output_db)
    
    sample_conn = sqlite3.connect(output_db)
    df.to_sql(target_table, sample_conn, index=False)
    sample_conn.close()
    print(f"Sample database created at {output_db}")
    
    # Display first few rows
    print("\n--- Sample Header & Data ---")
    print(df.head(10))

if __name__ == "__main__":
    create_sample()