Update data in database

The 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_lated() Method

  • query set proxy

  • QuerysetProxy.update_or_create(**kwargs) method

update

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

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

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

If you do not provide this flag or filter, a QueryDefinitionError is raised.

Returns the number of rows updated.

import asyncio

import databases
import ormar
import sqlalchemy
from examples import create_drop_database

DATABASE_URL = "sqlite:///test.db"

ormar_base_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL),
    metadata=sqlalchemy.MetaData(),
)


class Book(ormar.Model):
    ormar_config = ormar_base_config.copy(
        tablename="books",
    )

    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",
    )


@create_drop_database(base_config=ormar_base_config)
async def run_query():
    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


asyncio.run(run_query())

!!!Warns that query sets need to be filtered before updating to prevent accidental overrides.

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

update or create

update_or_create(**kwargs) -> Model

Update the model or create a new model if there are no matches in the database.

import asyncio

import databases
import ormar
import sqlalchemy
from examples import create_drop_database

DATABASE_URL = "sqlite:///test.db"

ormar_base_config = ormar.OrmarConfig(
    database=databases.Database(DATABASE_URL),
    metadata=sqlalchemy.MetaData(),
)


class Book(ormar.Model):
    ormar_config = ormar_base_config.copy()

    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",
    )


@create_drop_database(base_config=ormar_base_config)
async def run_query():
    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() == 4

    # 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() == 4


asyncio.run(run_query())

Note that if you want to create a new object, you must either pass the pk column value or the pk column must be set to autoincrement

batch update

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

Allows multiple instances to be updated at once.

All passed models need to populate primary key columns.

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

# 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 method

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

Update

You can update a model by updating model properties (fields) and calling the update() method.

If you try to update the model without setting the primary key, a ModelPersistenceError exception is thrown.

!!!tip Read more about the update() method in models-update

Update insert

It is a proxy for the model's save() or update(**kwargs) methods. If pk is set, the update() method is called.

!!!tip Read more about the upsert() method in models-upsert

Save Related

Method iterates through all relationships of the model calling it and calls the upsert() method on each unsaved model.

!!!tip Read more about the save_lated() method in models-save-lated

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.

update or create

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

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