Query API¶

This document describes how to use QuerySet to query the database.

Be sure to check examples.

Below is an example of a simple query that will return all events with a rating greater than 5:

await Event.filter(rating__gt=5)

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 the first object

• annotate() - create QuerySet with given annotation

The methods above return a QuerySet object, which supports chaining query operations.

The following methods can be used to create an object:

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

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

The instance of a model has the following 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 with async for:

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

The related objects can be filtered:

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

which will return you a QuerySet object with predefined filter

QuerySet¶

Once you have a QuerySet, you can perform the 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]¶

__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]

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]

earliest(*orderings)[source]¶

Returns the earliest object by ordering ascending on the specified field.

Params orderings:¶

Fields to order by.

Raises:¶

FieldError – If unknown or no fields has been provided.

Return type:¶

QuerySetSingle[Optional[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]

last()[source]¶

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

Return type:¶

QuerySetSingle[Optional[Model]]

latest(*orderings)[source]¶

Returns the most recent object by ordering descending on the providers fields.

Params orderings:¶

Fields to order by.

Raises:¶

FieldError – If unknown or no fields has been provided.

Return type:¶

QuerySetSingle[Optional[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()¶

Builds the common filters for a QuerySet.

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(params_inline=False)¶

Returns the SQL query that will be executed. By default, it will return the query with placeholders, but if you set params_inline=True, it will inline the parameters.

Parameters:¶

params_inline=False¶

Whether to inline the parameters

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[typing_extensions.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[typing_extensions.Literal[False]]

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

sql(params_inline=False)[source]¶

Returns the SQL query that will be executed. By default, it will return the query with placeholders, but if you set params_inline=True, it will inline the parameters.

Parameters:¶

params_inline=False¶

Whether to inline the parameters

Return type:¶

str

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

sql(params_inline=False)[source]¶

Returns the SQL query that will be executed. By default, it will return the query with placeholders, but if you set params_inline=True, it will inline the parameters.

Parameters:¶

params_inline=False¶

Whether to inline the parameters

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 the database. Only after you await QuerySet, it will execute the query.

Here are some common usage scenarios with QuerySet (we are using models defined in Getting started):

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 containe#r
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

Foreign Key¶

Tortoise ORM provides an API for working with FK relations

class tortoise.fields.relational.ReverseRelation(remote_model, relation_field, instance, from_field)[source]¶

Relation container for ForeignKeyField().

all()[source]¶

Returns a QuerySet with all related elements.

Return type:¶

QuerySet[MODEL]

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

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

Return type:¶

QuerySet[MODEL]

limit(limit)[source]¶

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

Return type:¶

QuerySet[MODEL]

offset(offset)[source]¶

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

Return type:¶

QuerySet[MODEL]

order_by(*orderings)[source]¶

Returns a QuerySet related elements in order.

Return type:¶

QuerySet[MODEL]

tortoise.fields.relational.ForeignKeyNullableRelation¶

Type hint for the result of accessing the ForeignKeyField() field in the model when obtained model can be nullable.

alias of Optional[ForeignKeyFieldInstance[MODEL]]

tortoise.fields.relational.ForeignKeyRelation¶

Type hint for the result of accessing the ForeignKeyField() field in the model.

alias of ForeignKeyFieldInstance[MODEL]

One to One¶

tortoise.fields.relational.OneToOneNullableRelation¶

Type hint for the result of accessing the OneToOneField() field in the model when obtained model can be nullable.

alias of Optional[OneToOneFieldInstance[MODEL]]

tortoise.fields.relational.OneToOneRelation¶

Type hint for the result of accessing the OneToOneField() field in the model.

alias of OneToOneFieldInstance[MODEL]

Many to Many¶

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().

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)

Filtering¶

When using the .filter() method, you can apply various modifiers to field names to specify the desired lookup type. In the following example, we filter the Team model to find all teams whose names contain the string CON (case-insensitive):

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

The following lookup types are available:

• 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

For PostgreSQL and MySQL, the following date related lookup types are available:

• year - e.g. await Team.filter(created_at__year=2020)

• quarter

• month

• week

• day

• hour

• minute

• second

• microsecond

In PostgreSQL and MYSQL, you can use the contains, contained_by and filter options in JSONField. The filter option allows you to filter the JSON object by its keys and values.

class JSONModel:
    data = fields.JSONField[list]()

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"])

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: In PostgreSQL, this is done with the ~ operator, while in MySQL the REGEXP operator is used.

In PostgreSQL, filter supports additional lookup types:

• in - await JSONModel.filter(data__filter={"breed__in": ["labrador", "poodle"]}).first()

• not_in

• gte

• gt

• lte

• lt

• range - await JSONModel.filter(data__filter={"age__range": [1, 10]}).first()

• startswith

• endswith

• iexact

• icontains

• istartswith

• iendswith

Complex prefetch¶

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

class tortoise.query_utils.Prefetch(relation, queryset, to_attr=None)[source]¶

Prefetcher container. One would directly use this when wanting to attach a custom QuerySet for specialised prefetching.

Parameters:¶

relation : str¶

Related field name.

queryset : 'QuerySet'¶

Custom QuerySet to use for prefetching.

to_attr : Optional[str]¶

Sets the result of the prefetch operation to a custom attribute.

resolve_for_queryset(queryset)[source]¶

Called internally to generate prefetching query.

Parameters:¶

queryset : 'QuerySet'¶

Custom QuerySet to use for prefetching.

Raises:¶

OperationalError – If field does not exist in model.

Return type:¶

None