duckdb-python

The current client is duckdb 2.0.0, requires Python 3.11 or newer, and installs with pip install duckdb. On Python 3.10 pip silently falls back to 1.x. Everything below runs on 2.0.0.

Two ways to run SQL, and when to use each

import duckdb

con = duckdb.connect()                 # in-memory; duckdb.connect("file.duckdb") persists
con.execute("CREATE TABLE t AS SELECT range AS i FROM range(10)")   # DDL and DML: execute
rel = con.sql("FROM t WHERE i > 5")    # a query: sql() returns a lazy relation
print(rel.fetchall())                  # [(6,), (7,), (8,), (9,)]