dd-db 0.1.2
Unified Relational DB abstraction layer — clean adapters for 9+ databases
License: MIT
Requires Python: >=3.9
Keywords: abstraction, adapter, database, duckdb, mysql, postgres, sql, sqlite

# dd-db

**Unified Relational DB abstraction layer for Python.**

Connect to any relational database and get a pandas DataFrame back — with a consistent API for schema inspection, connection management, and query timing.

## Supported Databases

| Adapter | Class | Extra |
|---------|-------|-------|
| SQLite (stdlib) | `SQLiteDB` | *(none)* |
| DuckDB | `DuckDB` | `duckdb` |
| PostgreSQL | `PostgresDB` | `postgres` |
| MySQL / MariaDB | `MySQLDB` | `mysql` |
| Snowflake | `SnowflakeDB` | `snowflake` |
| Google BigQuery | `BigQueryDB` | `bigquery` |
| ClickHouse | `ClickHouseDB` | `clickhouse` |
| SQL Server | `MSSQLDB` | `mssql` |
| Oracle | `OracleDB` | `oracle` |

## Install

```bash
pip install dd-db                   # SQLite only (stdlib, zero extra deps)
pip install "dd-db[duckdb]"         # + DuckDB
pip install "dd-db[postgres]"       # + PostgreSQL
pip install "dd-db[all]"            # all adapters
pip install "dd-db[dev]"            # dev tools + DuckDB
```

## Quick Start

```python
from dd_db import SQLiteDB

with SQLiteDB(":memory:") as db:
    db.run_query("CREATE TABLE t (id INT, name TEXT)")
    db.run_query("INSERT INTO t VALUES (1, 'Alice')")
    db.run_query("INSERT INTO t VALUES (2, 'Bob')")

    # SELECT returns a pandas DataFrame
    df = db.run_query("SELECT * FROM t")
    print(df)
    #    id   name
    # 0   1  Alice
    # 1   2    Bob

    # Parameterised queries
    row = db.run_query("SELECT * FROM t WHERE id = :id", params={"id": 1})

    # Schema inspection
    print(db.list_tables())          # ['t']
    print(db.describe("t"))          # DataFrame with column/type/pk columns
    schema = db.get_schema("t")      # TableSchema Pydantic model
    print(schema.row_count)          # 2

    # Timed query
    df, meta = db.timed_query("SELECT * FROM t")
    print(meta.execution_time_ms)    # e.g. 0.42
```

## API Reference

### Core methods (all adapters)

| Method | Returns | Description |
|--------|---------|-------------|
| `run_query(sql, params?)` | `DataFrame` | Execute SQL; SELECT → rows, DML → `{rows_affected}` |
| `list_tables(schema?)` | `list[str]` | Table names |
| `tables(schema?)` | `DataFrame` | Table names as DataFrame |
| `get_schema(table, schema?)` | `TableSchema` | Typed column metadata |
| `describe(table, schema?)` | `DataFrame` | Human-readable column info |
| `test_connection()` | `bool` | Health check |
| `connection_info()` | `ConnectionInfo` | Loggable summary (no passwords) |
| `timed_query(sql, params?)` | `(DataFrame, QueryResult)` | Query + execution metadata |
| `connect()` | `None` | Open connection |
| `disconnect()` | `None` | Close connection |

### Context manager

```python
with SQLiteDB("mydb.sqlite") as db:
    ...
# connection closed automatically
```

### Pydantic models

```python
from dd_db import TableSchema, ColumnInfo, QueryResult, ConnectionInfo
```

- **`ColumnInfo`** — `name`, `data_type`, `nullable`, `default`, `primary_key`
- **`TableSchema`** — `table_name`, `schema_name`, `columns`, `row_count`, `full_name`
- **`QueryResult`** — `sql`, `rows_returned`, `columns`, `execution_time_ms`, `success`, `error`
- **`ConnectionInfo`** — `adapter`, `host`, `port`, `database`, `username`

## Examples

### DuckDB analytics

DuckDB uses `$name` for named parameters (not `:name`):

```python
from dd_db import DuckDB

with DuckDB() as db:
    df = db.run_query("""
        SELECT region, SUM(amount) AS total
        FROM sales
        GROUP BY region
        ORDER BY total DESC
    """)
    print(df)

    # Parameterised — DuckDB uses $name style
    row = db.run_query(
        "SELECT * FROM sales WHERE region = $region AND amount > $min",
        params={"region": "North", "min": 1000.0},
    )
```

### PostgreSQL

```python
from dd_db import PostgresDB

with PostgresDB(host="localhost", database="mydb", user="me", password="pw") as db:
    df = db.run_query("SELECT * FROM orders WHERE status = :status",
                      params={"status": "pending"})
```

### Snowflake

```python
from dd_db import SnowflakeDB

with SnowflakeDB(account="xy12345", user="me", password="pw",
                 database="ANALYTICS", schema="PUBLIC",
                 warehouse="COMPUTE_WH") as db:
    df = db.run_query("SELECT TOP 100 * FROM my_table")
```

## Parameter style by adapter

Each adapter translates `params` dict to its driver's native style:

| Adapter | SQL placeholder | Example |
|---------|----------------|---------|
| SQLiteDB | `:name` | `WHERE id = :id` |
| PostgresDB | `:name` | `WHERE id = :id` |
| DuckDB | `$name` | `WHERE id = $id` |
| MySQLDB | `%(name)s` | `WHERE id = %(id)s` |
| SnowflakeDB | `%(name)s` | `WHERE id = %(id)s` |
| BigQueryDB | `@name` | `WHERE id = @id` |
| MSSQLDB | `?` (positional) | `WHERE id = ?` |
| OracleDB | `:name` | `WHERE id = :id` |
| ClickHouseDB | `{name:type}` | `WHERE id = {id:Int32}` |

## Cookbooks

See `cookbook/` for runnable examples:

- `01_sqlite_basics.py` — full walkthrough with SQLite (no install needed)
- `02_duckdb_basics.py` — analytics with DuckDB, including DataFrame joins

## Running Tests

```bash
pip install -e ".[dev]"
python -m pytest
```

Tests use `:memory:` SQLite — no external server required.

## Design

See `docs/DESIGN.md` for:
- Why synchronous-first?
- Why DataFrame return type everywhere?
- Relationship to vanna.ai
- How to add a new adapter

## License

MIT