Query API ¶

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
QuerySet¶
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.
all()[source]¶
Return the whole QuerySet.
Essentially a no-op except as the only operation.
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),
and returns created objects as a list, in the same order as provided
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]
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]
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 ...
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:¶
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]
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_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]
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]
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:¶
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:¶
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]
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(**kwargs)[source]¶
Return the actual SQL.
Return type:¶
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:¶
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 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 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
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]
offset(offset)[source]¶
Returns a QuerySet with all related elements offset by «offset».
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:¶
filter(*args, **kwargs)¶
Returns a QuerySet with related elements filtered by args/kwargs.
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:¶
You can use them like this:
await event.participants.add(participant_1, participant_2)
Filtering¶
When using .filter() method you can use number of modifiers to field names to specify desired operation
teams = await Team.filter(name__icontains='CON')
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()
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¶
Related field name.
queryset : QuerySet¶
Custom QuerySet to use for prefetching.
to_attr=None¶
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:¶
Query API ¶ (adsbygoogle = window.adsbygoogle || []).push({});

QuerySet¶

Foreign Key¶

One to One¶

Many to Many¶

Filtering¶

Complex prefetch¶

Query API ¶
