Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 38 ยท Python and Embedded Use

The DuckDB Python API Basics

Use DuckDB from Python: import duckdb, run SQL with duckdb.sql and connections, fetch tuples, lists and DataFrames, and bind parameters safely.

DuckDB is an embedded database: there is no server to install or start. In Python the whole engine arrives as a library with pip install duckdb, and your program calls it in-process. That is why DuckDB feels more like sqlite3 or pandas than like PostgreSQL, even though its SQL is far richer and tuned for analytics.

A note about this site: the real DuckDB cannot run in your browser here. The Python snippets that import duckdb are read-only. Where it helps, a runnable stand-in uses Python's built-in sqlite3 module, which has a very similar connect/execute/fetch shape, so you can practice the control flow. Every stand-in is labelled. The SQL itself is the same portable SQL you already know.

Installing and the first query

Python
# pip install duckdb
import duckdb

duckdb.sql("SELECT 42 AS answer").show()

duckdb.sql(...) runs a query on a hidden, default in-memory database. It returns a relation: a lazy description of the query, not yet a result. .show() prints a table. A relation becomes real data when you ask for it:

Python
rel = duckdb.sql("SELECT range AS n FROM range(5)")

rel.fetchall()      # [(0,), (1,), (2,), (3,), (4,)]
rel.fetchone()      # first row as a tuple (consumes it)
rel.df()            # pandas DataFrame (needs pandas)
rel.fetchnumpy()    # dict of NumPy arrays
rel.arrow()         # a pyarrow Table (needs pyarrow)
rel.pl()            # a Polars DataFrame (needs polars)

Relations are covered in depth in method chaining. For now remember: building a relation is cheap, fetching it does the work.

Explicit connections

The default database is convenient for scripts, but real programs create connections on purpose:

Python
import duckdb

con = duckdb.connect()                 # in-memory, private to this connection object
con.execute("CREATE TABLE trips (id INTEGER, km DOUBLE)")
con.execute("INSERT INTO trips VALUES (1, 4.2), (2, 11.8), (3, 7.5)")

print(con.execute("SELECT count(*), sum(km) FROM trips").fetchall())
# [(3, 23.5)]

con.close()

duckdb.connect() with no argument is in-memory and vanishes when the connection closes. duckdb.connect("analytics.duckdb") opens (or creates) a file; see persistence and concurrency. A connection is also a context manager:

Python
with duckdb.connect("analytics.duckdb") as con:
    con.sql("SELECT 1").show()

con.execute() returns the connection itself, which is why you can chain .fetchall() on it. con.sql() returns a relation instead. Use execute when you run statements that change things, and sql when you build queries to compose further.

Fetching shapes

Different consumers want different shapes:

Method Result
fetchone() one tuple, or None when exhausted
fetchmany(n) a list of up to n tuples
fetchall() a list of all tuples
df() / fetchdf() pandas DataFrame
arrow() / fetch_arrow_table() Arrow table
fetchnumpy() dict of NumPy arrays

Column names are in rel.columns and types in rel.types (or con.description after execute). For huge results prefer fetch_record_batch(), which streams Arrow batches so you never hold everything in memory.

Parameters: never format SQL with f-strings

Build queries with placeholders. DuckDB supports positional ? and $1, and named $name:

Python
con.execute("SELECT * FROM trips WHERE km > ?", [5])
con.execute("SELECT * FROM trips WHERE km > $min", {"min": 5})

Values are sent separately from the SQL text, so there is no injection risk and no quoting bug. Parameters can stand in for values, not for identifiers like table or column names. For a list of values use IN with a list parameter or = ANY(?).

Bulk inserts use executemany, or better, insert a DataFrame (see DataFrames and Arrow) because row-by-row inserts are slow in a column store.

Errors

DuckDB raises subclasses of duckdb.Error: duckdb.ParserException for bad syntax, duckdb.CatalogException for a missing table, duckdb.BinderException for a missing column, duckdb.ConversionException for failed casts, and duckdb.ConstraintException for primary-key or NOT NULL violations. Catching the specific class makes tests and retries clearer.

Runnable stand-in with sqlite3

This runs in the browser, using Python's sqlite3 as a stand-in for the connect, execute and fetch pattern:

Python Stand-in: connect, execute, fetch (sqlite3)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Placeholders and fetchall, fetchone and description behave the same way in DuckDB. Here is the error pattern, again with the stand-in:

Python Stand-in: catching specific errors

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In DuckDB the same three mistakes raise ConstraintException, ConstraintException and CatalogException.

flowchart LR
    A["Python code"] --> B["duckdb.sql / con.execute"]
    B --> C["Relation (lazy)"]
    C -->|"fetchall, df, arrow"| D["Python data"]
    B --> E["In-process engine"]
    E --> C

The engine runs inside your Python process; results are produced when you fetch.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does duckdb.sql('SELECT 1') return?

    sql() returns a relation. The query executes when you call a fetch method, show() or another consumer.
  2. 2. Which is the safe way to pass a user-supplied number?

    Placeholders send values separately from the SQL, which avoids injection and quoting bugs.
  3. 3. What does duckdb.connect() with no argument create?

    No path means in-memory. Give a file path to persist data.
  4. 4. You need results as Arrow batches to avoid holding everything in memory. What do you use?

    fetch_record_batch() streams Arrow record batches.

Summary

  • DuckDB runs inside your Python process; pip install duckdb is the whole setup.
  • duckdb.sql() and con.sql() return lazy relations; execute() returns the connection.
  • Fetch as tuples, DataFrames, Arrow or NumPy depending on the consumer.
  • Always use placeholders (?, $1, $name) for values.
  • Catch specific duckdb.*Exception classes.

Next: DataFrames, Arrow and Zero-Copy Scans