Type conversion functions

Cast

class piccolo.query.functions.type_conversion.Cast(identifier: Column | QueryString | bytes | date | datetime | time | timedelta | Decimal | dict | float | int | list | str | UUID, as_type: Column, alias: str | None = None)

Cast a value to a different type. For example:

>>> from piccolo.query.functions import Cast

>>> await Concert.select(
...     Cast(Concert.starts, Time(), alias="start_time")
... )
[{"start_time": datetime.time(19, 0)}]

You may also need Cast to explicitly tell the database which type you’re sending in the query (though this is an edge case). Here is a contrived example:

>>> from piccolo.query.functions.math import Count

# This fails with asyncpg:
>>> await Band.select(Count([1,2,3]))

If we explicitly specify the type of the array, then it works:

>>> await Band.select(
...     Count(
...         Cast(
...             [1,2,3],
...             Array(Integer())
...         ),
...     )
... )
Parameters:

  • identifier – Identifies what is being converted (e.g. a column, or a raw value).

  • as_type – The type to be converted to.

Notes on databases

Postgres and CockroachDB have very rich type systems, and you can convert between most types. SQLite is more limited.

The following query will work in Postgres / Cockroach, but you might get unexpected results in SQLite, because it doesn’t have a native TIME column type:

>>> from piccolo.columns import Time
>>> from piccolo.query.functions import Cast
>>> await Concert.select(Cast(Concert.starts, Time()))