DuckDBPyRelation is a queryable Python object for building and manipulating relational queries. It connects Python data workflows to SQL without requiring a separate database server or connection setup: the imported `duckdb` module supplies a default in-memory connection. section 6.1.2 section 6.2.1
Create a relation from a CSV file, then reuse it as a queryable object. Relational operations are lazy: methods construct symbolic query nodes, while `show()`, fetching, or another trigger executes the query. section 6.2.1
Compose a workflow with fluent relational operations:
- `filter` keeps rows matching a predicate; `project` selects columns; `limit` restricts the result. section 6.2.2
- `aggregate` computes grouped summaries, while `order` sorts the resulting relation. section 6.2.2
- Reuse intermediate relations and combine them with `except_` and `intersect` to build larger pipelines. section 6.2.2
- Prefer dedicated relational methods when table or column names are dynamic; they avoid assembling SQL through string concatenation. section 6.2.2
Filter and summarize a CSV relation
population = con.read_csv("https://bit.ly/3KoiZR0") over_10m = population.filter("Population > 10000000") (over_10m .aggregate("Region, CAST(avg(Population) AS int) AS pop") .order("pop DESC") .show()) The same relation can be counted, projected, limited, or converted into a table for later use. section 6.2.1 section 6.2.2
Listing 6.1 Querying a CSV file
import duckdb
con = duckdb.connect(database=':memory:')
con.execute("INSTALL httpfs") #1
con.execute("LOAD httpfs") #2
population = \
con.read_csv("https://bit.ly/3KoiZR0") #3
Use this listing to start a relational workflow by loading CSV data into a DuckDBPyRelation. section 6.2.1
Relational objects can also be queried with SQL through `sql()`. When values must be passed as parameters, use `execute()` and DB-API 2.0 fetching methods such as `fetchone()` or `fetchall()`. section 6.2.3
The API treats pandas and Polars DataFrames, Apache Arrow tables, and other sources uniformly enough to support reusable query fragments and conversion among common Python data formats. section 6.6
