Conditional functions¶

Coalesce¶

class piccolo.query.functions.conditional.Coalesce(*args: Column | QueryString | BasicTypes, alias: str | None = None)¶

Returns the first non-null value.

Here’s an example to try in the playground:

>>> await Album.select(Album.release_date)
[
    {'release_date': datetime.date(2021, 1, 1)},
    {'release_date': datetime.date(2025, 1, 1)},
    {'release_date': datetime.date(2022, 2, 2)},
    {'release_date': None}
]

One of the values is null - we can specify a fallback value:

>>> from piccolo.functions.conditional import Coalesce
>>> await Album.select(
...     Coalesce(Album.release_date, datetime.date(2050, 1, 1))
... )
[
    {'release_date': datetime.date(2021, 1, 1)},
    {'release_date': datetime.date(2025, 1, 1)},
    {'release_date': datetime.date(2022, 2, 2)},
    {'release_date': datetime.date(2050, 1, 1)}
]

Or us this abbreviated syntax:

>>> await Album.select(
...     Album.release_date | datetime.date(2050, 1, 1)
... )
[
    {'release_date': datetime.date(2021, 1, 1)},
    {'release_date': datetime.date(2025, 1, 1)},
    {'release_date': datetime.date(2022, 2, 2)},
    {'release_date': datetime.date(2050, 1, 1)}
]

NullIf¶

class piccolo.query.functions.conditional.NullIf(identifier: Column | QueryString, value: BasicTypes | QueryString, alias: str | None = None)¶

Returns null if the value in the database equals value.

An example is where a Varchar or Text column contains a mix of empty strings and null. We might want to standardise the response so it’s just null.

For example:

class Venue(Table):
    name = Varchar()
    address = Text(null=True)

>>> await Venue.select(Venue.name, NullIf(Venue.address, ''))
[{'name': 'Amazing venue', 'address': None}]

Case¶

class piccolo.query.functions.conditional.Case(*when_conditions: When, default: Column | QueryString | BasicTypes | None = None, alias: str | None = None)¶

A SQL CASE statement - it returns a different value depending on which condition is matched first. It’s the SQL equivalent of an if / elif / else block.

Here’s an example to try in the playground:

>>> from piccolo.query.functions import Case, When

>>> await Band.select(
...     Band.name,
...     Case(
...         When(Band.popularity > 900, then='super popular'),
...         When(Band.popularity > 500, then='popular'),
...         default='not popular',
...         alias='popularity_label',
...     ),
... )
[
    {'name': 'Pythonistas', 'popularity_label': 'super popular'},
    {'name': 'Rustaceans', 'popularity_label': 'not popular'},
    {'name': 'C-Sharps', 'popularity_label': 'popular'}
]

It can also be used in a where clause, and to sort results:

>>> await Band.select(Band.name).order_by(
...     Case(
...         When(Band.name == 'Rustaceans', then=1),
...         default=2,
...     )
... )

Parameters:

when_conditions – Each branch of the statement, which are evaluated in order.
default – The value to return if none of the conditions match (ELSE in SQL). If not specified, null is returned.
alias – The name of the column in the response.

When¶

class piccolo.query.functions.conditional.When(condition: Combinable | QueryString, then: Column | QueryString | BasicTypes | None)¶

A single branch of a Case statement - don’t use it directly.

Parameters:

condition – A where clause, for example Band.popularity > 1000.
then – The value to return if the condition is true.