Functions & Aggregates¶
To apply functions to values and get aggregates computed on the DB side, one needs to annotate the QuerySet.
results = await SomeModel.filter(...).annotate(clean_desc=Coalesce("desc", "N/A"))
This will add a new attribute on each SomeModel
instance called clean_desc
that will now contain the annotated data.
One can also call .values()
or .values_list()
on it to get the data as per regular.
Functions¶
Functions apply a transform on each instance of a Field.
- class tortoise.functions.Trim(field, *default_values)[source]¶
Trims whitespace off edges of text.
Trim("FIELD_NAME")
- class tortoise.functions.Length(field, *default_values)[source]¶
Returns length of text/blob.
Length("FIELD_NAME")
- class tortoise.functions.Coalesce(field, *default_values)[source]¶
Provides a default value if field is null.
Coalesce("FIELD_NAME", DEFAULT_VALUE)
- class tortoise.functions.Lower(field, *default_values)[source]¶
Converts text to lower case.
Lower("FIELD_NAME")
- class tortoise.functions.Upper(field, *default_values)[source]¶
Converts text to upper case.
Upper("FIELD_NAME")
- class tortoise.functions.Concat(field, *default_values)[source]¶
Concate field or constant text. Be care, DB like sqlite3 has no support for CONCAT.
Concat("FIELD_NAME", ANOTHER_FIELD_NAMES or CONSTANT_TEXT, *args)
-
class tortoise.contrib.mysql.functions.Rand(seed=
None
, alias=None
)[source]¶ Generate random number, with optional seed.
Rand()
-
class tortoise.contrib.postgres.functions.Random(alias=
None
)[source]¶ Generate random number.
Random()
-
class tortoise.contrib.sqlite.functions.Random(alias=
None
)[source]¶ Generate random number.
Random()
Aggregates¶
Aggregated apply on the entire column, and will often be used with grouping.
So often makes sense with a .first()
QuerySet.
-
class tortoise.functions.Count(field, *default_values, distinct=
False
, _filter=None
)[source]¶ Counts the no of entries for that column.
Count("FIELD_NAME")
-
class tortoise.functions.Sum(field, *default_values, distinct=
False
, _filter=None
)[source]¶ Adds up all the values for that column.
Sum("FIELD_NAME")
-
class tortoise.functions.Max(field, *default_values, distinct=
False
, _filter=None
)[source]¶ Returns largest value in the column.
Max("FIELD_NAME")
-
class tortoise.functions.Min(field, *default_values, distinct=
False
, _filter=None
)[source]¶ Returns smallest value in the column.
Min("FIELD_NAME")
-
class tortoise.functions.Avg(field, *default_values, distinct=
False
, _filter=None
)[source]¶ Returns average (mean) of all values in the column.
Avg("FIELD_NAME")
Base function class¶
- class tortoise.functions.Function(field, *default_values)[source]¶
Function/Aggregate base.
- database_func pypika.terms.Function¶
The pypika function this represents.
- populate_field_object bool = False¶
Enable populate_field_object where we want to try and preserve the field type.
-
class tortoise.functions.Aggregate(field, *default_values, distinct=
False
, _filter=None
)[source]¶ Base for SQL Aggregates.
Custom functions¶
You can define custom functions which are not builtin, such as TruncMonth
and JsonExtract
etc.
from pypika import CustomFunction
from tortoise.expressions import F, Function
class TruncMonth(Function):
database_func = CustomFunction("DATE_FORMAT", ["name", "dt_format"])
sql = Task.all().annotate(date=TruncMonth('created_at', '%Y-%m-%d')).values('date').sql()
print(sql)
# SELECT DATE_FORMAT(`created_at`,'%Y-%m-%d') `date` FROM `task`
And you can also use functions in update, the example is only suitable for MySQL and SQLite, but PostgreSQL is the same.
from tortoise.expressions import F
from pypika.terms import Function
class JsonSet(Function):
def __init__(self, field: F, expression: str, value: Any):
super().__init__("JSON_SET", field, expression, value)
json = await JSONFields.create(data_default={"a": 1})
json.data_default = JsonSet(F("data_default"), "$.a", 2)
await json.save()
# or use queryset.update()
sql = JSONFields.filter(pk=json.pk).update(data_default=JsonSet(F("data_default"), "$.a", 3)).sql()
print(sql)
# UPDATE jsonfields SET data_default=JSON_SET(`data_default`,'$.a',3) where id=1