Skip to content

Update data in database

Following methods and functions allow updating existing data in the database.

  • update(each: bool = False, **kwargs) -> int
  • update_or_create(**kwargs) -> Model

  • bulk_update(objects: List[Model], columns: List[str] = None) -> None

  • Model

    • Model.update() method
    • Model.upsert() method
    • Model.save_related() method

  • QuerysetProxy

    • QuerysetProxy.update_or_create(**kwargs) method

update

update(each: bool = False, **kwargs) -> int

QuerySet level update is used to update multiple records with the same value at once.

You either have to filter the QuerySet first or provide a each=True flag to update whole table.

If you do not provide this flag or a filter a QueryDefinitionError will be raised.

Return number of rows updated.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import databases
import ormar
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Book(ormar.Model):
    class Meta:
        tablename = "books"
        metadata = metadata
        database = database

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    author: str = ormar.String(max_length=100)
    genre: str = ormar.String(max_length=100, default='Fiction',
                              choices=['Fiction', 'Adventure', 'Historic', 'Fantasy'])


await Book.objects.create(title='Tom Sawyer', author="Twain, Mark", genre='Adventure')
await Book.objects.create(title='War and Peace', author="Tolstoy, Leo", genre='Fiction')
await Book.objects.create(title='Anna Karenina', author="Tolstoy, Leo", genre='Fiction')

await Book.objects.update(each=True, genre='Fiction')
all_books = await Book.objects.filter(genre='Fiction').all()
assert len(all_books) == 3

Warning

Queryset needs to be filtered before updating to prevent accidental overwrite.

To update whole database table each=True needs to be provided as a safety switch

update_or_create

update_or_create(**kwargs) -> Model

Updates the model, or in case there is no match in database creates a new one.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
import databases
import ormar
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Book(ormar.Model):
    class Meta:
        tablename = "books"
        metadata = metadata
        database = database

    id: int = ormar.Integer(primary_key=True)
    title: str = ormar.String(max_length=200)
    author: str = ormar.String(max_length=100)
    genre: str = ormar.String(max_length=100, default='Fiction',
                              choices=['Fiction', 'Adventure', 'Historic', 'Fantasy'])


await Book.objects.create(title='Tom Sawyer', author="Twain, Mark", genre='Adventure')
await Book.objects.create(title='War and Peace', author="Tolstoy, Leo", genre='Fiction')
await Book.objects.create(title='Anna Karenina', author="Tolstoy, Leo", genre='Fiction')

# if not exist the instance will be persisted in db
vol2 = await Book.objects.update_or_create(title="Volume II", author='Anonymous', genre='Fiction')
assert await Book.objects.count() == 1

# if pk or pkname passed in kwargs (like id here) the object will be updated
assert await Book.objects.update_or_create(id=vol2.id, genre='Historic')
assert await Book.objects.count() == 1

Note

Note that if you want to create a new object you either have to pass pk column value or pk column has to be set as autoincrement

bulk_update

bulk_update(objects: List["Model"], columns: List[str] = None) -> None

Allows to update multiple instance at once.

All Models passed need to have primary key column populated.

You can also select which fields to update by passing columns list as a list of string names.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# continuing the example from bulk_create
# update objects
for todo in todoes:
    todo.completed = False

# perform update of all objects at once
# objects need to have pk column set, otherwise exception is raised
await ToDo.objects.bulk_update(todoes)

completed = await ToDo.objects.filter(completed=False).all()
assert len(completed) == 3

Model methods

Each model instance have a set of methods to save, update or load itself.

update

You can update models by updating your model attributes (fields) and calling update() method.

If you try to update a model without a primary key set a ModelPersistenceError exception will be thrown.

Tip

Read more about update() method in models-update

upsert

It's a proxy to either save() or update(**kwargs) methods of a Model. If the pk is set the update() method will be called.

Tip

Read more about upsert() method in models-upsert

Method goes through all relations of the Model on which the method is called, and calls upsert() method on each model that is not saved.

Tip

Read more about save_related() method in models-save-related

QuerysetProxy methods

When access directly the related ManyToMany field as well as ReverseForeignKey returns the list of related models.

But at the same time it exposes subset of QuerySet API, so you can filter, create, select related etc related models directly from parent model.

update_or_create

Works exactly the same as update_or_create function above but allows you to update or create related objects from other side of the relation.

Tip

To read more about QuerysetProxy visit querysetproxy section