Metadata-Version: 2.4
Name: bareduckdb
Version: 0.20.200
Summary: Minimal Cython-based DuckDB Bindings
Author-Email: Paul T <paul@iqmo.com>
License-Expression: MIT
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: Python :: 3.15
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: Implementation :: CPython
Classifier: Programming Language :: Python :: Implementation :: GraalPy
Classifier: Programming Language :: Cython
Classifier: Programming Language :: Python :: Free Threading
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Classifier: Operating System :: OS Independent
Project-URL: Homepage, https://github.com/iqmo-org/bareduckdb
Project-URL: Repository, https://github.com/iqmo-org/bareduckdb
Project-URL: Issues, https://github.com/iqmo-org/bareduckdb/issues
Requires-Python: >=3.12
Provides-Extra: aio
Requires-Dist: anyio>=4.15; extra == "aio"
Provides-Extra: arrow
Requires-Dist: pyarrow>=26.0.0; (sys_platform != "win32" or platform_machine != "ARM64") and extra == "arrow"
Provides-Extra: test
Requires-Dist: pytest>=9.0.3; extra == "test"
Requires-Dist: anyio>=4.15; extra == "test"
Requires-Dist: trio>=0.34; extra == "test"
Requires-Dist: pytest-xdist>=3.8.0; extra == "test"
Requires-Dist: pytest-cov>=6.0.0; extra == "test"
Requires-Dist: pytest-timeout>=2.3.1; extra == "test"
Requires-Dist: pytest-randomly>=4.0.1; extra == "test"
Requires-Dist: pytest-run-parallel>=0.2.0; extra == "test"
Requires-Dist: pytest-forked>=1.6.0; extra == "test"
Requires-Dist: pytest-repeat>=0.9.3; extra == "test"
Requires-Dist: psutil>=5.9.0; extra == "test"
Requires-Dist: filelock>=3.16.0; extra == "test"
Requires-Dist: pyarrow>=26.0.0; extra == "test"
Requires-Dist: pandas>=3.0.0; extra == "test"
Description-Content-Type: text/markdown

# bareduckdb

Python bindings for DuckDB 2.0, bound only to DuckDB's v2 C API (`duckdb_v2_*`). bareduckdb
does not vendor or build DuckDB. It loads the official libduckdb shared library from DuckDB's
release, so the engine is upstream's release build.

[![PyPI version](https://img.shields.io/pypi/v/bareduckdb.svg)](https://pypi.org/project/bareduckdb)
[![Python 3.12+](https://img.shields.io/badge/python-3.12%2B-blue.svg)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)

```bash
pip install bareduckdb
```

The first import downloads libduckdb; see "The DuckDB library".

```python
import bareduckdb
import polars as pl

with bareduckdb.connect() as conn:
    conn.register("t", pl.DataFrame({"a": [1, 2, 3]}))
    conn.execute("SELECT sum(a) AS s FROM t").pl()         # polars frame, s = 6, no pyarrow
    conn.execute("SELECT 42 AS answer").fetchall()         # [(42,)]
```

## Design

### Querying Python data

- Registration is explicit. Pass a DataFrame, Arrow table or `pl.LazyFrame` inline with
  `conn.execute(sql, data={"t": df})`, or register it on the connection with
  `conn.register("t", df)`. A query sees only objects named one of these ways. duckdb-python
  can also find variables in the caller's scope; bareduckdb leaves that out.
- Input is zero-copy for Arrow and polars data. It is read in place and on demand, so a
  `LIMIT` reads only the batches it needs and a `pl.LazyFrame` is streamed, never collected.
- polars goes in through its own Arrow C stream and comes out through `.pl()` the same way,
  without importing pyarrow. CI runs a leg with pyarrow uninstalled.

### Free threading

Wheels ship for free-threaded 3.14t and 3.15t. Every engine call runs with the GIL released,
and every extension module declares itself free-threading compatible.

### Packaging

Wheels are under 400 KB and have no required Python dependencies; pyarrow, polars and pandas
are optional and imported on demand. The DuckDB library, 20 to 40 MB compressed, is downloaded
from DuckDB's release server on first import and cached.

## Guides

### Run a query

```python
import bareduckdb

with bareduckdb.connect() as conn:                     # or connect("file.db")
    conn.execute("CREATE TABLE t AS SELECT * FROM range(100) AS r(a)")
    conn.execute("SELECT * FROM t WHERE a > ?", [10]).fetchall()       # positional
    conn.execute("SELECT * FROM t WHERE a > $x", {"x": 10}).fetchall()  # named

    conn.execute("SELECT * FROM t").pl()            # polars
    conn.execute("SELECT * FROM t").arrow_table()   # pyarrow
    conn.execute("SELECT * FROM t").df()            # pandas, ArrowDtype columns
```

### Query in-memory data

`register()` names an object on the connection until `unregister()` or the connection closes.

```python
import bareduckdb
import polars as pl
import pyarrow as pa

orders = pl.DataFrame({"id": [1, 2, 3], "amount": [10.0, 25.0, 5.0]})

with bareduckdb.connect() as conn:
    conn.register("orders", orders)
    conn.execute("SELECT sum(amount) AS total FROM orders").pl()

    conn.register("events", pl.scan_parquet("events/*.parquet"))   # streamed, not collected
    conn.execute("SELECT count(*) FROM events WHERE kind = 'click'").pl()

    conn.unregister("orders")
```

`execute(..., data=...)` names objects for that one call, and the names are gone when the
call returns.

```python
customers = pa.table({"id": [1, 2, 3], "name": ["a", "b", "c"]})

with bareduckdb.connect() as conn:
    conn.execute(
        "SELECT c.name, o.amount FROM o JOIN c USING (id) WHERE o.amount > ?",
        [8],
        data={"o": orders, "c": customers},
    ).fetchall()
    # [('a', 10.0), ('b', 25.0)]
```

A variable named neither way is not found, even if it is in scope: `SELECT * FROM customers`
above raises a catalog error. "Registered sources" below lists what both accept and how each
source type is read.

### Use a registration twice in one query

A registration can be scanned once per query, so a self-join or a `UNION ALL` over the same
name raises. Read it through a materialized CTE instead:

```sql
WITH c AS MATERIALIZED (SELECT id, amount FROM orders)
SELECT count(*) FROM c x JOIN c y USING (id)
```

The registration is scanned once, and the CTE holds a copy of the selected columns until the
query ends. Write `MATERIALIZED` explicitly. Without it DuckDB may inline the CTE, which it
does when the query has a `LIMIT`, and the second scan raises. A CTE does not help concurrent
queries from other cursors.

### Stream a large result in batches

```python
reader = conn.execute("SELECT * FROM range(10_000_000)").arrow_reader(batch_size=100_000)
for batch in reader:
    ...
```

A reader holds its query open until it is read to the end, it is deleted, or the connection
runs its next statement. After that, a reader still referenced raises `ArrowInvalid` on its
next read, where duckdb-python ends it without an error. `reader.close()` does not release the
query. Use `conn.cursor()` to keep a reader open while running other queries.

### Cancel a query and show progress

```python
import threading
from bareduckdb import QueryCancelled, enable_progress, poll_progress

with bareduckdb.connect() as conn:
    threading.Timer(5.0, conn.interrupt).start()
    try:
        conn.execute("SELECT ... FROM big").fetchall()
    except QueryCancelled:
        pass
    conn.execute("SELECT 42").fetchall()            # the connection is still usable

    enable_progress(conn)
    with poll_progress(conn, lambda p: print(f"{p.percentage:.0f}%")):
        conn.execute("SELECT ... FROM big").pl()
```

`interrupt()` works from any thread. On a GIL build it cannot stop `.pl()` mid-read, because
polars holds the GIL while it reads. `max_execution_time` still applies, and
`arrow_table()` and `arrow_reader()` can be interrupted. See
[README_PROGRESS.md](README_PROGRESS.md).

### Run queries from asyncio or trio

The async pool is built on anyio. Each query runs on a cursor in a worker thread, and
cancelling the awaiting task interrupts the query. Results come back as `pyarrow.Table`, so
the pool needs pyarrow as well as the `aio` extra.

```bash
pip install "bareduckdb[aio,arrow]"
```

```python
from bareduckdb.aio import AsyncConnectionPool

async with AsyncConnectionPool(":memory:", pool_size=4) as pool:
    table = await pool.execute("SELECT * FROM range(?)", parameters=(10,))
```

A cancelled `execute` returns once the interrupt has stopped the worker thread, about 2 ms
after the cancel (see [README_PROGRESS.md](README_PROGRESS.md)), and the cursor is free by
then. anyio's `move_on_after` and `fail_after` work on either backend, and
`asyncio.wait_for` works under asyncio.

### Use bareduckdb where `duckdb` is expected

`bareduckdb.register_as_duckdb()` aliases the `duckdb` module to bareduckdb. CI runs
SQLAlchemy (duckdb-engine), jupysql and magic-duckdb this way.

```python
import bareduckdb

bareduckdb.register_as_duckdb()
import duckdb

print(duckdb.__version__)  # bareduckdb's version
with duckdb.connect() as conn:
    print(conn.execute("PRAGMA version").arrow_table())
    print(conn.execute("select * from range(10)").df())
```

### Run without network access

In a container, mount the cache directory (see "The DuckDB library"), or the download repeats
on every run. For an air-gapped host, fetch the library elsewhere and point bareduckdb at it:

```bash
python -m bareduckdb.install_duckdb --dir /opt/duckdb    # on a machine with network access
export BAREDUCKDB_DUCKDB_LIB=/opt/duckdb                 # on the target
export BAREDUCKDB_NO_DOWNLOAD=1                          # fail instead of downloading
```

With `BAREDUCKDB_NO_DOWNLOAD` set and nothing found, import raises
`DuckDBLibraryNotFoundError`, listing every path it tried.

## Reference

### Connection

`bareduckdb.connect(database=None, config=None, read_only=False, *, parse_cache_size=128)`
opens a connection; `None` is an in-memory database. `config` takes DuckDB settings, for
example `{"threads": "4", "memory_limit": "1GB"}`. The connection is a context manager that
closes on exit.

| Method | Effect |
| --- | --- |
| `execute(sql, parameters=None, *, data=None)` | Runs a statement and returns the connection, whose result methods read that statement's result. `data` names objects for this call only. |
| `register(name, obj)` | Names an object until `unregister()` or close. Returns the connection. |
| `unregister(name)` | Removes a name. An unknown name is a no-op. Returns the connection. |
| `cursor()` | A new connection to the same database, with its own result state. |
| `interrupt()` | Cancels the running query from any thread; it raises `QueryCancelled`. |
| `close()` | Closes the connection. |

Each connection keeps the last `parse_cache_size` parsed single-statement SELECTs, keyed by
SQL text, so a repeated query skips the parse. Bind and execute still run every time, and any
other statement clears the cache. `0` turns it off.

### Results

These are called on the connection after `execute()`, as in `conn.execute(sql).pl()`.

| Method | Returns | Needs |
| --- | --- | --- |
| `fetchone()`, `fetchmany(n)`, `fetchall()` | tuples | nothing |
| `pl()` | `pl.DataFrame` | polars |
| `pl_lazy()` | `pl.LazyFrame` over the stream | polars and pyarrow |
| `arrow_table()` | `pa.Table` | pyarrow |
| `arrow_reader(batch_size=None)` | `pa.RecordBatchReader` | pyarrow |
| `df(arrow_dtyped=True)` | `pd.DataFrame` | pandas and pyarrow |
| `description`, `columns`, `rowcount` | metadata | nothing |

A result is consumed once. `rowcount` is the changed-row count for INSERT, UPDATE and DELETE,
and -1 otherwise.

### Registered sources

`register()` and `data=` accept polars DataFrames and LazyFrames, pyarrow Tables, Datasets,
Scanners and RecordBatchReaders, pandas DataFrames, and any object with `__arrow_c_stream__`.

- Arrow and polars data is read in place. Columns the query does not name are never converted.
- pandas frames are converted with `pyarrow.Table.from_pandas`, which needs pyarrow and copies
  numpy-backed columns.
- A `pa.RecordBatchReader` is read fully at registration.
- A registration is re-read on every query, so a `pl.LazyFrame` re-runs its pipeline each
  time. For a frame that fits in memory and is queried repeatedly, `.collect()` it first.
- Filters are not pushed into the source. A `pl.LazyFrame` is asked for every row and DuckDB
  applies the `WHERE`.
- The planner gets a row count only for a source with a `len()`: a `pa.Table`, `pl.DataFrame`
  or pandas frame. Without one, DuckDB can pick a full sort over a top-N for a large
  `ORDER BY ... LIMIT`.
- Insertion order is preserved by default, as in DuckDB, and scans stay parallel.
  `SET preserve_insertion_order=false` opts into the unordered, lower-memory sink.

### Environment variables

| Variable | When | Effect |
| --- | --- | --- |
| `BAREDUCKDB_DUCKDB_LIB` | import | Path to libduckdb, or to a directory holding it. Checked first. |
| `BAREDUCKDB_NO_DOWNLOAD` | import | Raise instead of downloading libduckdb. |
| `BAREDUCKDB_LOG_LEVEL` | import | Configures logging at this level, for example `DEBUG`. |
| `BAREDUCKDB_COLLECT_LAZYFRAME` | query | `1` collects a `pl.LazyFrame` instead of streaming it. A measurement toggle, off by default. |
| `BAREDUCKDB_DUCKDB_DIR` | build | Use a local libduckdb directory instead of downloading one. |
| `BAREDUCKDB_DUCKDB_CHANNEL` | build | `stable` (default) or `preview`, DuckDB's rolling preview build. |
| `BAREDUCKDB_DUCKDB_VERSION` | build | Stable release tag to fetch. Empty uses the pinned release. |
| `BAREDUCKDB_DUCKDB_ARTIFACT` | build | Override the platform artifact name, for example `linux-arm64-musl`. |

### Platforms

| | Linux glibc (manylinux_2_28) | Linux musl (musllinux_1_2) | macOS | Windows |
| --- | --- | --- | --- | --- |
| x86_64 | yes | yes | no | yes |
| arm64 | yes | yes | yes | no |

One `cp312-abi3` limited-API wheel covers GIL builds of CPython 3.12 and newer. `cp314t` and
`cp315t` wheels cover free-threaded CPython. GraalPy 25.3 (Python 3.13) is built for Linux
x86_64 only; see [README_GRAALPY.md](README_GRAALPY.md). PyPy is not supported.

Every CPython wheel runs the core test suite before upload, and the GraalPy wheel runs a
subset. For thread safety, the suite also runs under `pytest-run-parallel` on 3.14 and 3.15t
on Linux, macOS and Windows, and TSAN and ASAN builds run nightly on free-threaded 3.14.

### The DuckDB library

The wheel does not contain libduckdb. On first import, bareduckdb looks for it in this order:

1. `BAREDUCKDB_DUCKDB_LIB`, a path to the library file or to a directory holding it.
2. `bareduckdb/_libs/` inside the package, which only a source build fills.
3. The user cache: `~/.cache/bareduckdb` (`$XDG_CACHE_HOME`), `~/Library/Caches/bareduckdb`,
   or `%LOCALAPPDATA%\bareduckdb\Cache`.
4. A download of the pinned release, currently `v2.0.0`, from
   `install.duckdb.org/v2.0.0/duckdb-shared-libs-<platform>.tar.gz`, into that cache.

The first import therefore needs network access, once per user and DuckDB version. The
download is checked for CPU architecture but not against a checksum or signature. It relies on
HTTPS.

`SELECT * FROM pragma_version()` reports DuckDB's own version and source id. Extensions load
from DuckDB's extension directory (`~/.duckdb/extensions/v2.0.0/...`). `duckdb` and
`bareduckdb` can be imported in the same process.

### Differences from duckdb-python

Defaults follow DuckDB, with these differences.

- `execute()` does not run a SELECT. The first `fetchall()`, `df()`, `pl()` or similar runs
  it. A SELECT you never read never runs.
- A SELECT is checked when you read it, not at `execute()`. An error such as an unknown column
  appears at the first read. A wrong number of parameters still fails at `execute()`.
- Reading `description` before any other read runs a SELECT as rows. A later `pl()` or
  `arrow_table()` then builds the table from those rows.
- A result can be read once, by one method. Calling `pl()` and then `fetchall()` raises, and
  the error names the first method. `arrow_table()` and `df()` are the exceptions: you can still
  fetch rows after them.
- After an Arrow read, `description` derives the types from the Arrow schema. Eight types get a
  different name: HUGEINT, UHUGEINT, TIMETZ, UUID, BIT, JSON, ENUM and TUPLE. With
  `SET arrow_lossless_conversion = true`, only ENUM and TUPLE differ.
- Parameters are typed from the Python value. An int that fits in 32 bits is BIGINT, not INTEGER. An int of
  2^63 or more is HUGEINT, not UBIGINT. A dict is a MAP, not a STRUCT, so it cannot be inserted
  into a STRUCT column. A nested list that mixes types, such as `[[1], ['a']]`, raises.
- A registered name can appear once per query. A self-join, or a `UNION ALL` of the name with
  itself, raises. See "Use a registration twice in one query".
- Registered names do not appear in `SHOW TABLES`. A real table with the same name wins.
- A SELECT whose CTE writes data keeps the write only if you read every row.
- `df()` returns pandas ArrowDtype columns, not NumPy columns. Use `df(arrow_dtyped=False)` for
  NumPy.
- VARINT values are `int`, not `str`.
- TIMESTAMPTZ values have a different `tzinfo`. They compare equal.
- `fetchall()` cannot read UNION, VARIANT or GEOMETRY columns. Read them as Arrow. See "DuckDB
  C API 2.0 limitations".
- PRAGMA, CALL and `RETURNING` statements run at `execute()`. Reading them as Arrow (`df()`,
  `pl()`, `arrow_table()`) builds the table from the fetched rows, which needs pyarrow and is
  slower. Some values cannot be rebuilt exactly, such as INTERVAL, BIT, nanosecond timestamps,
  infinite dates and the time 24:00:00. Those raise, and `fetchall()` still works. To get Arrow
  for them, pass `output_type="arrow_table"` to `execute()`.

### Not implemented

- `executemany` and the module-level functions (`bareduckdb.sql`, `read_csv`, ...). These
  raise `NotImplementedError`.
- Python table functions and replacement scans. `register_udtf()`, a non-empty
  `connect(udtf_functions=...)` and `connect(enable_replacement_scan=True)` raise
  `NotImplementedError`. Use `register()` instead.
- The relation API. Use SQL, or a frontend such as [ibis](https://ibis-project.org/) or
  [narwhals](https://github.com/narwhals-dev/narwhals).
- Python scalar UDFs and fsspec filesystems.
- Filter pushdown into registered sources.
- Some Arrow tables built from rows (see the PRAGMA bullet above). An ENUM column's dictionary
  holds only the values present. TIMETZ with `arrow_lossless_conversion` and a MAP with a nested
  key raise. Memory use is about 2.7 times DuckDB's.

### DuckDB C API 2.0 limitations

- **The v2 C API is unstable in DuckDB 2.0.0.** 
- **Rows cannot be converted to Arrow.** PRAGMA, CALL and `RETURNING` must go through Python -> Arrow.
  ([#25801](https://github.com/duckdb/duckdb/pull/25801) removed the old one).
- **An Arrow result has no DuckDB types.** `description` after an Arrow read is derived from
  the Arrow schema. See "Differences from duckdb-python".
- **VARIANT, UNION and GEOMETRY values cannot be read as rows.** `fetchall()` raises on them. See [#27570](https://github.com/duckdb/duckdb/issues/27570).
- **A PRAGMA or CALL that returns VARIANT raises on every read**, including `PRAGMA storage_info`. Use the workarounds below.
- **Some VARIANT contents cannot be exported to Arrow**: INTERVAL, TIMETZ, BIT, TIMESTAMP_S, TIMESTAMP_MS, and integers larger than int64.
- **v2 C API does not have support appending** Use `register()` with `INSERT ... SELECT`, or `INSERT` with parameters.

Workarounds:

```python
conn.execute("select * from pragma_storage_info('t')").arrow_table()  # PRAGMA as a SELECT
conn.execute("select * from duckdb_settings()").df()                  # CALL as a SELECT
conn.execute("pragma storage_info('t')", output_type="arrow_table")   # Arrow from the start
```

## Why it works this way

**A SELECT waits for its first consumer.** DuckDB fixes a result's format, rows or Arrow, when
the query is submitted, and offers no conversion afterwards. Submitting at `execute()` would
mean guessing the format, and running the query twice whenever the guess was wrong.

**A registration is scanned once per query.** The scan converts one Arrow batch at a time per
worker and discards each converted chunk as soon as it is emitted. Peak memory for a scan stays
at about one chunk per thread whatever the source's size, and there is nothing left over for a
second scan of the same name to replay. A materialized CTE makes that copy explicitly, for one
query.

**libduckdb is downloaded, not bundled.** Linking DuckDB's own release build keeps the wheels
small and the engine identical to upstream's. Moving to a new DuckDB release is a re-pin, not a
rebuild of DuckDB.

**Some features wait on the C API.** The v2 C API has no appender yet, and its `parse_sql`
exposes no table references or function-call sites, so Python table functions and
replacement scans cannot be discovered from SQL. Filter pushdown, the appender and table
functions existed in bareduckdb 0.12 on the 1.x API and need porting. Scalar UDFs and
filesystems need DuckDB threads to call back into Python, which needs design work first.

**Selective filters are slower than in duckdb-python.** Without filter pushdown, every row of
the named columns is converted before DuckDB filters it. Filtering in the source wins below
roughly 20% selectivity and loses above it.

## Development

```bash
uv pip install -e .                       # scikit-build-core + CMake; fetches libduckdb on first build
uv run pytest tests/capi -o addopts= -q   # fast subset
uv run pre-commit run --all-files
```

A source build compiles five Cython modules and downloads libduckdb at build time, unless
`BAREDUCKDB_DUCKDB_DIR` points at a local copy. Windows needs MSVC. Editing a `.pyx` or
`.pxd` requires a rebuild. polars and vortex-data are in the `dev-gil` group, which is not
installed by default. On a GIL build, add them with
`uv pip install --group dev-gil --only-binary :all:`.

The binding uses only released, non-deprecated `duckdb_v2_*` functions. Upstream marks all of
them `unstable: v2.0.0`, so a signature can change between releases.
`tests/capi/pxd_prototypes.txt` pins the header prototype of every function the binding
declares, and a re-pin that changes one fails until the snapshot is regenerated.

libduckdb is loaded with `RTLD_LOCAL`, which keeps its symbols apart from the official
`duckdb` wheel's statically linked copy when both are imported. pyarrow's `from_batches`
reader has a no-op `close()`, which is why `reader.close()` does not release a query.

## Disclaimer

Not affiliated with DuckDB Labs or the DuckDB Foundation.

## License

MIT. See [LICENSE](LICENSE).
