DuckDB Python Client

DuckDB provides binary WASM wheels to allow the DuckDB Python bindings to run within Pyodide, a WASM-based Python interpreter in the browser. To use it, you must load Pyodide, use micropip to install the specific DuckDB WASM wheel, and then you can import duckdb within your Python environment.

<html
  <head>
    <meta charset="utf-8" />
  </head>
  <body
    <script type="text/javascript" src="https://cdn.jsdelivr.net/pyodide/v0.25.1/full/pyodide.js"></script>
    <script type="text/javascript">
      async function main() {
        let pyodide = await loadPyodide();
        await pyodide.loadPackage("micropip");
        const micropip = pyodide.pyimport("micropip");
        // Install the DuckDB WASM wheel via micropip
        await micropip.install(["https://pyodide.duckdb.org/duckdb-0.10.2.dev479-cp311-cp311-emscripten_3_1_46_wasm32.whl"]);
        await pyodide.runPython(`
import duckdb

data = """\\\n
a,b,c
1,2,3
4,5,6
7,8,9"""

with open('data.csv', mode="w") as f:
    f.write(data)

print(duckdb.sql("SELECT COUNT(*) FROM 'data.csv'"))
        `);
      }
      main();
    </script>
  </body>
</html>

DuckDB's Python interface follows the Python DBAPI 2.0 specification for exceptions. Errors are organized in a hierarchy starting from Error (mapped from PyError) and Warning. You can catch specific error types to handle different failure scenarios, such as syntax errors, connection issues, or data integrity violations.

Common exception categories include:

• DatabaseError: The base class for most database-related errors.
• DataError: Errors related to data processing (e.g., ConversionException, TypeMismatchException).
• OperationalError: Errors related to the database operation itself (e.g., ConnectionException, IOException, OutOfMemoryException).
• IntegrityError: Errors related to data integrity (e.g., ConstraintException).
• InternalError: Errors occurring within the DuckDB engine (e.g., InternalException).
• ProgrammingError: Errors in the user's code or SQL (e.g., SyntaxException, ParserException, CatalogException).
• NotSupportedError: Errors when a feature is not implemented (e.g., NotImplementedException).

DuckDB provides DB API 2.0 compliant type objects to allow applications to categorize and check column types returned by queries. Instead of using isinstance(), you should use the equality operator (==) to compare a DuckDB type (from cursor.description) against these standard type objects.

Commonly used type objects include:

• duckdb.STRING: For text-based columns (e.g., VARCHAR).
• duckdb.NUMBER: For numeric columns (integers, decimals, floats).
• duckdb.DATETIME: For temporal data (dates, times, timestamps).
• duckdb.BINARY: For binary data (e.g., BLOB).

import duckdb

conn = duckdb.connect()
cursor = conn.cursor()
cursor.execute("SELECT 'hello' as text_col, 42 as num_col, CURRENT_DATE as date_col")

# Check column types using DB API type objects
for i, desc in enumerate(cursor.description):
    col_name, col_type = desc[0], desc[1]
    if col_type == duckdb.STRING:
        print(f"{col_name} is a string type")
    elif col_type == duckdb.NUMBER:
        print(f"{col_name} is a numeric type")
    elif col_type == duckdb.DATETIME:
        print(f"{col_name} is a date/time type")

When implementing or analyzing benchmarks, follow these conventions to ensure accuracy:

Conventions

• READ operations: Aggregate real columns (e.g., using sum or length) instead of count(*), as count(*) can be answered from metadata without scanning data.
• WRITE operations: Fully materialize the result or drain the lazy reader to ensure work is actually performed.
• Warm-up: Always warm the system once before starting measurements.
• Threading: The con fixture pins threads=1.

Critical Pitfalls (Avoid these to prevent measuring the wrong thing)

• Null Column Benches: For benchmarks testing OUT-col nulls, use real nulls (e.g., CASE WHEN ... THEN NULL). Using other methods may trigger a cheap std::move path instead of the intended logic.
• Numpy String Benches: For IN-numpy string benchmarks, use a mix of ASCII, non-ASCII, and a null sentinel. Failing to do so may cause the benchmark to skip the transcode/null ladder.