debug_db.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
Database Debugging Script
Checks the structure and content of the Labrys DuckDB database
"""

import duckdb
import os
from pathlib import Path

# Connect to the database
db_path = Path('/home/labrys/labrys_platform/LabrysPlatform/labrys.duckdb')
if not db_path.exists():
    print(f"Database file not found at {db_path}")
    exit(1)

print(f"Database file exists at {db_path}, size: {db_path.stat().st_size / (1024*1024):.2f} MB")

conn = duckdb.connect(str(db_path))

# Get all schemas
try:
    print("\n=== Schemas ===")
    schemas_df = conn.execute("PRAGMA show_tables").fetchdf()
    print(schemas_df)
except Exception as e:
    print(f"Error getting schemas: {e}")

# Try getting tables in specific schemas
try:
    print("\n=== Tables in 'raw' schema ===")
    tables_df = conn.execute("PRAGMA show_tables").fetchdf()
    print(tables_df[tables_df['schema'].str.contains('raw', case=False, na=False)])
except Exception as e:
    print(f"Error getting tables in 'raw' schema: {e}")

# Check for the measurements table specifically
try:
    print("\n=== Checking for measurements table ===")
    measurements_count = conn.execute("SELECT COUNT(*) FROM raw.measurements").fetchone()[0]
    print(f"Found {measurements_count} records in raw.measurements")
    
    # Get sample of measurements
    print("\n=== Sample of measurements ===")
    sample_df = conn.execute("SELECT * FROM raw.measurements LIMIT 5").fetchdf()
    print(sample_df)
except Exception as e:
    print(f"Error accessing measurements table: {e}")

# Try loading from raw data files if available
print("\n=== Checking for raw data files ===")
raw_data_dir = Path('/home/labrys/labrys_platform/data')
if raw_data_dir.exists():
    print(f"Raw data directory exists at {raw_data_dir}")
    data_files = list(raw_data_dir.glob('*.csv'))
    data_files.extend(raw_data_dir.glob('*.tsv'))
    data_files.extend(raw_data_dir.glob('*.json'))
    print(f"Found {len(data_files)} data files: {[f.name for f in data_files]}")
else:
    print(f"Raw data directory not found at {raw_data_dir}")

# Check if measurements might be in a different schema
try:
    print("\n=== Searching for measurement-related tables in all schemas ===")
    all_tables = conn.execute("PRAGMA show_tables").fetchdf()
    for _, row in all_tables.iterrows():
        if 'measure' in row['name'].lower():
            print(f"Found potential measurements table: {row['schema']}.{row['name']}")
            count = conn.execute(f"SELECT COUNT(*) FROM {row['schema']}.{row['name']}").fetchone()[0]
            print(f"Table contains {count} records")
except Exception as e:
    print(f"Error searching for measurement tables: {e}")

print("\nDebugging complete!")