Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 38 ยท Python and Embedded Use

Method Chaining with the Relational API

Build DuckDB queries with Python method chaining: filter, project, aggregate, join, order and limit on lazy relations, and mix them freely with SQL.

SQL strings are great, but sometimes you want to build a query step by step in Python: add a filter only if a flag is set, loop over columns, or pass a half-built query into a function. DuckDB's Relational API gives you that. A relation is a lazy, immutable description of a query. Every method returns a new relation, so you chain them like a pipeline.

The real DuckDB does not run on this site, so the relation code is read-only. Below, each chain is shown next to the SQL it produces, and runnable SQL examples (portable, run in sql.js) prove the logic.

Creating a relation

Python
import duckdb

con = duckdb.connect()
con.execute("CREATE TABLE sales (region TEXT, product TEXT, qty INT, price DOUBLE)")
con.execute("""INSERT INTO sales VALUES
  ('north','pen',10,1.5),('north','ink',2,8.0),
  ('south','pen',7,1.5),('east','pad',4,3.25)""")

rel = con.table("sales")                    # a whole table
rel = con.sql("SELECT * FROM sales")        # any query
rel = con.read_csv("sales.csv")             # a file
rel = con.from_df(my_dataframe)             # a DataFrame
rel = con.values([1, "a"])                  # literal rows

Nothing has executed yet. rel.show(), rel.fetchall() or rel.df() triggers the work.

The core verbs

Python
result = (
    con.table("sales")
       .filter("qty > 3")
       .project("region, qty * price AS revenue")
       .aggregate("region, sum(revenue) AS total")
       .order("total DESC")
       .limit(10)
)
result.show()

Each verb takes a SQL expression string:

Method Meaning
filter(expr) WHERE
project(exprs) SELECT list
aggregate(exprs, group_by=None) aggregates, optionally with groups
order(exprs) ORDER BY
limit(n, offset=0) LIMIT / OFFSET
distinct() SELECT DISTINCT
join(other, condition, how="inner") joins; how can be left, right, outer, semi, anti
union(other) UNION ALL of two relations
except_, intersect set operations
alias(name) give the relation a name for use in SQL

Because each step is a SQL expression, you keep everything DuckDB can do: list_agg, regexp_matches, window functions with OVER, and so on. The Relational API is not a different language. It is SQL assembled in pieces.

Seeing the SQL

Use rel.explain() for the plan, or rel.sql_query() to retrieve the generated statement. For the chain above it is essentially:

SQL
SELECT region, sum(revenue) AS total
FROM (SELECT region, qty * price AS revenue FROM sales WHERE qty > 3)
GROUP BY region
ORDER BY total DESC
LIMIT 10;

DuckDB's optimizer flattens such subqueries, so a long chain costs no more than the hand-written statement.

SQL The SQL a relation chain builds (portable SQL)

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

Mixing SQL and relations

Give a relation an alias and it becomes a table name inside later SQL:

Python
big = con.table("sales").filter("qty >= 7").alias("big_sales")
con.sql("SELECT product, count(*) FROM big_sales GROUP BY product").show()

You can also pass relations to each other:

Python
regions = con.sql("SELECT * FROM (VALUES ('north','N'),('south','S')) t(name, code)")
joined = con.table("sales").join(regions, "sales.region = t.name")

This blend is the practical sweet spot: use SQL text for the bulky logic, and Python for conditional composition.

Conditional pipelines

The real value is building queries programmatically:

Python
def sales_report(con, min_qty=None, region=None, top=None):
    rel = con.table("sales")
    if min_qty is not None:
        rel = rel.filter(f"qty >= {int(min_qty)}")
    if region:
        safe = region.replace("'", "''")
        rel = rel.filter(f"region = '{safe}'")
    rel = rel.aggregate("product, sum(qty) AS units").order("units DESC")
    if top:
        rel = rel.limit(top)
    return rel

Casting with int(...) and doubling single quotes keeps these f-strings safe for simple values. For anything user-facing, prefer a real SQL statement with bound parameters (con.execute(sql, [value])) over string-building.

Runnable stand-in: composing filters

This stand-in mimics the idea with plain Python building a query string. Run it to see how optional pieces are added only when needed.

Python Stand-in: build a query in pieces

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.

The Rel class is a toy that shows why lazy composition works: nothing runs until the final execute.

Choosing a style

  • SQL strings are best for fixed, readable queries and for sharing with analysts.
  • Relational API suits dynamic queries and library code.
  • DataFrame libraries are best when the rest of your pipeline already uses them.

All three can coexist in one script, and they all use the same engine.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does con.table('sales').filter('qty > 3') do immediately?

    Relations are lazy and immutable; work happens only when you fetch or show.
  2. 2. Which method corresponds to the SQL SELECT list?

    project takes the expressions that appear in the SELECT list.
  3. 3. How can a relation be used inside later SQL text?

    alias gives the relation a name that SQL statements on the same connection can reference.
  4. 4. Why is a long chain of filter/project steps not slower than one hand-written query?

    DuckDB optimizes the entire generated query, flattening subqueries and pushing filters down.

Summary

  • A relation is a lazy, immutable query; every method returns another relation.
  • Verbs take SQL expression strings: filter, project, aggregate, order, limit, join.
  • alias lets SQL refer to a relation; explain() shows the plan.
  • Use relations to assemble queries conditionally and SQL text for the rest.

Next: Persistence, Connections and Concurrency