Metadata-Version: 2.4
Name: cached-duckdb
Version: 0.2.1
Summary: Fast in-memory DataFrame cache using DuckDB with SQL query interface
Author-email: sreeyenan <sreeyenanek@gmail.com>
Keywords: duckdb,cache,dataframe,sql,in-memory
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: duckdb>=0.10.0
Requires-Dist: pandas>=1.5.0
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: pytest-cov; extra == "dev"
Provides-Extra: protected
Requires-Dist: Cython>=3.0; extra == "protected"
Provides-Extra: all
Requires-Dist: Cython>=3.0; extra == "all"
Dynamic: license-file

# cached_duckdb

Fast in-memory DataFrame cache using DuckDB with SQL query interface.

## Overview

`cached_duckdb` replaces pandas dict-based caching with DuckDB in-memory connections for:
- **Columnar storage** - 20-30% less RAM than pandas
- **SQL queries** - Single-pass filter+aggregate operations
- **Concurrency** - Safe parallel reads during writes
- **Zero disk usage** - Pure in-memory like pandas

## Key Features

- **Generic database/table API** - Works with any cache-based system
- **Two storage modes:**
  - `single_db`: One connection per database (enables cross-table JOINs)
  - `per_table_db`: One connection per (database, table) pair (fully parallel writes)
- **Atomic swap** - Readers see 100% old or 100% new data, never partial
- **TTL-based expiry** - Background cleanup with lazy stale flagging
- **Scheduler-managed tables** - Bypass TTL for scheduled updates
- **Thread-safe operations** - Per-database or per-table locking

## Installation

### From PyPI (Recommended)

```bash
pip install cached-duckdb
```

### Install Specific Version

```bash
pip install cached-duckdb==0.2.1
```

### With Optional Protected Build Extras

```bash
pip install "cached-duckdb[all]"
```

### From Local Source

```bash
pip install -r requirements.txt
```

Or install in development mode:

```bash
pip install -e .
```

Verify installed version:

```python
import cached_duckdb
print(cached_duckdb.__version__)
```

## Quick Start

### Basic Usage

```python
from cached_duckdb import DuckDbCacheManager
import pandas as pd

# Initialize cache (singleton)
cache = DuckDbCacheManager()

# Store DataFrame
df = pd.DataFrame({
    'date': ['2026-01-01', '2026-01-02'],
    'amount': [1000, 2000],
    'country': ['USA', 'UK']
})
cache.store(database="client_abc", table="sales_data", df=df)

# Query with SQL filtering
result = cache.query(
    database="client_abc",
    table="sales_data",
    sql_where="amount > 1000 AND country = 'USA'",
    columns=["date", "amount"]
)
print(result)
```

### Advanced Queries

```python
# Get all data
df = cache.query(database="app_x", table="dataset_1")

# Filter with WHERE
df = cache.query(
    database="app_x",
    table="dataset_1",
    sql_where="age > 25 AND country = 'USA'"
)

# Select specific columns
df = cache.query(
    database="app_x",
    table="dataset_1",
    columns=["name", "age", "salary"]
)

# Limit results
df = cache.query(
    database="app_x",
    table="dataset_1",
    sql_where="date >= '2026-01-01'",
    limit=100
)
```

### Cross-Table JOINs (single_db mode)

```python
# Execute raw SQL for complex queries
sql = """
    SELECT s.date, s.amount, o.customer_name
    FROM sales_data s
    JOIN orders o ON s.order_id = o.id
    WHERE s.amount > 1000
"""
result = cache.execute_sql(database="client_abc", sql=sql)
```

### Check if Data Exists

```python
if cache.exists(database="client_abc", table="sales_data"):
    # Data is ready and fresh
    df = cache.query(database="client_abc", table="sales_data")
else:
    # Data missing or stale - reload needed
    df = load_from_source()
    cache.store(database="client_abc", table="sales_data", df=df)
```

### TTL and Scheduler-Managed Tables

```python
# Store with custom TTL
cache.store(
    database="client_abc",
    table="sales_data",
    df=df,
    ttl_minutes=60  # Expires after 60 minutes
)

# Scheduler-managed table (no auto-expiry on reads)
cache.store(
    database="client_abc",
    table="sales_data",
    df=df,
    scheduler_managed=True  # Only scheduler updates it
)
```

### Invalidate Cache

```python
# Invalidate one table
cache.invalidate(database="client_abc", table="sales_data")

# Invalidate all tables for a database
cache.invalidate(database="client_abc")
```

### Get Metadata

```python
# Last updated timestamp
last_updated = cache.get_last_updated(database="client_abc", table="sales_data")
print(f"Last updated: {last_updated}")

# Table info
info = cache.get_table_info(database="client_abc", table="sales_data")
print(f"Rows: {info['row_count']}")
print(f"Columns: {info['columns']}")
print(f"Types: {info['column_types']}")
```

## Documentation

- [User Manual](cached_duckdb_USER_MANUAL.md)
- [Environment Variables](ENVIRONMENT_VARIABLES.md)
- [Changelog](CHANGELOG.md)

## Author

**sreeyenan** (sreeyenanek@gmail.com)

## Version

Current version: **0.2.1**

## Configuration

### Environment Variables

```bash
# Storage mode: single_db or per_table_db
CACHED_DUCKDB_DEFAULT_MODE=single_db

# Default TTL in minutes
CACHED_DUCKDB_DEFAULT_TTL_MINUTES=30

# Cleanup thread interval
CACHED_DUCKDB_CLEANUP_INTERVAL_MINUTES=5

# Lock timeout in seconds
CACHED_DUCKDB_LOCK_TIMEOUT_SECONDS=30

# Path to connector config file (optional)
CACHED_DUCKDB_CONFIG_FILE_PATH=/path/to/connector_config.json

# Logger name
CACHED_DUCKDB_LOG_NAME=cached_duckdb
```

### Per-Database Configuration File

Create `connector_config.json` for per-database settings:

```json
{
  "client_abc": {
    "duck_cache_mode": "per_table_db",
    "default_cache_ttl_minutes": 45,
    "sales_data": {
      "cache_ttl_minutes": 60,
      "scheduler_managed": false
    },
    "live_feed": {
      "cache_ttl_minutes": 0,
      "scheduler_managed": true
    }
  },
  "client_xyz": {
    "duck_cache_mode": "single_db"
  }
}
```

**Priority order:**
1. Per-table config in JSON file (highest)
2. Per-database config in JSON file
3. Environment variables
4. Hardcoded defaults (lowest)

### Load Configuration

```python
from cached_duckdb import DuckDbCacheConfig, DuckDbCacheManager

# From environment
config = DuckDbCacheConfig.from_env()
cache = DuckDbCacheManager(config)

# From dict
config = DuckDbCacheConfig.from_dict({
    "default_mode": "single_db",
    "default_cache_ttl_minutes": 30,
    "config_file_path": "/path/to/connector_config.json"
})
cache = DuckDbCacheManager(config)
```

## Storage Modes

### Mode A: single_db (Default)

- One DuckDB connection per `database`
- Multiple tables share the same connection
- Enables cross-table SQL JOINs
- Write contention: One lock per database

**Use when:** Database has few tables (< 20) or need cross-table queries

### Mode B: per_table_db

- One DuckDB connection per `(database, table)` pair
- Each table is fully isolated
- Zero write contention between tables
- Fully parallel writes

**Use when:** Database has many tables (20+) or high write concurrency

## Architecture

```
DuckDbCacheManager (singleton)
  ├── CacheStore        - Atomic writes, table management
  ├── CacheQuery        - SQL queries, metadata
  ├── TTLRegistry       - TTL tracking, cleanup thread
  └── CacheConfigResolver - Per-database config resolution
```

## Thread Safety

- **store()**: Write-locked per database or per table
- **query()**: Lock-free parallel reads
- **invalidate()**: Write-locked, waits for active readers
- **Background cleanup**: Minimal locking, uses stale flags

## Use Cases

- **Multi-tenant web APIs** - Cache per tenant with `database=tenant_id`
- **Analytics dashboards** - Fast in-memory OLAP queries
- **ETL pipelines** - Store intermediate DataFrames
- **Session managers** - Replace pandas dict caching
- **Microservices** - Shared cache library across services

## API Reference

### DuckDbCacheManager

- `store(database, table, df, ttl_minutes=None, scheduler_managed=False)` - Store DataFrame
- `query(database, table, sql_where=None, columns=None, limit=None)` - Query with filtering
- `execute_sql(database, sql)` - Execute raw SQL
- `exists(database, table)` - Check if exists and fresh
- `invalidate(database, table=None)` - Remove from cache
- `get_last_updated(database, table)` - Get timestamp
- `get_table_info(database, table)` - Get metadata
- `get_raw_connection(database, table=None)` - Get DuckDB connection
- `shutdown()` - Close all connections

### Exceptions

- `DuckDbCacheError` - Base exception
- `DuckDbCacheConfigError` - Configuration error
- `DuckDbCacheLockError` - Lock acquisition failed
- `DuckDbCacheNotFoundError` - Table not found
- `DuckDbCacheStaleError` - Data is stale

## License

MIT License - see LICENSE file

## Author

sreeyenan
