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, **kwds)

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

class tortoise.queryset.QuerySet(model)
all()

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

Return type

QuerySet[Model]

annotate(**kwargs)

Annotate result with aggregation or function result.

Raises

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

Return type

QuerySet[Model]

count()

Return count of objects in queryset instead of objects.

Return type

CountQuery

delete()

Delete all objects in QuerySet.

Return type

DeleteQuery

distinct()

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)

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

Return type

QuerySet[Model]

exists()

Return True/False whether queryset exists.

Return type

ExistsQuery

async explain()

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)

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

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

Return type

QuerySetSingle[Optional[Model]]

force_index(*index_names)

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)

Fetch exactly one object matching the parameters.

Return type

QuerySetSingle[Model]

get_or_none(*args, **kwargs)

Fetch exactly one object matching the parameters.

Return type

QuerySetSingle[Optional[Model]]

group_by(*fields)

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

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

Return type

QuerySet[Model]

limit(limit)

Limits QuerySet to given length.

Raises

ParamsError – Limit should be non-negative number.

Return type

QuerySet[Model]

offset(offset)

Query offset for QuerySet.

Raises

ParamsError – Offset should be non-negative number.

Return type

QuerySet[Model]

only(*fields_for_select)

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)

Accept args to filter by in format like this:

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

Supports ordering by related models too.

Raises

FieldError – If unknown field has been provided.

Return type

QuerySet[Model]

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 (Type[Model]) – The Model this queryset is based on.

  • q_objects (List[Q]) – The Q expressions to apply.

  • annotations (Dict[str, Any]) – Extra annotations to add.

  • custom_filters (Dict[str, Dict[str, Any]]) – Pre-resolved filters to be passed through.

Return type

None

resolve_ordering(model, table, orderings, annotations)

Applies standard ordering to QuerySet.

Parameters
  • model (Type[Model]) – The Model this queryset is based on.

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

  • orderings (Iterable[Tuple[str, str]]) – What columns/order to order by

  • annotations (Dict[str, Any]) – 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=())

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]

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

Return the actual SQL.

Return type

str

update(**kwargs)

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)

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)

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

Return type

QuerySet[Model]

values(*args, **kwargs)

Make QuerySet return dicts instead of objects.

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

values_list(*fields_, flat=False)

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

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

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

Return type

ValuesListQuery

class tortoise.queryset.CountQuery(model, db, q_objects, annotations, custom_filters, limit, offset)
class tortoise.queryset.DeleteQuery(model, db, q_objects, annotations, custom_filters, limit, orderings)
class tortoise.queryset.ExistsQuery(model, db, q_objects, annotations, custom_filters)
class tortoise.queryset.FieldSelectQuery(model, annotations)
class tortoise.queryset.UpdateQuery(model, update_kwargs, db, q_objects, annotations, custom_filters)
class tortoise.queryset.ValuesListQuery(model, db, q_objects, fields_for_select_list, limit, offset, distinct, orderings, flat, annotations, custom_filters, group_bys)
class tortoise.queryset.ValuesQuery(model, db, q_objects, fields_for_select, limit, offset, distinct, orderings, annotations, custom_filters, group_bys)

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)

Relation container for ForeignKeyField().

all()

Returns a QuerySet with all related elements.

Return type

QuerySet[MODEL]

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]

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[Union[Awaitable[Optional[MODEL]], MODEL]]

tortoise.fields.relational.ForeignKeyRelation

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

alias of Union[Awaitable[MODEL], 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[Union[Awaitable[Optional[MODEL]], MODEL]]

tortoise.fields.relational.OneToOneRelation

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

alias of Union[Awaitable[MODEL], MODEL]

Many to Many

Tortoise ORM provides an API for working with M2M relations

class tortoise.fields.relational.ManyToManyRelation(instance, m2m_field)

Many to many relation container for ManyToManyField().

async add(*instances, using_db=None)

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)

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)

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)

Q objects

Sometimes you need to do more complicated queries than the simple AND <model>.filter() provides. Luckily we have Q objects to spice things up and help you find what you need. These Q-objects can then be used as argument to <model>.filter() instead.

Q objects are extremely versatile, some example use cases:
  • creating an OR filter

  • nested filters

  • inverted filters

  • combining any of the above to simply write complicated multilayer filters

Q objects can take any (special) kwargs for filtering that <model>.filter() accepts, see those docs for a full list of filter options in that regard.

They can also be combined by using bitwise operators (| is OR and & is AND for those unfamiliar with bitwise operators)

For example to find the events with as name Event 1 or Event 2:

found_events = await Event.filter(
    Q(name='Event 1') | Q(name='Event 2')
)

Q objects can be nested as well, the above for example is equivalent to:

found_events = await Event.filter(
    Q(Q(name='Event 1'), Q(name='Event 2'), join_type="OR")
)

If join type is omitted it defaults to AND.

Note

Q objects without filter arguments are considered NOP and will be ignored for the final query (regardless on if they are used as AND or OR param)

Also, Q objects support negated to generate NOT (~ operator) clause in your query

not_third_events = await Event.filter(~Q(name='3'))
class tortoise.query_utils.Q(*args, join_type='AND', **kwargs)

Q Expression container. Q Expressions are a useful tool to compose a query from many small parts.

Parameters
  • join_type (str) – Is the join an AND or OR join type?

  • args (Q) – Inner Q expressions that you want to wrap.

  • kwargs (Any) – Filter statements that this Q object should encapsulate.

AND = 'AND'
OR = 'OR'
__and__(other)

Returns a binary AND of Q objects, use AND operator.

Raises

OperationalError – AND operation requires a Q node

Return type

Q

__invert__()

Returns a negated instance of the Q object, use ~ operator.

Return type

Q

__or__(other)

Returns a binary OR of Q objects, use OR operator.

Raises

OperationalError – OR operation requires a Q node

Return type

Q

children

Contains the sub-Q’s that this Q is made up of

filters

Contains the filters applied to this Q

join_type

Specifies if this Q does an AND or OR on its children

negate()

Negates the curent Q object. (mutation)

Return type

None

resolve(model, annotations, custom_filters, table)

Resolves the logical Q chain into the parts of a SQL statement.

Parameters
  • model (Type[Model]) – The Model this Q Expression should be resolved on.

  • annotations (Dict[str, Any]) – Extra annotations one wants to inject into the resultset.

  • custom_filters (Dict[str, Dict[str, Any]]) – Pre-resolved filters to be passed though.

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

Return type

QueryModifier

class tortoise.query_utils.QueryModifier(where_criterion=None, joins=None, having_criterion=None)

Internal structure used to generate SQL Queries.

get_query_modifiers()

Returns a tuple of the query criterion.

Return type

Tuple[Criterion, List[Tuple[Table, Criterion]], Criterion]

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')
  • 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)

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)

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)

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