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 filtersexclude(*args, **kwargs)- create QuerySet with given excluding filtersall()- create QuerySet without filtersfirst()- create QuerySet limited to one object and returning instance instead of listannotate()- 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 kwargsget_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 beforedelete()- delete instance from dbfetch_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.
- 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.
-
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:¶
- 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.
- 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.
- async explain()[source]¶
Fetch and return information about the query execution plan.
This is done by executing an
EXPLAINquery 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.
- 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.
- 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()
- 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.
- 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()aIncompleteInstanceErrorwill 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(...), thenIncompleteInstanceErrorwill 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 inupdate_fieldswas not in the.only(...), thenIncompleteInstanceErroras that field is not available to be updated.
- 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]
Like
.fetch_related()on instance, but works on all objects in QuerySet.FieldError – If the field to prefetch on is not a relation, or not found.
QuerySet[Model]
- resolve_filters(model, q_objects, annotations, custom_filters)¶
Builds the common filters for a QuerySet.
- resolve_ordering(model, table, orderings, annotations)¶
Applies standard ordering to QuerySet.
- Parameters:¶
- 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 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.
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:¶
- 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.
- 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=Trueand 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]¶
-
class tortoise.queryset.BulkUpdateQuery(model, db, q_objects, annotations, custom_filters, limit, orderings, objects, fields, batch_size=
None)[source]¶
- 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.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 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().
- 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
instancesto the relation.If it is already added, it will be silently ignored.
- Raises:¶
OperationalError – If Object to add is not saved.
- Return type:¶
None
- filter(*args, **kwargs)¶
Returns a QuerySet with related elements filtered by args/kwargs.
- limit(limit)¶
Returns a QuerySet with at most «limit» related elements.
- offset(offset)¶
Returns a QuerySet with all related elements offset by «offset».
-
async remove(*instances, using_db=
None)[source]¶ Removes one or more of
instancesfrom the relation.- Raises:¶
OperationalError – remove() was called with no instances.
- Return type:¶
None
-
async add(*instances, using_db=
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')
notin- checks if value of field is in passed listnot_ingte- greater or equals than passed valuegt- greater than passed valuelte- lower or equals than passed valuelt- lower than passed valuerange- between and given two valuesisnull- field is nullnot_isnull- field is not nullcontains- field contains specified substringicontains- case insensitivecontainsstartswith- if field starts with valueistartswith- case insensitivestartswithendswith- if field ends with valueiendswith- case insensitiveendswithiexact- case insensitive equalssearch- 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.
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