Metadata-Version: 2.4
Name: tangled-pyrqlite
Version: 0.1.5
Summary: DB-API 2.0 client and SQLAlchemy dialect for rqlite distributed SQLite clusters
Author-email: Tangled <noreply@tangledgroup.com>, Marko Tasic <mtasic85@gmail.com>
License-Expression: MIT
Project-URL: Repository, https://github.com/tangledgroup/tangled-pyrqlite
Project-URL: Issues, https://github.com/tangledgroup/tangled-pyrqlite/issues
Project-URL: Changelog, https://github.com/tangledgroup/tangled-pyrqlite/blob/main/CHANGELOG.md
Keywords: rqlite,tangled-rqlite,sqlite
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Requires-Python: <4.0,>=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: requests>=2.33.1
Requires-Dist: aiohttp>=3.9.0
Requires-Dist: sqlalchemy>=2.0.49
Provides-Extra: redis
Requires-Dist: redis>=7.4.0; extra == "redis"
Provides-Extra: valkey
Requires-Dist: valkey>=6.1.1; extra == "valkey"
Dynamic: license-file

# tangled-pyrqlite - python client

[![PyPI](https://img.shields.io/pypi/v/tangled-pyrqlite)](https://pypi.org/project/tangled-pyrqlite/)
[![PyPI Downloads](https://img.shields.io/pypi/dm/tangled-pyrqlite)](https://pypistats.org/packages/tangled-pyrqlite)
[![Supported Versions](https://img.shields.io/pypi/pyversions/tangled-pyrqlite)](https://pypi.org/project/tangled-pyrqlite)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](https://opensource.org/licenses/MIT)

A pure Python client for [rqlite](https://rqlite.io/) distributed SQLite clusters, providing:

- **DB-API 2.0** - Standard Python database API (PEP 249)
- **SQLAlchemy dialect** - Full ORM support via SQLAlchemy 2.0
- **Parameterized queries** - Safe, SQL injection-proof query execution
- **Serializable transaction support** - Atomic batch operations using locking mechanism — bring your own distributed locking implementation (Redis, Valkey)

## Installation

```bash
# Using uv (recommended)
uv add tangled-pyrqlite

# Using pip
pip install tangled-pyrqlite
```

## Quick Start

### Starting rqlite Server

Before using the client, start an rqlite server:

**Podman (recommended - no root required):**
```bash
podman rm -f rqlite-test
podman run -d --name rqlite-test -p 4001:4001 docker.io/rqlite/rqlite
```

**Docker:**
```bash
docker rm -f rqlite-test
docker run -d --name rqlite-test -p 4001:4001 rqlite/rqlite
```

### DB-API 2.0 Usage

```python
import rqlite
from rqlite import ReadConsistency, ThreadLock

# Basic connection (uses LINEARIZABLE consistency by default)
conn = rqlite.connect(host="localhost", port=4001)
cursor = conn.cursor()

# With custom read consistency and lock for transaction support
conn = rqlite.connect(
    host="localhost",
    port=4001,
    read_consistency=ReadConsistency.WEAK,  # or "weak" string
    lock=ThreadLock()  # Suppresses transaction warnings
)
cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
""")
conn.commit()

# Insert with positional parameters (recommended)
cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Alice", "alice@example.com")
)

# Insert with named parameters (also supported)
cursor.execute(
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    {"name": "Bob", "email": "bob@example.com"}
)
conn.commit()

# Query with positional parameters
cursor.execute("SELECT * FROM users WHERE name=?", ("Alice",))
row = cursor.fetchone()
print(row)  # (1, "Alice", "alice@example.com")

# Fetch all
cursor.execute("SELECT * FROM users")
for row in cursor:
    print(row)

# Close
cursor.close()
conn.close()

# Or use context managers
with rqlite.connect() as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        for row in cursor:
            print(row)
```



### Parameter Binding

The client supports both parameter styles per DB-API 2.0 standard:

**Positional parameters (`?`) - Recommended:**
```python
cursor.execute("SELECT * FROM users WHERE id=? AND name=?", (42, "Alice"))
```

**Named parameters (`:name`) - Also supported:**
```python
cursor.execute(
    "SELECT * FROM users WHERE id=:id AND name=:name",
    {"id": 42, "name": "Alice"}
)
```

**Note for SQLAlchemy users:** SQLAlchemy automatically uses positional parameters (`?`) for all queries. The ORM and Core layers handle parameter binding before reaching the dialect, so you don't need to worry about parameter format when using SQLAlchemy.

### SQLAlchemy Usage

```python
from sqlalchemy import Integer, String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
from rqlite import ReadConsistency, ThreadLock


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(nullable=False)
    email: Mapped[str | None] = mapped_column(unique=True)


# Basic engine (uses LINEARIZABLE consistency by default)
engine = create_engine("rqlite://localhost:4001")

# With custom read consistency and lock via connect_args
engine = create_engine(
    "rqlite://localhost:4001",
    connect_args={
        "read_consistency": ReadConsistency.WEAK,  # or "weak" string
        "lock": ThreadLock()  # Suppresses transaction warnings
    }
)

# Or use URL query parameter for read_consistency only
engine = create_engine("rqlite://localhost:4001?read_consistency=weak")

# Create tables (echo=True shows SQL)
engine = create_engine("rqlite://localhost:4001", echo=True)
Base.metadata.create_all(engine)

# Use with Session
with Session(engine) as session:
    user = User(name="Charlie", email="charlie@example.com")
    session.add(user)
    session.commit()
    
    # Query
    user = session.query(User).filter_by(name="Charlie").first()
    print(user.name)  # Charlie
```

## Features

### Read Consistency Levels

rqlite supports multiple read consistency levels to balance between data freshness and performance. The client defaults to **LINEARIZABLE** for guaranteed fresh reads.

| Level | Speed | Freshness | Best For |
|-------|-------|-----------|----------|
| **LINEARIZABLE** (default) | Moderate | Guaranteed fresh | Critical reads requiring latest data |
| **WEAK** | Fast | Usually current (sub-second staleness possible) | General-purpose reads |
| **NONE** | Fastest | No guarantee | Read-only nodes, max performance |
| **STRONG** | Slow | Guaranteed fresh + applied | Testing only |
| **AUTO** | Varies | Varies | Mixed node type clusters |

**Usage:**
```python
import rqlite
from rqlite import ReadConsistency

# Use LINEARIZABLE (default) for guaranteed fresh reads
conn = rqlite.connect()

# Use WEAK for faster reads with possible sub-second staleness
# Supports both enum and string:
conn = rqlite.connect(read_consistency=ReadConsistency.WEAK)
conn = rqlite.connect(read_consistency="weak")

# Use NONE for read-only nodes or maximum performance
conn = rqlite.connect(read_consistency="none")
```

**SQLAlchemy:**
```python
from sqlalchemy import create_engine

# Via URL query parameter
engine = create_engine("rqlite://localhost:4001?read_consistency=weak")

# Via connect_args
from rqlite import ReadConsistency

engine = create_engine(
    "rqlite://localhost:4001",
    connect_args={"read_consistency": ReadConsistency.WEAK}
)
```

See [rqlite documentation](https://rqlite.io/docs/db_api/#read-consistency-levels) for detailed explanations of each consistency level.

### DB-API 2.0 Compliance

| Feature | Status | Notes |
|---------|--------|-------|
| `connect()` | ✅ | Returns Connection |
| `Connection.cursor()` | ✅ | Returns Cursor |
| `Connection.commit()` | ✅ | Queues then sends statements |
| `Connection.rollback()` | ✅ | Discards queued statements |
| `Connection.close()` | ✅ | Clears queue, closes resources |
| `Cursor.execute()` | ✅ | Supports positional and named params |
| `Cursor.executemany()` | ⚠️ | Executes sequentially |
| `Cursor.fetchall()` | ✅ | Returns all rows as tuples |
| `Cursor.fetchmany()` | ✅ | Respects `arraysize` |
| `Cursor.fetchone()` | ✅ | Returns single row or None |
| `Cursor.description` | ✅ | Column metadata after SELECT |
| `Cursor.rowcount` | ⚠️ | Only for write operations |
| `Cursor.lastrowid` | ✅ | Available after INSERT |

### SQLAlchemy Support

| Feature | Status | Notes |
|---------|--------|-------|
| Core SELECT/INSERT/UPDATE/DELETE | ✅ | Full support |
| ORM Models | ✅ | Full support |
| Relationships | ✅ | Via SQLite dialect |
| Sessions | ✅ | Standard SQLAlchemy sessions |
| Transactions | ⚠️ | Limited (see below) |
| Reflection | ⚠️ | Basic table/column introspection |

## Transaction Model

rqlite's transaction model differs from traditional databases:

### How It Works

1. **Queue-based**: Statements are queued locally until `commit()` is called
2. **Atomic batch**: All queued statements sent in single HTTP request with `?transaction=true`
3. **All-or-nothing**: Either all statements succeed or none do

### Important Limitations

- ❌ No savepoints
- ⚠️ Explicit `BEGIN`/`COMMIT`/`ROLLBACK` SQL is ignored (use Python API)
- ❌ No native transaction isolation levels
- ⚠️ `rowcount` not available for SELECT statements

## Connection URLs

### DB-API 2.0

```python
# Basic connection (uses LINEARIZABLE consistency by default)
conn = rqlite.connect(host="localhost", port=4001)

# With authentication
conn = rqlite.connect(
    host="localhost",
    port=4001,
    username="admin",
    password="secret"
)

# Custom timeout
conn = rqlite.connect(host="localhost", port=4001, timeout=60.0)

# Custom read consistency (enum or string)
conn = rqlite.connect(host="localhost", port=4001, read_consistency="weak")

# Or using the enum:
from rqlite import ReadConsistency

conn = rqlite.connect(
    host="localhost",
    port=4001,
    read_consistency=ReadConsistency.WEAK
)

# With lock for transaction support (suppresses warnings)
from rqlite import ThreadLock

conn = rqlite.connect(
    host="localhost",
    port=4001,
    lock=ThreadLock()
)

# Combining read_consistency and lock
conn = rqlite.connect(
    host="localhost",
    port=4001,
    read_consistency=ReadConsistency.WEAK,
    lock=ThreadLock()
)
```

### Redis Distributed Lock (optional)

For **true cross-process transaction serialization**, use the built-in Redis-backed locks.
Install with the `redis` extra:

```bash
uv add tangled-pyrqlite[redis]
```

Start a Redis server:

```bash
podman rm -f redis-test
podman run -d --name redis-test -p 6379:6379 docker.io/redis
```

**Sync DB-API 2.0 with RedisLock:**

```python
import rqlite
from rqlite import RedisLock

# Connect with Redis distributed lock
lock = RedisLock(name="transfer", timeout=10.0)
conn = rqlite.connect(host="localhost", port=4001, lock=lock)
cursor = conn.cursor()

# Use lock for serialized bank transfer
with lock:
    cursor.execute("SELECT balance FROM accounts WHERE id=?", (1,))
    balance = cursor.fetchone()[0]
    cursor.execute("UPDATE accounts SET balance=? WHERE id=?", (balance - 100, 1))
    conn.commit()
```

**Async DB-API 2.0 with AioRedisLock:**

```python
import asyncio
import rqlite
from rqlite import AioRedisLock

async def transfer():
    lock = AioRedisLock(name="transfer", timeout=10.0)
    conn = rqlite.async_connect(lock=lock)
    cursor = await conn.cursor()

    async with lock:
        await cursor.execute("SELECT balance FROM accounts WHERE id=?", (1,))
        balance = cursor.fetchone()[0]
        await cursor.execute(
            "UPDATE accounts SET balance=? WHERE id=?",
            (balance - 100, 1),
        )
        await conn.commit()

    await cursor.close()
    await conn.close()

asyncio.run(transfer())
```

**SQLAlchemy with RedisLock:**

```python
from sqlalchemy import create_engine
from rqlite import RedisLock

lock = RedisLock(name="sa_lock", timeout=10.0)
engine = create_engine(
    "rqlite://localhost:4001",
    connect_args={"lock": lock}
)
```

**Available locks:**

| Lock | Sync/Async | Scope | Use case |
|------|-----------|-------|----------|
| `ThreadLock` | sync | In-process threads | Single process, thread-safe transactions |
| `threading.Lock` | sync | In-process threads | Direct stdlib lock |
| **`RedisLock`** | **sync** | **Cross-process (distributed)** | Multi-process, ACID isolation |
| `AioLock` | async | In-process tasks | Single process, async-safe transactions |
| **`AioRedisLock`** | **async** | **Cross-process (distributed)** | Multi-process async, ACID isolation |

For full examples see: `examples/sync_redis_lock_basic_usage.py`, `examples/async_redis_lock_basic_usage.py`,
`examples/sync_redis_lock_distributed_transfer.py`.
```

### SQLAlchemy

**Note:** For SQLAlchemy, custom parameters like `read_consistency` and `lock` must be passed via `connect_args` dictionary, not directly to `create_engine()`. This is because SQLAlchemy validates kwargs before passing them to the dialect.

```python
# Basic (uses LINEARIZABLE consistency by default)
engine = create_engine("rqlite://localhost:4001")

# With authentication
engine = create_engine("rqlite://admin:secret@localhost:4001")

# Enable SQL echo for debugging
engine = create_engine("rqlite://localhost:4001", echo=True)

# Custom read consistency via URL query parameter
engine = create_engine("rqlite://localhost:4001?read_consistency=weak")

# Custom read consistency via connect_args
from rqlite import ReadConsistency

engine = create_engine(
    "rqlite://localhost:4001",
    connect_args={"read_consistency": ReadConsistency.WEAK}
)

# With lock for transaction support (via connect_args)
from rqlite import ThreadLock

engine = create_engine(
    "rqlite://localhost:4001",
    connect_args={"lock": ThreadLock()}
)

# Both read_consistency and lock together
engine = create_engine(
    "rqlite://localhost:4001",
    connect_args={
        "read_consistency": ReadConsistency.WEAK,
        "lock": ThreadLock()
    }
)
```

## Error Handling

```python
import rqlite

try:
    conn = rqlite.connect()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM nonexistent_table")
except rqlite.ProgrammingError as e:
    print(f"SQL error: {e}")
except rqlite.OperationalError as e:
    print(f"Connection error: {e}")
except rqlite.DatabaseError as e:
    print(f"Database error: {e}")
```

### Exception Hierarchy

- `Error` - Base exception
  - `InterfaceError` - Interface-related errors
  - `DatabaseError` - Database errors
    - `DataError` - Data-related errors
    - `OperationalError` - Connection/operation errors
    - `IntegrityError` - Constraint violations
    - `InternalError` - Internal database errors
    - `ProgrammingError` - SQL syntax errors
    - `NotSupportedError` - Unsupported operations

## Examples

See the `examples/` directory for complete working examples.

### Running Sync Examples

**Without lock (shows transaction warnings):**
```bash
# Sync ThreadLock DB-API 2.0 examples with warnings
uv run python -B examples/sync_thread_lock_basic_usage.py

# Sync ThreadLock SQLAlchemy ORM examples with warnings
uv run python -B examples/sync_thread_lock_sqlalchemy_orm.py
```

**With lock (no transaction warnings):**
```bash
# Sync ThreadLock DB-API 2.0 examples without warnings
uv run python -B examples/sync_thread_lock_basic_usage.py --with-lock

# Sync ThreadLock SQLAlchemy ORM examples without warnings
uv run python -B examples/sync_thread_lock_sqlalchemy_orm.py --with-lock
```

The `-B` flag disables byte-code generation for cleaner output.

### Example Files

| File | Description |
|------|-------------|
| `sync_thread_lock_basic_usage.py` | Sync DB-API 2.0 CRUD with ThreadLock |
| `async_aio_lock_basic_usage.py` | Async DB-API 2.0 examples with AioLock |
| `sync_thread_lock_sqlalchemy_orm.py` | Sync SQLAlchemy ORM usage |
| `async_aio_lock_sqlalchemy_orm.py` | Async SQLAlchemy ORM usage |
| `sync_redis_lock_basic_usage.py` | Sync Redis distributed lock examples |
| `async_redis_lock_basic_usage.py` | Async Redis distributed lock examples |
| `sync_valkey_lock_basic_usage.py` | Sync Valkey distributed lock examples |
| `async_valkey_lock_basic_usage.py` | Async Valkey distributed lock examples |
| `sync_redis_lock_distributed_transfer.py` | Cross-process bank transfer demo (proves Redis lock works) |

### Locking Mechanism

The examples demonstrate the optional locking mechanism for transaction support:

- **Without lock**: Shows warnings about explicit BEGIN/COMMIT/ROLLBACK SQL not being supported
- **With lock (`--with-lock`)**: Uses `ThreadLock` to suppress warnings, allowing explicit transaction SQL

For more details, see the [Locking Mechanism](#locking-mechanism) section below.

## Locking Mechanism

rqlite provides an optional locking mechanism to support transactions and suppress warnings about explicit transaction SQL commands (BEGIN/COMMIT/ROLLBACK/SAVEPOINT).

### Why Use Locks?

By default, when using the rqlite library **without a lock**, you will receive a `UserWarning`:

```
UserWarning: Explicit BEGIN/COMMIT/ROLLBACK/SAVEPOINT SQL is not supported.
```

This warning is **expected behavior** and indicates that:
- You are aware of rqlite's transaction model (queue-based, atomic batch)
- You understand that explicit transaction SQL commands are not supported in the traditional sense
- You are using the Python API (`commit()`, `rollback()`) for transaction control

This is **fine** if you understand how rqlite transactions work. However, if you need **true ACID compliance** with proper isolation guarantees, it is **recommended to use a lock** (e.g., `ThreadLock()`). The lock:
- Suppresses the warning
- Indicates intentional handling of transaction limitations
- Provides thread-safety for concurrent operations

When you provide a lock, these warnings are suppressed, indicating that you're aware of the limitations and handling transactions appropriately.

### Available Lock Classes

1. **`ThreadLock`** (recommended) - Thread-safe wrapper around `threading.Lock`
2. **`threading.Lock`** - Use directly (satisfies `LockProtocol`)
3. **Custom locks** - Any class implementing `LockProtocol`

### Usage Examples

**DB-API 2.0 with ThreadLock:**
```python
import rqlite
from rqlite import ThreadLock

# Connect with lock to suppress warnings
conn = rqlite.connect(lock=ThreadLock())
cursor = conn.cursor()

# No warning about explicit transaction SQL
cursor.execute("BEGIN")
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
cursor.execute("COMMIT")
```

**Using threading.Lock directly:**
```python
import rqlite
import threading

conn = rqlite.connect(lock=threading.Lock())
# Same behavior as ThreadLock
```

**SQLAlchemy with lock:**
```python
from sqlalchemy import create_engine
from rqlite import ThreadLock

engine = create_engine(
    "rqlite://localhost:4001",
    connect_args={"lock": ThreadLock()}
)
# No warnings when using explicit transaction SQL
```

**Custom lock implementation:**
```python
import rqlite
from rqlite import LockProtocol

class MyLock:
    """Custom lock satisfying LockProtocol."""
    def __init__(self): pass
    def acquire(self, blocking=True, timeout=-1): return True
    def release(self): pass
    def __enter__(self): return self
    def __exit__(
        self,
        exc_type: type[Exception] | None,
        exc_val: Exception | None,
        exc_tb: object,
    ) -> None: pass

conn = rqlite.connect(lock=MyLock())
```

### LockProtocol

Any lock implementation must satisfy the `LockProtocol`:

```python
from typing import Protocol

class LockProtocol(Protocol):
    def __init__(self) -> None: ...
    def acquire(self, blocking: bool = ..., timeout: float = ...) -> bool: ...
    def release(self) -> None: ...
    def __enter__(self) -> "LockProtocol": ...
    def __exit__(
        self,
        exc_type: type[Exception] | None,
        exc_val: Exception | None,
        exc_tb: object,
    ) -> None: ...
```

### Abstract Lock Class

The `rqlite.Lock` class is abstract and should NOT be instantiated directly. Use `ThreadLock` or `threading.Lock` instead:

```python
from rqlite import Lock

# ❌ Don't do this - raises NotImplementedError
lock = Lock()

# ✅ Do this instead
from rqlite import ThreadLock
lock = ThreadLock()
```

## Development

### Setup

```bash
uv sync
```

### Run Tests

```bash
# Start fresh rqlite first
podman rm -f rqlite-test
podman run -d --name rqlite-test -p 4001:4001 docker.io/rqlite/rqlite

# Run tests
pytest -v

# With coverage
pytest --cov=rqlite --cov-report=term-missing
```

### Linting & Type Checking

```bash
uv run ruff check .
uv run ty check
```

## Architecture

```
rqlite/
├── __init__.py              # Package init, exports
├── connection.py            # Connection class (DB-API 2.0 sync)
├── cursor.py                # Cursor class (DB-API 2.0 sync)
├── types.py                 # Type helpers & sync locks (ThreadLock, LockProtocol)
├── exceptions.py            # Exception classes
├── async_connection.py      # Async Connection class
├── async_cursor.py          # Async Cursor class
├── async_types.py           # Async locks (AioLock, AsyncLockProtocol)
├── redis_lock.py            # Redis distributed lock (sync, RedisLock)
├── async_redis_lock.py      # Async Redis distributed lock (AioRedisLock)
├── valkey_lock.py           # Valkey distributed lock (sync, ValkeyLock)
├── async_valkey_lock.py     # Async Valkey distributed lock (AioValkeyLock)
└── sqlalchemy/              # SQLAlchemy dialect
    ├── __init__.py          # Dialect exports
    ├── dialect.py           # RQLiteDialect implementation (sync)
    └── async_dialect.py     # AioRQLiteDialect implementation (async)
```

## References

- [rqlite](https://rqlite.io) — Distributed SQLite database
- [Python DB-API 2.0 (PEP 249)](https://www.python.org/dev/peps/pep-0249/) — Python database API specification
- [SQLAlchemy Documentation](https://docs.sqlalchemy.org/) — Python SQL toolkit and ORM
- [Redis](https://github.com/redis/redis) — Redis in-memory data store (distributed locking)
- [Valkey](https://github.com/valkey-io/valkey) — Valkey in-memory data store (distributed locking, Redis-compatible)

## License

MIT License - see LICENSE file for details.
