Skip to content

Return to original data

The following methods allow you to execute a query, but instead of returning an ormar model, they return a dictionary or tuple list.

  • values(fields = None, exclude_through = False) -> List[Dict]

  • values_list(fields = None, exclude_through = False, flatten = False) -> List

  • query set proxy

    • QuerysetProxy.values(fields = None, exclude_through = False) method
    • QuerysetProxy.values_list(fields = None, exclude_through= False, flatten = False) method

!!!danger Note that values and values_list skip parsing the results to ormar models and therefore also skip validation of the results!

!!!warning Note that each entry in the results list is a one-to-one reflection of the query result rows. Because rows are not resolved if you have one-to-many or many-to-many relationships, duplicate column values appear in the results entries if a parent row has multiple related rows.

value

values(fields: Union[List, str, Set, Dict] = None, exclude_through: bool = False) -> List[Dict]

Returns a dictionary list representing the values of columns from the database.

You can use the fields parameter to select a subset of fields that accepts the same parameter set as the fields() method.

Note that passing fields to values(fields) is actually a shortcut to calling fields(fields).values().

!!!tip To learn more about what you can pass to fields and how to select nested model fields, read the Select Columns documentation

You can limit the number of rows by providing conditions in filter() and except(), but note that even if only one row (or none!) matches your condition, you will return a list in response.

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# declared models

class Category(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="categories")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=40)
    sort_order: int = ormar.Integer(nullable=True)


class Post(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=200)
    category: Optional[Category] = ormar.ForeignKey(Category)

# sample data
news = await Category(name="News", sort_order=0).save()
await Post(name="Ormar strikes again!", category=news).save()
await Post(name="Why don't you use ormar yet?", category=news).save()
await Post(name="Check this out, ormar now for free", category=news).save()

Visit Postal Model:

1
2
3
4
5
6
posts = await Post.objects.values()
assert posts == [
    {"id": 1, "name": "Ormar strikes again!", "category": 1},
    {"id": 2, "name": "Why don't you use ormar yet?", "category": 1},
    {"id": 3, "name": "Check this out, ormar now for free", "category": 1},
]

To select related models at the same time, use select_lated or prefetch_lated.

Notice how nested model columns are prefixed from the full relational path of the master model (the model used in the query).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# declare models

class User(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class Role(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)
    users: List[User] = ormar.ManyToMany(User)

# sample data
creator = await User(name="Anonymous").save()
admin = await Role(name="admin").save()
editor = await Role(name="editor").save()
await creator.roles.add(admin)
await creator.roles.add(editor)

Select users with roles

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
user = await User.objects.select_related("roles").values()
# note nested prefixes: roleuser and roles
assert user == [
    {
        "id": 1,
        "name": "Anonymous",
        "roleuser__id": 1,
        "roleuser__role": 1,
        "roleuser__user": 1,
        "roles__id": 1,
        "roles__name": "admin",
    },
    {
        "id": 1,
        "name": "Anonymous",
        "roleuser__id": 2,
        "roleuser__role": 2,
        "roleuser__user": 1,
        "roles__id": 2,
        "roles__name": "editor",
    },
]

!!!Note Note that personas have many-to-many relationships with users, so you can also get model columns by default.

Select related fields and fields to select only 3 fields.

Note that we also exclude definitions by model, because by definition, every model included in the join but without any references in the fields is assumed to be fully selected (including all fields).

!!!Note that, contrary to the other query set approaches here, you can exclude the middle model but keep the last column, which makes no sense when parsing the original data into models.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
So in relation category -> category_x_post -> post -> user you can exclude
category_x_post and post models but can keep the user one. (in ormar model
context that is not possible as if you would exclude through and post model
there would be no way to reach user model from category model).


user = (
        await Role.objects.select_related("users__categories")
        .filter(name="admin")
        .fields({"name": ..., "users": {"name": ..., "categories": {"name"}}})
        .exclude_fields("roleuser")
        .values()
    )
assert user == [
    {
        "name": "admin",
        "users__name": "Anonymous",
        "users__categories__name": "News",
    }
]

If there are multiple ManyToMany models in the query, you must manually exclude each model.

To avoid this burden, ormar provides you with the except_through = False parameter. If this flag is set to True, all models will be completely excluded.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# equivalent to query above, note lack of exclude_fields call
user = (
    await Role.objects.select_related("users__categories")
    .filter(name="admin")
    .fields({"name": ..., "users": {"name": ..., "categories": {"name"}}})
    .values(exclude_through=True)
)
assert user == [
    {
        "name": "admin",
        "users__name": "Anonymous",
        "users__categories__name": "News",
    }
]

values list

values_list(fields: Union[List, str, Set, Dict] = None, flatten: bool = False, exclude_through: bool = False) -> List

Returns a list of tuples representing the values of columns from the database.

You can use the fields parameter to select a subset of fields that accepts the same parameter set as the fields() method.

Note that passing fields to values_list(fields) is actually a shortcut to calling fields(fields).values_list().

!!!tip To learn more about what you can pass to fields and how to select nested model fields, read the Select Columns documentation

If you select only one column/field, you can pass flatten=True, which returns a list of values instead of a list of tuples of elements.

!!!Warning Setting flatten=True will throw a QueryDefinitionError exception if multiple (or none, meaning all) fields are selected.

You can limit the number of rows by providing conditions in filter() and except(), but note that even if only one row (or none!) matches your condition, you will return a list in response.

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# declared models

class Category(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="categories")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=40)
    sort_order: int = ormar.Integer(nullable=True)


class Post(ormar.Model):
    ormar_config = base_ormar_config.copy()

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=200)
    category: Optional[Category] = ormar.ForeignKey(Category)

# sample data
news = await Category(name="News", sort_order=0).save()
await Post(name="Ormar strikes again!", category=news).save()
await Post(name="Why don't you use ormar yet?", category=news).save()
await Post(name="Check this out, ormar now for free", category=news).save()

Visit Postal Model:

1
2
3
4
5
6
7
posts = await Post.objects.values_list()
# note how columns refer to id, name and category (fk)
assert posts == [
    (1, "Ormar strikes again!", 1),
    (2, "Why don't you use ormar yet?", 1),
    (3, "Check this out, ormar now for free", 1),
]

To select related models at the same time, use select_lated or prefetch_lated.

Let's complicate the relationship and modify the category model mentioned earlier to reference the user model.

1
2
3
4
5
6
7
8
class Category(ormar.Model):
    ormar_config = base_ormar_config.copy(tablename="categories")

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=40)
    sort_order: int = ormar.Integer(nullable=True)
    # new column below
    created_by: Optional[User] = ormar.ForeignKey(User, related_name="categories")

Now create sample data with user links.

1
2
3
4
5
6
creator = await User(name="Anonymous").save()
admin = await Role(name="admin").save()
editor = await Role(name="editor").save()
await creator.roles.add(admin)
await creator.roles.add(editor)
news = await Category(name="News", sort_order=0, created_by=creator).save()

Select related fields and fields to select only 3 fields.

Note that we also exclude definitions by model, because by definition, every model included in the join but without any references in the fields is assumed to be fully selected (including all fields).

!!!Note that, contrary to the other query set approaches here, you can exclude the middle model but keep the last column, which makes no sense when parsing the original data into models.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
So in relation category -> category_x_post -> post -> user you can exclude
category_x_post and post models but can keep the user one. (in ormar model
context that is not possible as if you would exclude through and post model
there would be no way to reach user model from category model).


user = (
        await Role.objects.select_related("users__categories")
        .filter(name="admin")
        .fields({"name": ..., "users": {"name": ..., "categories": {"name"}}})
        .exclude_fields("roleuser")
        .values_list()
    )
assert user == [("admin", "Anonymous", "News")]

If there are multiple ManyToMany models in the query, you must manually exclude each model.

To avoid this burden, ormar provides you with the except_through=False parameter. If this flag is set to True, all models will be completely excluded.

1
2
3
4
5
6
7
8
# equivalent to query above, note lack of exclude_fields call
user = (
        await Role.objects.select_related("users__categories")
        .filter(name="admin")
        .fields({"name": ..., "users": {"name": ..., "categories": {"name"}}})
        .values_list(exclude_through=True)
    )
assert user == [("admin", "Anonymous", "News")]

Use flatten to get a list of values.

1
2
3
4
5
6
# using flatten with more than one field will raise exception!
await Role.objects.fields({"name", "id"}).values_list(flatten=True)

# proper usage
roles = await Role.objects.fields("name").values_list(flatten=True)
assert roles == ["admin", "editor"]

QuerysetProxy method

Returns a list of related models when directly accessing the related ManyToMany field and ReverseForeignKey.

But it also exposes a subset of the QuerySet API, so you can filter, create, select related models, and so on directly from the parent model.

!!!warning Because using values and values_list skips parsing and validation of models, these two methods do not purge the currently loaded dependent model and overwrite the currently loaded model with their own call results, compared to all other read methods in querysetproxy!

value

Exactly the same value function as above, but allows you to get related objects from the other end of the relationship.

!!!tip To learn more about QuerysetProxy, visit the QuerysetProxy section

values list

Identical to the value_list function above, but allows you to query or create related objects from the other end of the relationship.

!!!tip To learn more about QuerysetProxy, visit the QuerysetProxy section