py-rowboat 1.0.0.16
MCP server for querying CSV files with SQL
License: BSD-3-Clause
Requires Python: >=3.12
Keywords: csv, data, mcp, query, sql, sqlite

# rowboat

[![Release](https://github.com/dannyheskett/py-rowboat/actions/workflows/release.yml/badge.svg)](https://github.com/dannyheskett/py-rowboat/releases)
![Python 3.12+](https://img.shields.io/badge/python-3.12+-blue.svg)
![License](https://img.shields.io/badge/license-BSD--3--Clause-green.svg)

Query CSV files using SQL through Claude. Rowboat is an MCP server that converts CSV data to SQLite and executes read-only SQL queries with automatic schema inference.

## Quick Start (Recommended)

The easiest way to use rowboat is with the **hosted server** - no installation required.

### Claude.ai

Add as a connector in Claude.ai:

1. Go to **Settings > Integrations > Add Connector**
2. Enter:
   - **Name**: Rowboat
   - **URL**: `https://rowboat.mcp.danheskett.com/`
3. Start querying CSV files in your conversations

### Claude Desktop

Add to `~/.claude/claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "rowboat": {
      "type": "url",
      "url": "https://rowboat.mcp.danheskett.com/"
    }
  }
}
```

That's it! Claude can now query CSV files for you.

## How It Works

Rowboat provides three tools to Claude:

| Tool | Purpose |
|------|---------|
| `csvsql_prep_url` | Fetch CSV from a public URL and prepare for querying |
| `csvsql_prep_base64` | Upload CSV content (base64 encoded) and prepare for querying |
| `csvsql_query_remote` | Execute SQL queries against prepared data |

### Querying a Public CSV URL

Just paste a URL to a CSV file and ask Claude to analyze it:

> "Analyze this dataset and tell me the top 10 products by revenue: https://example.com/sales.csv"

Claude will:
1. Fetch the CSV using `csvsql_prep_url`
2. Examine the schema and sample data
3. Write and execute SQL using `csvsql_query_remote`
4. Present the results

**Example with real public data:**

> "What are the most common winning numbers in this lottery data? https://data.ny.gov/api/views/d6yy-54nr/rows.csv?accessType=DOWNLOAD"

### Querying a Local File

For local files, Claude will base64-encode the content and upload it:

> "Query this CSV file and show me a summary: /path/to/local/data.csv"

Claude will:
1. Read and base64-encode the file
2. Upload using `csvsql_prep_base64`
3. Query as needed

**Note:** Large files (>10MB) may take longer to upload. For very large files, consider hosting them at a public URL.

### Sample Prompts

Here are prompts that work well with rowboat:

**Data exploration:**
> "Look at this CSV and tell me what's in it: [URL or path]"

**Aggregation:**
> "What's the total sales by region in this data? [URL]"

**Filtering:**
> "Show me all records where status is 'pending' and amount > 1000"

**Joins (same file, self-join):**
> "Find duplicate entries based on email address"

**Time-based analysis:**
> "What's the trend of signups by month?"

**Statistical queries:**
> "What's the average, min, max, and standard deviation of the price column?"

## Data Persistence & Expiration

When you prepare a CSV file, rowboat:

1. Converts it to a SQLite database
2. Stores it in cloud storage with a unique ID (content hash)
3. Returns the `db_id` for subsequent queries

**Retention policy:**
- Databases expire after **30 days** of inactivity
- Each query **resets the 30-day timer**
- The same CSV content always produces the same `db_id` (content-addressed)

This means:
- You can query the same data across multiple conversations
- Frequently-used datasets stay available indefinitely
- Unused data is automatically cleaned up

## Error Handling

Rowboat validates all inputs and provides clear error messages:

**Invalid URL:**
```json
{"errors": ["Invalid URL (must start with http:// or https://): ftp://example.com/file.csv"]}
```

**File path passed to base64 tool:**
```json
{"errors": ["It looks like you passed a file path ('/path/to/file.csv'). This tool expects base64-encoded CSV content."]}
```

**SQL validation (write operations blocked):**
```json
{"errors": ["Only SELECT statements are allowed", "Forbidden SQL keyword: DROP"]}
```

**Database not found:**
```json
{"errors": ["Database not found: abc123.db"]}
```

Claude handles these errors gracefully and will explain the issue to you.

## SQL Security

Only SELECT statements are allowed. The following are blocked:
- INSERT, UPDATE, DELETE, REPLACE, TRUNCATE
- CREATE, DROP, ALTER
- ATTACH, DETACH
- PRAGMA, VACUUM, REINDEX

Databases are opened in read-only mode for additional protection.

## Type Inference

Rowboat automatically infers SQLite column types from your data:

| Inferred Type | When |
|---------------|------|
| INTEGER | All non-empty values are valid integers |
| REAL | All non-empty values are valid numbers (int or float) |
| TEXT | Any non-numeric values present |

Empty strings and whitespace are treated as NULL and mark the column as nullable.

---

## Local Installation (Alternative)

If you prefer to run rowboat locally (for privacy or offline use), you have several options.

### Using uvx (Recommended for Local)

No installation required - uvx downloads and runs automatically:

```bash
# Test it works
uvx py-rowboat --help
```

Add to Claude Desktop config:

```json
{
  "mcpServers": {
    "rowboat": {
      "command": "uvx",
      "args": ["py-rowboat"]
    }
  }
}
```

### Using pip

```bash
pip install py-rowboat
```

Add to Claude Desktop config:

```json
{
  "mcpServers": {
    "rowboat": {
      "command": "rowboat"
    }
  }
}
```

### Using uv (Project Dependency)

```bash
uv add py-rowboat
```

### Local Tools

The local server provides different tools that work with filesystem paths:

| Tool | Description |
|------|-------------|
| `csvsql_prep` | Convert local CSV file to SQLite |
| `csvsql_query` | Query the local SQLite database |

**Example prompt for local mode:**
> "Analyze this CSV: /Users/me/Documents/sales.csv"

### Testing Your Installation

```bash
# With MCP Inspector
npx @modelcontextprotocol/inspector uvx py-rowboat

# Or if installed via pip
npx @modelcontextprotocol/inspector rowboat
```

---

## Privacy & Data Retention

### Hosted Server (`rowboat.mcp.danheskett.com`)

- **Storage**: Prepared databases are stored in S3 for 30 days, extended on each query
- **Content-addressed**: Same CSV content = same database ID (no duplicates)
- **No logging**: Query content and PII are not logged
- **Open source**: Verify server behavior in [`src/rowboat/remote_server.py`](src/rowboat/remote_server.py)
- **Rate limited**: 60 requests per minute per IP

**Recommendation**: For sensitive data, use the local installation instead.

### Local Mode

- All data stays on your machine
- Databases stored in system temp directory
- No network calls except to read CSV URLs you provide

---

## Self-Hosting

Run your own rowboat server:

```bash
# Install with remote dependencies
pip install py-rowboat[remote]

# Or with uv
uv add py-rowboat --extra remote

# Start the server
rowboat-remote --host 0.0.0.0 --port 8000
```

**Required environment variables:**

| Variable | Description |
|----------|-------------|
| AWS_ENDPOINT_URL_S3 | S3-compatible endpoint (e.g., Tigris, MinIO) |
| AWS_ACCESS_KEY_ID | S3 access key |
| AWS_SECRET_ACCESS_KEY | S3 secret key |
| BUCKET_NAME | S3 bucket name (default: `rowboat-storage`) |

**Optional:**

| Variable | Default | Description |
|----------|---------|-------------|
| ROWBOAT_HOST | 0.0.0.0 | Host to bind |
| ROWBOAT_PORT | 8000 | Port to bind |
| ROWBOAT_RATE_LIMIT | 60 | Requests per minute per IP |

---

## Development

```bash
# Clone and setup
git clone https://github.com/dannyheskett/py-rowboat.git
cd py-rowboat
uv sync --extra dev --extra remote

# Run tests
uv run pytest

# With coverage
uv run pytest --cov=rowboat --cov-report=term-missing

# Lint and format
uv run ruff check --fix src/ tests/
uv run ruff format src/ tests/
```

---

## Troubleshooting

### "Database not found" error

The database may have expired (30 days of inactivity). Re-run the prep tool to recreate it.

### CSV not parsing correctly

- Ensure standard CSV format (comma-separated, optional quotes)
- Check if `has_header` should be true or false
- Files without headers get columns named `col_1`, `col_2`, etc.

### Query returns unexpected results

- Use the prep tool first to see the schema and sample data
- Check the `errors` field in responses
- Verify SQLite-compatible SQL syntax

### Rate limit exceeded

The hosted server allows 60 requests per minute. Wait a moment and retry, or use local installation for unlimited queries.

---

## License

BSD-3-Clause License

## Contributing

1. Fork the repository
2. Create a feature branch
3. Run tests: `uv run pytest`
4. Run linter: `uv run ruff check --fix`
5. Submit a Pull Request