Queries¶

Making queries is a must when using an ORM and being able to make complex queries is even better when allowed.

SQLAlchemy is known for its performance when querying a database and it is very fast. The core being part of Saffier also means that saffier performs extremely well when doing it.

When making queries in a model, the ORM uses the managers to perform those same actions.

If you haven't yet seen the models and managers section, now would be a great time to have a look and get yourself acquainted .

QuerySet¶

When making queries within Saffier, this return or an object if you want only one result or a queryset which is the internal representation of the results.

If you are familiar with Django querysets, this is almost the same and by almost is because saffier restricts loosely queryset variable assignments.

Let us get familar with queries.

Let us assume you have the following User model defined.

import saffier
from saffier import Database, Registry

database = Database("sqlite:///db.sqlite")
models = Registry(database=database)


class User(saffier.Model):
    is_active = saffier.BooleanField(default=True)
    first_name = saffier.CharField(max_length=50)
    last_name = saffier.CharField(max_length=50)
    email = saffier.EmailField(max_lengh=100)
    password = saffier.CharField(max_length=1000)

    class Meta:
        registry = models


class User(saffier.Model):
    user = saffier.ForeignKey(User, on_delete=saffier.CASCADE)

    class Meta:
        registry = models

As mentioned before, Saffier returns queysets and simple objects and when queysets are returned those can be chained together, for example, with filter() or limit().

await User.query.filter(is_active=True).filter(first_name__icontains="a").order_by("id")

Do we really need two filters here instead of one containing both conditions? No, we do not but this is for example purposes.

Internally when querying the model and with returning querysets, Saffier runs the all(). This can be done manually by you or automatically by the ORM.

Let us refactor the previous queryset and apply the manual all().

await User.query.filter(is_active=True, first_name__icontains="a").order_by("id").all()

And that is it. Of course there are more filters and operations that you can do with the ORM and we will be covering that in this document but in a nutshell, querying the database is this simple.

Returning querysets¶

There are many operations you can do with the querysets and then you can also leverage those for your use cases.

Exclude¶

The exclude() is used when you want to filter results by excluding instances.

users = await User.query.exclude(is_active=False)

Filter¶

Django-style¶

These filters are the same Django-style lookups.

users = await User.query.filter(is_active=True, email__icontains="gmail")

The same special operators are also automatically added on every column.

in - SQL IN operator.
exact - Filter instances matching the exact value.
iexact - Filter instances mathing the exact value but case-insensitive.
contains - Filter instances that contains a specific value.
icontains - Filter instances that contains a specific value but case-insensitive.
lt - Filter instances having values Less Than.
lte - Filter instances having values Less Than Equal.
gt - Filter instances having values Greater Than.
gte - Filter instances having values Greater Than Equal.

Example¶

users = await User.query.filter(email__icontains="foo")

users = await User.query.filter(id__in=[1, 2, 3])

SQLAlchemy style¶

Since Saffier uses SQLAlchemy core, it is also possible to do queries in SQLAlchemy style. The filter accepts also those.

Example¶

users = await User.query.filter(User.columns.email.contains("foo"))

users = await User.query.filter(User.columns.id.in_([1, 2, 3]))

Warning

The columns refers to the columns of the underlying SQLAlchemy table.

Limit¶

Limiting the number of results. The LIMIT in SQL.

users = await User.query.limit(1)

users = await User.query.filter(email__icontains="foo").limit(2)

Offset¶

Applies the office to the query results.

users = await User.query.offset(1)

users = await User.query.filter(is_active=False).offset(2)

Since you can chain the querysets from other querysets, you can aggregate multiple operators in one go as well.

await User.query.filter(email__icontains="foo").limit(5).order_by("id")

Order by¶

Classic SQL operation and you need to order results.

Order by descending id and ascending email

users = await User.query.order_by("email", "-id")

Order by ascending id and ascending email

users = await User.query.order_by("email", "id")

Lookup¶

This is a broader way of searching for a given term. This can be quite an expensive operation so be careful when using it.

users = await User.query.lookup(term="gmail")

Distinct¶

Applies the SQL DISTINCT ON on a table.

users = await User.query.distinct("email")

Warning

Not all the SQL databases support the DISTINCT ON fields equally, for example, mysql has has that limitation whereas postgres does not. Be careful to know and understand where this should be applied.

Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query.

This is a performance booster which results in a single more complex query but means

later use of foreign-key relationships won’t require database queries.

A simple query:

profiles = await Profile.query.select_related("user")

Or adding more operations on the top

profiles = await Profile.query.select_related("user").filter(email__icontains="foo").limit(2)

Returning results¶

All¶

Returns all the instances.

users = await User.query.all()

Tip

The all as mentioned before it automatically executed by Saffier if not provided and it can also be aggregated with other queryset operations.

Save¶

This is a classic operation that is very useful depending on which operations you need to perform. Used to save an existing object in the database. Slighly different from the update and simpler to read.

await User.query.create(is_active=True, email="foo@bar.com")

user = await User.query.get(email="foo@bar.com")
user.email = "bar@foo.com"

await user.save()

Now a more unique, yet possible scenario with a save. Imagine you need to create an exact copy of an object and store it in the database. These cases are more common than you think but this is for example purposes only.

await User.query.create(is_active=True, email="foo@bar.com", name="John Doe")

user = await User.query.get(email="foo@bar.com")
# User(id=1)

# Making a quick copy
user.id = None
new_user = await user.save()
# user(id=2)

Create¶

Used to create model instances.

await User.query.create(is_active=True, email="foo@bar.com")
await User.query.create(is_active=False, email="bar@foo.com")
await User.query.create(is_active=True, email="foo@bar.com", first_name="Foo", last_name="Bar")

Delete¶

Used to delete an instance.

await User.query.filter(email="foo@bar.com").delete()

Or directly in the instance.

user = await User.query.get(email="foo@bar.com")

await user.delete()

Update¶

You can update model instances by calling this operator.

await User.query.filter(email="foo@bar.com").update(email="bar@foo.com")

Or directly in the instance.

user = await User.query.get(email="foo@bar.com")

await user.update(email="bar@foo.com")

Or not very common but also possible, update all rows in a table.

user = await User.query.update(email="bar@foo.com")

Get¶

Obtains a single record from the database.

user = await User.query.get(email="foo@bar.com")

You can mix the queryset returns with this operator as well.

user = await User.query.filter(email="foo@bar.com").get()

First¶

When you need to return the very first result from a queryset.

user = await User.query.first()

You can also apply filters when needed.

Last¶

When you need to return the very last result from a queryset.

user = await User.query.last()

You can also apply filters when needed.

Exists¶

Returns a boolean confirming if a specific record exists.

exists = await User.query.filter(email="foo@bar.com").exists()

Count¶

Returns an integer with the total of records.

total = await User.query.count()

Contains¶

Returns true if the QuerySet contains the provided object.

user = await User.query.create(email="foo@bar.com")

exists = await User.query.contains(instance=user)

Values¶

Returns the model results in a dictionary like format.

await User.query.create(name="John" email="foo@bar.com")

# All values
user = User.query.values()
users == [
    {"id": 1, "name": "John", "email": "foo@bar.com"},
]

# Only the name
user = User.query.values("name")
users == [
    {"name": "John"},
]
# Or as a list
# Only the name
user = User.query.values(["name"])
users == [
    {"name": "John"},
]

# Exclude some values
user = User.query.values(exclude=["id"])
users == [
    {"name": "John", "email": "foo@bar.com"},
]

The values() can also be combined with filter, only, exclude as per usual.

Parameters:

fields - Fields of values to return.
exclude - Fields to exclude from the return.
exclude_none - Boolean flag indicating if the fields with None should be excluded.

Values list¶

Returns the model results in a tuple like format.

await User.query.create(name="John" email="foo@bar.com")

# All values
user = User.query.values_list()
users == [
    (1, "John" "foo@bar.com"),
]

# Only the name
user = User.query.values_list("name")
users == [
    ("John",),
]
# Or as a list
# Only the name
user = User.query.values_list(["name"])
users == [
    ("John",),
]

# Exclude some values
user = User.query.values(exclude=["id"])
users == [
    ("John", "foo@bar.com"),
]

# Flattened
user = User.query.values_list("email", flat=True)
users == [
    "foo@bar.com",
]

The values_list() can also be combined with filter, only, exclude as per usual.

Parameters:

fields - Fields of values to return.
exclude - Fields to exclude from the return.
exclude_none - Boolean flag indicating if the fields with None should be excluded.
flat - Boolean flag indicating the results should be flattened.

Get or none¶

When querying a model and do not want to raise a ObjectNotFound and instead returns a None.

user = await User.query.get_or_none(id=1)

Useful methods¶

Get or create¶

When you need get an existing model instance from the matching query. If exists, returns or creates a new one in case of not existing.

Returns a tuple of instance and boolean created.

user, created = await User.query.get_or_create(email="foo@bar.com", defaults={
    "is_active": False, "first_name": "Foo"
})

This will query the User model with the email as the lookup key. If it doesn't exist, then it will use that value with the defaults provided to create a new instance.

Warning

Since the get_or_create() is doing a get internally, it can also raise a MultipleObjectsReturned.

Update or create¶

When you need to update an existing model instance from the matching query. If exists, returns or creates a new one in case of not existing.

Returns a tuple of instance and boolean created.

user, created = await User.query.update_or_create(email="foo@bar.com", defaults={
    "is_active": False, "first_name": "Foo"
})

This will query the User model with the email as the lookup key. If it doesn't exist, then it will use that value with the defaults provided to create a new instance.

Warning

Since the get_or_create() is doing a get internally, it can also raise a MultipleObjectsReturned.

Bulk create¶

When you need to create many instances in one go, or in bulk.

await User.query.bulk_create([
    {"email": "foo@bar.com", "first_name": "Foo", "last_name": "Bar", "is_active": True},
    {"email": "bar@foo.com", "first_name": "Bar", "last_name": "Foo", "is_active": True},
])

Bulk update¶

When you need to update many instances in one go, or in bulk.

await User.query.bulk_create([
    {"email": "foo@bar.com", "first_name": "Foo", "last_name": "Bar", "is_active": True},
    {"email": "bar@foo.com", "first_name": "Bar", "last_name": "Foo", "is_active": True},
])

users = await User.query.all()

for user in users:
    user.is_active = False

await User.query.bulk_update(users, fields=['is_active'])

Operators¶

There are sometimes the need of adding some extra conditions like AND, or OR or even the NOT into your queries and therefore Saffier provides a simple integration with those.

Saffier provides the and_, or_ and not_ operators directly for you to use, although this ones come with a slighly different approach.

For all the examples, let us use the model below.

import saffier
from saffier import Database, Registry

database = Database("sqlite:///db.sqlite")
models = Registry(database=database)


class User(saffier.Model):
    first_name: str = saffier.CharField(max_length=50, null=True)
    email: str = saffier.EmailField(max_lengh=100, null=True)

    class Meta:
        registry = models

SQLAlchemy style¶

Since Saffier is built on the top of SQL Alchemy core, that also means we can also use directly that same functionality within our queries.

In other words, uses the SQLAlchemy style.

Warning

The or_, and_ and not_ do not work with related operations and only directly with the model itself.

This might sound confusing so let us see some examples.

AND¶

As the name suggests, you want to add the AND explicitly.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the and_
await User.query.filter(
    saffier.and_(User.columns.name == "Adam", User.columns.email == "adam@saffier.dev"),
)

As mentioned before, applying the SQLAlchemy style also means you can do this.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the and_
await User.query.filter(
    saffier.and_(
        User.columns.email.contains("saffier"),
    )
)

And you can do nested querysets like multiple filters.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the and_
await User.query.filter(saffier.and_(User.columns.name == "Adam")).filter(
    saffier.and_(User.columns.email == "adam@saffier.dev")
)

OR¶

The same principle as the and_ but applied to the OR.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the or_
await User.query.filter(
    saffier.or_(User.columns.name == "Adam", User.columns.email == "adam@saffier.dev"),
)

As mentioned before, applying the SQLAlchemy style also means you can do this.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the or_
await User.query.filter(
    saffier.or_(
        User.columns.email.contains("saffier"),
    )
)

And you can do nested querysets like multiple filters.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the or_
await User.query.filter(saffier.or_(User.columns.name == "Adam")).filter(
    saffier.or_(User.columns.email == "adam@saffier.dev")
)

NOT¶

This is simple and direct, this is where you apply the NOT.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the not_
await User.query.filter(saffier.not_(User.columns.name == "Adam"))

As mentioned before, applying the SQLAlchemy style also means you can do this.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the not_
await User.query.filter(
    saffier.not_(
        User.columns.email.contains("saffier"),
    )
)

And you can do nested querysets like multiple filters.

import saffier

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")
await User.query.create(name="John", email="john@example.com")

# Query using the not_
await User.query.filter(saffier.not_(User.columns.name == "Adam")).filter(
    saffier.not_(User.columns.email.contains("saffier"))
)

Saffier Style¶

This is the most common used scenario where you can use the related for your queries and all the great functionalities of Saffier while using the operands.

Tip

The same way you apply the filters for the queries using the related, this can also be done with the Saffier style but the same cannot be said for the SQLAlchemy style. So if you want to leverage the full power of Saffier, it is advised to go Saffier style.

AND¶

The AND operand with the syntax is the same as using the filter or any queryset operatator but for visualisation purposes this is also available in the format of and_.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the and_
await User.query.and_(email__icontains="saffier")

With multiple parameters.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the and_
await User.query.and_(name="Adam", email="adam@saffier.dev")

And you can do nested querysets like multiple filters.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the and_
await User.query.filter(name="Adam").and_(email="adam@saffier.dev")

OR¶

The same principle as the and_ but applied to the OR.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the or_
await User.query.or_(name="Adam", email="adam@saffier.dev")

With multiple or_ or nultiple parametes in the same or_

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the multiple or_
await User.query.or_(email__icontains="saffier").or_(name__icontains="a")

# Query using the or_ with multiple fields
await User.query.or_(email__icontains="saffier", name__icontains="a")

And you can do nested querysets like multiple filters.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the or_
await User.query.or_(name="Adam").filter(email="adam@saffier.dev")

NOT¶

The not_ as the same principle as the exclude and like the and, for representation purposes, Saffier also has that function.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the not_
await User.query.not_(name="Adam")

With multiple not_.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")

# Query using the not_
await User.query.not_(email__icontains="saffier").not_(name__icontains="a")

And you can do nested querysets like multiple filters.

# Create some records

await User.query.create(name="Adam", email="adam@saffier.dev")
await User.query.create(name="Eve", email="eve@saffier.dev")
await User.query.create(name="John", email="john@example.com")

# Query using the not_
await User.query.filter(email__icontains="saffier").not_(name__iexact="Adam")

Internally, the not_ is calling the exclude and applying the operators so this is more for cosmetic purposes than anything else, really.

Queries¶

QuerySet¶

Returning querysets¶

Exclude¶

Filter¶

Django-style¶

Example¶

SQLAlchemy style¶

Example¶

Limit¶

Offset¶

Order by¶

Lookup¶

Distinct¶

Select related¶

Returning results¶

All¶

Save¶

Create¶

Delete¶

Update¶

Get¶

First¶

Last¶

Exists¶

Count¶

Contains¶

Values¶

Values list¶

Get or none¶

Useful methods¶

Get or create¶

Update or create¶

Bulk create¶

Bulk update¶

Operators¶

SQLAlchemy style¶

AND¶

OR¶

NOT¶

Saffier Style¶

AND¶

OR¶

NOT¶