Updating to DB

In this chapter, we’ll learn how to update records in a database using SQLAlchemy. Updating records is crucial when modifying existing data in your tables, such as changing descriptions or other fields. SQLAlchemy provides efficient and straightforward methods for performing updates.

Updating a Single Record

The most common update operation is modifying a single record. To do this, you need to fetch the object, update its attributes, and then save the changes.

python
Скопіювати код
9
1
2
3
4
5
product = session.query(Product).filter(Product.id == 1).first()

if product:
    product.description = "Updated description for high-end gaming laptop"
    session.commit()

To retrieve a product by its ID, you use a query with a filter to specify the desired ID. After updating the product's description, calling session.commit() saves the changes to the database.

Updating Multiple Records

Sometimes, you need to update multiple records at once. SQLAlchemy allows you to use filter() with the update() method to modify records efficiently.

python
Скопіювати код
9
1
2
3
4
5
session.query(Product).filter(Product.price > 1000).update(
    {Product.description: "Updated description for premium products"}, 
    synchronize_session="fetch"
)
session.commit()

To filter products priced above $1000, you use a query with a condition on the price. The update method applies the desired changes to all matching records, while the synchronize_session="fetch" argument ensures the session remains synchronized after the update.

Bulk Updates

For large-scale changes, bulk updates are more efficient as they directly modify database records without loading objects into memory. This makes them ideal for updating many rows at once.

python
Скопіювати код
9
1
2
3
4
5
session.query(Product).filter(Product.price < 500).update(
    {Product.description: "Generic description for affordable products"}, 
    synchronize_session="fetch"
)
session.commit()

This code efficiently updates the description for all products priced below $500 in a single operation. Bulk updates are faster and conserve memory compared to updating records individually.