⌘K ctrl+k
Search Shortcut cmd + k | ctrl + k
Relational API

The Relational API builds a query one step at a time from Python. A relation, a DuckDBPyRelation, is a piece of SQL text: the methods of the Transformation and Functions sections return a new relation whose text extends the one they were called on.

Lazy Evaluation

Building a relation runs nothing: DuckDB binds the text once to learn the columns and their types, and holds no data. For example, a relation over a billion rows is built at once:

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("FROM range(1_000_000_000) t(id) SELECT id, id % 7 AS bucket")
print(rel.sql_query())

FROM range(1_000_000_000) t(id) SELECT id, id % 7 AS bucket

sql_query() returns exactly the text that runs. The query runs when a method of the Output section reads the relation, and it runs again on every read, so each read sees the current data. Printing a relation, or calling show(), also runs it and displays the first rows:

import duckdb

duckdb_conn = duckdb.connect()
rel = duckdb_conn.sql("FROM range(1_000_000_000) t(id) SELECT id, id % 7 AS bucket")
rel.filter("bucket = 3").order("id").limit(3).show()

┌───────┬────────┐
│  id   │ bucket │
│ int64 │ int64  │
├───────┼────────┤
│     3 │      3 │
│    10 │      3 │
│    17 │      3 │
└───────┴────────┘

Only a statement that reads, such as a SELECT, gives a lazy relation. Any other statement passed to sql(), such as CREATE TABLE or INSERT, runs at the call, and the relation holds what it returned. The params list or dict is copied when the relation is created, and its values are converted each time the relation runs. A column is also an attribute of the relation, as in rel.price, unless a method has its name: a column called offset is reached as rel["offset"], since rel.offset is the offset() method.

To run a query once and keep its rows, call materialize():

import duckdb

duckdb_conn = duckdb.connect()
duckdb_conn.sql("CREATE TABLE items (id INTEGER)")
counted = duckdb_conn.sql("SELECT count(*) AS n FROM items")
held = counted.materialize()
duckdb_conn.sql("INSERT INTO items VALUES (1), (2)")
print(counted.fetchall(), held.fetchall())

[(2,)] [(0,)]

Relation Creation

This section contains the details on how a relation is created. A relation over a query that reads is lazy evaluated.

Name Description
from_arrow Create a relation object from an Arrow object.
from_csv_auto Alias of read_csv().
from_df Create a relation object from the DataFrame in df.
from_parquet Alias of read_parquet().
from_query Alias of sql().
query Alias of sql().
read_csv Create a relation object from the CSV file in 'name'.
read_json Create a relation object from the JSON file in 'name'.
read_parquet Create a relation object from the Parquet path(s) or file-like object(s) in 'path_or_buffer'.
sql Run a SQL query. A statement that only reads returns a relation that runs the query each time it is read, parameter values bound at every run. Any other statement runs once, now, and gives a relation over what it returned. Statements before the last one run immediately.
table Create a relation object for the named table.
table_function Create a relation object from the named table function with given parameters.
values Create a relation object from the passed values.
view Create a relation object for the named view.

from_arrow 

from_arrow(self: _duckdb.DuckDBPyConnection, arrow_object: object) -> DuckDBPyRelation

Create a relation object from an Arrow object.

Parameters

  • arrow_object : object

    The Arrow object to create a relation from: a table, a record batch, a record batch reader, a dataset, a scanner, a polars DataFrame or an object that exports the Arrow PyCapsule interface.

Example

import duckdb
import pyarrow as pa

ids = pa.array([1], type=pa.int8())
texts = pa.array(['a'], type=pa.string())
example_table = pa.table([ids, texts], names=["id", "text"])

duckdb_conn = duckdb.connect()

rel = duckdb_conn.from_arrow(example_table)

rel.show()

Result

┌──────┬─────────┐
│  id  │  text   │
│ int8 │ varchar │
├──────┼─────────┤
│    1 │ a       │
└──────┴─────────┘

from_csv_auto 

from_csv_auto(self: _duckdb.DuckDBPyConnection, *args: object, **kwargs: object) -> DuckDBPyRelation

Alias of read_csv().

from_df 

from_df(self: _duckdb.DuckDBPyConnection, df: object) -> DuckDBPyRelation

Create a relation object from the DataFrame in df.

Parameters

  • df : pandas.DataFrame

    A pandas DataFrame to be converted into a DuckDB relation.

Example

import duckdb
import pandas as pd

df = pd.DataFrame(data = {'id': [1], "text":["a"]})

duckdb_conn = duckdb.connect()

rel = duckdb_conn.from_df(df)

rel.show()

Result

┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘

from_parquet 

from_parquet(self: _duckdb.DuckDBPyConnection, path_or_buffer: object, binary_as_string: bool = False, **kwargs: Any) -> DuckDBPyRelation

Alias of read_parquet().

from_query 

from_query(self: _duckdb.DuckDBPyConnection, query: object, *, alias: str = '', params: object = None) -> DuckDBPyRelation | None

Alias of sql().

query 

query(self: _duckdb.DuckDBPyConnection, query: object, *, alias: str = '', params: object = None) -> DuckDBPyRelation | None

Alias of sql().

read_csv 

read_csv(self: _duckdb.DuckDBPyConnection, *args: object, **kwargs: object) -> DuckDBPyRelation

Create a relation object from the CSV file in 'name'.

The options other than path_or_buffer are keyword arguments; an unknown one raises InvalidInputException. Most are the option of the same name of the SQL read_csv function; sep and delimiter are delim, dtype is dtypes, na_values is nullstr, skiprows is skip, quotechar is quote, escapechar is escape, date_format is dateformat, timestamp_format is timestampformat, lineterminator is new_line and decimal is decimal_separator.

Aliases: from_csv_auto

Parameters

  • path_or_buffer : str, list[str], file-like object

    Path to the file, a glob pattern, a list of paths or a file-like object such as io.StringIO or io.BytesIO.

  • header : bool, int, keyword-only, default: None

    Whether the first line holds the column names; 0 means True. Detected when omitted.

  • compression : str, keyword-only, default: None

    Compression of the files: 'none', 'gzip' or 'zstd'. Detected from the file extension when omitted.

  • sep : str, keyword-only, default: None

    Delimiter between the columns, up to 4 bytes; cannot be combined with delimiter. Detected when omitted.

  • delimiter : str, keyword-only, default: None

    Same as sep.

  • dtype : dict[str, str], list[str], keyword-only, default: None

    Column types, as a dict of column name to type or a list of types by position; a type is a type name or a DuckDBPyType.

  • na_values : str, list[str], keyword-only, default: None

    Strings that are read as NULL.

  • skiprows : int, keyword-only, default: None

    Number of lines to skip at the start of each file.

  • quotechar : str, keyword-only, default: None

    Character used to quote values.

  • escapechar : str, keyword-only, default: None

    Character that escapes the quote character inside a quoted value.

  • comment : str, keyword-only, default: None

    Character that starts a comment: a line is read only up to it, and a line that starts with it is skipped.

  • thousands : str, keyword-only, default: None

    Character that separates the thousands in numbers; it must differ from decimal.

  • encoding : str, keyword-only, default: None

    Only UTF-8 is accepted, written 'utf-8' or 'utf8'; any other encoding raises BinderException.

  • parallel : bool, keyword-only, default: None

    Whether to use the parallel CSV reader.

  • date_format : str, keyword-only, default: None

    Format used to parse dates.

  • timestamp_format : str, keyword-only, default: None

    Format used to parse timestamps.

  • sample_size : int, keyword-only, default: None

    Number of lines sampled to detect the dialect and the types.

  • files_to_sniff : int, keyword-only, default: None

    Number of files sampled to detect the schema when reading several files; -1 samples all of them.

  • auto_detect : bool, keyword-only, default: None

    Whether to detect the dialect, the header and the types. Detected when omitted.

  • all_varchar : bool, keyword-only, default: None

    Read every column as VARCHAR, without detecting the types.

  • normalize_names : bool, keyword-only, default: None

    Normalize the column names; for example, My Col becomes my_col.

  • null_padding : bool, keyword-only, default: None

    Pad a line that lacks columns with NULL values on the right.

  • strict_mode : bool, keyword-only, default: None

    Whether to refuse a file that is not structurally valid CSV; with False, the reader tries to read it anyway.

  • names : list[str], keyword-only, default: None

    Column names, as a list.

  • lineterminator : str, CSVLineTerminator, keyword-only, default: None

    The line ending: one of duckdb.CSVLineTerminator.LINE_FEED, duckdb.CSVLineTerminator.CARRIAGE_RETURN_LINE_FEED, '\\n' and '\\r\\n', the last two being escape text as written in Python; a string that holds the newline character itself, such as '\n', is refused.

  • columns : dict[str, str], keyword-only, default: None

    Column names and types, as a dict of column name to type name; the types are then not detected.

  • auto_type_candidates : list[str], keyword-only, default: None

    Types the reader may choose from when it detects the column types; VARCHAR is always one of them.

  • max_line_size : int, str, keyword-only, default: None

    Maximum line size, in bytes.

  • ignore_errors : bool, keyword-only, default: None

    Skip the lines that fail to parse.

  • store_rejects : bool, keyword-only, default: None

    Skip the lines with errors and store them in the rejects table.

  • rejects_table : str, keyword-only, default: None

    Name of the temporary table that holds the faulty lines.

  • rejects_scan : str, keyword-only, default: None

    Name of the temporary table that holds information on the scans that found faulty lines.

  • rejects_limit : int, keyword-only, default: None

    Maximum number of faulty lines stored for each file; 0 means no limit.

  • force_not_null : list[str], keyword-only, default: None

    Columns whose values are never read as NULL.

  • buffer_size : int, keyword-only, default: None

    Size of the buffers used to read the files, in bytes.

  • decimal : str, keyword-only, default: None

    Decimal separator of numbers.

  • allow_quoted_nulls : bool, keyword-only, default: None

    Whether a quoted value can be read as NULL.

  • filename : bool, keyword-only, default: None

    Add a column filename with the path of the file each row came from.

  • hive_partitioning : bool, keyword-only, default: None

    Interpret the path as a Hive partitioned path.

  • union_by_name : bool, keyword-only, default: None

    Align the columns of several files by name instead of by position.

  • hive_types : dict[str, str], keyword-only, default: None

    Types of the Hive partition columns, as a dict of column name to type name.

  • hive_types_autocast : bool, keyword-only, default: None

    Whether to detect the types of the Hive partition columns.

Example

import csv
import duckdb

duckdb_conn = duckdb.connect()

with open('code_example.csv', 'w', newline='') as csvfile:
    fieldnames = ['id', 'text']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerow({'id': '1', 'text': 'a'})

rel = duckdb_conn.read_csv("code_example.csv")

rel.show()

Result

┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘

read_json 

read_json(self: _duckdb.DuckDBPyConnection, path_or_buffer: object, **kwargs: Any) -> DuckDBPyRelation

Create a relation object from the JSON file in 'name'.

The options other than path_or_buffer are keyword arguments; an unknown one raises TypeError. Each is the option of the same name of the SQL read_json function.

Parameters

  • path_or_buffer : str, list[str], file-like object

    Path to the file, a glob pattern, a list of paths or a file-like object such as io.StringIO or io.BytesIO.

  • columns : dict[str, str], keyword-only, default: None

    Key names and types, as a dict of key name to type name; the schema is then not detected.

  • sample_size : int, keyword-only, default: None

    Number of objects sampled to detect the types; -1 samples all of them.

  • maximum_depth : int, keyword-only, default: None

    Maximum nesting depth to which the types are detected; -1 detects all levels.

  • records : str, keyword-only, default: None

    'true' to read each object as a row of columns, 'false' to read each as one value, 'auto' to detect it.

  • format : str, keyword-only, default: None

    'auto', 'unstructured', 'newline_delimited' or 'array'.

  • date_format : str, keyword-only, default: None

    Format used to parse dates.

  • timestamp_format : str, keyword-only, default: None

    Format used to parse timestamps.

  • compression : str, keyword-only, default: None

    Compression of the files: 'none', 'gzip', 'zstd' or 'auto_detect'. Detected from the file extension when omitted.

  • maximum_object_size : int, keyword-only, default: None

    Maximum size of a JSON object, in bytes.

  • ignore_errors : bool, keyword-only, default: None

    Read the objects that fail to parse as NULL rows; only with format='newline_delimited'.

  • convert_strings_to_integers : bool, keyword-only, default: None

    Whether strings that hold integers are read as numbers.

  • field_appearance_threshold : float, keyword-only, default: None

    When the fields of an object appear, on average, in a smaller fraction of the sampled objects than this, the object is read as a MAP.

  • map_inference_threshold : int, keyword-only, default: None

    An object with more fields than this is read as a MAP instead of a STRUCT; -1 never reads a MAP.

  • maximum_sample_files : int, keyword-only, default: None

    Maximum number of files sampled to detect the schema.

  • filename : bool, keyword-only, default: None

    Add a column filename with the path of the file each row came from.

  • hive_partitioning : bool, keyword-only, default: None

    Interpret the path as a Hive partitioned path.

  • union_by_name : bool, keyword-only, default: None

    Align the columns of several files by name instead of by position.

  • hive_types : dict[str, str], keyword-only, default: None

    Types of the Hive partition columns, as a dict of column name to type name.

  • hive_types_autocast : bool, keyword-only, default: None

    Whether to detect the types of the Hive partition columns.

Example

import duckdb
import json

with open("code_example.json", mode="w") as f:
    json.dump([{'id': 1, "text":"a"}], f)

duckdb_conn = duckdb.connect()

rel = duckdb_conn.read_json("code_example.json")

rel.show()

Result

┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘

read_parquet 

read_parquet(self: _duckdb.DuckDBPyConnection, path_or_buffer: object, binary_as_string: bool = False, **kwargs: Any) -> DuckDBPyRelation

Create a relation object from the Parquet path(s) or file-like object(s) in 'path_or_buffer'.

Each option is the option of the same name of the SQL read_parquet function.

Aliases: from_parquet

Parameters

  • path_or_buffer : str, list[str], file-like object

    Path to the Parquet file, a glob pattern, a list of paths or a file object opened in binary mode.

  • binary_as_string : bool, default: False

    Read binary columns as strings, for files from writers that do not mark their strings as UTF-8.

  • file_row_number : bool, keyword-only, default: False

    Add a column file_row_number with the number of each row within its file.

  • filename : bool, keyword-only, default: False

    Add a column filename with the path of the file each row came from.

  • hive_partitioning : bool, keyword-only, default: False

    Interpret the path as a Hive partitioned path.

  • union_by_name : bool, keyword-only, default: False

    Align the columns of several files by name instead of by position.

  • compression : str, keyword-only, default: None

    Accepted and ignored: a Parquet file records its own compression.

Example

import duckdb
import pyarrow as pa
import pyarrow.parquet as pq

ids = pa.array([1], type=pa.int8())
texts = pa.array(['a'], type=pa.string())
example_table = pa.table([ids, texts], names=["id", "text"])

pq.write_table(example_table, "code_example.parquet")

duckdb_conn = duckdb.connect()

rel = duckdb_conn.read_parquet("code_example.parquet")

rel.show()

Result

┌──────┬─────────┐
│  id  │  text   │
│ int8 │ varchar │
├──────┼─────────┤
│    1 │ a       │
└──────┴─────────┘

sql 

sql(self: _duckdb.DuckDBPyConnection, query: object, *, alias: str = '', params: object = None) -> DuckDBPyRelation | None

Run a SQL query. A statement that only reads returns a relation that runs the query each time it is read, parameter values bound at every run. Any other statement runs once, now, and gives a relation over what it returned. Statements before the last one run immediately.

Aliases: from_query, query

Parameters

  • query : str

    The SQL text; when it holds several statements, the ones before the last run at the call.

  • alias : str, keyword-only, default: ''

    Alias of the relation.

  • params : list, dict, keyword-only, default: None

    Values of the query parameters: a list for ? and $1 markers, a dict for $name markers. The list or dict is copied at the call, as is any list, tuple or dict inside it; the values in it are converted each time the relation runs, so a bytearray changed in place afterwards is read changed.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("SELECT * FROM range(1, 4) tbl(id) WHERE id > $minimum", params={"minimum": 1})

rel.show()

Result

┌───────┐
│  id   │
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘

table 

table(self: _duckdb.DuckDBPyConnection, table_name: str) -> DuckDBPyRelation

Create a relation object for the named table.

Parameters

  • table_name : str

    Name of the table to create a relation from.

Example

import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE code_example AS SELECT * FROM range(1, 2) tbl(id)")

rel = duckdb_conn.table("code_example")

rel.show()

Result

┌───────┐
│  id   │
│ int64 │
├───────┤
│     1 │
└───────┘

table_function 

table_function(self: _duckdb.DuckDBPyConnection, name: str, parameters: object = None) -> DuckDBPyRelation

Create a relation object from the named table function with given parameters.

Parameters

  • name : str

    Name of the table function to call.

  • parameters : list, default: None

    The arguments to pass to the table function, as a list.

Example

import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("""
    CREATE MACRO get_record_for(x) AS TABLE
    SELECT x * range AS result FROM range(1, 2)
""")

rel = duckdb_conn.table_function(name="get_record_for", parameters=[1])

rel.show()

Result

┌────────┐
│ result │
│ int64  │
├────────┤
│      1 │
└────────┘

values 

values(self: _duckdb.DuckDBPyConnection, *args: object) -> DuckDBPyRelation

Create a relation object from the passed values.

Python values are converted once, at the call, so a later change to the Python objects does not change the relation. Such a relation reads rows held in this process, which create_view() refuses.

Parameters

  • *args : list, tuple, object

    The rows: one list holding the values of one row, several tuples for several rows or the values of one row as separate arguments. A value is a Python value or an Expression.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.values([1, 'a'])

rel.show()

Result

┌───────┬─────────┐
│ col0  │  col1   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘

view 

view(self: _duckdb.DuckDBPyConnection, view_name: str) -> DuckDBPyRelation

Create a relation object for the named view.

Parameters

  • view_name : str

    Name of the view to create a relation from.

Example

import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE VIEW code_example AS SELECT * FROM range(1, 2) tbl(id)")

rel = duckdb_conn.view("code_example")

rel.show()

Result

┌───────┐
│  id   │
│ int64 │
├───────┤
│     1 │
└───────┘

Relation Definition Details

This section contains the details on how to inspect a relation.

Name Description
alias Get the name of the current alias.
columns Return a list containing the names of the columns of the relation.
describe Gives basic statistics (e.g., min, max) and if NULL exists for each column of the relation.
description Return the description of the result.
dtypes Alias of types.
explain The plan the engine runs for this relation, as text; under Jupyter the default is shown as HTML.
preview The first limit rows drawn as a table, with the footer __repr__ prints for the relation.
query Run the given SQL query in sql_query on the view named virtual_table_name that refers to the relation object.
set_alias Rename the relation object to new alias.
shape Tuple of # of rows, # of columns in relation.
show Display a summary of the data.
sql_query Get the SQL query that is equivalent to the relation; values given as params= stay markers.
type Get the type of the relation.
types Return a list containing the types of the columns of the relation.

alias

Get the name of the current alias.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.set_alias("my_relation").alias

Result

'my_relation'

columns

Return a list containing the names of the columns of the relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.columns

Result

['id', 'item', 'category', 'price', 'in_stock']

describe 

describe(self) -> DuckDBPyRelation

Gives basic statistics (e.g., min, max) and if NULL exists for each column of the relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.describe()

Result

┌─────────┬────────────────────┬─────────┬──────────┬───────────────────┬──────────┐
│  aggr   │         id         │  item   │ category │       price       │ in_stock │
│ varchar │       double       │ varchar │ varchar  │      double       │ varchar  │
├─────────┼────────────────────┼─────────┼──────────┼───────────────────┼──────────┤
│ count   │                6.0 │ 6       │ 6        │               6.0 │ 6        │
│ mean    │                3.5 │ NULL    │ NULL     │              38.5 │ NULL     │
│ stddev  │ 1.8708286933869707 │ NULL    │ NULL     │ 33.25507480069771 │ NULL     │
│ min     │                1.0 │ drill   │ clothing │               7.0 │ false    │
│ max     │                6.0 │ t-shirt │ tools    │              89.0 │ true     │
│ median  │                3.5 │ NULL    │ NULL     │              31.0 │ NULL     │
└─────────┴────────────────────┴─────────┴──────────┴───────────────────┴──────────┘

description

Return the description of the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.description

Result

[('id', INTEGER, None, None, None, None, None), ('item', VARCHAR, None, None, None, None, None), ('category', VARCHAR, None, None, None, None, None), ('price', INTEGER, None, None, None, None, None), ('in_stock', BOOLEAN, None, None, None, None, None)]

dtypes

Alias of types.

explain 

explain(self, type: object = ExplainType.STANDARD, format: str | None = None, *, analyze: bool = False) -> str

The plan the engine runs for this relation, as text; under Jupyter the default is shown as HTML.

With analyze=True or with type set to 'analyze' or duckdb.ExplainType.ANALYZE, the query runs and the plan shows the rows and the time of each operator, as EXPLAIN ANALYZE does.

Parameters

  • type : str, ExplainType, default: ExplainType.STANDARD

    'standard' or duckdb.ExplainType.STANDARD for the plan, 'analyze' or duckdb.ExplainType.ANALYZE to run the query and profile it.

  • format : str, default: None

    The output format, such as 'text', 'json', 'html', 'graphviz' or 'yaml'. The default draws the plan as text, and as HTML under Jupyter.

  • analyze : bool, keyword-only, default: False

    Run the query and profile it, as type='analyze' does.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

print(rel.explain())

Result

╭─ Column Data Scan ─╮
│ ~6 rows            │
╰────────────────────╯

preview 

preview(self, limit: int = 10) -> str

The first limit rows drawn as a table, with the footer __repr__ prints for the relation.

Parameters

  • limit : int, default: 10

    The number of rows to draw.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

print(rel.preview(3))

Result

┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     1 │ jeans   │ clothing │    20 │ true     │
│     2 │ t-shirt │ clothing │     8 │ false    │
│     3 │ jacket  │ clothing │    65 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘

query 

query(self, virtual_table_name: str, sql_query: str) -> DuckDBPyRelation | None

Run the given SQL query in sql_query on the view named virtual_table_name that refers to the relation object.

The name is this query's own: it is a common table expression over the relation, and a catalog entry for the length of the call, so a table function or a query that defines the name itself can bind against it. Nothing of it is left afterwards. A statement that does not only read, which includes a SELECT whose common table expression writes, runs at the call and the relation holds what it returned.

Parameters

  • virtual_table_name : str

    The name under which sql_query reads the relation.

  • sql_query : str

    The SQL query, a single statement, that reads the relation by the name virtual_table_name.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.query(virtual_table_name="items", sql_query="SELECT max(price) AS max_price FROM items")

Result

┌───────────┐
│ max_price │
│   int32   │
├───────────┤
│        89 │
└───────────┘

set_alias 

set_alias(self, alias: str) -> DuckDBPyRelation

Rename the relation object to new alias.

Parameters

  • alias : str

    The alias name to assign to the relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.set_alias("items").select("items.item, items.price")

Result

┌─────────────┬───────┐
│    item     │ price │
│   varchar   │ int32 │
├─────────────┼───────┤
│ jeans       │    20 │
│ t-shirt     │     8 │
│ jacket      │    65 │
│ hammer      │    42 │
│ screwdriver │     7 │
│ drill       │    89 │
└─────────────┴───────┘

shape

Tuple of # of rows, # of columns in relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.shape

Result

(6, 5)

show 

show(self, *, max_width: int | None = None, max_rows: int | None = None, max_col_width: int | None = None, null_value: str | None = None, render_mode: object = None) -> None

Display a summary of the data.

Parameters

  • max_width : int, keyword-only, default: None

    Maximum display width for the entire output in characters.

  • max_rows : int, keyword-only, default: None

    Maximum number of rows to display.

  • max_col_width : int, keyword-only, default: None

    Maximum number of characters to display per column.

  • null_value : str, keyword-only, default: None

    String to display in place of NULL values.

  • render_mode : RenderMode, keyword-only, default: None

    duckdb.RenderMode.ROWS to draw a row per line, duckdb.RenderMode.COLUMNS to draw a column per line.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.show()

Result

┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     1 │ jeans       │ clothing │    20 │ true     │
│     2 │ t-shirt     │ clothing │     8 │ false    │
│     3 │ jacket      │ clothing │    65 │ true     │
│     4 │ hammer      │ tools    │    42 │ true     │
│     5 │ screwdriver │ tools    │     7 │ true     │
│     6 │ drill       │ tools    │    89 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘

sql_query 

sql_query(self) -> str

Get the SQL query that is equivalent to the relation; values given as params= stay markers.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

print(rel.sql_query())

Result

SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)

type

Get the type of the relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.type

Result

'QUERY_RELATION'

types

Return a list containing the types of the columns of the relation.

Aliases: dtypes

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.types

Result

[INTEGER, VARCHAR, VARCHAR, INTEGER, BOOLEAN]

Transformation

This section contains the methods which can be used to chain queries. They build a new query from the relation and run nothing.

Name Description
aggregate Compute the aggregate aggr_expr by the optional groups group_expr on the relation.
apply Compute the function of a single column or a list of columns by the optional groups on the relation.
cross Create cross/cartesian product of two relational objects.
distinct Retrieve distinct rows from this relation object, or the first row of each distinct value of on.
drop Remove columns.
except_ Create the set except of this relation object with another relation object in other_rel.
filter Filter the relation object by the filter in filter_expr.
group_by Begin a grouped aggregation; continue with .agg(...).
head The first n rows.
intersect Create the set intersection of this relation object with another relation object in other_rel.
join Join the relation object with another relation object in other_rel.
limit Only retrieve the first n rows from this relation object, starting at offset.
map Calls the passed function on the relation.
offset Skip n rows.
order Reorder the relation object by order_expr, which is SQL order text or an expression.
pivot Turn the given values of column into columns holding aggregate; the values are explicit.
project Alias of select().
rename Rename columns, given as old=new.
sample A random subset: n rows, or percent of them, with seed repeating a draw.
select Project the relation object by the projection in project_expr.
select_dtypes Alias of select_types().
select_types Select columns from the relation, by filtering based on type(s).
sort Reorder the relation object by the provided expressions.
union Create the set union of this relation object with another relation object in other_rel.
union_by_name Rows from both relations, matching columns by name; keeps duplicates unless all is false.
unnest Expand list columns to one row per element.
unpivot Turn the named columns into rows; name and value name the two new columns.
with_columns Add columns, replacing any of the same name in place.

aggregate 

aggregate(self, aggr_expr: object, group_expr: object = '') -> DuckDBPyRelation

Compute the aggregate aggr_expr by the optional groups group_expr on the relation.

Parameters

  • aggr_expr : str, list[Expression]

    The columns and aggregates to compute: SQL text, or a list of Expressions, in which a string is a column name.

  • group_expr : str, Expression, None, default: ''

    The columns to group by, as SQL text or an Expression. '' or None groups by every column of aggr_expr that is not an aggregate (GROUP BY ALL).

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.aggregate("category, max(price) AS max_price").order("category")

Result

┌──────────┬───────────┐
│ category │ max_price │
│ varchar  │   int32   │
├──────────┼───────────┤
│ clothing │        65 │
│ tools    │        89 │
└──────────┴───────────┘

apply 

apply(self, function_name: str, function_aggr: str, group_expr: str = '', function_parameter: str = '', projected_columns: str = '') -> DuckDBPyRelation

Compute the function of a single column or a list of columns by the optional groups on the relation.

Parameters

  • function_name : str

    Name of the function to apply over the relation.

  • function_aggr : str

    The list of columns to apply the function over.

  • group_expr : str, default: ''

    Optional SQL expression for grouping.

  • function_parameter : str, default: ''

    Optional parameters to pass into the function.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.apply("max", "price", group_expr="category", projected_columns="category").order("category")

Result

┌──────────┬────────────┐
│ category │ max(price) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │         65 │
│ tools    │         89 │
└──────────┴────────────┘

cross 

cross(self, other_rel: DuckDBPyRelation) -> DuckDBPyRelation

Create cross/cartesian product of two relational objects.

Parameters

  • other_rel : DuckDBPyRelation

    Another relation to perform a cross product with.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

sizes = duckdb_conn.sql("SELECT * FROM (VALUES ('S'), ('M')) AS sizes(size)")

rel.filter("category = 'clothing'").select("item").cross(sizes).order("item, size")

Result

┌─────────┬─────────┐
│  item   │  size   │
│ varchar │ varchar │
├─────────┼─────────┤
│ jacket  │ M       │
│ jacket  │ S       │
│ jeans   │ M       │
│ jeans   │ S       │
│ t-shirt │ M       │
│ t-shirt │ S       │
└─────────┴─────────┘

distinct 

distinct(self, on: object = None) -> DuckDBPyRelation

Retrieve distinct rows from this relation object, or the first row of each distinct value of on.

Parameters

  • on : str, Expression, list, default: None

    A column name, an Expression or a list of them: keep one row for each distinct value of these columns, as SQL DISTINCT ON does. Omitted, only fully duplicate rows are removed.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select("category").distinct().order("category")

Result

┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ tools    │
└──────────┘

drop 

drop(self, *columns: str) -> DuckDBPyRelation

Remove columns.

Parameters

  • *columns : str

    Names of the columns to remove; a name the relation does not have raises BinderException.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.drop("id", "in_stock")

Result

┌─────────────┬──────────┬───────┐
│    item     │ category │ price │
│   varchar   │ varchar  │ int32 │
├─────────────┼──────────┼───────┤
│ jeans       │ clothing │    20 │
│ t-shirt     │ clothing │     8 │
│ jacket      │ clothing │    65 │
│ hammer      │ tools    │    42 │
│ screwdriver │ tools    │     7 │
│ drill       │ tools    │    89 │
└─────────────┴──────────┴───────┘

except_ 

except_(self, other_rel: DuckDBPyRelation) -> DuckDBPyRelation

Create the set except of this relation object with another relation object in other_rel.

The difference is EXCEPT ALL, so duplicates count: a row that appears three times in this relation and once in other_rel appears twice in the result.

Parameters

  • other_rel : DuckDBPyRelation

    The relation to combine with this one; it must have the same number of columns.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

categories = rel.select("category")
expensive = rel.filter("price > 30").select("category")

categories.except_(expensive).order("category")

Result

┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ clothing │
│ tools    │
└──────────┘

filter 

filter(self, filter_expr: object) -> DuckDBPyRelation

Filter the relation object by the filter in filter_expr.

Parameters

  • filter_expr : str, Expression

    The filter expression to apply over the relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.filter("price > 30")

Result

┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     3 │ jacket  │ clothing │    65 │ true     │
│     4 │ hammer  │ tools    │    42 │ true     │
│     6 │ drill   │ tools    │    89 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘

group_by 

group_by(self, *columns: object) -> GroupedRelation

Begin a grouped aggregation; continue with .agg(...).

group_by() returns a grouped relation whose only method is agg(*aggregates). agg() reads its arguments as aggregate() reads aggr_expr: one string is SQL text, which may list several aggregates; among several arguments, a string is a column name and an Expression is an aggregate. It returns a relation whose first columns are the group columns.

Parameters

  • *columns : str, Expression

    The columns to group by: column names or Expressions.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.group_by("category").agg("sum(price) AS total, count(*) AS n").order("category")

Result

┌──────────┬────────┬───────┐
│ category │ total  │   n   │
│ varchar  │ int128 │ int64 │
├──────────┼────────┼───────┤
│ clothing │     93 │     3 │
│ tools    │    138 │     3 │
└──────────┴────────┴───────┘

head(self, n: int = 5) -> DuckDBPyRelation

The first n rows.

Parameters

  • n : int, default: 5

    The number of rows to keep.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.head(3)

Result

┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     1 │ jeans   │ clothing │    20 │ true     │
│     2 │ t-shirt │ clothing │     8 │ false    │
│     3 │ jacket  │ clothing │    65 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘

intersect 

intersect(self, other_rel: DuckDBPyRelation) -> DuckDBPyRelation

Create the set intersection of this relation object with another relation object in other_rel.

The intersection is INTERSECT ALL, so duplicates count: a row that appears three times in this relation and twice in other_rel appears twice in the result.

Parameters

  • other_rel : DuckDBPyRelation

    The relation to combine with this one; it must have the same number of columns.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

categories = rel.select("category")
expensive = rel.filter("price > 30").select("category")

categories.intersect(expensive).order("category")

Result

┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ tools    │
│ tools    │
└──────────┘

join 

join(self, other_rel: object, condition: object = None, how: str = 'inner', suffix: str | None = None) -> DuckDBPyRelation

Join the relation object with another relation object in other_rel.

The condition is SQL text, a column name or a list of them for a USING join, an Expression, or a function of the two sides. Types supported are 'inner', 'left', 'right', 'outer', 'full', 'semi', 'anti', 'cross', 'positional', 'natural' and 'asof'. With suffix, a column of the right side whose name the left side already has is renamed with the suffix appended.

An 'inner', 'left', 'right', 'outer', 'full', 'semi', 'anti' or 'asof' join needs a condition, and raises InvalidInputException without one; a 'cross', 'positional' or 'natural' join takes none. The joins section of the FROM clause says what each join type does. A CROSS join is also provided through the cross() method.

Parameters

  • other_rel : DuckDBPyRelation

    The relation to join with the current relation.

  • condition : str, list[str], Expression, Callable, default: None

    The join condition: SQL text such as 'a.id = b.id', a column name or a list of column names for a USING join, an Expression or a function that takes the two sides and returns an Expression, where side['id'] is the column id of that side.

  • how : str, default: 'inner'

    The type of join: 'inner', 'left', 'right', 'outer', 'full', 'semi', 'anti', 'cross', 'positional', 'natural' or 'asof'.

  • suffix : str, default: None

    Rename each column of the right side whose name the left side also has, by appending the suffix; the columns of a USING or natural join, which appear once, keep their names.

Example

import duckdb

duckdb_conn = duckdb.connect()

left = duckdb_conn.sql("SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS t(id, name)")
right = duckdb_conn.sql("SELECT * FROM (VALUES (1, 'x'), (3, 'z'), (4, 'w')) AS t(id, name)")

print(left.join(right, "id").order("id"))

left.join(right, lambda a, b: a["id"] == b["id"], how="left", suffix="_right").order("id")

Result

┌───────┬─────────┬─────────┐
│  id   │  name   │  name   │
│ int32 │ varchar │ varchar │
├───────┼─────────┼─────────┤
│     1 │ a       │ x       │
│     3 │ c       │ z       │
└───────┴─────────┴─────────┘

┌───────┬─────────┬──────────┬────────────┐
│  id   │  name   │ id_right │ name_right │
│ int32 │ varchar │  int32   │  varchar   │
├───────┼─────────┼──────────┼────────────┤
│     1 │ a       │        1 │ x          │
│     2 │ b       │     NULL │ NULL       │
│     3 │ c       │        3 │ z          │
└───────┴─────────┴──────────┴────────────┘

limit 

limit(self, n: int, offset: int = 0) -> DuckDBPyRelation

Only retrieve the first n rows from this relation object, starting at offset.

To read the first rows into Python, as fetchone() and fetchmany() did on a relation before DuckDB 2.0, use rel.limit(1).fetchall() and rel.limit(n).fetchall(). To read all the rows a batch at a time, use iter_rows().

Parameters

  • n : int

    The maximum number of rows to return.

  • offset : int, default: 0

    The number of rows to skip before starting to return rows.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.limit(1)

Result

┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     1 │ jeans   │ clothing │    20 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘

map 

map(self, map_function: Callable[..., Any], *, schema: object = None) -> DuckDBPyRelation

Calls the passed function on the relation.

Parameters

  • map_function : Callable

    A Python function that takes a DataFrame and returns a transformed DataFrame.

  • schema : dict, keyword-only, default: None

    The columns of the output relation, as a dict of name to type. Without it, the function is called once on an empty DataFrame when the relation is built, to learn the columns it returns.

Example

import duckdb
from pandas import DataFrame

def multiply_by_2(df: DataFrame):
    df["id"] = df["id"] * 2
    return df

duckdb_conn = duckdb.connect()
rel = duckdb_conn.sql("SELECT range AS id, 'dummy' AS text FROM range(1, 3)")

rel.map(multiply_by_2, schema={"id": int, "text": str})

Result

┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     2 │ dummy   │
│     4 │ dummy   │
└───────┴─────────┘

offset 

offset(self, n: int) -> DuckDBPyRelation

Skip n rows.

Parameters

  • n : int

    The number of rows to skip.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.order("id").offset(4)

Result

┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     5 │ screwdriver │ tools    │     7 │ true     │
│     6 │ drill       │ tools    │    89 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘

order 

order(self, order_expr: object) -> DuckDBPyRelation

Reorder the relation object by order_expr, which is SQL order text or an expression.

Parameters

  • order_expr : str, Expression

    The ordering of the rows: SQL text such as 'price DESC', or an Expression such as col('price').desc().

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.order("price DESC")

Result

┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     6 │ drill       │ tools    │    89 │ true     │
│     3 │ jacket      │ clothing │    65 │ true     │
│     4 │ hammer      │ tools    │    42 │ true     │
│     1 │ jeans       │ clothing │    20 │ true     │
│     2 │ t-shirt     │ clothing │     8 │ false    │
│     5 │ screwdriver │ tools    │     7 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘

pivot 

pivot(self, column: object, values: Iterable[object], aggregate: object) -> DuckDBPyRelation

Turn the given values of column into columns holding aggregate; the values are explicit.

It runs a PIVOT with the values given; the columns not named in column or aggregate are the rows of the result.

Parameters

  • column : str, Expression

    The column whose values become the new columns.

  • values : Iterable

    The values of column that become columns, as Python values.

  • aggregate : str, Expression

    The aggregate each new column holds, as SQL text or an Expression.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select("in_stock, category, price").pivot("category", ["clothing", "tools"], "sum(price)").order("in_stock")

Result

┌──────────┬──────────┬────────┐
│ in_stock │ clothing │ tools  │
│ boolean  │  int128  │ int128 │
├──────────┼──────────┼────────┤
│ false    │        8 │   NULL │
│ true     │       85 │    138 │
└──────────┴──────────┴────────┘

project 

project(self, *args: object, groups: str = '') -> DuckDBPyRelation

Alias of select().

rename 

rename(self, **columns: str) -> DuckDBPyRelation

Rename columns, given as old=new.

Parameters

  • **columns : str

    The columns to rename, as old=new; a name the relation does not have raises BinderException.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.rename(price="unit_price")

Result

┌───────┬─────────────┬──────────┬────────────┬──────────┐
│  id   │    item     │ category │ unit_price │ in_stock │
│ int32 │   varchar   │ varchar  │   int32    │ boolean  │
├───────┼─────────────┼──────────┼────────────┼──────────┤
│     1 │ jeans       │ clothing │         20 │ true     │
│     2 │ t-shirt     │ clothing │          8 │ false    │
│     3 │ jacket      │ clothing │         65 │ true     │
│     4 │ hammer      │ tools    │         42 │ true     │
│     5 │ screwdriver │ tools    │          7 │ true     │
│     6 │ drill       │ tools    │         89 │ true     │
└───────┴─────────────┴──────────┴────────────┴──────────┘

sample 

sample(self, n: int | None = None, *, percent: float | None = None, seed: int | None = None, method: str | None = None) -> DuckDBPyRelation

A random subset: n rows, or percent of them, with seed repeating a draw.

See Samples for the sampling methods. A fixed seed gives the same sample on every run only when DuckDB runs on one thread.

Parameters

  • n : int, default: None

    The number of rows to draw; give either n or percent.

  • percent : float, keyword-only, default: None

    The percentage of the rows to draw.

  • seed : int, keyword-only, default: None

    The seed of the random draw.

  • method : str, keyword-only, default: None

    'reservoir', 'bernoulli' or 'system'; 'reservoir' for n and 'bernoulli' for percent when omitted.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

duckdb_conn.sql("SET threads = 1")

rel.sample(3, seed=42).order("id")

Result

┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     3 │ jacket  │ clothing │    65 │ true     │
│     4 │ hammer  │ tools    │    42 │ true     │
│     6 │ drill   │ tools    │    89 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘

select 

select(self, *args: object, groups: str = '') -> DuckDBPyRelation

Project the relation object by the projection in project_expr.

Aliases: project

Parameters

  • *args : str, Expression

    The columns to keep, as strings or Expressions. A single string is SQL text, which may list several columns or expressions; among several arguments, each string names one column.

  • groups : str, keyword-only, default: ''

    Comma-separated list of columns to include in the GROUP BY.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select("item, price")

Result

┌─────────────┬───────┐
│    item     │ price │
│   varchar   │ int32 │
├─────────────┼───────┤
│ jeans       │    20 │
│ t-shirt     │     8 │
│ jacket      │    65 │
│ hammer      │    42 │
│ screwdriver │     7 │
│ drill       │    89 │
└─────────────┴───────┘

select_dtypes 

select_dtypes(self, types: object) -> DuckDBPyRelation

Alias of select_types().

select_types 

select_types(self, types: object) -> DuckDBPyRelation

Select columns from the relation, by filtering based on type(s).

Aliases: select_dtypes

Parameters

  • types : list

    The types of the columns to keep, as a list of DuckDBPyType values or type names such as 'VARCHAR'.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select_types([duckdb.sqltypes.VARCHAR])

Result

┌─────────────┬──────────┐
│    item     │ category │
│   varchar   │ varchar  │
├─────────────┼──────────┤
│ jeans       │ clothing │
│ t-shirt     │ clothing │
│ jacket      │ clothing │
│ hammer      │ tools    │
│ screwdriver │ tools    │
│ drill       │ tools    │
└─────────────┴──────────┘

sort 

sort(self, *args: object) -> DuckDBPyRelation

Reorder the relation object by the provided expressions.

Parameters

  • *args : str, Expression

    The expressions to sort by, as SQL text such as 'price DESC' or as Expressions.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.sort("category", "price DESC")

Result

┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     3 │ jacket      │ clothing │    65 │ true     │
│     1 │ jeans       │ clothing │    20 │ true     │
│     2 │ t-shirt     │ clothing │     8 │ false    │
│     6 │ drill       │ tools    │    89 │ true     │
│     4 │ hammer      │ tools    │    42 │ true     │
│     5 │ screwdriver │ tools    │     7 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘

union 

union(self, union_rel: DuckDBPyRelation) -> DuckDBPyRelation

Create the set union of this relation object with another relation object in other_rel.

The union is UNION ALL, so it keeps duplicate rows. To keep only distinct rows, apply distinct().

Parameters

  • union_rel : DuckDBPyRelation

    The relation to combine with this one; it must have the same number of columns.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.union(rel).count("*")

Result

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│           12 │
└──────────────┘

union_by_name 

union_by_name(self, other: DuckDBPyRelation, *, all: bool = True) -> DuckDBPyRelation

Rows from both relations, matching columns by name; keeps duplicates unless all is false.

Parameters

  • other : DuckDBPyRelation

    The relation to combine with this one; a column that only one of them has is NULL in the rows of the other.

  • all : bool, keyword-only, default: True

    Keep duplicate rows (UNION ALL BY NAME); with False, keep only distinct rows (UNION BY NAME).

Example

import duckdb

duckdb_conn = duckdb.connect()

first = duckdb_conn.sql("SELECT 1 AS id, 10 AS x")
second = duckdb_conn.sql("SELECT 20 AS y, 2 AS id")

first.union_by_name(second).order("id")

Result

┌───────┬───────┬───────┐
│  id   │   x   │   y   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     1 │    10 │  NULL │
│     2 │  NULL │    20 │
└───────┴───────┴───────┘

unnest 

unnest(self, *columns: str) -> DuckDBPyRelation

Expand list columns to one row per element.

Parameters

  • *columns : str

    Names of the list columns to expand; each keeps its name and holds one element per row.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("SELECT 1 AS id, [10, 20] AS amounts UNION ALL SELECT 2, [30]")

rel.unnest("amounts").order("id, amounts")

Result

┌───────┬─────────┐
│  id   │ amounts │
│ int32 │  int32  │
├───────┼─────────┤
│     1 │      10 │
│     1 │      20 │
│     2 │      30 │
└───────┴─────────┘

unpivot 

unpivot(self, *columns: str, name: str = 'name', value: str = 'value') -> DuckDBPyRelation

Turn the named columns into rows; name and value name the two new columns.

It runs an UNPIVOT; the columns not named stay as they are, repeated for each new row.

Parameters

  • *columns : str

    Names of the columns to turn into rows.

  • name : str, keyword-only, default: 'name'

    Name of the new column that holds the names of the unpivoted columns.

  • value : str, keyword-only, default: 'value'

    Name of the new column that holds their values.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("SELECT * FROM (VALUES ('north', 10, 20), ('south', 30, 40)) AS t(region, q1, q2)")

rel.unpivot("q1", "q2", name="quarter", value="amount").order("region, quarter")

Result

┌─────────┬─────────┬────────┐
│ region  │ quarter │ amount │
│ varchar │ varchar │ int32  │
├─────────┼─────────┼────────┤
│ north   │ q1      │     10 │
│ north   │ q2      │     20 │
│ south   │ q1      │     30 │
│ south   │ q2      │     40 │
└─────────┴─────────┴────────┘

with_columns 

with_columns(self, **columns: object) -> DuckDBPyRelation

Add columns, replacing any of the same name in place.

Parameters

  • **columns : Expression, object

    The columns, as name=value, where a value is an Expression, a column name or a Python value; a column of the same name is replaced in place, any other is added at the end.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

from duckdb import col, lit

rel.with_columns(price=col("price") * 2, currency=lit("EUR"))

Result

┌───────┬─────────────┬──────────┬───────┬──────────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │ currency │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │ varchar  │
├───────┼─────────────┼──────────┼───────┼──────────┼──────────┤
│     1 │ jeans       │ clothing │    40 │ true     │ EUR      │
│     2 │ t-shirt     │ clothing │    16 │ false    │ EUR      │
│     3 │ jacket      │ clothing │   130 │ true     │ EUR      │
│     4 │ hammer      │ tools    │    84 │ true     │ EUR      │
│     5 │ screwdriver │ tools    │    14 │ true     │ EUR      │
│     6 │ drill       │ tools    │   178 │ true     │ EUR      │
└───────┴─────────────┴──────────┴───────┴──────────┴──────────┘

Functions

This section contains the functions which can be applied to a relation, in order to get a (scalar) result. They build a new query and run nothing.

Name Description
any_value Returns the first non-null value from a given expression.
arg_max Finds the row with the maximum value for a value column and returns the value of that row for an argument column.
arg_min Finds the row with the minimum value for a value column and returns the value of that row for an argument column.
avg Computes the average of a given expression.
bit_and Computes the bitwise AND of all bits present in a given expression.
bit_or Computes the bitwise OR of all bits present in a given expression.
bit_xor Computes the bitwise XOR of all bits present in a given expression.
bitstring_agg Computes a bitstring with bits set for each distinct value in a given expression.
bool_and Computes the logical AND of all values present in a given expression.
bool_or Computes the logical OR of all values present in a given expression.
count Computes the number of elements present in a given expression.
cume_dist Computes the cumulative distribution within the partition.
dense_rank Computes the dense rank within the partition.
favg Computes the average of all values present in a given expression using a more accurate floating point summation (Kahan Sum).
first Returns the first value of a given expression.
first_value Computes the first value within the group or partition.
fsum Computes the sum of all values present in a given expression using a more accurate floating point summation (Kahan Sum).
geomean Computes the geometric mean over all values present in a given expression.
histogram Computes the histogram over all values present in a given expression.
lag Computes the lag within the partition.
last Returns the last value of a given expression.
last_value Computes the last value within the group or partition.
lead Computes the lead within the partition.
list Returns a list containing all values present in a given expression.
max Returns the maximum value present in a given expression.
mean Alias of avg().
median Computes the median over all values present in a given expression.
min Returns the minimum value present in a given expression.
mode Computes the mode over all values present in a given expression.
n_tile Divides the partition as equally as possible into num_buckets.
nth_value Computes the nth value within the partition.
percent_rank Computes the relative rank within the partition.
product Returns the product of all values present in a given expression.
quantile Alias of quantile_disc().
quantile_cont Computes the interpolated quantile value for a given expression.
quantile_disc Computes the exact quantile value for a given expression.
rank Computes the rank within the partition.
rank_dense Alias of dense_rank().
row_number Computes the row number within the partition.
std Alias of stddev_samp().
stddev Alias of stddev_samp().
stddev_pop Computes the population standard deviation for a given expression.
stddev_samp Computes the sample standard deviation for a given expression.
string_agg Concatenates the values present in a given expression with a separator.
sum Computes the sum of all values present in a given expression.
unique Returns the distinct values in a column.
value_counts Computes the number of elements present in a given expression, also projecting the original expression.
var Alias of var_samp().
var_pop Computes the population variance for a given expression.
var_samp Computes the sample variance for a given expression.
variance Alias of var_samp().

any_value 

any_value(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Returns the first non-null value from a given expression.

Parameters

  • expression : str

    The column or expression from which to retrieve any value.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.any_value("item")

Result

┌─────────────────┐
│ any_value(item) │
│     varchar     │
├─────────────────┤
│ jeans           │
└─────────────────┘

arg_max 

arg_max(self, arg_column: str, value_column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Finds the row with the maximum value for a value column and returns the value of that row for an argument column.

Parameters

  • arg_column : str

    The column whose value is returned, taken from the row where value_column is largest.

  • value_column : str

    The column whose largest value picks the row.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.arg_max("item", "price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬──────────────────────┐
│ category │ arg_max(item, price) │
│ varchar  │       varchar        │
├──────────┼──────────────────────┤
│ clothing │ jacket               │
│ tools    │ drill                │
└──────────┴──────────────────────┘

arg_min 

arg_min(self, arg_column: str, value_column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Finds the row with the minimum value for a value column and returns the value of that row for an argument column.

Parameters

  • arg_column : str

    The column whose value is returned, taken from the row where value_column is smallest.

  • value_column : str

    The column whose smallest value picks the row.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.arg_min("item", "price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬──────────────────────┐
│ category │ arg_min(item, price) │
│ varchar  │       varchar        │
├──────────┼──────────────────────┤
│ clothing │ t-shirt              │
│ tools    │ screwdriver          │
└──────────┴──────────────────────┘

avg 

avg(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the average of a given expression.

Aliases: mean

Parameters

  • expression : str

    The column or expression to calculate the average of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.avg("price")

Result

┌────────────┐
│ avg(price) │
│   double   │
├────────────┤
│       38.5 │
└────────────┘

bit_and 

bit_and(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the bitwise AND of all bits present in a given expression.

Parameters

  • expression : str

    The column or expression to perform the bitwise AND aggregation on.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bit_and("id", groups="category", projected_columns="category").order("category")

Result

┌──────────┬─────────────┐
│ category │ bit_and(id) │
│ varchar  │    int32    │
├──────────┼─────────────┤
│ clothing │           0 │
│ tools    │           4 │
└──────────┴─────────────┘

bit_or 

bit_or(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the bitwise OR of all bits present in a given expression.

Parameters

  • expression : str

    The column or expression to perform the bitwise OR aggregation on.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bit_or("id", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────┐
│ category │ bit_or(id) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │          3 │
│ tools    │          7 │
└──────────┴────────────┘

bit_xor 

bit_xor(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the bitwise XOR of all bits present in a given expression.

Parameters

  • expression : str

    The column or expression to perform the bitwise XOR aggregation on.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bit_xor("id", groups="category", projected_columns="category").order("category")

Result

┌──────────┬─────────────┐
│ category │ bit_xor(id) │
│ varchar  │    int32    │
├──────────┼─────────────┤
│ clothing │           0 │
│ tools    │           7 │
└──────────┴─────────────┘

bitstring_agg 

bitstring_agg(self, expression: str, min: int | None = None, max: int | None = None, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes a bitstring with bits set for each distinct value in a given expression.

Parameters

  • expression : str

    The column or expression to aggregate as a bitstring.

  • min : int, default: None

    The smallest value the bitstring can hold; give both min and max, or neither.

  • max : int, default: None

    The largest value the bitstring can hold; give both min and max, or neither.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bitstring_agg("id", min=1, max=6, groups="category", projected_columns="category").order("category")

Result

┌──────────┬─────────────────────────┐
│ category │ bitstring_agg(id, 1, 6) │
│ varchar  │           bit           │
├──────────┼─────────────────────────┤
│ clothing │ 111000                  │
│ tools    │ 000111                  │
└──────────┴─────────────────────────┘

bool_and 

bool_and(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the logical AND of all values present in a given expression.

Parameters

  • expression : str

    The column or expression to perform the boolean AND aggregation on.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bool_and("in_stock", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────┐
│ category │ bool_and(in_stock) │
│ varchar  │      boolean       │
├──────────┼────────────────────┤
│ clothing │ false              │
│ tools    │ true               │
└──────────┴────────────────────┘

bool_or 

bool_or(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the logical OR of all values present in a given expression.

Parameters

  • expression : str

    The column or expression to perform the boolean OR aggregation on.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bool_or("in_stock", groups="category", projected_columns="category").order("category")

Result

┌──────────┬───────────────────┐
│ category │ bool_or(in_stock) │
│ varchar  │      boolean      │
├──────────┼───────────────────┤
│ clothing │ true              │
│ tools    │ true              │
└──────────┴───────────────────┘

count 

count(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the number of elements present in a given expression.

Parameters

  • expression : str

    The column or expression to count the values of; '*' counts the rows.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.count("id")

Result

┌───────────┐
│ count(id) │
│   int64   │
├───────────┤
│         6 │
└───────────┘

cume_dist 

cume_dist(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation

Computes the cumulative distribution within the partition.

Parameters

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.cume_dist(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")

Result

┌──────────┬─────────────┬───────┬─────────────────────────────────────────────────────────┐
│ category │    item     │ price │ cume_dist() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                         double                          │
├──────────┼─────────────┼───────┼─────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                      0.3333333333333333 │
│ clothing │ jeans       │    20 │                                      0.6666666666666666 │
│ clothing │ jacket      │    65 │                                                     1.0 │
│ tools    │ screwdriver │     7 │                                      0.3333333333333333 │
│ tools    │ hammer      │    42 │                                      0.6666666666666666 │
│ tools    │ drill       │    89 │                                                     1.0 │
└──────────┴─────────────┴───────┴─────────────────────────────────────────────────────────┘

dense_rank 

dense_rank(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation

Computes the dense rank within the partition.

Aliases: rank_dense

Parameters

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.dense_rank(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")

Result

┌──────────┬─────────────┬───────┬──────────────────────────────────────────────────────────┐
│ category │    item     │ price │ dense_rank() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                          int64                           │
├──────────┼─────────────┼───────┼──────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                        1 │
│ clothing │ jeans       │    20 │                                                        2 │
│ clothing │ jacket      │    65 │                                                        3 │
│ tools    │ screwdriver │     7 │                                                        1 │
│ tools    │ hammer      │    42 │                                                        2 │
│ tools    │ drill       │    89 │                                                        3 │
└──────────┴─────────────┴───────┴──────────────────────────────────────────────────────────┘

favg 

favg(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the average of all values present in a given expression using a more accurate floating point summation (Kahan Sum).

Parameters

  • expression : str

    The column or expression to calculate the average of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.favg("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬─────────────┐
│ category │ favg(price) │
│ varchar  │   double    │
├──────────┼─────────────┤
│ clothing │        31.0 │
│ tools    │        46.0 │
└──────────┴─────────────┘

first 

first(self, expression: str, groups: str = '', projected_columns: str = '') -> DuckDBPyRelation

Returns the first value of a given expression.

Parameters

  • expression : str

    The column or expression from which to retrieve the first value.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.first("item", groups="category", projected_columns="category").order("category")

Result

┌──────────┬───────────────┐
│ category │ "first"(item) │
│ varchar  │    varchar    │
├──────────┼───────────────┤
│ clothing │ jeans         │
│ tools    │ hammer        │
└──────────┴───────────────┘

first_value 

first_value(self, expression: str, window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the first value within the group or partition.

Parameters

  • expression : str

    The column or expression from which to retrieve the first value within the window.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.first_value("item", window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category").distinct().order("category")

Result

┌──────────┬───────────────────────────────────────────────────────────────┐
│ category │ first_value(item) OVER (PARTITION BY category ORDER BY price) │
│ varchar  │                            varchar                            │
├──────────┼───────────────────────────────────────────────────────────────┤
│ clothing │ t-shirt                                                       │
│ tools    │ screwdriver                                                   │
└──────────┴───────────────────────────────────────────────────────────────┘

fsum 

fsum(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the sum of all values present in a given expression using a more accurate floating point summation (Kahan Sum).

Parameters

  • expression : str

    The column or expression to calculate the sum of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.fsum("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬─────────────┐
│ category │ fsum(price) │
│ varchar  │   double    │
├──────────┼─────────────┤
│ clothing │        93.0 │
│ tools    │       138.0 │
└──────────┴─────────────┘

geomean 

geomean(self, expression: str, groups: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the geometric mean over all values present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the geometric mean of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.geomean("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────┐
│ category │   geomean(price)   │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │ 21.827857661222115 │
│ tools    │  29.68787497217981 │
└──────────┴────────────────────┘

histogram 

histogram(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the histogram over all values present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the histogram of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.histogram("in_stock", groups="category", projected_columns="category").order("category")

Result

┌──────────┬───────────────────────┐
│ category │  histogram(in_stock)  │
│ varchar  │ map(boolean, ubigint) │
├──────────┼───────────────────────┤
│ clothing │ {false=1, true=2}     │
│ tools    │ {true=3}              │
└──────────┴───────────────────────┘

lag 

lag(self, expression: str, window_spec: str, offset: int = 1, default_value: str = 'NULL', ignore_nulls: bool = False, projected_columns: str = '') -> DuckDBPyRelation

Computes the lag within the partition.

Parameters

  • expression : str

    The column or expression to apply the lag function on.

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • offset : int, default: 1

    The number of rows to lag behind.

  • default_value : str, default: 'NULL'

    The value, as SQL text, to return when the lag offset goes out of bounds.

  • ignore_nulls : bool, default: False

    Whether to ignore NULL values when computing the lag.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.lag("item", window_spec="OVER (ORDER BY price)", projected_columns="item, price").order("price")

Result

┌─────────────┬───────┬──────────────────────────────────────────┐
│    item     │ price │ lag(item, 1, NULL) OVER (ORDER BY price) │
│   varchar   │ int32 │                 varchar                  │
├─────────────┼───────┼──────────────────────────────────────────┤
│ screwdriver │     7 │ NULL                                     │
│ t-shirt     │     8 │ screwdriver                              │
│ jeans       │    20 │ t-shirt                                  │
│ hammer      │    42 │ jeans                                    │
│ jacket      │    65 │ hammer                                   │
│ drill       │    89 │ jacket                                   │
└─────────────┴───────┴──────────────────────────────────────────┘

last 

last(self, expression: str, groups: str = '', projected_columns: str = '') -> DuckDBPyRelation

Returns the last value of a given expression.

Parameters

  • expression : str

    The column or expression from which to retrieve the last value.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.last("item", groups="category", projected_columns="category").order("category")

Result

┌──────────┬──────────────┐
│ category │ "last"(item) │
│ varchar  │   varchar    │
├──────────┼──────────────┤
│ clothing │ jacket       │
│ tools    │ drill        │
└──────────┴──────────────┘

last_value 

last_value(self, expression: str, window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the last value within the group or partition.

Parameters

  • expression : str

    The column or expression from which to retrieve the last value within the window.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.last_value("item", window_spec="OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)", projected_columns="category").distinct().order("category")

Result

┌──────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ category │ last_value(item) OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDE │
│          │                                               D FOLLOWING)                                                │
│ varchar  │                                                  varchar                                                  │
├──────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ clothing │ jacket                                                                                                    │
├──────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ tools    │ drill                                                                                                     │
└──────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

lead 

lead(self, expression: str, window_spec: str, offset: int = 1, default_value: str = 'NULL', ignore_nulls: bool = False, projected_columns: str = '') -> DuckDBPyRelation

Computes the lead within the partition.

Parameters

  • expression : str

    The column or expression to apply the lead function on.

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • offset : int, default: 1

    The number of rows to lead ahead.

  • default_value : str, default: 'NULL'

    The value, as SQL text, to return when the lead offset goes out of bounds.

  • ignore_nulls : bool, default: False

    Whether to ignore NULL values when computing the lead.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.lead("item", window_spec="OVER (ORDER BY price)", projected_columns="item, price").order("price")

Result

┌─────────────┬───────┬───────────────────────────────────────────┐
│    item     │ price │ lead(item, 1, NULL) OVER (ORDER BY price) │
│   varchar   │ int32 │                  varchar                  │
├─────────────┼───────┼───────────────────────────────────────────┤
│ screwdriver │     7 │ t-shirt                                   │
│ t-shirt     │     8 │ jeans                                     │
│ jeans       │    20 │ hammer                                    │
│ hammer      │    42 │ jacket                                    │
│ jacket      │    65 │ drill                                     │
│ drill       │    89 │ NULL                                      │
└─────────────┴───────┴───────────────────────────────────────────┘

list 

list(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Returns a list containing all values present in a given expression.

Parameters

  • expression : str

    The column or expression to aggregate values into a list.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.list("item", groups="category", projected_columns="category").order("category")

Result

┌──────────┬──────────────────────────────┐
│ category │          list(item)          │
│ varchar  │          varchar[]           │
├──────────┼──────────────────────────────┤
│ clothing │ [jeans, t-shirt, jacket]     │
│ tools    │ [hammer, screwdriver, drill] │
└──────────┴──────────────────────────────┘

max 

max(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Returns the maximum value present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the maximum value of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.max("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────┐
│ category │ max(price) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │         65 │
│ tools    │         89 │
└──────────┴────────────┘

mean 

mean(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Alias of avg().

median 

median(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the median over all values present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the median value of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.median("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬───────────────┐
│ category │ median(price) │
│ varchar  │    double     │
├──────────┼───────────────┤
│ clothing │          20.0 │
│ tools    │          42.0 │
└──────────┴───────────────┘

min 

min(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Returns the minimum value present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the min value of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.min("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────┐
│ category │ min(price) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │          8 │
│ tools    │          7 │
└──────────┴────────────┘

mode 

mode(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the mode over all values present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the mode (most frequent value) of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

sales = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES ('clothing', 20), ('clothing', 20), ('clothing', 8), ('tools', 42), ('tools', 7), ('tools', 7))
        AS sales(category, price)
""")

sales.mode("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬───────────────┐
│ category │ "mode"(price) │
│ varchar  │     int32     │
├──────────┼───────────────┤
│ clothing │            20 │
│ tools    │             7 │
└──────────┴───────────────┘

n_tile 

n_tile(self, window_spec: str, num_buckets: int, projected_columns: str = '') -> DuckDBPyRelation

Divides the partition as equally as possible into num_buckets.

Parameters

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • num_buckets : int

    The number of buckets to divide the rows into.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.n_tile(window_spec="OVER (PARTITION BY category ORDER BY price)", num_buckets=2, projected_columns="category, item, price").order("category, price")

Result

┌──────────┬─────────────┬───────┬──────────────────────────────────────────────────────┐
│ category │    item     │ price │ ntile(2) OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                        int64                         │
├──────────┼─────────────┼───────┼──────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                    1 │
│ clothing │ jeans       │    20 │                                                    1 │
│ clothing │ jacket      │    65 │                                                    2 │
│ tools    │ screwdriver │     7 │                                                    1 │
│ tools    │ hammer      │    42 │                                                    1 │
│ tools    │ drill       │    89 │                                                    2 │
└──────────┴─────────────┴───────┴──────────────────────────────────────────────────────┘

nth_value 

nth_value(self, expression: str, window_spec: str, offset: int, ignore_nulls: bool = False, projected_columns: str = '') -> DuckDBPyRelation

Computes the nth value within the partition.

Parameters

  • expression : str

    The column or expression from which to retrieve the nth value within the window.

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • offset : int

    The position of the value to retrieve within the window (1-based index).

  • ignore_nulls : bool, default: False

    Whether to ignore NULL values when computing the nth value.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.nth_value("item", window_spec="OVER (PARTITION BY category ORDER BY price)", offset=2, projected_columns="category, item, price").order("category, price")

Result

┌──────────┬─────────────┬───────┬────────────────────────────────────────────────────────────────┐
│ category │    item     │ price │ nth_value(item, 2) OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                            varchar                             │
├──────────┼─────────────┼───────┼────────────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │ NULL                                                           │
│ clothing │ jeans       │    20 │ jeans                                                          │
│ clothing │ jacket      │    65 │ jeans                                                          │
│ tools    │ screwdriver │     7 │ NULL                                                           │
│ tools    │ hammer      │    42 │ hammer                                                         │
│ tools    │ drill       │    89 │ hammer                                                         │
└──────────┴─────────────┴───────┴────────────────────────────────────────────────────────────────┘

percent_rank 

percent_rank(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation

Computes the relative rank within the partition.

Parameters

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.percent_rank(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")

Result

┌──────────┬─────────────┬───────┬────────────────────────────────────────────────────────────┐
│ category │    item     │ price │ percent_rank() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                           double                           │
├──────────┼─────────────┼───────┼────────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                        0.0 │
│ clothing │ jeans       │    20 │                                                        0.5 │
│ clothing │ jacket      │    65 │                                                        1.0 │
│ tools    │ screwdriver │     7 │                                                        0.0 │
│ tools    │ hammer      │    42 │                                                        0.5 │
│ tools    │ drill       │    89 │                                                        1.0 │
└──────────┴─────────────┴───────┴────────────────────────────────────────────────────────────┘

product 

product(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Returns the product of all values present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the product of.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.product("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────┐
│ category │ product(price) │
│ varchar  │     double     │
├──────────┼────────────────┤
│ clothing │        10400.0 │
│ tools    │        26166.0 │
└──────────┴────────────────┘

quantile 

quantile(self, expression: str, q: float | builtins.list[float] = 0.5, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Alias of quantile_disc().

quantile_cont 

quantile_cont(self, expression: str, q: float | builtins.list[float] = 0.5, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the interpolated quantile value for a given expression.

Parameters

  • expression : str

    The column or expression to compute the continuous quantile for.

  • q : float, list[float], default: 0.5

    The quantile to compute, such as 0.5 for the median, or a list of quantiles.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.quantile_cont("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────────────────┐
│ category │ quantile_cont(price, 0.500000) │
│ varchar  │             double             │
├──────────┼────────────────────────────────┤
│ clothing │                           20.0 │
│ tools    │                           42.0 │
└──────────┴────────────────────────────────┘

quantile_disc 

quantile_disc(self, expression: str, q: float | builtins.list[float] = 0.5, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the exact quantile value for a given expression.

Aliases: quantile

Parameters

  • expression : str

    The column or expression to compute the discrete quantile for.

  • q : float, list[float], default: 0.5

    The quantile to compute, such as 0.5 for the median, or a list of quantiles.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.quantile_disc("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────────────────┐
│ category │ quantile_disc(price, 0.500000) │
│ varchar  │             int32              │
├──────────┼────────────────────────────────┤
│ clothing │                             20 │
│ tools    │                             42 │
└──────────┴────────────────────────────────┘

rank 

rank(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation

Computes the rank within the partition.

Parameters

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.rank(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")

Result

┌──────────┬─────────────┬───────┬────────────────────────────────────────────────────┐
│ category │    item     │ price │ rank() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                       int64                        │
├──────────┼─────────────┼───────┼────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                  1 │
│ clothing │ jeans       │    20 │                                                  2 │
│ clothing │ jacket      │    65 │                                                  3 │
│ tools    │ screwdriver │     7 │                                                  1 │
│ tools    │ hammer      │    42 │                                                  2 │
│ tools    │ drill       │    89 │                                                  3 │
└──────────┴─────────────┴───────┴────────────────────────────────────────────────────┘

rank_dense 

rank_dense(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation

Alias of dense_rank().

row_number 

row_number(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation

Computes the row number within the partition.

Parameters

  • window_spec : str

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...).

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.row_number(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")

Result

┌──────────┬─────────────┬───────┬──────────────────────────────────────────────────────────┐
│ category │    item     │ price │ row_number() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                          int64                           │
├──────────┼─────────────┼───────┼──────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                        1 │
│ clothing │ jeans       │    20 │                                                        2 │
│ clothing │ jacket      │    65 │                                                        3 │
│ tools    │ screwdriver │     7 │                                                        1 │
│ tools    │ hammer      │    42 │                                                        2 │
│ tools    │ drill       │    89 │                                                        3 │
└──────────┴─────────────┴───────┴──────────────────────────────────────────────────────────┘

std 

std(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Alias of stddev_samp().

stddev 

stddev(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Alias of stddev_samp().

stddev_pop 

stddev_pop(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the population standard deviation for a given expression.

Parameters

  • expression : str

    The column or expression to calculate the standard deviation for.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.stddev_pop("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────┐
│ category │ stddev_pop(price)  │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │ 24.535688292770594 │
│ tools    │ 33.595634637057636 │
└──────────┴────────────────────┘

stddev_samp 

stddev_samp(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the sample standard deviation for a given expression.

Aliases: std, stddev

Parameters

  • expression : str

    The column or expression to calculate the standard deviation for.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.stddev_samp("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────┐
│ category │ stddev_samp(price) │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │  30.04995840263344 │
│ tools    │  41.14608122288197 │
└──────────┴────────────────────┘

string_agg 

string_agg(self, expression: str, sep: str = ',', groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Concatenates the values present in a given expression with a separator.

Parameters

  • expression : str

    The column or expression to concatenate values from.

  • sep : str, default: ','

    Separator string to use between concatenated values.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.string_agg("item", sep=", ", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────────────┐
│ category │   string_agg(item, ', ')   │
│ varchar  │          varchar           │
├──────────┼────────────────────────────┤
│ clothing │ jeans, t-shirt, jacket     │
│ tools    │ hammer, screwdriver, drill │
└──────────┴────────────────────────────┘

sum 

sum(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the sum of all values present in a given expression.

Parameters

  • expression : str

    The column or expression to calculate the sum for.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.sum("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────┐
│ category │ sum(price) │
│ varchar  │   int128   │
├──────────┼────────────┤
│ clothing │         93 │
│ tools    │        138 │
└──────────┴────────────┘

unique 

unique(self, unique_aggr: str) -> DuckDBPyRelation

Returns the distinct values in a column.

Parameters

  • unique_aggr : str

    The column to get the distinct values for.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.unique("category").order("category")

Result

┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ tools    │
└──────────┘

value_counts 

value_counts(self, expression: str, groups: str = '') -> DuckDBPyRelation

Computes the number of elements present in a given expression, also projecting the original expression.

Parameters

  • expression : str

    The column or expression to count values from.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.value_counts("in_stock", groups="in_stock").order("in_stock")

Result

┌──────────┬─────────────────┐
│ in_stock │ count(in_stock) │
│ boolean  │      int64      │
├──────────┼─────────────────┤
│ false    │               1 │
│ true     │               5 │
└──────────┴─────────────────┘

var 

var(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Alias of var_samp().

var_pop 

var_pop(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the population variance for a given expression.

Parameters

  • expression : str

    The column or expression to calculate the population variance for.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.var_pop("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬────────────────────┐
│ category │   var_pop(price)   │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │              602.0 │
│ tools    │ 1128.6666666666667 │
└──────────┴────────────────────┘

var_samp 

var_samp(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Computes the sample variance for a given expression.

Aliases: var, variance

Parameters

  • expression : str

    The column or expression to calculate the sample variance for.

  • groups : str, default: ''

    Comma-separated list of columns to include in the GROUP BY; cannot be combined with window_spec.

  • window_spec : str, default: ''

    Window specification, written as OVER (PARTITION BY ... ORDER BY ...); cannot be combined with groups.

  • projected_columns : str, default: ''

    Comma-separated list of columns to include in the result.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.var_samp("price", groups="category", projected_columns="category").order("category")

Result

┌──────────┬─────────────────┐
│ category │ var_samp(price) │
│ varchar  │     double      │
├──────────┼─────────────────┤
│ clothing │           903.0 │
│ tools    │          1693.0 │
└──────────┴─────────────────┘

variance 

variance(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation

Alias of var_samp().

Output

This section contains the methods which read the relation, store it in a table, a view or a file, or write to the table it reads.

Name Description
insert Inserts the given values into the relation.
insert_into Inserts the relation object into an existing table named table_name.
update Update the given relation with the provided expressions.
arrow Alias of to_arrow_reader().
close Does nothing: a relation holds no result.
create Creates a new table named table_name with the contents of the relation object.
create_view Creates a view named view_name that refers to the relation object.
df Execute and fetch all rows as a pandas DataFrame.
fetch_arrow_reader Deprecated alias of to_arrow_reader().
fetch_arrow_table Deprecated alias of to_arrow_table().
fetch_record_batch Deprecated alias of to_arrow_reader().
fetchall Execute and fetch all rows as a list of tuples.
fetchdf Alias of df().
fetchnumpy Execute and fetch all rows as a Python dict mapping each column to one numpy arrays.
iter_rows Every row, a batch at a time, over one run of the relation.
materialize Execute once and return a relation over the rows, held in client memory.
pl Execute and fetch all rows as a Polars DataFrame.
tf Fetch a result as dict of TensorFlow Tensors.
to_arrow_reader Execute and return an Arrow Record Batch Reader that yields all rows.
to_arrow_table Execute and fetch all rows as an Arrow Table.
to_csv Alias of write_csv().
to_df Alias of df().
to_dicts Every row, as a dict keyed by column name.
to_parquet Alias of write_parquet().
to_table Alias of create().
to_view Alias of create_view().
torch Fetch a result as dict of PyTorch Tensors.
write_csv Write the relation object to a CSV file in 'file_name'.
write_parquet Write the relation object to a Parquet file in 'file_name'.

insert 

insert(self, values: object) -> None

Inserts the given values into the relation.

The relation must read a table, as one from table() does. The relation runs again on every read, so reading it afterwards shows the new rows.

Parameters

  • values : tuple, list, object

    The values of one row in the order of the table's columns: a tuple, a list or, for a table of one column, a single value.

Example

import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items (item VARCHAR, price INTEGER)")

rel = duckdb_conn.table("items")

rel.insert(("jeans", 20))
rel.insert(["hammer", 42])

rel.order("item")

Result

┌─────────┬───────┐
│  item   │ price │
│ varchar │ int32 │
├─────────┼───────┤
│ hammer  │    42 │
│ jeans   │    20 │
└─────────┴───────┘

insert_into 

insert_into(self, table_name: str) -> None

Inserts the relation object into an existing table named table_name.

The name is table, schema.table or catalog.schema.table, and a part may be double quoted; a name of more than three parts needs SQL (INSERT INTO) for now.

Parameters

  • table_name : str

    The name of the table: table, schema.table or catalog.schema.table; a name of more parts raises NotImplementedException. The table must exist, and the relation must have its columns in the table's column order.

Example

import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items (item VARCHAR, price INTEGER)")

rel = duckdb_conn.values(("jeans", 20), ("hammer", 42))

rel.insert_into("items")

duckdb_conn.table("items").order("item")

Result

┌─────────┬───────┐
│  item   │ price │
│ varchar │ int32 │
├─────────┼───────┤
│ hammer  │    42 │
│ jeans   │    20 │
└─────────┴───────┘

update 

update(self, set: object, *, condition: object = None) -> None

Update the given relation with the provided expressions.

The relation must read a table, as one from table() does. The relation runs again on every read, so reading it afterwards shows the updated rows.

Parameters

  • set : dict

    The new values, as a dict of column name to an Expression or a Python value. A string value raises TypeError: write col('y') for the column y, or lit('y') for the text.

  • condition : str, Expression, keyword-only, default: None

    Optional condition to filter which rows to update.

Example

import duckdb
from duckdb import col, lit

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items AS SELECT * FROM (VALUES (1, 'jeans', 20), (2, 'hammer', 42)) AS t(id, item, price)")

rel = duckdb_conn.table("items")

rel.update(set={"item": lit("claw hammer"), "price": col("price") + 5}, condition=col("id") == 2)

rel.order("id")

Result

┌───────┬─────────────┬───────┐
│  id   │    item     │ price │
│ int32 │   varchar   │ int32 │
├───────┼─────────────┼───────┤
│     1 │ jeans       │    20 │
│     2 │ claw hammer │    47 │
└───────┴─────────────┴───────┘

arrow 

arrow(self, batch_size: int = 131072) -> Any

Alias of to_arrow_reader().

We recommend using to_arrow_reader() instead.

close 

close(self) -> None

Does nothing: a relation holds no result.

create 

create(self, table_name: str) -> None

Creates a new table named table_name with the contents of the relation object.

The name is table, schema.table or catalog.schema.table, and a part may be double quoted; a name of more than three parts needs SQL (CREATE TABLE AS) for now.

Aliases: to_table

Parameters

  • table_name : str

    The name of the table: table, schema.table or catalog.schema.table; a name of more parts raises NotImplementedException. There must not be a table of that name already.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.filter("category = 'tools'").create("tools")

duckdb_conn.table("tools")

Result

┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     4 │ hammer      │ tools    │    42 │ true     │
│     5 │ screwdriver │ tools    │     7 │ true     │
│     6 │ drill       │ tools    │    89 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘

create_view 

create_view(self, view_name: str, replace: bool = True) -> DuckDBPyRelation

Creates a view named view_name that refers to the relation object.

A view stores SQL text, so a relation over rows that DuckDB holds for it cannot become one: a relation from values() or materialize() or over a statement that ran at the call, such as an INSERT, raises InvalidInputException. Use create() to store such rows in a table, or con.register(name, rel) for a temporary view that lasts as long as the connection.

Aliases: to_view

Parameters

  • view_name : str

    The name of the view to be created.

  • replace : bool, default: True

    If the view should be created with CREATE OR REPLACE. When set to False, there must not be a view of that name already.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.filter("price > 30").create_view("expensive_items")

duckdb_conn.view("expensive_items")

Result

┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     3 │ jacket  │ clothing │    65 │ true     │
│     4 │ hammer  │ tools    │    42 │ true     │
│     6 │ drill   │ tools    │    89 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘

df 

df(self, *, date_as_object: bool = False) -> Any

Execute and fetch all rows as a pandas DataFrame.

Aliases: fetchdf, to_df

Parameters

  • date_as_object : bool, keyword-only, default: False

    If the date columns should be interpreted as Python date objects.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.df()

Result

   id         item  category  price  in_stock
0   1        jeans  clothing     20      True
1   2      t-shirt  clothing      8     False
2   3       jacket  clothing     65      True
3   4       hammer     tools     42      True
4   5  screwdriver     tools      7      True
5   6        drill     tools     89      True

fetch_arrow_reader 

fetch_arrow_reader(self, batch_size: int = 131072) -> Any

Deprecated alias of to_arrow_reader().

Deprecated fetch_arrow_reader() is deprecated. Use to_arrow_reader() instead.

fetch_arrow_table 

fetch_arrow_table(self, batch_size: int = 131072) -> Any

Deprecated alias of to_arrow_table().

Deprecated fetch_arrow_table() is deprecated. Use to_arrow_table() instead.

fetch_record_batch 

fetch_record_batch(self, rows_per_batch: int = 131072) -> Any

Deprecated alias of to_arrow_reader().

Deprecated fetch_record_batch() is deprecated. Use to_arrow_reader() instead.

fetchall 

fetchall(self) -> builtins.list[tuple[Any, ...]]

Execute and fetch all rows as a list of tuples.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.limit(1).fetchall()

Result

[(1, 'jeans', 'clothing', 20, True)]

fetchdf 

fetchdf(self, *, date_as_object: bool = False) -> Any

Alias of df().

fetchnumpy 

fetchnumpy(self) -> dict[str, Any]

Execute and fetch all rows as a Python dict mapping each column to one numpy arrays.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.fetchnumpy()

Result

{'id': array([1, 2, 3, 4, 5, 6], dtype=int32), 'item': array(['jeans', 't-shirt', 'jacket', 'hammer', 'screwdriver', 'drill'],
      dtype=object), 'category': array(['clothing', 'clothing', 'clothing', 'tools', 'tools', 'tools'],
      dtype=object), 'price': array([20,  8, 65, 42,  7, 89], dtype=int32), 'in_stock': array([ True, False,  True,  True,  True,  True])}

iter_rows 

iter_rows(self, batch_size: int = 1024) -> Iterator[tuple[Any, ...]]

Every row, a batch at a time, over one run of the relation.

The rows are read once into the engine's memory, so every batch comes from the same run and the connection is free between batches; the caller holds one batch at a time rather than the whole result.

To read a relation row by row, as a loop over fetchone() or fetchmany() did before DuckDB 2.0, iterate over iter_rows().

Parameters

  • batch_size : int, default: 1024

    The number of rows fetched at a time; it must be above zero.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

for row in rel.iter_rows(batch_size=4):
    print(row)

Result

(1, 'jeans', 'clothing', 20, True)
(2, 't-shirt', 'clothing', 8, False)
(3, 'jacket', 'clothing', 65, True)
(4, 'hammer', 'tools', 42, True)
(5, 'screwdriver', 'tools', 7, True)
(6, 'drill', 'tools', 89, True)

materialize 

materialize(self) -> DuckDBPyRelation

Execute once and return a relation over the rows, held in client memory.

The returned relation reads the held rows, so it does not see later changes to the data, and create_view() refuses it. materialize() takes the place of execute(), which relations no longer have since DuckDB 2.0.

Example

import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items AS SELECT range AS id FROM range(3)")

rel = duckdb_conn.table("items")
held = rel.materialize()

duckdb_conn.sql("INSERT INTO items VALUES (3)")

print(len(rel), len(held))

Result

4 3

pl 

pl(self, batch_size: int = 131072, *, lazy: bool = False) -> Any

Execute and fetch all rows as a Polars DataFrame.

Needs the polars package.

Parameters

  • batch_size : int, default: 131072

    The number of rows of each Arrow record batch.

  • lazy : bool, keyword-only, default: False

    Return a polars LazyFrame instead of a DataFrame.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.pl(batch_size=1)

Result

shape: (6, 5)
┌─────┬─────────────┬──────────┬───────┬──────────┐
│ id  ┆ item        ┆ category ┆ price ┆ in_stock │
│ --- ┆ ---         ┆ ---      ┆ ---   ┆ ---      │
│ i32 ┆ str         ┆ str      ┆ i32   ┆ bool     │
╞═════╪═════════════╪══════════╪═══════╪══════════╡
│ 1   ┆ jeans       ┆ clothing ┆ 20    ┆ true     │
│ 2   ┆ t-shirt     ┆ clothing ┆ 8     ┆ false    │
│ 3   ┆ jacket      ┆ clothing ┆ 65    ┆ true     │
│ 4   ┆ hammer      ┆ tools    ┆ 42    ┆ true     │
│ 5   ┆ screwdriver ┆ tools    ┆ 7     ┆ true     │
│ 6   ┆ drill       ┆ tools    ┆ 89    ┆ true     │
└─────┴─────────────┴──────────┴───────┴──────────┘

tf 

tf(self) -> dict[str, Any]

Fetch a result as dict of TensorFlow Tensors.

Needs the tensorflow package.

to_arrow_reader 

to_arrow_reader(self, batch_size: int = 131072) -> Any

Execute and return an Arrow Record Batch Reader that yields all rows.

Until the reader is read to the end or closed, the connection refuses other queries with ResourceInUseException. A with block closes the reader when it ends, also when the loop stops early. The example sets a small batch_size only to show several batches. To read the rows a batch at a time, as fetch_df_chunk() did before DuckDB 2.0, read the batches of this reader.

Aliases: arrow, fetch_arrow_reader, fetch_record_batch

Parameters

  • batch_size : int, default: 131072

    The number of rows of each Arrow record batch.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

with rel.select("item, price").to_arrow_reader(batch_size=2) as reader:
    for batch in reader:
        print(batch.to_pydict())

Result

{'item': ['jeans', 't-shirt'], 'price': [20, 8]}
{'item': ['jacket', 'hammer'], 'price': [65, 42]}
{'item': ['screwdriver', 'drill'], 'price': [7, 89]}

to_arrow_table 

to_arrow_table(self, batch_size: int = 131072) -> Any

Execute and fetch all rows as an Arrow Table.

Aliases: fetch_arrow_table

Parameters

  • batch_size : int, default: 131072

    The number of rows of each Arrow record batch.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.to_arrow_table()

Result

pyarrow.Table
id: int32
item: string
category: string
price: int32
in_stock: bool
----
id: [[1,2,3,4,5,6]]
item: [["jeans","t-shirt","jacket","hammer","screwdriver","drill"]]
category: [["clothing","clothing","clothing","tools","tools","tools"]]
price: [[20,8,65,42,7,89]]
in_stock: [[true,false,true,true,true,true]]

to_csv 

to_csv(self, file_name: str, *, sep: str | None = None, na_rep: str | None = None, header: bool | None = None, quotechar: str | None = None, escapechar: str | None = None, date_format: str | None = None, timestamp_format: str | None = None, quoting: str | int | None = None, encoding: str | None = None, compression: str | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None) -> None

Alias of write_csv().

to_df 

to_df(self, *, date_as_object: bool = False) -> Any

Alias of df().

to_dicts 

to_dicts(self) -> builtins.list[dict[str, Any]]

Every row, as a dict keyed by column name.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.limit(2).to_dicts()

Result

[{'id': 1, 'item': 'jeans', 'category': 'clothing', 'price': 20, 'in_stock': True}, {'id': 2, 'item': 't-shirt', 'category': 'clothing', 'price': 8, 'in_stock': False}]

to_parquet 

to_parquet(self, file_name: str, *, compression: str | None = None, field_ids: object = None, row_group_size_bytes: int | str | None = None, row_group_size: int | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None, append: bool | None = None, filename_pattern: str | None = None, file_size_bytes: str | int | None = None) -> None

Alias of write_parquet().

to_table 

to_table(self, table_name: str) -> None

Alias of create().

to_view 

to_view(self, view_name: str, replace: bool = True) -> DuckDBPyRelation

Alias of create_view().

torch 

torch(self) -> dict[str, Any]

Fetch a result as dict of PyTorch Tensors.

Needs the torch package.

write_csv 

write_csv(self, file_name: str, *, sep: str | None = None, na_rep: str | None = None, header: bool | None = None, quotechar: str | None = None, escapechar: str | None = None, date_format: str | None = None, timestamp_format: str | None = None, quoting: str | int | None = None, encoding: str | None = None, compression: str | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None) -> None

Write the relation object to a CSV file in 'file_name'.

Most options are the option of the same name of the COPY ... TO statement; sep is DELIMITER, na_rep is NULL, quotechar is QUOTE, escapechar is ESCAPE, quoting sets FORCE_QUOTE for every column and overwrite is OVERWRITE_OR_IGNORE.

Aliases: to_csv

Parameters

  • file_name : str

    Path of the CSV file to write; a directory with partition_by or per_thread_output.

  • sep : str, keyword-only, default: None

    Delimiter between the columns; a comma when omitted.

  • na_rep : str, keyword-only, default: None

    Text written for NULL values; empty when omitted.

  • header : bool, keyword-only, default: None

    Whether to write a first line with the column names; written when omitted.

  • quotechar : str, keyword-only, default: None

    Character used to quote values; " when omitted.

  • escapechar : str, keyword-only, default: None

    Character written before a quote character inside a quoted value; " when omitted.

  • date_format : str, keyword-only, default: None

    Format used to write dates.

  • timestamp_format : str, keyword-only, default: None

    Format used to write timestamps.

  • quoting : str, int, keyword-only, default: None

    Quote every value: pass csv.QUOTE_ALL, 'ALL' or 'FORCE'; any other value is refused. Values are quoted only where needed when omitted.

  • encoding : str, keyword-only, default: None

    Only UTF-8 is accepted, written 'utf-8' or 'utf8'.

  • compression : str, keyword-only, default: None

    Compression of the file: 'none', 'gzip' or 'zstd'. Chosen from the file extension when omitted.

  • overwrite : bool, keyword-only, default: None

    Whether an existing file may be replaced. Omitted or True, an existing file is replaced; False refuses an existing file with IOException. With partition_by, only True writes into a directory that already holds files.

  • per_thread_output : bool, keyword-only, default: None

    Write one file per thread into the directory file_name, rather than one file.

  • use_tmp_file : bool, keyword-only, default: None

    Write to a temporary file first, so a cancelled write does not leave a broken file in place of an existing one.

  • partition_by : list[str], keyword-only, default: None

    Columns to partition the output by, written as Hive partitioned directories under file_name.

  • write_partition_columns : bool, keyword-only, default: None

    Whether to also write the partition columns into the files; only has an effect with partition_by.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.write_csv("items.csv")

write_parquet 

write_parquet(self, file_name: str, *, compression: str | None = None, field_ids: object = None, row_group_size_bytes: int | str | None = None, row_group_size: int | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None, append: bool | None = None, filename_pattern: str | None = None, file_size_bytes: str | int | None = None) -> None

Write the relation object to a Parquet file in 'file_name'.

Each option is the option of the same name of the COPY ... TO statement, except overwrite, which is OVERWRITE_OR_IGNORE.

Aliases: to_parquet

Parameters

  • file_name : str

    Path of the Parquet file to write; a directory with partition_by, per_thread_output or file_size_bytes.

  • compression : str, keyword-only, default: None

    Compression codec: 'uncompressed', 'snappy', 'gzip', 'zstd', 'brotli', 'lz4' or 'lz4_raw'; 'snappy' when omitted.

  • field_ids : dict, str, keyword-only, default: None

    The Parquet field_id of each column, as a dict of column name to id, or 'auto' to assign them.

  • row_group_size_bytes : int, str, keyword-only, default: None

    Target size of each row group, as a number of bytes or a text such as '2MB'.

  • row_group_size : int, keyword-only, default: None

    Target number of rows of each row group; 122880 when omitted.

  • overwrite : bool, keyword-only, default: None

    Whether an existing file may be replaced. Omitted or True, an existing file is replaced; False refuses an existing file with IOException. With partition_by, True writes into a directory that already holds files, as append=True does.

  • per_thread_output : bool, keyword-only, default: None

    Write one file per thread into the directory file_name, rather than one file.

  • use_tmp_file : bool, keyword-only, default: None

    Write to a temporary file first, so a cancelled write does not leave a broken file in place of an existing one.

  • partition_by : list[str], keyword-only, default: None

    Columns to partition the output by, written as Hive partitioned directories under file_name.

  • write_partition_columns : bool, keyword-only, default: None

    Whether to also write the partition columns into the files; only has an effect with partition_by.

  • append : bool, keyword-only, default: None

    When a generated file name already exists, choose another name instead of overwriting the file; only has an effect when several files are written. Combined with overwrite, it raises BinderException.

  • filename_pattern : str, keyword-only, default: None

    Pattern of the names of the files written, where {i} is replaced by an index and {uuid} by a UUID; only has an effect when several files are written.

  • file_size_bytes : int, str, keyword-only, default: None

    Start a new file in the directory file_name when a file grows past this size, given as a number of bytes or a text such as '1MB'.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.write_parquet("items.parquet")
© 2026 DuckDB Foundation, Amsterdam NL
DuckDB Home Code of Conduct Trademark Use Blog