DuckDB CLI and Python

The DuckDB CLI and the Python Client

The CLI is a SQL shell with SQLite-style dot commands (.timer on, .mode csv, .mode markdown, .read file.sql); -c runs a command and exits, and -readonly opens a file without taking the write lock:

duck_cli_demo.sh: the CLI on BookNest's databaseShell

cd /home/dev/v7-l2/ch04 && PATH=/home/dev/v7-l2/tools/duckdb-1.5.5:$PATH
duckdb -readonly booknest.duckdb -c ".timer on" -c "
SELECT channel, count(DISTINCT order_id) AS orders, sum(gross_amount) AS gross
FROM sales WHERE status <> 'cancelled' GROUP BY ALL ORDER BY gross DESC"

Output

┌─────────┬────────┬───────────────┐
│ channel │ orders │     gross     │
│ varchar │ int64  │ decimal(38,2) │
├─────────┼────────┼───────────────┤
│ ios     │  42218 │    1478671.61 │
│ android │  32869 │    1153234.77 │
│ web     │  19023 │     671520.92 │
└─────────┴────────┴───────────────┘
Run Time (s): real 0.043 user 0.039539 sys 0.009523

GROUP BY ALL groups by every non-aggregated column. The Python client offers SQL strings, a lazy relational API, and replacement scans that let SQL name a pandas 16,086 , Polars 268,908 or Arrow 129 table in scope (Zero-Copy Interop):

client.py: SQL, the relational API and a DataFrame queried by namePython

import duckdb
con = duckdb.connect("booknest.duckdb", read_only=True)
top3 = (con.table("sales").filter("status <> 'cancelled'")          # relational API, lazy
        .aggregate("country, sum(gross_amount) AS gross").order("gross DESC").limit(3))
print(top3.fetchall())
df = con.sql("SELECT genre, avg(qty) AS avg_qty FROM sales GROUP BY ALL").df()   # to pandas
print(duckdb.sql("SELECT genre, round(avg_qty, 3) FROM df ORDER BY 2 DESC LIMIT 1").fetchone())

Output

[('US', Decimal('1329446.67')), ('GB', Decimal('370230.22')), ('IN', Decimal('314443.50'))]
('Fiction', 1.185)

Nothing ran until fetchall(): the relational calls assembled one plan the optimizer saw whole. duckdb.sql() uses a default in-memory connection; in real code pass a connection explicitly and close it (with duckdb.connect(...) as con:) so a file's write lock is released promptly.

MENU