查询API¶

本文件描述了如何使用 QuerySet 构建查询。

请确保查看 examples 以便更好地理解。

你可以从模型类开始你的查询：

Event.filter(id=1)

模型本身有几个方法来开始查询：

• filter(*args, **kwargs) - 创建带有给定过滤条件的 QuerySet

• exclude(*args, **kwargs) - 创建带有给定排除条件的 QuerySet

• all() - 创建不带过滤条件的 QuerySet

• first() - 创建限于一个对象的 QuerySet，并返回实例而不是列表

• annotate() - 创建带有给定注释的 QuerySet

这些方法返回一个 QuerySet 对象，允许进一步的过滤和一些更复杂的操作。

模型类还具有以下方法来创建对象：

• create(**kwargs) - 使用给定的 kwargs 创建对象

• get_or_create(defaults, **kwargs) - 获取给定 kwargs 的对象，如果未找到，则使用来自 defaults 字典的附加 kwargs 创建它

模型实例本身还具有这些方法：

• save() - 更新实例，或在从未保存之前插入它

• delete() - 从数据库中删除实例

• fetch_related(*args) - 获取与实例相关的对象。它可以获取外键关系、反向外键关系和多对多关系。它还可以获取可变深度的相关对象，例如：await team.fetch_related('events__tournament') - 这将获取团队的所有事件，并且每个事件的锦标赛也将被预取。在获取对象后，它们应该可以正常使用，例如：team.events[0].tournament.name

与实例上相关对象的另一种处理方法是在 async for 中显式查询它们：

async for team in event.participants:
    print(team.name)

你也可以这样过滤相关对象：

await team.events.filter(name='First')

这将返回一个带有预定义过滤条件的 QuerySet 对象。

This document describes how to use QuerySet to build your queries

Be sure to check examples for better understanding

You start your query from your model class:

Event.filter(id=1)

There are several method on model itself to start query:

• filter(*args, **kwargs) - create QuerySet with given filters

• exclude(*args, **kwargs) - create QuerySet with given excluding filters

• all() - create QuerySet without filters

• first() - create QuerySet limited to one object and returning instance instead of list

• annotate() - create QuerySet with given annotation

This method returns QuerySet object, that allows further filtering and some more complex operations

Also model class have this methods to create object:

• create(**kwargs) - creates object with given kwargs

• get_or_create(defaults, **kwargs) - gets object for given kwargs, if not found create it with additional kwargs from defaults dict

Also instance of model itself has these methods:

• save() - update instance, or insert it, if it was never saved before

• delete() - delete instance from db

• fetch_related(*args) - fetches objects related to instance. It can fetch FK relation, Backward-FK relations and M2M relations. It also can fetch variable depth of related objects like this: await team.fetch_related('events__tournament') - this will fetch all events for team, and for each of this events their tournament will be prefetched too. After fetching objects they should be available normally like this: team.events[0].tournament.name

Another approach to work with related objects on instance is to query them explicitly in async for:

async for team in event.participants:
    print(team.name)

You also can filter related objects like this:

await team.events.filter(name='First')

which will return you a QuerySet object with predefined filter

在你从对象获得查询集后，可以对其执行以下操作：

class tortoise.queryset.QuerySetSingle(*args, **kwargs)[source]¶

Awaiting on this will resolve a single instance of the Model object, and not a sequence.

class tortoise.queryset.QuerySet(model)[source]¶

classmethod __class_getitem__(params)¶

Parameterizes a generic class.

At least, parameterizing a generic class is the main thing this method does. For example, for some generic class Foo, this is called when we do Foo[int] - there, with cls=Foo and params=int.

However, note that this method is also called when defining generic classes in the first place with class Foo(Generic[T]): ….

__getitem__(key)[source]¶

Query offset and limit for Queryset.

Raises:¶

• ParamsError – QuerySet indices must be slices.

• ParamsError – Slice steps should be 1 or None.

• ParamsError – Slice start should be non-negative number or None.

• ParamsError – Slice stop should be non-negative number greater that slice start,

or None.

Return type:¶

QuerySet[Model]

all()[source]¶

Return the whole QuerySet. Essentially a no-op except as the only operation.

Return type:¶

QuerySet[Model]

annotate(**kwargs)[source]¶

Annotate result with aggregation or function result.

Raises:¶

TypeError – Value of kwarg is expected to be a Function instance.

Return type:¶

QuerySet[Model]

as_query()¶

Return the actual query.

Return type:¶

QueryBuilder

bulk_create(objects, batch_size=None, ignore_conflicts=False, update_fields=None, on_conflict=None)[source]¶

This method inserts the provided list of objects into the database in an efficient manner (generally only 1 query, no matter how many objects there are).

Parameters:¶

on_conflict=None¶

On conflict index name

update_fields=None¶

Update fields when conflicts

ignore_conflicts=False¶

Ignore conflicts when inserting

objects¶

List of objects to bulk create

batch_size=None¶

How many objects are created in a single query

Raises:¶

ValueError – If params do not meet specifications

Return type:¶

BulkCreateQuery[Model]

bulk_update(objects, fields, batch_size=None)[source]¶

Update the given fields in each of the given objects in the database.

Parameters:¶

objects¶

List of objects to bulk create

fields¶

The fields to update

batch_size=None¶

How many objects are created in a single query

Raises:¶

ValueError – If objects have no primary key set

Return type:¶

BulkUpdateQuery[Model]

count()[source]¶

Return count of objects in queryset instead of objects.

Return type:¶

CountQuery

delete()[source]¶

Delete all objects in QuerySet.

Return type:¶

DeleteQuery

distinct()[source]¶

Make QuerySet distinct.

Only makes sense in combination with a .values() or .values_list() as it precedes all the fetched fields with a distinct.

Return type:¶

QuerySet[Model]

exclude(*args, **kwargs)[source]¶

Same as .filter(), but with appends all args with NOT

Return type:¶

QuerySet[Model]

exists()[source]¶

Return True/False whether queryset exists.

Return type:¶

ExistsQuery

async explain()[source]¶

Fetch and return information about the query execution plan.

This is done by executing an EXPLAIN query whose exact prefix depends on the database backend, as documented below.

• PostgreSQL: EXPLAIN (FORMAT JSON, VERBOSE) ...

• SQLite: EXPLAIN QUERY PLAN ...

• MySQL: EXPLAIN FORMAT=JSON ...

Note

This is only meant to be used in an interactive environment for debugging and query optimization. The output format may (and will) vary greatly depending on the database backend.

Return type:¶

Any

filter(*args, **kwargs)[source]¶

Filters QuerySet by given kwargs. You can filter by related objects like this:

Team.filter(events__tournament__name='Test')

You can also pass Q objects to filters as args.

Return type:¶

QuerySet[Model]

first()[source]¶

Limit queryset to one object and return one object instead of list.

Return type:¶

QuerySetSingle[Optional[Model]]

force_index(*index_names)[source]¶

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive.

Return type:¶

QuerySet[Model]

get(*args, **kwargs)[source]¶

Fetch exactly one object matching the parameters.

Return type:¶

QuerySetSingle[Model]

get_or_none(*args, **kwargs)[source]¶

Fetch exactly one object matching the parameters.

Return type:¶

QuerySetSingle[Optional[Model]]

group_by(*fields)[source]¶

Make QuerySet returns list of dict or tuple with group by.

Must call before .values() or .values_list()

Return type:¶

QuerySet[Model]

async in_bulk(id_list, field_name)[source]¶

Return a dictionary mapping each of the given IDs to the object with that ID. If id_list isn’t provided, evaluate the entire QuerySet.

Parameters:¶

id_list¶

A list of field values

field_name¶

Must be a unique field

Return type:¶

Dict[str, Model]

limit(limit)[source]¶

Limits QuerySet to given length.

Raises:¶

ParamsError – Limit should be non-negative number.

Return type:¶

QuerySet[Model]

offset(offset)[source]¶

Query offset for QuerySet.

Raises:¶

ParamsError – Offset should be non-negative number.

Return type:¶

QuerySet[Model]

only(*fields_for_select)[source]¶

Fetch ONLY the specified fields to create a partial model.

Persisting changes on the model is allowed only when:

• All the fields you want to update is specified in <model>.save(update_fields=[...])

• You included the Model primary key in the .only(…)`

To protect against common mistakes we ensure that errors get raised:

• If you access a field that is not specified, you will get an AttributeError.

• If you do a <model>.save() a IncompleteInstanceError will be raised as the model is, as requested, incomplete.

• If you do a <model>.save(update_fields=[...]) and you didn’t include the primary key in the .only(...), then IncompleteInstanceError will be raised indicating that updates can’t be done without the primary key being known.

• If you do a <model>.save(update_fields=[...]) and one of the fields in update_fields was not in the .only(...), then IncompleteInstanceError as that field is not available to be updated.

Return type:¶

QuerySet[Model]

order_by(*orderings)[source]¶

Accept args to filter by in format like this:

.order_by('name', '-tournament__name')

Supports ordering by related models too. A ‘-’ before the name will result in descending sort order, default is ascending.

Raises:¶

FieldError – If unknown field has been provided.

Return type:¶

QuerySet[Model]

prefetch_related(*args)[source]¶

Like .fetch_related() on instance, but works on all objects in QuerySet.

Raises:¶

FieldError – If the field to prefetch on is not a relation, or not found.

Return type:¶

QuerySet[Model]

raw(sql)[source]¶

Return the QuerySet from raw SQL

Return type:¶

RawSQLQuery

resolve_filters(model, q_objects, annotations, custom_filters)¶

Builds the common filters for a QuerySet.

Parameters:¶

model¶

The Model this queryset is based on.

q_objects¶

The Q expressions to apply.

annotations¶

Extra annotations to add.

custom_filters¶

Pre-resolved filters to be passed through.

Return type:¶

None

resolve_ordering(model, table, orderings, annotations)¶

Applies standard ordering to QuerySet.

Parameters:¶

model¶

The Model this queryset is based on.

table¶

pypika.Table to keep track of the virtual SQL table (to allow self referential joins)

orderings¶

What columns/order to order by

annotations¶

Annotations that may be ordered on

Raises:¶

FieldError – If a field provided does not exist in model.

Return type:¶

None

select_for_update(nowait=False, skip_locked=False, of=())[source]¶

Make QuerySet select for update.

Returns a queryset that will lock rows until the end of the transaction, generating a SELECT … FOR UPDATE SQL statement on supported databases.

Return type:¶

QuerySet[Model]

select_related(*fields)[source]¶

Return a new QuerySet instance that will select related objects.

If fields are specified, they must be ForeignKey fields and only those related objects are included in the selection.

Return type:¶

QuerySet[Model]

sql(**kwargs)¶

Return the actual SQL.

Return type:¶

str

update(**kwargs)[source]¶

Update all objects in QuerySet with given kwargs.

Will instead of returning a resultset, update the data in the DB itself.

Return type:¶

UpdateQuery

use_index(*index_names)[source]¶

The USE INDEX (index_list) hint tells MySQL to use only one of the named indexes to find rows in the table.

Return type:¶

QuerySet[Model]

using_db(_db)[source]¶

Executes query in provided db client. Useful for transactions workaround.

Return type:¶

QuerySet[Model]

values(*args, **kwargs)[source]¶

Make QuerySet return dicts instead of objects.

If call after .get(), .get_or_none() or .first() return dict instead of object.

Can pass names of fields to fetch, or as a field_name='name_in_dict' kwarg.

If no arguments are passed it will default to a dict containing all fields.

Raises:¶

FieldError – If duplicate key has been provided.

Return type:¶

ValuesQuery[Literal[False]]

values_list(*fields_, flat=False)[source]¶

Make QuerySet returns list of tuples for given args instead of objects.

If call after .get(), .get_or_none() or .first() return tuples for given args instead of object.

If `flat=True and only one arg is passed can return flat list or just scalar.

If no arguments are passed it will default to a tuple containing all fields in order of declaration.

Return type:¶

ValuesListQuery[Literal[False]]

class tortoise.queryset.BulkCreateQuery(model, db, objects, batch_size=None, ignore_conflicts=False, update_fields=None, on_conflict=None)[source]¶

sql(**kwargs)[source]¶

Return the actual SQL.

Return type:¶

str

class tortoise.queryset.BulkUpdateQuery(model, db, q_objects, annotations, custom_filters, limit, orderings, objects, fields, batch_size=None)[source]¶

sql(**kwargs)[source]¶

Return the actual SQL.

Return type:¶

str

class tortoise.queryset.CountQuery(model, db, q_objects, annotations, custom_filters, limit, offset, force_indexes, use_indexes)[source]¶

class tortoise.queryset.DeleteQuery(model, db, q_objects, annotations, custom_filters, limit, orderings)[source]¶

class tortoise.queryset.ExistsQuery(model, db, q_objects, annotations, custom_filters, force_indexes, use_indexes)[source]¶

class tortoise.queryset.FieldSelectQuery(model, annotations)[source]¶

class tortoise.queryset.RawSQLQuery(model, db, sql)[source]¶

class tortoise.queryset.UpdateQuery(model, update_kwargs, db, q_objects, annotations, custom_filters, limit, orderings)[source]¶

class tortoise.queryset.ValuesListQuery(model, db, q_objects, single, raise_does_not_exist, fields_for_select_list, limit, offset, distinct, orderings, flat, annotations, custom_filters, group_bys, force_indexes, use_indexes)[source]¶

class tortoise.queryset.ValuesQuery(model, db, q_objects, single, raise_does_not_exist, fields_for_select, limit, offset, distinct, orderings, annotations, custom_filters, group_bys, force_indexes, use_indexes)[source]¶

QuerySet 可以构建、过滤并在不实际访问数据库的情况下传递。只有在你 await 查询集后，它才会生成查询并在数据库中运行。

以下是一些使用查询集的常见场景（我们使用在 开始入门 中定义的模型）：

常规选择模型实例：

await Event.filter(name__startswith='FIFA')

该查询将获取所有 name 以 FIFA 开头的事件，其中 name 是模型中定义的字段，startswith 是过滤修饰符。请注意，修饰符应通过双下划线分隔。你可以在本文档的 Filtering 部分阅读有关过滤修饰符的更多信息。

你也可以使用 .exclude() 来过滤查询：

await Team.exclude(name__icontains='junior')

作为更有趣的案例，当你处理相关数据时，你还可以围绕相关实体构建查询：

# 获取所有锦标赛名称为 "World Cup" 的事件
await Event.filter(tournament__name='World Cup')

# 获取参与事件 ID 为 1、2、3 的所有团队
await Team.filter(events__id__in=[1, 2, 3])

# 获取参与锦标赛的名称中包含 "junior" 的团队的所有锦标赛
await Tournament.filter(event__participants__name__icontains='junior').distinct()

通常，你不仅想按相关数据进行过滤，还希望获取该相关数据。你可以使用 .prefetch_related() 来做到这一点：

# 这将获取事件，并且每个事件的 ``.tournament`` 字段将填充相应的 ``Tournament`` 实例
await Event.all().prefetch_related('tournament')

# 这将获取锦标赛及其事件和每个事件的团队
tournament_list = await Tournament.all().prefetch_related('events__participants')

# 获取的多对多和反向外键关系的结果存储在类似列表的容器中
for tournament in tournament_list:
    print([e.name for e in tournament.events])

关于 prefetch_related() 的一般规则是，每个相关模型的深度层级产生一个额外的查询，因此 .prefetch_related('events__participants') 将产生两个额外的查询来获取你的数据。

有时，当性能至关重要时，你不希望进行额外的查询。在这种情况下，你可以使用 values() 或 values_list() 来生成更高效的查询。

# 这将返回包含键 'id'、'name' 和 'tournament_name' 的字典列表，
# 'tournament_name' 将由相关锦标赛的名称填充。
# 并且将通过一个查询完成
events = await Event.filter(id__in=[1, 2, 3]).values('id', 'name', tournament_name='tournament__name')

查询集还通过 .annotate() 方法支持聚合和数据库函数。

from tortoise.functions import Count, Trim, Lower, Upper, Coalesce

# 该查询将获取所有事件数量为 10 或更多的锦标赛，并将
# 在实例中填充字段 `.events_count`，其值相应
await Tournament.annotate(events_count=Count('events')).filter(events_count__gte=10)
await Tournament.annotate(clean_name=Trim('name')).filter(clean_name='tournament')
await Tournament.annotate(name_upper=Upper('name')).filter(name_upper='TOURNAMENT')
await Tournament.annotate(name_lower=Lower('name')).filter(name_lower='tournament')
await Tournament.annotate(desc_clean=Coalesce('desc', '')).filter(desc_clean='')

请查看 examples 以了解其工作原理。

After you obtained queryset from object you can do following operations with it:

class tortoise.queryset.QuerySetSingle(*args, **kwargs)[source]¶

Awaiting on this will resolve a single instance of the Model object, and not a sequence.

class tortoise.queryset.QuerySet(model)[source]¶

classmethod __class_getitem__(params)¶

Parameterizes a generic class.

At least, parameterizing a generic class is the main thing this method does. For example, for some generic class Foo, this is called when we do Foo[int] - there, with cls=Foo and params=int.

However, note that this method is also called when defining generic classes in the first place with class Foo(Generic[T]): ….

__getitem__(key)[source]¶

Query offset and limit for Queryset.

Raises:¶

• ParamsError – QuerySet indices must be slices.

• ParamsError – Slice steps should be 1 or None.

• ParamsError – Slice start should be non-negative number or None.

• ParamsError – Slice stop should be non-negative number greater that slice start,

or None.

Return type:¶

QuerySet[Model]

all()[source]¶

Return the whole QuerySet. Essentially a no-op except as the only operation.

Return type:¶

QuerySet[Model]

annotate(**kwargs)[source]¶

Annotate result with aggregation or function result.

Raises:¶

TypeError – Value of kwarg is expected to be a Function instance.

Return type:¶

QuerySet[Model]

as_query()¶

Return the actual query.

Return type:¶

QueryBuilder

bulk_create(objects, batch_size=None, ignore_conflicts=False, update_fields=None, on_conflict=None)[source]¶

This method inserts the provided list of objects into the database in an efficient manner (generally only 1 query, no matter how many objects there are).

Parameters:¶

on_conflict=None¶

On conflict index name

update_fields=None¶

Update fields when conflicts

ignore_conflicts=False¶

Ignore conflicts when inserting

objects¶

List of objects to bulk create

batch_size=None¶

How many objects are created in a single query

Raises:¶

ValueError – If params do not meet specifications

Return type:¶

BulkCreateQuery[Model]

bulk_update(objects, fields, batch_size=None)[source]¶

Update the given fields in each of the given objects in the database.

Parameters:¶

objects¶

List of objects to bulk create

fields¶

The fields to update

batch_size=None¶

How many objects are created in a single query

Raises:¶

ValueError – If objects have no primary key set

Return type:¶

BulkUpdateQuery[Model]

count()[source]¶

Return count of objects in queryset instead of objects.

Return type:¶

CountQuery

delete()[source]¶

Delete all objects in QuerySet.

Return type:¶

DeleteQuery

distinct()[source]¶

Make QuerySet distinct.

Only makes sense in combination with a .values() or .values_list() as it precedes all the fetched fields with a distinct.

Return type:¶

QuerySet[Model]

exclude(*args, **kwargs)[source]¶

Same as .filter(), but with appends all args with NOT

Return type:¶

QuerySet[Model]

exists()[source]¶

Return True/False whether queryset exists.

Return type:¶

ExistsQuery

async explain()[source]¶

Fetch and return information about the query execution plan.

This is done by executing an EXPLAIN query whose exact prefix depends on the database backend, as documented below.

• PostgreSQL: EXPLAIN (FORMAT JSON, VERBOSE) ...

• SQLite: EXPLAIN QUERY PLAN ...

• MySQL: EXPLAIN FORMAT=JSON ...

Note

This is only meant to be used in an interactive environment for debugging and query optimization. The output format may (and will) vary greatly depending on the database backend.

Return type:¶

Any

filter(*args, **kwargs)[source]¶

Filters QuerySet by given kwargs. You can filter by related objects like this:

Team.filter(events__tournament__name='Test')

You can also pass Q objects to filters as args.

Return type:¶

QuerySet[Model]

first()[source]¶

Limit queryset to one object and return one object instead of list.

Return type:¶

QuerySetSingle[Optional[Model]]

force_index(*index_names)[source]¶

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive.

Return type:¶

QuerySet[Model]

get(*args, **kwargs)[source]¶

Fetch exactly one object matching the parameters.

Return type:¶

QuerySetSingle[Model]

get_or_none(*args, **kwargs)[source]¶

Fetch exactly one object matching the parameters.

Return type:¶

QuerySetSingle[Optional[Model]]

group_by(*fields)[source]¶

Make QuerySet returns list of dict or tuple with group by.

Must call before .values() or .values_list()

Return type:¶

QuerySet[Model]

async in_bulk(id_list, field_name)[source]¶

Return a dictionary mapping each of the given IDs to the object with that ID. If id_list isn’t provided, evaluate the entire QuerySet.

Parameters:¶

id_list¶

A list of field values

field_name¶

Must be a unique field

Return type:¶

Dict[str, Model]

limit(limit)[source]¶

Limits QuerySet to given length.

Raises:¶

ParamsError – Limit should be non-negative number.

Return type:¶

QuerySet[Model]

offset(offset)[source]¶

Query offset for QuerySet.

Raises:¶

ParamsError – Offset should be non-negative number.

Return type:¶

QuerySet[Model]

only(*fields_for_select)[source]¶

Fetch ONLY the specified fields to create a partial model.

Persisting changes on the model is allowed only when:

• All the fields you want to update is specified in <model>.save(update_fields=[...])

• You included the Model primary key in the .only(…)`

To protect against common mistakes we ensure that errors get raised:

• If you access a field that is not specified, you will get an AttributeError.

• If you do a <model>.save() a IncompleteInstanceError will be raised as the model is, as requested, incomplete.

• If you do a <model>.save(update_fields=[...]) and you didn’t include the primary key in the .only(...), then IncompleteInstanceError will be raised indicating that updates can’t be done without the primary key being known.

• If you do a <model>.save(update_fields=[...]) and one of the fields in update_fields was not in the .only(...), then IncompleteInstanceError as that field is not available to be updated.

Return type:¶

QuerySet[Model]

order_by(*orderings)[source]¶

Accept args to filter by in format like this:

.order_by('name', '-tournament__name')

Supports ordering by related models too. A ‘-’ before the name will result in descending sort order, default is ascending.

Raises:¶

FieldError – If unknown field has been provided.

Return type:¶

QuerySet[Model]

prefetch_related(*args)[source]¶

Like .fetch_related() on instance, but works on all objects in QuerySet.

Raises:¶

FieldError – If the field to prefetch on is not a relation, or not found.

Return type:¶

QuerySet[Model]

raw(sql)[source]¶

Return the QuerySet from raw SQL

Return type:¶

RawSQLQuery

resolve_filters(model, q_objects, annotations, custom_filters)¶

Builds the common filters for a QuerySet.

Parameters:¶

model¶

The Model this queryset is based on.

q_objects¶

The Q expressions to apply.

annotations¶

Extra annotations to add.

custom_filters¶

Pre-resolved filters to be passed through.

Return type:¶

None

resolve_ordering(model, table, orderings, annotations)¶

Applies standard ordering to QuerySet.

Parameters:¶

model¶

The Model this queryset is based on.

table¶

pypika.Table to keep track of the virtual SQL table (to allow self referential joins)

orderings¶

What columns/order to order by

annotations¶

Annotations that may be ordered on

Raises:¶

FieldError – If a field provided does not exist in model.

Return type:¶

None

select_for_update(nowait=False, skip_locked=False, of=())[source]¶

Make QuerySet select for update.

Returns a queryset that will lock rows until the end of the transaction, generating a SELECT … FOR UPDATE SQL statement on supported databases.

Return type:¶

QuerySet[Model]

select_related(*fields)[source]¶

Return a new QuerySet instance that will select related objects.

If fields are specified, they must be ForeignKey fields and only those related objects are included in the selection.

Return type:¶

QuerySet[Model]

sql(**kwargs)¶

Return the actual SQL.

Return type:¶

str

update(**kwargs)[source]¶

Update all objects in QuerySet with given kwargs.

Will instead of returning a resultset, update the data in the DB itself.

Return type:¶

UpdateQuery

use_index(*index_names)[source]¶

The USE INDEX (index_list) hint tells MySQL to use only one of the named indexes to find rows in the table.

Return type:¶

QuerySet[Model]

using_db(_db)[source]¶

Executes query in provided db client. Useful for transactions workaround.

Return type:¶

QuerySet[Model]

values(*args, **kwargs)[source]¶

Make QuerySet return dicts instead of objects.

If call after .get(), .get_or_none() or .first() return dict instead of object.

Can pass names of fields to fetch, or as a field_name='name_in_dict' kwarg.

If no arguments are passed it will default to a dict containing all fields.

Raises:¶

FieldError – If duplicate key has been provided.

Return type:¶

ValuesQuery[Literal[False]]

values_list(*fields_, flat=False)[source]¶

Make QuerySet returns list of tuples for given args instead of objects.

If call after .get(), .get_or_none() or .first() return tuples for given args instead of object.

If `flat=True and only one arg is passed can return flat list or just scalar.

If no arguments are passed it will default to a tuple containing all fields in order of declaration.

Return type:¶

ValuesListQuery[Literal[False]]

class tortoise.queryset.BulkCreateQuery(model, db, objects, batch_size=None, ignore_conflicts=False, update_fields=None, on_conflict=None)[source]¶

sql(**kwargs)[source]¶

Return the actual SQL.

Return type:¶

str

class tortoise.queryset.BulkUpdateQuery(model, db, q_objects, annotations, custom_filters, limit, orderings, objects, fields, batch_size=None)[source]¶

sql(**kwargs)[source]¶

Return the actual SQL.

Return type:¶

str

class tortoise.queryset.CountQuery(model, db, q_objects, annotations, custom_filters, limit, offset, force_indexes, use_indexes)[source]¶

class tortoise.queryset.DeleteQuery(model, db, q_objects, annotations, custom_filters, limit, orderings)[source]¶

class tortoise.queryset.ExistsQuery(model, db, q_objects, annotations, custom_filters, force_indexes, use_indexes)[source]¶

class tortoise.queryset.FieldSelectQuery(model, annotations)[source]¶

class tortoise.queryset.RawSQLQuery(model, db, sql)[source]¶

class tortoise.queryset.UpdateQuery(model, update_kwargs, db, q_objects, annotations, custom_filters, limit, orderings)[source]¶

class tortoise.queryset.ValuesListQuery(model, db, q_objects, single, raise_does_not_exist, fields_for_select_list, limit, offset, distinct, orderings, flat, annotations, custom_filters, group_bys, force_indexes, use_indexes)[source]¶

class tortoise.queryset.ValuesQuery(model, db, q_objects, single, raise_does_not_exist, fields_for_select, limit, offset, distinct, orderings, annotations, custom_filters, group_bys, force_indexes, use_indexes)[source]¶

QuerySet could be constructed, filtered and passed around without actually hitting database. Only after you await QuerySet, it will generate query and run it against database.

Here are some common usage scenarios with QuerySet (we are using models defined in 开始入门):

Regular select into model instances:

await Event.filter(name__startswith='FIFA')

This query will get you all events with name starting with FIFA, where name is fields defined on model, and startswith is filter modifier. Take note, that modifiers should be separated by double underscore. You can read more on filter modifiers in Filtering section of this document.

It’s also possible to filter your queries with .exclude():

await Team.exclude(name__icontains='junior')

As more interesting case, when you are working with related data, you could also build your query around related entities:

# getting all events, which tournament name is "World Cup"
await Event.filter(tournament__name='World Cup')

# Gets all teams participating in events with ids 1, 2, 3
await Team.filter(events__id__in=[1,2,3])

# Gets all tournaments where teams with "junior" in their name are participating
await Tournament.filter(event__participants__name__icontains='junior').distinct()

Usually you not only want to filter by related data, but also get that related data as well. You could do it using .prefetch_related():

# This will fetch events, and for each of events ``.tournament`` field will be populated with
# corresponding ``Tournament`` instance
await Event.all().prefetch_related('tournament')

# This will fetch tournament with their events and teams for each event
tournament_list = await Tournament.all().prefetch_related('events__participants')

# Fetched result for m2m and backward fk relations are stored in list-like container
for tournament in tournament_list:
    print([e.name for e in tournament.events])

General rule about how prefetch_related() works is that each level of depth of related models produces 1 additional query, so .prefetch_related('events__participants') will produce two additional queries to fetch your data.

Sometimes, when performance is crucial, you don’t want to make additional queries like this. In cases like this you could use values() or values_list() to produce more efficient query

# This will return list of dicts with keys 'id', 'name', 'tournament_name' and
# 'tournament_name' will be populated by name of related tournament.
# And it will be done in one query
events = await Event.filter(id__in=[1,2,3]).values('id', 'name', tournament_name='tournament__name')

QuerySet also supports aggregation and database functions through .annotate() method

from tortoise.functions import Count, Trim, Lower, Upper, Coalesce

# This query will fetch all tournaments with 10 or more events, and will
# populate filed `.events_count` on instances with corresponding value
await Tournament.annotate(events_count=Count('events')).filter(events_count__gte=10)
await Tournament.annotate(clean_name=Trim('name')).filter(clean_name='tournament')
await Tournament.annotate(name_upper=Upper('name')).filter(name_upper='TOURNAMENT')
await Tournament.annotate(name_lower=Lower('name')).filter(name_lower='tournament')
await Tournament.annotate(desc_clean=Coalesce('desc', '')).filter(desc_clean='')

Check examples to see it all in work

Tortoise ORM 提供了用于处理 FK 关系的 API

Tortoise ORM provides an API for working with FK relations

Tortoise ORM 提供了一个用于处理多对多（M2M）关系的 API。

class tortoise.fields.relational.ManyToManyRelation(instance, m2m_field)[source]¶

Many-to-many relation container for ManyToManyField().

classmethod __class_getitem__(params)¶

Parameterizes a generic class.

At least, parameterizing a generic class is the main thing this method does. For example, for some generic class Foo, this is called when we do Foo[int] - there, with cls=Foo and params=int.

However, note that this method is also called when defining generic classes in the first place with class Foo(Generic[T]): ….

async add(*instances, using_db=None)[source]¶

Adds one or more of instances to the relation.

If it is already added, it will be silently ignored.

Raises:¶

OperationalError – If Object to add is not saved.

Return type:¶

None

all()¶

Returns a QuerySet with all related elements.

Return type:¶

QuerySet[MODEL]

async clear(using_db=None)[source]¶

Clears ALL relations.

Return type:¶

None

filter(*args, **kwargs)¶

Returns a QuerySet with related elements filtered by args/kwargs.

Return type:¶

QuerySet[MODEL]

limit(limit)¶

Returns a QuerySet with at most «limit» related elements.

Return type:¶

QuerySet[MODEL]

offset(offset)¶

Returns a QuerySet with all related elements offset by «offset».

Return type:¶

QuerySet[MODEL]

order_by(*orderings)¶

Returns a QuerySet related elements in order.

Return type:¶

QuerySet[MODEL]

async remove(*instances, using_db=None)[source]¶

Removes one or more of instances from the relation.

Raises:¶

OperationalError – remove() was called with no instances.

Return type:¶

None

你可以这样使用它们：

await event.participants.add(participant_1, participant_2)

Tortoise ORM provides an API for working with M2M relations

class tortoise.fields.relational.ManyToManyRelation(instance, m2m_field)[source]¶

Many-to-many relation container for ManyToManyField().

classmethod __class_getitem__(params)¶

Parameterizes a generic class.

At least, parameterizing a generic class is the main thing this method does. For example, for some generic class Foo, this is called when we do Foo[int] - there, with cls=Foo and params=int.

However, note that this method is also called when defining generic classes in the first place with class Foo(Generic[T]): ….

async add(*instances, using_db=None)[source]¶

Adds one or more of instances to the relation.

If it is already added, it will be silently ignored.

Raises:¶

OperationalError – If Object to add is not saved.

Return type:¶

None

all()¶

Returns a QuerySet with all related elements.

Return type:¶

QuerySet[MODEL]

async clear(using_db=None)[source]¶

Clears ALL relations.

Return type:¶

None

filter(*args, **kwargs)¶

Returns a QuerySet with related elements filtered by args/kwargs.

Return type:¶

QuerySet[MODEL]

limit(limit)¶

Returns a QuerySet with at most «limit» related elements.

Return type:¶

QuerySet[MODEL]

offset(offset)¶

Returns a QuerySet with all related elements offset by «offset».

Return type:¶

QuerySet[MODEL]

order_by(*orderings)¶

Returns a QuerySet related elements in order.

Return type:¶

QuerySet[MODEL]

async remove(*instances, using_db=None)[source]¶

Removes one or more of instances from the relation.

Raises:¶

OperationalError – remove() was called with no instances.

Return type:¶

None

You can use them like this:

await event.participants.add(participant_1, participant_2)

使用 .filter() 方法时，可以使用多个修饰符来指定所需的操作。

teams = await Team.filter(name__icontains='CON')

• not

• in - 检查字段的值是否在传入的列表中

• not_in

• gte - 大于或等于传入的值

• gt - 大于传入的值

• lte - 小于或等于传入的值

• lt - 小于传入的值

• range - 在两个给定值之间

• isnull - 字段为 null

• not_isnull - 字段不为 null

• contains - 字段包含指定子字符串

• icontains - 不区分大小写的 contains

• startswith - 字段是否以值开头

• istartswith - 不区分大小写的 startswith

• endswith - 字段是否以值结尾

• iendswith - 不区分大小写的 endswith

• iexact - 不区分大小写的相等

• search - 全文搜索

特别地，你可以使用以下之一过滤日期部分，注意目前只支持 PostgreSQL 和 MySQL，但不支持 SQLite：

class DatePart(Enum):
    year = "YEAR"
    quarter = "QUARTER"
    month = "MONTH"
    week = "WEEK"
    day = "DAY"
    hour = "HOUR"
    minute = "MINUTE"
    second = "SECOND"
    microsecond = "MICROSECOND"

teams = await Team.filter(created_at__year=2020)
teams = await Team.filter(created_at__month=12)
teams = await Team.filter(created_at__day=5)

在 PostgreSQL 和 MySQL 中，你可以在 JSONField 中使用 contains、contained_by 和 filter 选项：

class JSONModel:
    data = fields.JSONField()

await JSONModel.create(data=["text", 3, {"msg": "msg2"}])
obj = await JSONModel.filter(data__contains=[{"msg": "msg2"}]).first()

await JSONModel.create(data=["text"])
await JSONModel.create(data=["tortoise", "msg"])
await JSONModel.create(data=["tortoise"])

objects = await JSONModel.filter(data__contained_by=["text", "tortoise", "msg"])

class JSONModel:
    data = fields.JSONField()

await JSONModel.create(data={"breed": "labrador",
                            "owner": {
                                "name": "Boby",
                                "last": None,
                                "other_pets": [
                                    {
                                        "name": "Fishy",
                                    }
                                ],
                            },
                        })

obj1 = await JSONModel.filter(data__filter={"breed": "labrador"}).first()
obj2 = await JSONModel.filter(data__filter={"owner__name": "Boby"}).first()
obj3 = await JSONModel.filter(data__filter={"owner__other_pets__0__name": "Fishy"}).first()
obj4 = await JSONModel.filter(data__filter={"breed__not": "a"}).first()
obj5 = await JSONModel.filter(data__filter={"owner__name__isnull": True}).first()
obj6 = await JSONModel.filter(data__filter={"owner__last__not_isnull": False}).first()

在 PostgreSQL 和 MySQL 中，你可以使用 postgres_posix_regex 使用 POSIX 正则表达式进行比较： 在 PostgreSQL 中，这使用 ~ 操作符，在 MySQL 中它使用 REGEXP 操作符。

class DemoModel:

    demo_text = fields.TextField()

await DemoModel.create(demo_text="Hello World")
obj = await DemoModel.filter(demo_text__posix_regex="^Hello World$").first()

When using .filter() method you can use number of modifiers to field names to specify desired operation

teams = await Team.filter(name__icontains='CON')

• not

• in - checks if value of field is in passed list

• not_in

• gte - greater or equals than passed value

• gt - greater than passed value

• lte - lower or equals than passed value

• lt - lower than passed value

• range - between and given two values

• isnull - field is null

• not_isnull - field is not null

• contains - field contains specified substring

• icontains - case insensitive contains

• startswith - if field starts with value

• istartswith - case insensitive startswith

• endswith - if field ends with value

• iendswith - case insensitive endswith

• iexact - case insensitive equals

• search - full text search

Specially, you can filter date part with one of following, note that current only support PostgreSQL and MySQL, but not sqlite:

class DatePart(Enum):
    year = "YEAR"
    quarter = "QUARTER"
    month = "MONTH"
    week = "WEEK"
    day = "DAY"
    hour = "HOUR"
    minute = "MINUTE"
    second = "SECOND"
    microsecond = "MICROSECOND"

teams = await Team.filter(created_at__year=2020)
teams = await Team.filter(created_at__month=12)
teams = await Team.filter(created_at__day=5)

In PostgreSQL and MYSQL, you can use the contains, contained_by and filter options in JSONField:

class JSONModel:
    data = fields.JSONField()

await JSONModel.create(data=["text", 3, {"msg": "msg2"}])
obj = await JSONModel.filter(data__contains=[{"msg": "msg2"}]).first()

await JSONModel.create(data=["text"])
await JSONModel.create(data=["tortoise", "msg"])
await JSONModel.create(data=["tortoise"])

objects = await JSONModel.filter(data__contained_by=["text", "tortoise", "msg"])

class JSONModel:
    data = fields.JSONField()

await JSONModel.create(data={"breed": "labrador",
                            "owner": {
                                "name": "Boby",
                                "last": None,
                                "other_pets": [
                                    {
                                        "name": "Fishy",
                                    }
                                ],
                            },
                        })

obj1 = await JSONModel.filter(data__filter={"breed": "labrador"}).first()
obj2 = await JSONModel.filter(data__filter={"owner__name": "Boby"}).first()
obj3 = await JSONModel.filter(data__filter={"owner__other_pets__0__name": "Fishy"}).first()
obj4 = await JSONModel.filter(data__filter={"breed__not": "a"}).first()
obj5 = await JSONModel.filter(data__filter={"owner__name__isnull": True}).first()
obj6 = await JSONModel.filter(data__filter={"owner__last__not_isnull": False}).first()

In PostgreSQL and MySQL, you can use postgres_posix_regex to make comparisons using POSIX regular expressions: On PostgreSQL, this uses the ~ operator, on MySQL it uses the REGEXP operator.

class DemoModel:

    demo_text = fields.TextField()

await DemoModel.create(demo_text="Hello World")
obj = await DemoModel.filter(demo_text__posix_regex="^Hello World$").first()

有时需要仅提取某些相关记录。你可以使用 Prefetch 对象来实现：

tournament_with_filtered = await Tournament.all().prefetch_related(
    Prefetch('events', queryset=Event.filter(name='First'))
).first()

你可以在这里查看完整示例： 预取(Prefetching)

Sometimes it is required to fetch only certain related records. You can achieve it with Prefetch object:

tournament_with_filtered = await Tournament.all().prefetch_related(
    Prefetch('events', queryset=Event.filter(name='First'))
).first()

You can view full example here: 预取(Prefetching)