函数和聚合¶

Functions & Aggregates

中文英文

要对值应用函数并获取在数据库端计算的聚合，需要对 QuerySet 进行注释。

results = await SomeModel.filter(...).annotate(clean_desc=Coalesce("desc", "N/A"))

这将为每个 SomeModel 实例添加一个新属性 clean_desc，该属性现在将包含注释的数据。

还可以在其上调用 .values() 或 .values_list() 来按照常规方式获取数据。

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

中文英文

聚合应用于整个列，并且经常与分组一起使用。因此通常使用 .first() QuerySet 才有意义。

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.

Parameters:¶

field¶: Field name
*default_values¶: Extra parameters to the function.

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.

resolve(model, table)[source]¶

Used to resolve the Function statement for SQL generation.

Parameters:¶

model¶: Model the function is applied on to.
table¶: pypika.Table to keep track of the virtual SQL table (to allow self referential joins)

Return type:¶

dict

Returns:¶

Dict with keys "joins" and "fields"

class tortoise.functions.Aggregate(field, *default_values, distinct=False, _filter=None)[source]¶

Base for SQL Aggregates.

Parameters:¶

field¶: Field name
*default_values¶: Extra parameters to the function.
is_distinct: Flag for aggregate with distinction

自定义函数¶

Custom functions

中文英文

您可以定义自定义函数，这些函数不是内置的，例如 TruncMonth 和 JsonExtract 等。

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`

您还可以在更新中使用函数，该示例仅适用于 MySQL 和 SQLite，但 PostgreSQL 也是一样的。

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