Database Operations

🗄️ Flask Database Operations: Your Data Kitchen Adventure

Analogy: Think of your database like a giant recipe box in a kitchen. CRUD operations are how you add new recipe cards, read them, update them when you discover better ingredients, and throw away the ones you don’t need anymore.

🍳 What Are Database Operations?

Imagine you run a magical kitchen. Your database is your recipe box, and Flask-SQLAlchemy is your helpful kitchen assistant who organizes everything for you.

In real life:

• When you save a contact on your phone → that’s Create
• When you look up someone’s number → that’s Read
• When you change their address → that’s Update
• When you delete an old contact → that’s Delete

📝 CRUD Operations: The Four Magic Spells

Create - Adding New Recipe Cards

When you want to save something new to your database, you create it.

# Create a new user
new_user = User(
    name="Alice",
    email="alice@example.com"
)
db.session.add(new_user)
db.session.commit()

What’s happening?

1. You write a new recipe card (User(...))
2. You hand it to your assistant (db.session.add())
3. Your assistant files it permanently (db.session.commit())

Read - Finding Recipe Cards

Reading is how you look up information that’s already saved.

# Find one user by ID
user = User.query.get(1)

# Find all users
all_users = User.query.all()

# Find first matching user
first_user = User.query.first()

Think of it like:

• get(1) → “Give me recipe card #1”
• all() → “Show me ALL recipe cards”
• first() → “Just show me the first one you find”

Update - Fixing Recipe Cards

Sometimes you need to change information that’s already saved.

# Find the user first
user = User.query.get(1)

# Change their email
user.email = "newemail@example.com"

# Save the change
db.session.commit()

Simple as:

1. Find the card
2. Erase and rewrite
3. Put it back in the box

Delete - Throwing Away Recipe Cards

When something is no longer needed, you delete it.

# Find the user
user = User.query.get(1)

# Remove them
db.session.delete(user)
db.session.commit()

⚠️ Warning: Once deleted and committed, it’s gone forever!

🔍 Database Query Methods: Different Ways to Search

graph TD
    A["Start Query"] --> B{What do you need?}
    B -->|One specific item| C[".get id"]
    B -->|All items| D[".all"]
    B -->|First match| E[".first"]
    B -->|With conditions| F[".filter_by"]
    B -->|Complex conditions| G[".filter"]

The Search Methods

# Examples
user = User.query.get(5)
users = User.query.all()
admin = User.query.first()
total = User.query.count()

⛓️ Query Chaining: Building Complex Searches

Query chaining is like adding more instructions to your search. Each method adds another filter, like a series of sieves.

# Find active admins, sorted by name
results = User.query \
    .filter_by(role='admin') \
    .filter_by(is_active=True) \
    .order_by(User.name) \
    .all()

Think of it like:

1. “Start with all users” → User.query
2. “Keep only admins” → .filter_by(role='admin')
3. “Keep only active ones” → .filter_by(is_active=True)
4. “Sort by name” → .order_by(User.name)
5. “Give me the list” → .all()

Common Chain Methods

.filter()       # Add conditions
.filter_by()    # Simple equality filters
.order_by()     # Sort results
.limit()        # Max number of results
.offset()       # Skip first N results
.distinct()     # Remove duplicates

🎯 Advanced Query Filters: Precision Searching

Sometimes filter_by() isn’t enough. That’s when you use filter() with special operators.

Comparison Operators

# Greater than
User.query.filter(User.age > 18).all()

# Less than or equal
User.query.filter(User.age <= 65).all()

# Not equal
User.query.filter(User.name != 'Admin').all()

# Between values
User.query.filter(
    User.age.between(18, 30)
).all()

Text Search Magic

# Contains text (like %word%)
User.query.filter(
    User.name.like('%john%')
).all()

# Case-insensitive search
User.query.filter(
    User.name.ilike('%JOHN%')
).all()

# Starts with
User.query.filter(
    User.email.startswith('admin')
).all()

Combining Conditions

from sqlalchemy import and_, or_, not_

# AND: Both must be true
User.query.filter(
    and_(User.age > 18, User.is_active == True)
).all()

# OR: Either can be true
User.query.filter(
    or_(User.role == 'admin', User.role == 'mod')
).all()

# NOT: Opposite of condition
User.query.filter(
    not_(User.is_banned)
).all()

The IN Operator

# Find users with specific IDs
User.query.filter(
    User.id.in_([1, 2, 3, 4, 5])
).all()

# Find users NOT in list
User.query.filter(
    ~User.role.in_(['banned', 'suspended'])
).all()

📄 Pagination: Serving Data in Portions

Imagine you have 10,000 recipes. You can’t show them all at once! Pagination breaks data into pages.

graph LR
    A["All 100 Users"] --> B["Page 1: Users 1-10"]
    A --> C["Page 2: Users 11-20"]
    A --> D["Page 3: Users 21-30"]
    A --> E["... more pages"]

Basic Pagination

# Get page 1, with 10 items per page
page = User.query.paginate(
    page=1,
    per_page=10
)

# Access the items
users = page.items

# Navigation info
page.has_next    # Is there a next page?
page.has_prev    # Is there a previous page?
page.next_num    # Next page number
page.prev_num    # Previous page number
page.pages       # Total number of pages
page.total       # Total number of items

In Your Flask Route

@app.route('/users')
def list_users():
    page_num = request.args.get(
        'page', 1, type=int
    )

    users = User.query.paginate(
        page=page_num,
        per_page=20,
        error_out=False
    )

    return render_template(
        'users.html',
        pagination=users
    )

🎭 Database Session Management: The Behind-the-Scenes Crew

The session is like a notepad where you write all your changes before making them permanent.

graph TD
    A["Your Changes"] --> B["Session Notepad"]
    B -->|commit| C["Database - Permanent!"]
    B -->|rollback| D["Thrown Away"]

Key Session Operations

# Add new item to notepad
db.session.add(new_user)

# Add multiple items
db.session.add_all([user1, user2, user3])

# Make changes permanent
db.session.commit()

# Undo all changes in notepad
db.session.rollback()

# Refresh item from database
db.session.refresh(user)

# Remove item from session
db.session.expunge(user)

Why Use Sessions?

Without sessions: Every change goes directly to database = SLOW and RISKY

With sessions:

• Batch multiple changes together
• Review before saving
• Undo mistakes easily

🔒 Database Transactions: All or Nothing

A transaction groups operations together. Either ALL succeed, or NONE of them happen.

The Bank Transfer Example

Imagine transferring $100 from Alice to Bob:

try:
    # Start transaction (automatic)

    # Step 1: Take from Alice
    alice.balance -= 100

    # Step 2: Give to Bob
    bob.balance += 100

    # All good? Save everything!
    db.session.commit()

except Exception as e:
    # Something went wrong!
    # Undo EVERYTHING
    db.session.rollback()
    raise e

Why transactions matter:

• If Bob’s account update fails, Alice gets her money back
• No money disappears into thin air
• Data stays consistent

Using Context Manager

from flask_sqlalchemy import SQLAlchemy

# Clean way to handle transactions
def transfer_money(from_id, to_id, amount):
    try:
        sender = User.query.get(from_id)
        receiver = User.query.get(to_id)

        sender.balance -= amount
        receiver.balance += amount

        db.session.commit()
        return True

    except:
        db.session.rollback()
        return False

Nested Transactions with Savepoints

# Create a savepoint
db.session.begin_nested()

try:
    # Risky operation
    user.credits -= 50
    db.session.commit()  # Commits savepoint

except:
    db.session.rollback()  # Only rolls back to savepoint

🎯 Quick Reference: Your Cheat Codes

CRUD in 4 Lines Each

# CREATE
db.session.add(User(name="X"))
db.session.commit()

# READ
user = User.query.get(1)

# UPDATE
user.name = "New Name"
db.session.commit()

# DELETE
db.session.delete(user)
db.session.commit()

Filter Shortcuts

🌟 You Did It!

You now understand:

✅ CRUD - Create, Read, Update, Delete ✅ Query Methods - Different ways to fetch data ✅ Query Chaining - Building complex searches ✅ Advanced Filters - Precision searching ✅ Pagination - Handling large datasets ✅ Sessions - Managing changes ✅ Transactions - Keeping data safe

Remember: Your database is your recipe box. Flask-SQLAlchemy is your kitchen assistant. Together, you can cook up amazing applications! 🍳✨