Course contentsShow
← Data Science
Lesson 1124 of 2,14528. Working with Databases from PythonFree lesson

Insert, Update, Delete, and Bulk Operations

Adding new records, modifying existing data, removing rows, and optimizing bulk operations.

Insert, Update, Delete, and Bulk Operations

What you'll learn: How to add, modify, and remove records in your database using SQLAlchemy ORM, plus techniques for handling many rows efficiently.

The Core Idea

Now that you can query data with SQLAlchemy's ORM, you need to modify it. Think of your database as a spreadsheet: sometimes you add rows (insert), change existing cells (update), remove rows (delete), or perform these actions on hundreds of rows at once (bulk operations). SQLAlchemy wraps these operations in Python methods attached to your session object.

Basic Operations

Inserting new records means creating model instances and adding them to the session:

new_user = User(name="Alice", email="alice@example.com")
session.add(new_user)
session.commit()  # Persists to database

Updating existing records involves querying, modifying attributes, then committing:

user = session.query(User).filter_by(name="Alice").first()
user.email = "alice.new@example.com"
session.commit()

Deleting works similarly—query, then delete:

user = session.query(User).filter_by(name="Alice").first()
session.delete(user)
session.commit()

Bulk Operations

For many rows, individual operations are slow. SQLAlchemy offers bulk methods that bypass some ORM overhead:

# Bulk insert
session.bulk_insert_mappings(User, [
    {"name": "Bob", "email": "bob@example.com"},
    {"name": "Carol", "email": "carol@example.com"}
])

# Bulk update
session.bulk_update_mappings(User, [
    {"id": 1, "email": "updated1@example.com"},
    {"id": 2, "email": "updated2@example.com"}
])

session.commit()

These methods trade ORM features (like relationship handling) for speed.

Key Takeaway: SQLAlchemy ORM provides add(), attribute modification, and delete() for single-record operations, while bulk_insert_mappings() and bulk_update_mappings() optimize performance when working with many rows at once—always remember to commit() your changes.