The Relational API is an alternative API that can be used to incrementally construct queries. The API is centered around DuckDBPyRelation nodes. The relations can be seen as symbolic representations of SQL queries.

Lazy Evaluation

The relations do not hold any data – and nothing is executed – until a method that triggers execution is called.

For example, we create a relation, which loads 1 billion rows:

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("from range(1_000_000_000)")

At the moment of execution, rel does not hold any data and no data is retrieved from the database.

By calling rel.show() or simply printing rel on the terminal, the first 10K rows are fetched. If there are more than 10K rows, the output window will show >9999 rows (as the amount of rows in the relation is unknown).

By calling an output method, the data is retrieved and stored in the specified format:

rel.to_table("example_rel")

# 100% ▕████████████████████████████████████████████████████████████▏ 

Relation Creation

This section contains the details on how a relation is created. The methods are lazy evaluated.

from_arrow

Signature

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

Description

Create a relation object from an Arrow object

Parameters

• arrow_object : pyarrow.Table, pyarrow.RecordBatch

  Arrow object to create a relation from

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

Signature

from_csv_auto(self: duckdb.duckdb.DuckDBPyConnection, path_or_buffer: object, **kwargs) -> duckdb.duckdb.DuckDBPyRelation

Description

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

Aliases: read_csv

Parameters

• path_or_buffer : Union[str, StringIO, TextIOBase]

  Path to the CSV file or buffer to read from.

• header : Optional[bool], Optional[int]

  Row number(s) to use as the column names, or None if no header.

• compression : Optional[str]

  Compression type (e.g., 'gzip', 'bz2').

• sep : Optional[str]

  Delimiter to use; defaults to comma.

• delimiter : Optional[str]

  Alternative delimiter to use.

• dtype : Optional[Dict[str, str]], Optional[List[str]]

  Data types for columns.

• na_values : Optional[str], Optional[List[str]]

  Additional strings to recognize as NA/NaN.

• skiprows : Optional[int]

  Number of rows to skip at the start.

• quotechar : Optional[str]

  Character used to quote fields.

• escapechar : Optional[str]

  Character used to escape delimiter or quote characters.

• encoding : Optional[str]

  Encoding to use for UTF when reading/writing.

• parallel : Optional[bool]

  Enable parallel reading.

• date_format : Optional[str]

  Format to parse dates.

• timestamp_format : Optional[str]

  Format to parse timestamps.

• sample_size : Optional[int]

  Number of rows to sample for schema inference.

• all_varchar : Optional[bool]

  Treat all columns as VARCHAR.

• normalize_names : Optional[bool]

  Normalize column names to lowercase.

• null_padding : Optional[bool]

  Enable null padding for rows with missing columns.

• names : Optional[List[str]]

  List of column names to use.

• lineterminator : Optional[str]

  Character to break lines on.

• columns : Optional[Dict[str, str]]

  Column mapping for schema.

• auto_type_candidates : Optional[List[str]]

  List of columns for automatic type inference.

• max_line_size : Optional[int]

  Maximum line size in bytes.

• ignore_errors : Optional[bool]

  Ignore parsing errors.

• store_rejects : Optional[bool]

  Store rejected rows.

• rejects_table : Optional[str]

  Table name to store rejected rows.

• rejects_scan : Optional[str]

  Scan to use for rejects.

• rejects_limit : Optional[int]

  Limit number of rejects stored.

• force_not_null : Optional[List[str]]

  List of columns to force as NOT NULL.

• buffer_size : Optional[int]

  Buffer size in bytes.

• decimal : Optional[str]

  Character to recognize as decimal point.

• allow_quoted_nulls : Optional[bool]

  Allow quoted NULL values.

• filename : Optional[bool], Optional[str]

  Add filename column or specify filename.

• hive_partitioning : Optional[bool]

  Enable Hive-style partitioning.

• union_by_name : Optional[bool]

  Union files by column name instead of position.

• hive_types : Optional[Dict[str, str]]

  Hive types for columns.

• hive_types_autocast : Optional[bool]

  Automatically cast Hive types.

• connection : DuckDBPyConnection

  DuckDB connection to use.

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.from_csv_auto("code_example.csv")

rel.show()

Result

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

from_df

Signature

from_df(self: duckdb.duckdb.DuckDBPyConnection, df: pandas.DataFrame) -> duckdb.duckdb.DuckDBPyRelation

Description

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

Signature

from_parquet(*args, **kwargs)
Overloaded function.

1. from_parquet(self: duckdb.duckdb.DuckDBPyConnection, file_glob: str, binary_as_string: bool = False, *, file_row_number: bool = False, filename: bool = False, hive_partitioning: bool = False, union_by_name: bool = False, compression: object = None) -> duckdb.duckdb.DuckDBPyRelation

Create a relation object from the Parquet files in file_glob

2. from_parquet(self: duckdb.duckdb.DuckDBPyConnection, file_globs: list[str], binary_as_string: bool = False, *, file_row_number: bool = False, filename: bool = False, hive_partitioning: bool = False, union_by_name: bool = False, compression: object = None) -> duckdb.duckdb.DuckDBPyRelation

Create a relation object from the Parquet files in file_globs

Description

Create a relation object from the Parquet files

Aliases: read_parquet

Parameters

• file_glob : str

  File path or glob pattern pointing to Parquet files to be read.

• binary_as_string : bool, default: False

  Interpret binary columns as strings instead of blobs.

• file_row_number : bool, default: False

  Add a column containing the row number within each file.

• filename : bool, default: False

  Add a column containing the name of the file each row came from.

• hive_partitioning : bool, default: False

  Enable automatic detection of Hive-style partitions in file paths.

• union_by_name : bool, default: False

  Union Parquet files by matching column names instead of positions.

• compression : object

  Optional compression codec to use when reading the Parquet files.

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.from_parquet("code_example.parquet")

rel.show()

Result

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

from_query

Signature

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

Description

Run a SQL query. If it is a SELECT statement, create a relation object from the given SQL query, otherwise run the query as-is.

Aliases: query, sql

Parameters

• query : object

  The SQL query or subquery to be executed and converted into a relation.

• alias : str, default: ''

  Optional alias name to assign to the resulting relation.

• params : object

  Optional query parameters to be used in the SQL query.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.from_query("from range(1,2) tbl(id)")

rel.show()

Result

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

query

Signature

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

Description

Run a SQL query. If it is a SELECT statement, create a relation object from the given SQL query, otherwise run the query as-is.

Aliases: from_query, sql

Parameters

• query : object

  The SQL query or subquery to be executed and converted into a relation.

• alias : str, default: ''

  Optional alias name to assign to the resulting relation.

• params : object

  Optional query parameters to be used in the SQL query.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.query("from range(1,2) tbl(id)")

rel.show()

Result

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

read_csv

Signature

read_csv(self: duckdb.duckdb.DuckDBPyConnection, path_or_buffer: object, **kwargs) -> duckdb.duckdb.DuckDBPyRelation

Description

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

Aliases: from_csv_auto

Parameters

• path_or_buffer : Union[str, StringIO, TextIOBase]

  Path to the CSV file or buffer to read from.

• header : Optional[bool], Optional[int]

  Row number(s) to use as the column names, or None if no header.

• compression : Optional[str]

  Compression type (e.g., 'gzip', 'bz2').

• sep : Optional[str]

  Delimiter to use; defaults to comma.

• delimiter : Optional[str]

  Alternative delimiter to use.

• dtype : Optional[Dict[str, str]], Optional[List[str]]

  Data types for columns.

• na_values : Optional[str], Optional[List[str]]

  Additional strings to recognize as NA/NaN.

• skiprows : Optional[int]

  Number of rows to skip at the start.

• quotechar : Optional[str]

  Character used to quote fields.

• escapechar : Optional[str]

  Character used to escape delimiter or quote characters.

• encoding : Optional[str]

  Encoding to use for UTF when reading/writing.

• parallel : Optional[bool]

  Enable parallel reading.

• date_format : Optional[str]

  Format to parse dates.

• timestamp_format : Optional[str]

  Format to parse timestamps.

• sample_size : Optional[int]

  Number of rows to sample for schema inference.

• all_varchar : Optional[bool]

  Treat all columns as VARCHAR.

• normalize_names : Optional[bool]

  Normalize column names to lowercase.

• null_padding : Optional[bool]

  Enable null padding for rows with missing columns.

• names : Optional[List[str]]

  List of column names to use.

• lineterminator : Optional[str]

  Character to break lines on.

• columns : Optional[Dict[str, str]]

  Column mapping for schema.

• auto_type_candidates : Optional[List[str]]

  List of columns for automatic type inference.

• max_line_size : Optional[int]

  Maximum line size in bytes.

• ignore_errors : Optional[bool]

  Ignore parsing errors.

• store_rejects : Optional[bool]

  Store rejected rows.

• rejects_table : Optional[str]

  Table name to store rejected rows.

• rejects_scan : Optional[str]

  Scan to use for rejects.

• rejects_limit : Optional[int]

  Limit number of rejects stored.

• force_not_null : Optional[List[str]]

  List of columns to force as NOT NULL.

• buffer_size : Optional[int]

  Buffer size in bytes.

• decimal : Optional[str]

  Character to recognize as decimal point.

• allow_quoted_nulls : Optional[bool]

  Allow quoted NULL values.

• filename : Optional[bool], Optional[str]

  Add filename column or specify filename.

• hive_partitioning : Optional[bool]

  Enable Hive-style partitioning.

• union_by_name : Optional[bool]

  Union files by column name instead of position.

• hive_types : Optional[Dict[str, str]]

  Hive types for columns.

• hive_types_autocast : Optional[bool]

  Automatically cast Hive types.

• connection : DuckDBPyConnection

  DuckDB connection to use.

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

Signature

read_json(self: duckdb.duckdb.DuckDBPyConnection, path_or_buffer: object, *, columns: typing.Optional[object] = None, sample_size: typing.Optional[object] = None, maximum_depth: typing.Optional[object] = None, records: typing.Optional[str] = None, format: typing.Optional[str] = None, date_format: typing.Optional[object] = None, timestamp_format: typing.Optional[object] = None, compression: typing.Optional[object] = None, maximum_object_size: typing.Optional[object] = None, ignore_errors: typing.Optional[object] = None, convert_strings_to_integers: typing.Optional[object] = None, field_appearance_threshold: typing.Optional[object] = None, map_inference_threshold: typing.Optional[object] = None, maximum_sample_files: typing.Optional[object] = None, filename: typing.Optional[object] = None, hive_partitioning: typing.Optional[object] = None, union_by_name: typing.Optional[object] = None, hive_types: typing.Optional[object] = None, hive_types_autocast: typing.Optional[object] = None) -> duckdb.duckdb.DuckDBPyRelation

Description

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

Parameters

• path_or_buffer : object

  File path or file-like object containing JSON data to be read.

• columns : object

  Optional list of column names to project from the JSON data.

• sample_size : object

  Number of rows to sample for inferring JSON schema.

• maximum_depth : object

  Maximum depth to which JSON objects should be parsed.

• records : str

  Format string specifying whether JSON is in records mode.

• format : str

  Format of the JSON data (e.g., 'auto', 'newline_delimited').

• date_format : object

  Format string for parsing date fields.

• timestamp_format : object

  Format string for parsing timestamp fields.

• compression : object

  Compression codec used on the JSON data (e.g., 'gzip').

• maximum_object_size : object

  Maximum size in bytes for individual JSON objects.

• ignore_errors : object

  If True, skip over JSON records with parsing errors.

• convert_strings_to_integers : object

  If True, attempt to convert strings to integers where appropriate.

• field_appearance_threshold : object

  Threshold for inferring optional fields in nested JSON.

• map_inference_threshold : object

  Threshold for inferring maps from JSON object patterns.

• maximum_sample_files : object

  Maximum number of files to sample for schema inference.

• filename : object

  If True, include a column with the source filename for each row.

• hive_partitioning : object

  If True, enable Hive partitioning based on directory structure.

• union_by_name : object

  If True, align JSON columns by name instead of position.

• hive_types : object

  If True, use Hive types from directory structure for schema.

• hive_types_autocast : object

  If True, automatically cast data types to match Hive types.

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

Signature

read_parquet(*args, **kwargs)
Overloaded function.

1. read_parquet(self: duckdb.duckdb.DuckDBPyConnection, file_glob: str, binary_as_string: bool = False, *, file_row_number: bool = False, filename: bool = False, hive_partitioning: bool = False, union_by_name: bool = False, compression: object = None) -> duckdb.duckdb.DuckDBPyRelation

Create a relation object from the Parquet files in file_glob

2. read_parquet(self: duckdb.duckdb.DuckDBPyConnection, file_globs: list[str], binary_as_string: bool = False, *, file_row_number: bool = False, filename: bool = False, hive_partitioning: bool = False, union_by_name: bool = False, compression: object = None) -> duckdb.duckdb.DuckDBPyRelation

Create a relation object from the Parquet files in file_globs

Description

Create a relation object from the Parquet files

Aliases: from_parquet

Parameters

• file_glob : str

  File path or glob pattern pointing to Parquet files to be read.

• binary_as_string : bool, default: False

  Interpret binary columns as strings instead of blobs.

• file_row_number : bool, default: False

  Add a column containing the row number within each file.

• filename : bool, default: False

  Add a column containing the name of the file each row came from.

• hive_partitioning : bool, default: False

  Enable automatic detection of Hive-style partitions in file paths.

• union_by_name : bool, default: False

  Union Parquet files by matching column names instead of positions.

• compression : object

  Optional compression codec to use when reading the Parquet files.

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

Signature

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

Description

Run a SQL query. If it is a SELECT statement, create a relation object from the given SQL query, otherwise run the query as-is.

Aliases: from_query, query

Parameters

• query : object

  The SQL query or subquery to be executed and converted into a relation.

• alias : str, default: ''

  Optional alias name to assign to the resulting relation.

• params : object

  Optional query parameters to be used in the SQL query.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("from range(1,2) tbl(id)")

rel.show()

Result

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

table

Signature

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

Description

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

Signature

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

Description

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

Parameters

• name : str

  Name of the table function to call.

• parameters : object

  Optional parameters to pass to the table function.

Example

import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("""
    create macro get_record_for(x) as table
    select x*range from range(1,2)
""")

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

rel.show()

Result

┌───────────────┐
│ (1 * "range") │
│     int64     │
├───────────────┤
│             1 │
└───────────────┘

values

Signature

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

Description

Create a relation object from the passed values

Example

import duckdb

duckdb_conn = duckdb.connect()

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

rel.show()

Result

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

view

Signature

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

Description

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 table 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.

alias

Description

Get the name of the current alias

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.alias

Result

unnamed_relation_43c808c247431be5

columns

Description

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

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.columns

Result

 ['id', 'description', 'value', 'created_timestamp']

describe

Signature

describe(self: duckdb.duckdb.DuckDBPyRelation) -> duckdb.duckdb.DuckDBPyRelation

Description

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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.describe()

Result

┌─────────┬──────────────────────────────────────┬─────────────────┬────────────────────┬────────────────────────────┐
│  aggr   │                  id                  │   description   │       value        │     created_timestamp      │
│ varchar │               varchar                │     varchar     │       double       │          varchar           │
├─────────┼──────────────────────────────────────┼─────────────────┼────────────────────┼────────────────────────────┤
│ count   │ 9                                    │ 9               │                9.0 │ 9                          │
│ mean    │ NULL                                 │ NULL            │                5.0 │ NULL                       │
│ stddev  │ NULL                                 │ NULL            │ 2.7386127875258306 │ NULL                       │
│ min     │ 08fdcbf8-4e53-4290-9e81-423af263b518 │ value is even   │                1.0 │ 2025-04-09 15:41:20.642+02 │
│ max     │ fb10390e-fad5-4694-91cb-e82728cb6f9f │ value is uneven │                9.0 │ 2025-04-09 15:49:20.642+02 │
│ median  │ NULL                                 │ NULL            │                5.0 │ NULL                       │
└─────────┴──────────────────────────────────────┴─────────────────┴────────────────────┴────────────────────────────┘ 

description

Description

Return the description of the result

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.description

Result

[('id', 'UUID', None, None, None, None, None),
 ('description', 'STRING', None, None, None, None, None),
 ('value', 'NUMBER', None, None, None, None, None),
 ('created_timestamp', 'DATETIME', None, None, None, None, None)]  

dtypes

Description

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

Aliases: types

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.dtypes

Result

 [UUID, VARCHAR, BIGINT, TIMESTAMP WITH TIME ZONE]

explain

Description

explain(self: duckdb.duckdb.DuckDBPyRelation, type: duckdb.duckdb.ExplainType = 'standard') -> str

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.explain()

Result

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│        description        │
│           value           │
│     created_timestamp     │
│                           │
│          ~9 Rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           RANGE           │
│    ────────────────────   │
│      Function: RANGE      │
│                           │
│          ~9 Rows          │
└───────────────────────────┘

query

Signature

query(self: duckdb.duckdb.DuckDBPyRelation, virtual_table_name: str, sql_query: str) -> duckdb.duckdb.DuckDBPyRelation

Description

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

Parameters

• virtual_table_name : str

  The name to assign to the current relation when referenced in the SQL query.

• sql_query : str

  The SQL query string that uses the virtual table name to query the relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.query(virtual_table_name="rel_view", sql_query="from rel")

duckdb_conn.sql("show rel_view")

Result

┌───────────────────┬──────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│    column_name    │       column_type        │  null   │   key   │ default │  extra  │
│      varchar      │         varchar          │ varchar │ varchar │ varchar │ varchar │
├───────────────────┼──────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ id                │ UUID                     │ YES     │ NULL    │ NULL    │ NULL    │
│ description       │ VARCHAR                  │ YES     │ NULL    │ NULL    │ NULL    │
│ value             │ BIGINT                   │ YES     │ NULL    │ NULL    │ NULL    │
│ created_timestamp │ TIMESTAMP WITH TIME ZONE │ YES     │ NULL    │ NULL    │ NULL    │
└───────────────────┴──────────────────────────┴─────────┴─────────┴─────────┴─────────┘

set_alias

Signature

set_alias(self: duckdb.duckdb.DuckDBPyRelation, alias: str) -> duckdb.duckdb.DuckDBPyRelation

Description

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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.set_alias('abc').select('abc.id')

Result

In the SQL query, the alias will be `abc`

shape

Description

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

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.shape

Result

(9, 4)

show

Signature

show(self: duckdb.duckdb.DuckDBPyRelation, *, max_width: typing.Optional[int] = None, max_rows: typing.Optional[int] = None, max_col_width: typing.Optional[int] = None, null_value: typing.Optional[str] = None, render_mode: object = None) -> None

Description

Display a summary of the data

Parameters

• max_width : int

  Maximum display width for the entire output in characters.

• max_rows : int

  Maximum number of rows to display.

• max_col_width : int

  Maximum number of characters to display per column.

• null_value : str

  String to display in place of NULL values.

• render_mode : object

  Render mode for displaying the output.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.show()

Result

┌──────────────────────────────────────┬─────────────────┬───────┬────────────────────────────┐
│                  id                  │   description   │ value │     created_timestamp      │
│                 uuid                 │     varchar     │ int64 │  timestamp with time zone  │
├──────────────────────────────────────┼─────────────────┼───────┼────────────────────────────┤
│ 642ea3d7-793d-4867-a759-91c1226c25a0 │ value is uneven │     1 │ 2025-04-09 15:41:20.642+02 │
│ 6817dd31-297c-40a8-8e40-8521f00b2d08 │ value is even   │     2 │ 2025-04-09 15:42:20.642+02 │
│ 45143f9a-e16e-4e59-91b2-3a0800eed6d6 │ value is uneven │     3 │ 2025-04-09 15:43:20.642+02 │
│ fb10390e-fad5-4694-91cb-e82728cb6f9f │ value is even   │     4 │ 2025-04-09 15:44:20.642+02 │
│ 111ced5c-9155-418e-b087-c331b814db90 │ value is uneven │     5 │ 2025-04-09 15:45:20.642+02 │
│ 66a870a6-aef0-4085-87d5-5d1b35d21c66 │ value is even   │     6 │ 2025-04-09 15:46:20.642+02 │
│ a7e8e796-bca0-44cd-a269-1d71090fb5cc │ value is uneven │     7 │ 2025-04-09 15:47:20.642+02 │
│ 74908d48-7f2d-4bdd-9c92-1e7920b115b5 │ value is even   │     8 │ 2025-04-09 15:48:20.642+02 │
│ 08fdcbf8-4e53-4290-9e81-423af263b518 │ value is uneven │     9 │ 2025-04-09 15:49:20.642+02 │
└──────────────────────────────────────┴─────────────────┴───────┴────────────────────────────┘

sql_query

Signature

sql_query(self: duckdb.duckdb.DuckDBPyRelation) -> str

Description

Get the SQL query that is equivalent to the relation

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.sql_query()

Result

SELECT 
    gen_random_uuid() AS id, 
    concat('value is ', CASE  WHEN ((mod("range", 2) = 0)) THEN ('even') ELSE 'uneven' END) AS description, 
    "range" AS "value", 
    (now() + CAST(concat("range", ' ', 'minutes') AS INTERVAL)) AS created_timestamp 
FROM "range"(1, 10)

type

Description

Get the type of the relation.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.type

Result

QUERY_RELATION

types

Description

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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.types

Result

[UUID, VARCHAR, BIGINT, TIMESTAMP WITH TIME ZONE]

Transformation

This section contains the methods which can be used to chain queries. The methods are lazy evaluated.

aggregate

Signature

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

Description

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

Parameters

• aggr_expr : str, list[Expression]

  The list of columns and aggregation functions.

• group_expr : str, default: ''

  The list of columns to be included in group_by. If None, group by all is applied.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.aggregate('max(value)')

Result

┌──────────────┐
│ max("value") │
│    int64     │
├──────────────┤
│            9 │
└──────────────┘
        

apply

Signature

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

Description

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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.apply(
    function_name="count", 
    function_aggr="id", 
    group_expr="description",
    projected_columns="description"
)

Result

┌─────────────────┬───────────┐
│   description   │ count(id) │
│     varchar     │   int64   │
├─────────────────┼───────────┤
│ value is uneven │         5 │
│ value is even   │         4 │
└─────────────────┴───────────┘

cross

Signature

cross(self: duckdb.duckdb.DuckDBPyRelation, other_rel: duckdb.duckdb.DuckDBPyRelation) -> duckdb.duckdb.DuckDBPyRelation

Description

Create cross/cartesian product of two relational objects

Parameters

• other_rel : duckdb.duckdb.DuckDBPyRelation

  Another relation to perform a cross product with.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.cross(other_rel=rel.set_alias("other_rel"))

Result

┌─────────────────────────────┬─────────────────┬───────┬───────────────────────────┬──────────────────────────────────────┬─────────────────┬───────┬───────────────────────────┐
│             id              │   description   │ value │     created_timestamp     │                  id                  │   description   │ value │     created_timestamp     │
│            uuid             │     varchar     │ int64 │ timestamp with time zone  │                 uuid                 │     varchar     │ int64 │ timestamp with time zone  │
├─────────────────────────────┼─────────────────┼───────┼───────────────────────────┼──────────────────────────────────────┼─────────────────┼───────┼───────────────────────────┤
│ cb2b453f-1a06-4f5e-abe1-b…  │ value is uneven │     1 │ 2025-04-10 09:53:29.78+02 │ cb2b453f-1a06-4f5e-abe1-bfd413581bcf │ value is uneven │     1 │ 2025-04-10 09:53:29.78+02 │
...

except_

Signature

except_(self: duckdb.duckdb.DuckDBPyRelation, other_rel: duckdb.duckdb.DuckDBPyRelation) -> duckdb.duckdb.DuckDBPyRelation

Description

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

Parameters

• other_rel : duckdb.duckdb.DuckDBPyRelation

  The relation to subtract from the current relation (set difference).

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.except_(other_rel=rel.set_alias("other_rel"))

Result

The relation query is executed twice, therefore generating different ids and timestamps:
┌──────────────────────────────────────┬─────────────────┬───────┬────────────────────────────┐
│                  id                  │   description   │ value │     created_timestamp      │
│                 uuid                 │     varchar     │ int64 │  timestamp with time zone  │
├──────────────────────────────────────┼─────────────────┼───────┼────────────────────────────┤
│ f69ed6dd-a7fe-4de2-b6af-1c2418096d69 │ value is uneven │     3 │ 2025-04-10 11:43:05.711+02 │
│ 08ad11dc-a9c2-4aaa-9272-760b27ad1f5d │ value is uneven │     7 │ 2025-04-10 11:47:05.711+02 │
...

filter

Signature

filter(self: duckdb.duckdb.DuckDBPyRelation, filter_expr: object) -> duckdb.duckdb.DuckDBPyRelation

Description

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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.filter("value = 2")

Result

┌──────────────────────────────────────┬───────────────┬───────┬───────────────────────────┐
│                  id                  │  description  │ value │     created_timestamp     │
│                 uuid                 │    varchar    │ int64 │ timestamp with time zone  │
├──────────────────────────────────────┼───────────────┼───────┼───────────────────────────┤
│ b0684ab7-fcbf-41c5-8e4a-a51bdde86926 │ value is even │     2 │ 2025-04-10 09:54:29.78+02 │
└──────────────────────────────────────┴───────────────┴───────┴───────────────────────────┘

insert

Signature

insert(self: duckdb.duckdb.DuckDBPyRelation, values: object) -> None

Description

Inserts the given values into the relation

Parameters

• values : object

  A tuple of values matching the relation column list, to be inserted.

Example

import duckdb

from datetime import datetime
from uuid import uuid4

duckdb_conn = duckdb.connect()

duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
).to_table("code_example")

rel = duckdb_conn.table("code_example")

rel.insert(
    (
        uuid4(), 
        'value is even',
        10, 
        datetime.now()
    )
)

rel.filter("value = 10")

Result

┌──────────────────────────────────────┬───────────────┬───────┬───────────────────────────────┐
│                  id                  │  description  │ value │       created_timestamp       │
│                 uuid                 │    varchar    │ int64 │   timestamp with time zone    │
├──────────────────────────────────────┼───────────────┼───────┼───────────────────────────────┤
│ c6dfab87-fae6-4213-8f76-1b96a8d179f6 │ value is even │    10 │ 2025-04-10 10:02:24.652218+02 │
└──────────────────────────────────────┴───────────────┴───────┴───────────────────────────────┘

insert_into

Signature

insert_into(self: duckdb.duckdb.DuckDBPyRelation, table_name: str) -> None

Description

Inserts the relation object into an existing table named table_name

Parameters

• table_name : str

  The table name to insert the data into. The relation must respect the column order of the table.

Example

import duckdb

from datetime import datetime
from uuid import uuid4

duckdb_conn = duckdb.connect()

duckdb_conn.sql("""
        select
            gen_random_uuid() as id,
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value,
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
).to_table("code_example")

rel = duckdb_conn.values(
    [
        uuid4(),
        'value is even',
        10,
        datetime.now()
    ]
)

rel.insert_into("code_example")

duckdb_conn.table("code_example").filter("value = 10")

Result

┌──────────────────────────────────────┬───────────────┬───────┬───────────────────────────────┐
│                  id                  │  description  │ value │       created_timestamp       │
│                 uuid                 │    varchar    │ int64 │   timestamp with time zone    │
├──────────────────────────────────────┼───────────────┼───────┼───────────────────────────────┤
│ 271c5ddd-c1d5-4638-b5a0-d8c7dc9e8220 │ value is even │    10 │ 2025-04-10 14:29:18.616379+02 │
└──────────────────────────────────────┴───────────────┴───────┴───────────────────────────────┘

intersect

Signature

intersect(self: duckdb.duckdb.DuckDBPyRelation, other_rel: duckdb.duckdb.DuckDBPyRelation) -> duckdb.duckdb.DuckDBPyRelation

Description

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

Parameters

• other_rel : duckdb.duckdb.DuckDBPyRelation

  The relation to intersect with the current relation (set intersection).

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.intersect(other_rel=rel.set_alias("other_rel"))

Result

The relation query is executed once with `rel` and once with `other_rel`,
therefore generating different ids and timestamps:
┌──────┬─────────────┬───────┬──────────────────────────┐
│  id  │ description │ value │    created_timestamp     │
│ uuid │   varchar   │ int64 │ timestamp with time zone │
├──────┴─────────────┴───────┴──────────────────────────┤
│                        0 rows                         │
└───────────────────────────────────────────────────────┘

join

Signature

join(self: duckdb.duckdb.DuckDBPyRelation, other_rel: duckdb.duckdb.DuckDBPyRelation, condition: object, how: str = 'inner') -> duckdb.duckdb.DuckDBPyRelation

Description

Join the relation object with another relation object in other_rel using the join condition expression in join_condition. Types supported are 'inner', 'left', 'right', 'outer', 'semi' and 'anti'

Depending on how the condition parameter is provided, the JOIN clause generated is:

• USING

import duckdb

duckdb_conn = duckdb.connect()

rel1 = duckdb_conn.sql("select range as id, concat('dummy 1', range) as text from range(1,10)")
rel2 = duckdb_conn.sql("select range as id, concat('dummy 2', range) as text from range(5,7)")

rel1.join(rel2, condition="id", how="inner").sql_query()

with following SQL:

SELECT * 
FROM (
        SELECT "range" AS id, 
            concat('dummy 1', "range") AS "text" 
        FROM "range"(1, 10)
    ) AS unnamed_relation_41bc15e744037078 
INNER JOIN (
        SELECT "range" AS id, 
        concat('dummy 2', "range") AS "text" 
        FROM "range"(5, 7)
    ) AS unnamed_relation_307e245965aa2c2b 
USING (id)

• ON

import duckdb

duckdb_conn = duckdb.connect()

rel1 = duckdb_conn.sql("select range as id, concat('dummy 1', range) as text from range(1,10)")
rel2 = duckdb_conn.sql("select range as id, concat('dummy 2', range) as text from range(5,7)")

rel1.join(rel2, condition=f"{rel1.alias}.id = {rel2.alias}.id", how="inner").sql_query()

with the following SQL:

SELECT * 
FROM (
        SELECT "range" AS id, 
            concat('dummy 1', "range") AS "text" 
        FROM "range"(1, 10)
    ) AS unnamed_relation_41bc15e744037078 
INNER JOIN (
        SELECT "range" AS id, 
        concat('dummy 2', "range") AS "text" 
        FROM "range"(5, 7)
    ) AS unnamed_relation_307e245965aa2c2b 
ON ((unnamed_relation_41bc15e744037078.id = unnamed_relation_307e245965aa2c2b.id))

NATURAL, POSITIONAL and ASOF joins are not provided by the relational API. CROSS joins are provided through the cross method.

Parameters

• other_rel : duckdb.duckdb.DuckDBPyRelation

  The relation to join with the current relation.

• condition : object

  The join condition, typically a SQL expression or the duplicated column name to join on.

• how : str, default: 'inner'

  The type of join to perform: 'inner', 'left', 'right', 'outer', 'semi' and 'anti'.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.set_alias("rel").join(
    other_rel=rel.set_alias("other_rel"), 
    condition="rel.id = other_rel.id",
    how="left"
)

rel.count("*")

Result

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

limit

Signature

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

Description

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

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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.limit(1)

Result

┌──────────────────────────────────────┬─────────────────┬───────┬────────────────────────────┐
│                  id                  │   description   │ value │     created_timestamp      │
│                 uuid                 │     varchar     │ int64 │  timestamp with time zone  │
├──────────────────────────────────────┼─────────────────┼───────┼────────────────────────────┤
│ 4135597b-29e7-4cb9-a443-41f3d54f25df │ value is uneven │     1 │ 2025-04-10 10:52:03.678+02 │
└──────────────────────────────────────┴─────────────────┴───────┴────────────────────────────┘

map

Signature

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

Description

Calls the passed function on the relation

Parameters

• map_function : Callable

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

• schema : object, default: None

  Optional schema describing the structure of the output relation.

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   │
└───────┴─────────┘

order

Signature

order(self: duckdb.duckdb.DuckDBPyRelation, order_expr: str) -> duckdb.duckdb.DuckDBPyRelation

Description

Reorder the relation object by order_expr

Parameters

• order_expr : str

  SQL expression defining the ordering of the result rows.

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.order("value desc").limit(1, offset=4)

Result

┌──────────────────────────────────────┬─────────────────┬───────┬────────────────────────────┐
│                  id                  │   description   │ value │     created_timestamp      │
│                 uuid                 │     varchar     │ int64 │  timestamp with time zone  │
├──────────────────────────────────────┼─────────────────┼───────┼────────────────────────────┤
│ 55899131-e3d3-463c-a215-f65cb8aef3bf │ value is uneven │     5 │ 2025-04-10 10:56:03.678+02 │
└──────────────────────────────────────┴─────────────────┴───────┴────────────────────────────┘

project

Signature

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

Description

Project the relation object by the projection in project_expr

Aliases: select

Parameters

• 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.project("description").limit(1)

Result

┌─────────────────┐
│   description   │
│     varchar     │
├─────────────────┤
│ value is uneven │
└─────────────────┘

select

Signature

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

Description

Project the relation object by the projection in project_expr

Aliases: project

Parameters

• 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.select("description").limit(1)

Result

┌─────────────────┐
│   description   │
│     varchar     │
├─────────────────┤
│ value is uneven │
└─────────────────┘

sort

Signature

sort(self: duckdb.duckdb.DuckDBPyRelation, *args) -> duckdb.duckdb.DuckDBPyRelation

Description

Reorder the relation object by the provided expressions

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.sort("description")

Result

┌──────────────────────────────────────┬─────────────────┬───────┬────────────────────────────┐
│                  id                  │   description   │ value │     created_timestamp      │
│                 uuid                 │     varchar     │ int64 │  timestamp with time zone  │
├──────────────────────────────────────┼─────────────────┼───────┼────────────────────────────┤
│ 5e0dfa8c-de4d-4ccd-8cff-450dabb86bde │ value is even   │     6 │ 2025-04-10 16:52:15.605+02 │
│ 95f1ad48-facf-4a84-a971-0a4fecce68c7 │ value is even   │     2 │ 2025-04-10 16:48:15.605+02 │
...

union

Signature

union(self: duckdb.duckdb.DuckDBPyRelation, union_rel: duckdb.duckdb.DuckDBPyRelation) -> duckdb.duckdb.DuckDBPyRelation

Description

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

The union is union all. In order to retrieve distinct values, apply distinct.

Parameters

• union_rel : duckdb.duckdb.DuckDBPyRelation

  The relation to union with the current relation (set union).

Example

import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.union(union_rel=rel)

rel.count("*")

Result

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

update

Signature

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

Description

Update the given relation with the provided expressions

Parameters

• set : object

  Mapping of columns to new values for the update operation.

• condition : object, default: None

  Optional condition to filter which rows to update.

Example

import duckdb

from duckdb import ColumnExpression

duckdb_conn = duckdb.connect()

duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
).to_table("code_example")

rel = duckdb_conn.table("code_example")

rel.update(set={"description":None}, condition=ColumnExpression("value") == 1)

# the update is executed on the table, but not reflected on the relationship
# the relationship has to be recreated to retrieve the modified data
rel = duckdb_conn.table("code_example")

rel.show()

Result

┌──────────────────────────────────────┬─────────────────┬───────┬────────────────────────────┐
│                  id                  │   description   │ value │     created_timestamp      │
│                 uuid                 │     varchar     │ int64 │  timestamp with time zone  │
├──────────────────────────────────────┼─────────────────┼───────┼────────────────────────────┤
│ 66dcaa14-f4a6-4a55-af3b-7f6aa23ab4ad │ NULL            │     1 │ 2025-04-10 16:54:49.317+02 │
│ c6a18a42-67fb-4c95-827b-c966f2f95b88 │ value is even   │     2 │ 2025-04-10 16:55:49.317+02 │
...

Functions

This section contains the functions which can be applied to a relation, in order to get a (scalar) result. The functions are lazy evaluated.

any_value

Signature

any_value(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Returns the first non-null value from a given column

Parameters

• column : str

  The column name from which to retrieve any value.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.any_value('id')

Result

┌──────────────────────────────────────┐
│            any_value(id)             │
│                 uuid                 │
├──────────────────────────────────────┤
│ 642ea3d7-793d-4867-a759-91c1226c25a0 │
└──────────────────────────────────────┘

arg_max

Signature

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

Description

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 name for which to find the argument maximizing the value.

• value_column : str

  The column name containing values used to determine the maximum.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.arg_max(arg_column="value", value_column="value", groups="description", projected_columns="description")

Result

┌─────────────────┬───────────────────────────┐
│   description   │ arg_max("value", "value") │
│     varchar     │           int64           │
├─────────────────┼───────────────────────────┤
│ value is uneven │                         9 │
│ value is even   │                         8 │
└─────────────────┴───────────────────────────┘

arg_min

Signature

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

Description

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 name for which to find the argument minimizing the value.

• value_column : str

  The column name containing values used to determine the minimum.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.arg_min(arg_column="value", value_column="value", groups="description", projected_columns="description")

Result

┌─────────────────┬───────────────────────────┐
│   description   │ arg_min("value", "value") │
│     varchar     │           int64           │
├─────────────────┼───────────────────────────┤
│ value is even   │                         2 │
│ value is uneven │                         1 │
└─────────────────┴───────────────────────────┘

avg

Signature

avg(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Computes the average on a given column

Parameters

• column : str

  The column name to calculate the average on.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.avg('value')

Result

┌──────────────┐
│ avg("value") │
│    double    │
├──────────────┤
│          5.0 │
└──────────────┘
 

bit_and

Signature

bit_and(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Computes the bitwise AND of all bits present in a given column

Parameters

• column : str

  The column name to perform the bitwise AND aggregation on.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.select("description, value::bit as value_bit")

rel.bit_and(column="value_bit", groups="description", projected_columns="description")

Result

┌─────────────────┬──────────────────────────────────────────────────────────────────┐
│   description   │                        bit_and(value_bit)                        │
│     varchar     │                               bit                                │
├─────────────────┼──────────────────────────────────────────────────────────────────┤
│ value is uneven │ 0000000000000000000000000000000000000000000000000000000000000001 │
│ value is even   │ 0000000000000000000000000000000000000000000000000000000000000000 │
└─────────────────┴──────────────────────────────────────────────────────────────────┘    

bit_or

Signature

bit_or(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Computes the bitwise OR of all bits present in a given column

Parameters

• column : str

  The column name to perform the bitwise OR aggregation on.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.select("description, value::bit as value_bit")

rel.bit_or(column="value_bit", groups="description", projected_columns="description")

Result

┌─────────────────┬──────────────────────────────────────────────────────────────────┐
│   description   │                        bit_or(value_bit)                         │
│     varchar     │                               bit                                │
├─────────────────┼──────────────────────────────────────────────────────────────────┤
│ value is uneven │ 0000000000000000000000000000000000000000000000000000000000001111 │
│ value is even   │ 0000000000000000000000000000000000000000000000000000000000001110 │
└─────────────────┴──────────────────────────────────────────────────────────────────┘    

bit_xor

Signature

bit_xor(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Computes the bitwise XOR of all bits present in a given column

Parameters

• column : str

  The column name to perform the bitwise XOR aggregation on.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.select("description, value::bit as value_bit")

rel.bit_xor(column="value_bit", groups="description", projected_columns="description")

Result

┌─────────────────┬──────────────────────────────────────────────────────────────────┐
│   description   │                        bit_xor(value_bit)                        │
│     varchar     │                               bit                                │
├─────────────────┼──────────────────────────────────────────────────────────────────┤
│ value is even   │ 0000000000000000000000000000000000000000000000000000000000001000 │
│ value is uneven │ 0000000000000000000000000000000000000000000000000000000000001001 │
└─────────────────┴──────────────────────────────────────────────────────────────────┘

bitstring_agg

Signature

bitstring_agg(self: duckdb.duckdb.DuckDBPyRelation, column: str, min: typing.Optional[object] = None, max: typing.Optional[object] = None, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

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

Parameters

• column : str

  The column name to aggregate as a bitstring.

• min : object, default: None

  Optional minimum bitstring value for aggregation.

• max : object, default: None

  Optional maximum bitstring value for aggregation.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.bitstring_agg(column="value", groups="description", projected_columns="description", min=1, max=9)

Result

┌─────────────────┬────────────────────────┐
│   description   │ bitstring_agg("value") │
│     varchar     │          bit           │
├─────────────────┼────────────────────────┤
│ value is uneven │ 101010101              │
│ value is even   │ 010101010              │
└─────────────────┴────────────────────────┘

bool_and

Signature

bool_and(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Computes the logical AND of all values present in a given column

Parameters

• column : str

  The column name to perform the boolean AND aggregation on.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.select("description, mod(value,2)::boolean as uneven")

rel.bool_and(column="uneven", groups="description", projected_columns="description")

Result

┌─────────────────┬──────────────────┐
│   description   │ bool_and(uneven) │
│     varchar     │     boolean      │
├─────────────────┼──────────────────┤
│ value is even   │ false            │
│ value is uneven │ true             │
└─────────────────┴──────────────────┘

bool_or

Signature

bool_or(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Computes the logical OR of all values present in a given column

Parameters

• column : str

  The column name to perform the boolean OR aggregation on.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel = rel.select("description, mod(value,2)::boolean as uneven")

rel.bool_or(column="uneven", groups="description", projected_columns="description")

Result

┌─────────────────┬─────────────────┐
│   description   │ bool_or(uneven) │
│     varchar     │     boolean     │
├─────────────────┼─────────────────┤
│ value is even   │ false           │
│ value is uneven │ true            │
└─────────────────┴─────────────────┘                

count

Signature

count(self: duckdb.duckdb.DuckDBPyRelation, column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> duckdb.duckdb.DuckDBPyRelation

Description

Computes the number of elements present in a given column

Parameters

• column : str

  The column name to perform count on.

• groups : str, default: ''

  Comma-separated list of columns to include in the group by.

• window_spec : str, default: ''

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.count("id")

Result

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

cume_dist

Signature

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

Description

Computes the cumulative distribution within the partition

Parameters

• window_spec : str

  Optional window specification for window functions, provided 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 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.cume_dist(window_spec="over (partition by description order by value)", projected_columns="description, value")

Result

┌─────────────────┬───────┬──────────────────────────────────────────────────────────────┐
│   description   │ value │ cume_dist() OVER (PARTITION BY description ORDER BY "value") │
│     varchar     │ int64 │                            double                            │
├─────────────────┼───────┼──────────────────────────────────────────────────────────────┤
│ value is uneven │     1 │                                                          0.2 │
│ value is uneven │     3 │                                                          0.4 │
│ value is uneven │     5 │                                                          0.6 │
│ value is uneven │     7 │                                                          0.8 │
│ value is uneven │     9 │                                                          1.0 │
│ value is even   │     2 │                                                         0.25 │
│ value is even   │     4 │                                                          0.5 │
│ value is even   │     6 │                                                         0.75 │
│ value is even   │     8 │                                                          1.0 │
└─────────────────┴───────┴──────────────────────────────────────────────────────────────┘

dense_rank

Signature

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

Description

Computes the dense rank within the partition

Aliases: rank_dense

Parameters

• window_spec : str

  Optional window specification for window functions, provided as over (partition by ... order by ...)

• projected_columns : str, default: ''

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