class pixeltable.Query

Represents a query for retrieving and transforming data from Pixeltable tables. Thread-safe.

method collect()

Signature

collect() -> ResultSet

method cursor()

Signature

cursor() -> ResultCursor

Return a ResultCursor that iterates over the query results row by row. See ResultCursor for usage examples and lifecycle details.

method distinct()

Signature

distinct() -> Query

Remove duplicate rows from this Query. Note that grouping will be applied to the rows based on the select clause of this Query. In the absence of a select clause, by default, all columns are selected in the grouping. Examples: Select unique addresses from table addresses.

results = addresses.distinct()

Select unique cities in table addresses

results = addresses.city.distinct()

Select unique locations (street, city) in the state of CA

results = (
    addresses.select(addresses.street, addresses.city)
    .where(addresses.state == 'CA')
    .distinct()
)

method group_by()

Signature

group_by(*grouping_items: Any) -> Query

Add a group-by clause to this Query. Variants:

group_by(base_tbl): group a component view by their respective base table rows
group_by(expr1, expr2, expr3): group by the given expressions

Note that grouping will be applied to the rows and take effect when used with an aggregation function like sum(), count() etc. Parameters:

grouping_items (Any): expressions to group by

Returns:

Query: A new Query with the specified group-by clause.

Examples: Given the Query book from a table t with all its columns and rows:

book = t.select()

Group the above Query book by the ‘genre’ column (referenced in table t):

query = book.group_by(t.genre)

Use the above Query grouped by genre to count the number of books for each ‘genre’:

query = (
    book.group_by(t.genre).select(t.genre, count=count(t.genre)).show()
)

Use the above Query grouped by genre to the total price of books for each ‘genre’:

query = book.group_by(t.genre).select(t.genre, total=sum(t.price)).show()

method head()

Signature

head(n: int = 10) -> ResultSet

Return the first n rows of the Query, in insertion order of the underlying Table. head() is not supported for joins. Parameters:

n (int, default: 10): Number of rows to select. Default is 10.

Returns:

ResultSet: A ResultSet with the first n rows of the Query.

method join()

Signature

join(
    other: catalog.Table,
    on: exprs.Expr | Sequence[exprs.ColumnRef] | None = None,
    how: plan.JoinType.LiteralType = 'inner'
) -> Query

Join this Query with a table. Parameters:

other (catalog.Table): the table to join with
on (exprs.Expr | Sequence[exprs.ColumnRef] | None): the join condition, which can be either a) references to one or more columns or b) a boolean expression.
- column references: implies an equality predicate that matches columns in both this Query and other by name.
  - column in other: A column with that same name must be present in this Query, and it must be unique (otherwise the join is ambiguous).
  - column in this Query: A column with that same name must be present in other.
- boolean expression: The expressions must be valid in the context of the joined tables.
how (plan.JoinType.LiteralType, default: 'inner'): the type of join to perform.
- 'inner': only keep rows that have a match in both
- 'left': keep all rows from this Query and only matching rows from the other table
- 'right': keep all rows from the other table and only matching rows from this Query
- 'full_outer': keep all rows from both this Query and the other table
- 'cross': Cartesian product; no on condition allowed

Returns:

Query: A new Query.

Examples: Perform an inner join between t1 and t2 on the column id:

join1 = t1.join(t2, on=t2.id)

Perform a left outer join of join1 with t3, also on id (note that we can’t specify on=t3.id here, because that would be ambiguous, since both t1 and t2 have a column named id):

join2 = join1.join(t3, on=t2.id, how='left')

Do the same, but now with an explicit join predicate:

join2 = join1.join(t3, on=t2.id == t3.id, how='left')

Join t with d, which has a composite primary key (columns pk1 and pk2, with corresponding foreign key columns d1 and d2 in t):

query = t.join(d, on=(t.d1 == d.pk1) & (t.d2 == d.pk2), how='left')

method limit()

Signature

limit(n: int, offset: int | None = None) -> Query

Limit the number of rows in the Query, optionally skipping rows for pagination. Parameters:

n (int): Number of rows to select.
offset (int | None): Number of rows to skip before returning results. Default is None (no offset).

Returns:

Query: A new Query with the specified limited rows.

Examples:

query = t.select()

Get the first 10 rows:

query.limit(10).collect()

Get rows 21-30 (skip first 20, return next 10):

query.limit(10, offset=20).collect()

method order_by()

Signature

order_by(*expr_list: exprs.Expr, asc: bool = True) -> Query

Add an order-by clause to this Query. Parameters:

expr_list (exprs.Expr): expressions to order by
asc (bool, default: True): whether to order in ascending order (True) or descending order (False). Default is True.

Returns:

Query: A new Query with the specified order-by clause.

Examples: Given the Query book from a table t with all its columns and rows:

book = t.select()

Order the above Query book by two columns (price, pages) in descending order:

query = book.order_by(t.price, t.pages, asc=False)

Order the above Query book by price in descending order, but order the pages in ascending order:

query = book.order_by(t.price, asc=False).order_by(t.pages)

method sample()

Signature

sample(
    n: int | None = None,
    n_per_stratum: int | None = None,
    fraction: float | None = None,
    seed: int | None = None,
    stratify_by: Any = None
) -> Query

Return a new Query specifying a sample of rows from the Query, considered in a shuffled order. The size of the sample can be specified in three ways:

n: the total number of rows to produce as a sample
n_per_stratum: the number of rows to produce per stratum as a sample
fraction: the fraction of available rows to produce as a sample

The sample can be stratified by one or more columns, which means that the sample will be selected from each stratum separately. The data is shuffled before creating the sample. Parameters:

n (int | None): Total number of rows to produce as a sample.
n_per_stratum (int | None): Number of rows to produce per stratum as a sample. This parameter is only valid if stratify_by is specified. Only one of n or n_per_stratum can be specified.
fraction (float | None): Fraction of available rows to produce as a sample. This parameter is not usable with n or n_per_stratum. The fraction must be between 0.0 and 1.0.
seed (int | None): Random seed for reproducible shuffling
stratify_by (Any): If specified, the sample will be stratified by these values.

Returns:

Query: A new Query which specifies the sampled rows

Examples: Given the Table person containing the field ‘age’, we can create samples of the table in various ways: Sample 100 rows from the above Table:

query = person.sample(n=100)

Sample 10% of the rows from the above Table:

query = person.sample(fraction=0.1)

Sample 10% of the rows from the above Table, stratified by the column ‘age’:

query = person.sample(fraction=0.1, stratify_by=t.age)

Equal allocation sampling: Sample 2 rows from each age present in the above Table:

query = person.sample(n_per_stratum=2, stratify_by=t.age)

Sampling is compatible with the where clause, so we can also sample from a filtered Query:

query = person.where(t.age > 30).sample(n=100)

method select()

Signature

select(*items: Any, **named_items: Any) -> Query

Select columns or expressions from the Query. Parameters:

items (Any): expressions to be selected
named_items (Any): named expressions to be selected

Returns:

Query: A new Query with the specified select list.

Examples: Given the Query person from a table t with all its columns and rows:

person = t.select()

Select the columns ‘name’ and ‘age’ (referenced in table t) from the Query person:

query = person.select(t.name, t.age)

Select the columns ‘name’ (referenced in table t) from the Query person, and a named column ‘is_adult’ from the expression age >= 18 where ‘age’ is another column in table t:

query = person.select(t.name, is_adult=(t.age >= 18))

method show()

Signature

show(n: int = 20) -> ResultSet

method tail()

Signature

tail(n: int = 10) -> ResultSet

Return the last n rows of the Query, in insertion order of the underlying Table. tail() is not supported for joins. Parameters:

n (int, default: 10): Number of rows to select. Default is 10.

Returns:

ResultSet: A ResultSet with the last n rows of the Query.

method to_coco_dataset()

Signature

to_coco_dataset() -> Path

Convert the Query to a COCO dataset. This Query must return a single json-typed output column in the following format:

{
    'image': PIL.Image.Image,
    'annotations': [
        {
            'bbox': [x: int, y: int, w: int, h: int],
            'category': str | int,
        },
        ...
    ],
}

Returns:

Path: Path to the COCO dataset file.

method to_pytorch_dataset()

Signature

to_pytorch_dataset(image_format: str = 'pt') -> torch.utils.data.IterableDataset

Convert the Query to a pytorch IterableDataset suitable for parallel loading with torch.utils.data.DataLoader. This method requires pyarrow >= 13, torch and torchvision to work. This method serializes data so it can be read from disk efficiently and repeatedly without re-executing the query. This data is cached to disk for future re-use. Parameters:

image_format (str, default: 'pt'): format of the images. Can be ‘pt’ (pytorch tensor) or ‘np’ (numpy array). ‘np’ means image columns return as an RGB uint8 array of shape HxWxC. ‘pt’ means image columns return as a CxHxW tensor with values in [0,1] and type torch.float32. (the format output by torchvision.transforms.ToTensor())

Returns:

'torch.utils.data.IterableDataset': A pytorch IterableDataset: Columns become fields of the dataset, where rows are returned as a dictionary compatible with torch.utils.data.DataLoader default collation.

method where()

Signature

where(pred: exprs.Expr) -> Query

Filter rows based on a predicate. Parameters:

pred (exprs.Expr): the predicate to filter rows

Returns:

Query: A new Query with the specified predicates replacing the where-clause.

Examples: Given the Query person from a table t with all its columns and rows:

person = t.select()

Filter the above Query person to only include rows where the column ‘age’ (referenced in table t) is greater than 30:

query = person.where(t.age > 30)

SDK Reference

Documentation Index

​class pixeltable.Query

​method collect()

​method cursor()

​method distinct()

​method group_by()

​method head()

​method join()

​method limit()

​method order_by()

​method sample()

​method select()

​method show()

​method tail()

​method to_coco_dataset()

​method to_pytorch_dataset()

​method where()

class pixeltable.Query

method collect()

method cursor()

method distinct()

method group_by()

method head()

method join()

method limit()

method order_by()

method sample()

method select()

method show()

method tail()

method to_coco_dataset()

method to_pytorch_dataset()

method where()