How to update SQLAlchemy row entry?

python

sqlalchemy

database-operations

update-queries

byAnton Shumikhin·Dec 19, 2024

To update a SQLAlchemy row, fetch the instance, modify its properties, and invoke session.commit(). Take a look at the code below:

# 'session' is your SQLAlchemy session, 'User' is the model, 'user_id' is the id of the user to update
user = session.query(User).get(user_id)
if user:
    user.name = 'Updated Name'  # New Identity, who dis?
    session.commit()

This syntax fetches the user by primary key with session.query(User).get(user_id), verifies the existence of the user, assigns a new name, and commits the changes to the database.

Diving deep into SQLAlchemy updates

Bulk updates: The surgical approach

For bulk updates where you don't need to keep track of individual instances:

# Equivalent to surgical name change in Witness Protection Program
session.query(User).filter(User.username == 'old_username').update({'username': 'new_username'})
session.commit()

Field increments: Counting your victories

To avoid race conditions when updating a count, do the increment on the database side:

# Every login is a victory,
session.query(User).filter(User.username == 'user123').update({User.no_of_logins: User.no_of_logins + 1})
session.commit()

Exceptions: They do happen

Always catch exceptions during database operations to avoid unexpected surprises:

try:
    session.query(User).filter(User.username == 'user123').update({'username': 'new_username'})
    session.commit()
    # You have successfully modified the matrix.
except Exception as e:
    session.rollback()
    # Oops: The matrix resisted your modification!
    raise

Consider concurrent updates: Isolation is key

To handle concurrency during an update, use a database transaction isolation level that suits your requirements:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///database.db', isolation_level="SERIALIZABLE")  # Isolating, just like my 6th-grade lunch period
Session = sessionmaker(bind=engine)
session = Session()

user = session.query(User).with_for_update().filter(User.username == 'user123').first()  # While everyone's seen the user, let's make an update
user.no_of_logins += 1
session.commit()

Multiple updates: With great power comes great responsibility

Update multiple records in a single sweep, reducing trips to the database:

# Going dark, turning the 'is_active' field to False!
session.query(User).filter(User.is_active).update({'is_active': False}, synchronize_session='fetch')
session.commit()

Database-side operations: SQLAlchemy brings the fun

Embrace the full power of SQL expressions within SQLAlchemy for more complex updates:

from sqlalchemy import func

# Users inactive? Let's do some cleanup
session.query(User).filter(User.last_login < func.now() - timedelta(days=90)).update({'status': 'inactive'}, synchronize_session=False)
session.commit()