duckdb 2.0.0

DuckDB v2.0.0

on Python PyPI

duckdb-python 2.0.0 is the Python package for DuckDB 2.0. It supports Python >= 3.11 and drops Python 3.10. From Python 3.12 on, one wheel per platform serves every Python version. The binding layer was rewritten on nanobind, query results stream instead of being collected up front, the relational API was rebuilt on SQL text, and the way DataFrames, Arrow data and polars frames are read was replaced. Many long-standing bugs are fixed, and moving data in and out of Python is much faster. The engine is DuckDB 2.0.0; see its release notes for what changed in SQL and storage.

Highlights

• Moving data is much faster. Numeric exports to numpy, pandas, Arrow and polars take a quarter to two fifths of the 1.5 time; in-memory Arrow, polars and pandas 3 inputs are scanned on all threads, 8x to 20x faster; Arrow UDFs run about 20x faster; draining an Arrow reader on 16 threads takes an eighth of the time. (#693, #697, #700)
• Results stream. con.execute() starts the query and you read rows as you fetch them; con.complete() and con.abort() finish or cancel an open result, and an open result or Arrow reader owns the connection until it is done.
• The relational API is rebuilt on SQL text. A relation is a lazy query, bound at every step so its schema is known; rel.sql_query() shows exactly what will run. Many new methods and expression helpers (col, lit, when, window functions). One semantic change: a plain string in an expression is a value, not a column name. (#697)
• A new Appender for fast bulk inserts: with con.appender("t") as a: a.append_row(...), under a microsecond per row. (#661)
• One stable ABI wheel per platform for Python 3.12 and later, so a new Python release works on day one. Python 3.15 is supported. (#672, #673, #684)
• Arrow export on con.execute() results changed: fetch_arrow_table, fetch_record_batch and arrow(n) are gone; use con.sql(query).to_arrow_table() or .to_arrow_reader(). (#702)
• The experimental Spark API is brought up to date: it is now compatible with PySpark 4.2.0 and 3.5.9 (#686). We would love your input on whether to continue support or to drop it in future releases.

Upgrading from 1.x: what to check

1. Make sure you run Python 3.11 or later.
2. If you call fetch_arrow_table, fetch_record_batch, arrow(n) or pl() on the result of con.execute(), switch to con.sql(query).to_arrow_table(), to_arrow_reader() or pl(). Libraries that call the removed names on an execute result need an update. (#702)
3. If you call fetchone(), fetchmany(), fetch_df_chunk() or execute() on a relation, use rel.limit(n).fetchall(), rel.to_arrow_reader(), rel.materialize(), or run the query through con.execute(). (#697)
4. If you compare a relation column with a plain string, col("s") == "x" now compares with the text x. Wrap column names in col(). (#697)
5. If you rely on import duckdb having opened the default database, note that it happens on the first duckdb.sql() or duckdb.connect() call instead. (#680)
6. If you pass Decimal values with a positive exponent, such as Decimal("1E+2"), they now arrive as the number they denote, 100, where 1.5 produced 1.00. (#654)
7. If you write files with overwrite=False, an existing file is now refused instead of replaced. (#668)
8. If a test or script depends on str(rel) being free, note that it runs the query each time now.

Breaking changes

Arrow export on DB-API results (#702)

Results of con.execute() are now read row by row and are not kept in memory, so they cannot hand their data to Arrow for free. fetch_arrow_table, fetch_record_batch, arrow(rows_per_batch) and pl() on a DB-API result are removed; they had been deprecated. to_arrow_table() and to_arrow_reader() remain, also as duckdb.to_arrow_table() and duckdb.to_arrow_reader(), but they materialize the rows twice, once as the result and once as Arrow, and emit a UserWarning pointing at the relational path, con.sql(query).to_arrow_table(), which we recommend instead. The reader no longer streams: its first batch arrives after the whole query ran. After a partial fetchone or fetchmany the Arrow call returns the remaining rows instead of raising. With preserve_insertion_order off, the row order can differ between calls. duckdb.arrow(n) with an integer is gone; duckdb.arrow(arrow_object) still builds a relation.

# 1.5
table = con.execute("SELECT * FROM t").fetch_arrow_table()
reader = con.execute("SELECT * FROM t").fetch_record_batch(10_000)

# 2.0
table = con.sql("SELECT * FROM t").to_arrow_table()
reader = con.sql("SELECT * FROM t").to_arrow_reader(10_000)

Relations are SQL text (#697)

The relational API is a query building API, not an execution API. A relation is a piece of SQL text with a schema bound once when it is built, so every step knows its columns and types. It runs nothing until you ask for data, and it runs again on every read; rel.sql_query() returns exactly the text that runs, as one SELECT per group of steps and a common table expression where the order of clauses breaks. The engine's binder decides every column name and type; nothing is renamed by the client.

What you are most likely to notice:

• A bare string operand of an expression is a value, not a column. For one release a warning is raised when the string matches a column name; the warning goes in 2.1. A dotted string is not split into parts; pass the parts separately.

  from duckdb import ColumnExpression as c
  rel.filter(c("s") == "x")       # 1.5: s = x when a column x exists; 2.0: s = 'x'
  rel.filter(c("s") == c("x"))    # both: s = x

• fetchone(), fetchmany(), fetch_df_chunk() and execute() are gone from relations. The error names the replacement: limit(1).fetchall(), to_arrow_reader() or limit(n).df() for batches, and materialize() to run a relation once and keep its rows.

• con.sql(text, params=...) is lazy and binds its parameter values on every read; it used to run once.

• con.sql() of a statement that returns no rows, such as CREATE TABLE or an INSERT without RETURNING, returns a relation over what the statement returned instead of None: no rows for DDL, the changed row count for DML. The statement still runs at the call. Only SELECT-shaped statements are lazy; CALL, PRAGMA, CHECKPOINT and EXPLAIN run at the call and the relation holds their output.

• Over a join, l.x and r.y resolve inside the join's own SELECT. Once a later step starts a new SELECT the input alias is no longer known and the engine's error is raised. Two columns of the same name are name and name_1 past that point.

• A persistent view over held rows (materialize(), values(), a statement that ran at the call) is refused; use rel.create("name") or con.register("name", rel).

• A relation over a file pins its schema when it is built; a file that changes between building and reading no longer yields rows that disagree with the declared columns. A reader with filename=True or a Hive partition declares that column among its columns.

• The new verb names are methods, so a column called offset, sample, head, rename, pivot, drop, unnest, unpivot, preview or group_by is reached as rel["offset"].

Executing statements through the DB-API (con.execute)

A query is now started at execute() and read as you fetch, instead of being run to completion first. An error in the first rows still raises from execute(); an error later in the query raises from the fetch call that reaches it. Statements that write, including RETURNING, complete inside execute(). Calling execute() again while a result is open replaces that result; a replaced statement that may write is run to completion first so the transaction stays valid. Any other call that needs the connection while a result is open, such as a relation fetch or a UDF reaching for its own connection, refuses with an error that names the open result instead of deadlocking. An Arrow reader being read from a relation blocks the connection the same way. Finish reading, or use a cursor for the other work.

Two new calls manage the open result: con.complete() runs it to its end without reading its rows, and con.abort() cancels the rest of it; aborting a statement that may write fails an open transaction. Both exist as duckdb.complete() and duckdb.abort() for the default connection. A statement that fails inside an explicit transaction aborts that transaction, and con.rowcount reports the number of rows a statement changed.

Importing duckdb opens nothing (#680)

import duckdb no longer creates the default in-memory database or starts its threads. The first duckdb.sql(), duckdb.execute() or similar module-level call does.

Python 3.10 dropped, stable ABI wheels from 3.12 (#672, #673, #684)

The package requires Python 3.11 or later. For Python 3.12 and later there is one abi3 wheel per platform that works on 3.12, 3.13, 3.14 and 3.15; Python 3.11 keeps a wheel of its own. Free-threaded builds of CPython are not supported. On the stable ABI wheels, fetching fixed-width values row by row (integers, booleans, dates, times, timestamps) costs 13 to 17 percent more than on a wheel built against the full Python API; DataFrame and Arrow output are within 5 percent. The wheel is 24.4 MB against 21.5 MB for 1.5.5, and 68.8 MB against 60.9 MB installed.

Values and types

• Decimal("1E+2") arrives as 100 with type DECIMAL(3,0); 1.5 produced 1.00. The inferred type and the value now always agree. (#654, #566)
• A dict converted to a STRUCT matches keys exactly. A missing key no longer gets another key's value. (#660)
• A str that UTF-8 cannot encode, such as a lone surrogate, raises InvalidInputException and leaves the database usable, where 1.5 raised a bare bad_cast on every path. (#671)
• A NaN inside a list, struct, map or typed duckdb.Value parameter stays NaN; 1.5 turned it into NULL. (#686)
• A subclass of int is converted from its stored value without running its own methods. (#679, #408)
• A registered dict of arrays whose lengths differ fails with an error naming both, such as Column 'b' has 2 rows where 'a' has 5. 1.5 read past the end of the shorter array. (#700)
• Columns of a registered dict or a map() result whose names differ only in case, such as a and A, are renamed as in a DataFrame (a, A_1). 1.5 refused them as duplicate column names. (#700)

Reading Arrow, pandas, numpy and polars inputs

• Filters are pushed into an Arrow source only when they can be evaluated exactly. An OR with a part that cannot be translated is not pushed at all, where 1.5 dropped that part silently and could return wrong rows. In-memory Arrow tables take no pushed filters; Datasets and polars LazyFrames do. (#693)
• The default Arrow batch size dropped from 1,000,000 to 131,072 rows for to_arrow_table, to_arrow_reader and arrow. Pass batch_size= to keep the old size.
• A one-shot stream such as a RecordBatchReader is read as the query needs it, not far ahead of it. Scanning the same reader twice raises "Stream has already been consumed" instead of returning partial or no rows. (#693, #655, #691, #70)
• One-chunk Arrow tables, record batches, arrays and polars frames are scanned by all threads; 1.5 read such a source on one thread, however large. A query over one that depends on row order without ORDER BY, such as LIMIT or first(), can return different rows between runs. (#700)
• A numpy masked array is read with its mask, so masked elements are NULL. 1.5 ignored the mask, so a scan now costs more: about 2.4 times as long with 10 percent masked, 1.5 times when nothing is masked. (#700)

Files

• write_csv and write_parquet with overwrite=False refuse to replace an existing file. Only a single file is checked. (#668, #156)
• rel.insert_into("db.schema.t") and rel.create("db.schema.t") keep the catalog part. A longer path is refused with a clear error; 1.5 dropped the middle parts and wrote to the wrong table. (#682)

Versions

Development builds carry a time stamp suffix of the form 2.0.0.dev2610162032; the engine version string is passed through verbatim.

Deprecations

• A bare string in a relation expression that matches a column name warns in 2.0 and stops being treated specially in 2.1. (#697)
• The DB API arrow export methods might be removed in a future release. Right now they emit a warning.
• (Considering) The experimental Spark API (duckdb.experimental.spark) might be deprecated in favor of SQLFrame. Not decided yet.

Removed

• fetch_arrow_table, fetch_record_batch, arrow(rows_per_batch) and pl() on DB-API results, and duckdb.arrow(n) with an integer. Deprecated in 1.x. (#702)
• fetchone(), fetchmany(), fetch_df_chunk() and execute() on relations. (#697)
• Python 3.10 wheels.

New features

Appender (#661)

con.appender("table") returns a context manager for bulk inserts, mirroring the C++ Appender: append_row(*values), append_rows(rows), flush() and clear(), with a flush on leaving the with block. The target's schema is read when the appender is made, and a mismatch raises at the first append. Column names that differ only in case are refused. It answers discussions #369 and #193.

Relation methods and expression helpers (#697)

New relation methods: with_columns, drop, rename, unnest, unpivot, pivot(column, values, aggregate), sample, head, offset, union_by_name, distinct(on=...), group_by(...).agg(...), preview, to_dicts, iter_rows(batch_size=), explain(analyze=True) and join(..., suffix=...); join takes how in inner, left, right, outer, full, semi, anti, cross, positional, natural and asof, with the condition optional where the kind takes none. New expression functions: col, lit, fn, when().then().otherwise(), star, sql_expr, coalesce, count_all, window constructors with .over(partition_by=, order_by=, rows=, range=), .where(), .try_cast(), .like(), .ilike(), [] for struct fields and list elements, the .str(), .dt(), .list() and .json() function families, aggregate methods, and Python callables as SQL lambdas. A projection may hold an aggregate. Notebooks display a relation once per cell as text and HTML. con.sql(text, params=...) binds lazily, so one relation can be read with fresh values.

Python 3.15 and stable ABI wheels

Python 3.15 is in the test matrix and the classifiers. One wheel per platform covers 3.12 and later.

Spark API works against PySpark 4.2.0 and 3.5.9 (#686)

duckdb.experimental.spark imports on a clean install (it no longer needs typing_extensions or numpy) and, for the functions it covers, returns Spark's answer or raises; 38 deliberate differences are documented. Fixes #653 and #183 and part of #428.

Binding layer rewritten on nanobind (#522)

The C++ bindings moved from pybind11 to nanobind 3. This is what makes the stable ABI wheels possible and accounts for the faster import and columnar paths. Behavior at the Python boundary was checked against the old binding in a ten agent regression sweep before the cutover.

Fixes

Each line says what went wrong in 1.5 or an earlier 2.0 build.

• con.table("s.v") for a view in a schema raised "Table does not exist". (#670)
• rel.query(name, sql) could not refer to the relation's columns by the given name. (#656, #604)
• create_function resolved type names on the default connection, so an ENUM created on another connection was unknown, and a failure silently cancelled the transaction. (#681)
• Arrow filter pushdown on dictionary-encoded columns of binary, decimal or zoned timestamp values crashed or returned wrong rows; zoned second and millisecond timestamp columns returned wrong rows since 1.5.6. (#677, #664)
• Pushing a TIMESTAMPTZ_NS constant into an Arrow scan scaled it a second time, so modern dates failed to convert and dates near 1970 matched the wrong rows (2.0 regression). (#687)
• Comparing an Arrow column with NaN or using a float IN list pushed the wrong condition. (#657, #615)
• Polars lazy frame pushdown truncated datetime literals into the column's unit and failed on inf or NaN literals. (#688)
• map() leaked its argument tuple and the input DataFrames of every call. (#669, #534)
• The process crashed at exit when the module outlived sys.modules cleanup. (#658, #638)
• Once a subinterpreter existed, pandas scans raised "GIL was already held" and Arrow readers crashed. (#662)
• Python ints above 64 bits and int subclasses could convert to the wrong value; a Python error inside a conversion or UDF could be swallowed or replaced, and MemoryError was lost. The Arrow conversion error now carries pyarrow's own reason. (#679)
• A registered fsspec filesystem without modified() crashed the process; it now reports NotImplementedException. (#683, #584)
• ColumnExpression("s.a.b.c") dropped name parts, and StarExpression(exclude=[ColumnExpression("b", "id")]) excluded every id instead of b.id. (#686)
• duckdb.struct_type({}) was refused. (#686)
• describe() failed on GROUP BY ALL with UNNEST. (#546)
• A sliced nullable pandas column, such as df[::2] of Int64, moved its NULLs to other rows. (#700)
• Masked numpy elements read as their hidden values instead of NULL. (#700)
• A fetchnumpy result with NULL strings, registered and queried again, gave the text <NULL> instead of NULL. (#700)
• A duckdb.Value in a pandas object column raised an internal error and invalidated the database. (#700)
• count(*) and EXISTS over a DataFrame converted its first column, so an unused column could fail the query, in 1.5 with an internal error for a lone surrogate. (#700)
• A numpy timedelta scan raised a deprecation warning. (#502)
• read_json sniffed the file again on every read, so a changed file returned rows that did not match the declared columns. (#697)
• A lone surrogate in a CSV quotechar or a file path escaped as a bare RuntimeError: std::bad_cast. (#697)
• StarExpression(exclude=[...]) with a qualifier over a join of tables was refused; the engine now decides. (#697)
• unregister of a registered view that SQL had renamed kept both views. (#697)
• The source distribution lacked the engine's extension configuration, so building from source failed. (#652)
• An empty result could stay open in the engine after execute() and refuse the next statement now and then when more than one thread was in use. (#702)
• The ADBC driver inside the 2.0 development wheels failed unless the process had imported duckdb first, because the built-in extensions were registered only on import; any ADBC query that needed them, such as adbc_ingest into a file database, failed with a catalog error. They are now registered when the library loads. (#701, #698)

Performance

Measured with the 2.0.0 release wheel against the PyPI 1.5.5 wheel on x86-64 Linux, CPython 3.13, one CPU core unless stated. Each figure is the 2.0 time divided by the 1.5.5 time, so 0.5x means twice as fast. Over the 297 cases of the benchmark suite the geometric mean is 0.74x: 161 cases are faster, 77 slower and 48 unchanged.

Moving data out:

• Numeric exports of 500k rows: numpy 0.31x, pandas 0.43x, Arrow 0.24x, polars 0.39x.
• Arrow table of 6M rows 0.67x; polars frame of 6M rows 0.71x; an Arrow reader of 6M rows drained on 16 threads 0.13x (1.07 s to 134 ms).
• Rows of TIMESTAMPTZ 0.83x, TIMETZ 0.75x, INTERVAL 0.60x and 128-bit DECIMAL 0.69x values; iterating a result row by row with fetchone 0.58x.

Moving data in:

• A one-chunk Arrow table or polars frame of 8M rows scanned on 8 threads: 0.13x (32 to 4.2 ms); CREATE TABLE AS from such a table on 8 threads 0.05x (193 to 10 ms). (#700)
• Summing a 2M row pandas 3 str column: 0.20x on one thread, 0.05x on eight. count(*) over a 2M row DataFrame whose first column is str: 0.01x (102 ms to 0.87 ms). (#700)
• con.append(df) and insert_into from a 1M row frame on 16 threads: 0.39x and 0.44x. The new Appender inserts a row in under a microsecond (0.86 us, 0.71 us through append_rows), where executemany takes about 315 us per row. (#661)
• values() of 10k dates, times, datetimes or timedeltas 0.47x to 0.67x; of 10k bytes 0.49x and memoryviews 0.60x. (#697)
• Arrow UDFs about 0.05x (a 400 ms call to 16 to 24 ms); string UDFs 0.70x.

At scale:

• TPC-H sf100 on 16 threads runs at the engine's own speed: the 2.0 over 1.5.5 geomean through Python is 0.712 against 0.750 on the core engine dashboard for the same engines. On the dashboard's machine type the Python client adds about 2 percent per query over the DuckDB command line client, down from about 5 percent on 1.5.5.
• Peak resident memory of df(), fetchnumpy() and fetchall() over large results is equal within 1 percent; an Arrow table of 6M rows takes 5 percent more and a polars frame of 6M rows 7 percent more. No path retains memory across calls.

Slower:

• connect() 8.4 to 16.0 ms, because a database now starts up to 256 asynchronous I/O threads by default (async_threads); connect(config={"threads": 1, "async_threads": 1}) takes 10.7 ms.
• The first row of a 6M row query through execute() and fetchone() arrives later, 4.75x on one thread (1.1 to 5.2 ms) and 1.6x on 16; the first 10k rows take the same time as before.
• Scanning a 2M row pandas object string column (the pandas 2 default) on 16 threads no longer scales: 12.8x (6.6 to 84 ms); on one thread 1.08x. The pandas 3 str column is the fast path. A pandas object column of naive datetimes 2.24x.
• An 8M row int64 numpy masked array on one thread: 2.44x with 10 percent masked, 1.48x with nothing masked, because the mask is now read. (#700)
• fetchall of 100k DATE, TIME or TIMESTAMP rows 1.28x, 1.23x and 1.11x.

Thanks

Thanks to everyone who reported issues, tested the development wheels and contributed fixes to this release.