SQLAlchemy ORM: CRUD Operations

1. The Session: Your Workspace for Database Operations

Before we can perform any operations, we need to understand the most important concept in the SQLAlchemy ORM: the Session.

Think of the Session as a temporary workspace or a staging area for your database objects. When you want to interact with the database, you open a session. You can then load objects from the database into the session, create new objects and add them, or mark existing ones for deletion.

None of these changes are permanent until you commit the session. This "unit of work" pattern allows you to group multiple changes into a single, atomic transaction. If something goes wrong, you can rollback the entire transaction, leaving the database in its original state.

To work with a Session, we first need to create it. A common pattern is to create a sessionmaker, which is a factory that produces new Session objects configured to use our engine.

Let's modify the setup from our previous lesson. Create a new file, main.py, to contain our application logic.

# main.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker




# Import the User model and Base from our setup file
from database_setup import Base, User 




# We use the same database URL and engine
DATABASE_URL = "sqlite:///app.db"
engine = create_engine(DATABASE_URL, echo=True)




# Create the table if it doesn't exist (useful for the first run)
Base.metadata.create_all(engine)




# Create a configured "Session" class
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)




# Now, we can create an individual session instance anytime we need to talk to the DB
db_session = SessionLocal()




# ... we will perform our operations here ...




# It's crucial to close the session when you're done
db_session.close()

A more robust and common way to handle sessions is with a with statement, which ensures the session is always closed properly, even if errors occur.

with SessionLocal() as session:



    # do work with the session
    # it will be automatically closed at the end of this block
    pass

We will use this pattern in the examples that follow.

2. CRUD Operations with SQLAlchemy

Now, let's dive into the four core operations. We'll be using a fantastic video tutorial as our guide. It walks through creating, reading, updating, and deleting data for a User and Comment model. The principles are exactly the same for our single User model.

Create: Adding New Records

To add a new record to the users table, you simply:

1. Create an instance of your User model in Python.
2. Add it to the session using session.add().
3. Commit the session to save it to the database.

Here's how we can apply this to our main.py file:

# In main.py, inside a `with` block

with SessionLocal() as session:
    print("--- Creating users ---")
    user1 = User(name="Alice", email="alice@example.com")
    user2 = User(name="Bob", email="bob@example.com")
    



    # Add the new objects to the session
    session.add_all([user1, user2])
    



    # Commit the transaction to the database
    session.commit()
    print("Users created.")

Run this script. The echo=True in our engine will show you the INSERT statements SQLAlchemy generated. You've just created new rows in your database using pure Python!

Read: Querying for Records

Reading data is where SQLAlchemy's power truly shines. The modern, SQLAlchemy 2.0 approach uses the select() function to construct queries in a Pythonic way.

The basic steps are:

1. Construct a select() statement, specifying the model you want to query.
2. Optionally add a .where() clause to filter the results.
3. Execute the statement using session.execute().
4. Process the results. A Result object is returned, and you typically use .scalars() to get your model instances.

Let's write some queries using the modern select() style.

# In main.py, inside a `with` block
from sqlalchemy import select

with SessionLocal() as session:
    print("\n--- Reading users ---")
    



    # 1. Select all users
    all_users_stmt = select(User)
    all_users_result = session.execute(all_users_stmt)
    users = all_users_result.scalars().all() # .scalars() gets the User objects from the result rows
    print(f"All users: {users}")




    # 2. Select a user by name (filtering)
    alice_stmt = select(User).where(User.name == "Alice")
    alice_result = session.execute(alice_stmt)
    alice = alice_result.scalars().first() # .first() gets the first result or None
    print(f"Found Alice: {alice}")




    # 3. Select a user by their primary key (ID)
    # A more direct way is using session.get()
    user_with_id_1 = session.get(User, 1) # Assumes a user with id=1 exists
    print(f"User with ID 1: {user_with_id_1}")

Update: Modifying Existing Records

Updating a record is straightforward and feels very natural in Python.

1. First, read the object you want to modify from the database and load it into your session.
2. Change the attributes of the Python object directly.
3. Commit the session. SQLAlchemy automatically detects the change and issues an UPDATE statement.

Here's the code to update Bob's email address:

# In main.py, inside a `with` block

with SessionLocal() as session:
    print("\n--- Updating a user ---")
    



    # Find the user we want to update
    bob_stmt = select(User).where(User.name == "Bob")
    bob = session.execute(bob_stmt).scalars().first()

    if bob:
        print(f"Bob's old email: {bob.email}")
        bob.email = "robert@example.com" # Just change the attribute
        session.commit() # The session knows the object was modified
        print(f"Bob's new email: {bob.email}")
    else:
        print("Bob not found.")

Delete: Removing Records

Finally, to delete a record:

1. Read the object you want to delete from the database.
2. Use session.delete() to mark it for deletion.
3. Commit the session to finalize the removal.

Let's delete Alice from our database.

# In main.py, inside a `with` block

with SessionLocal() as session:
    print("\n--- Deleting a user ---")
    



    # Find the user to delete
    alice_stmt = select(User).where(User.name == "Alice")
    alice = session.execute(alice_stmt).scalars().first()

    if alice:
        print(f"Deleting user: {alice}")
        session.delete(alice)
        session.commit()
        print("Alice deleted.")
    else:
        print("Alice not found.")

3. Reviewing CRUD Operations

For a text-based summary of the CRUD operations we just covered, you can refer to the following resource. It provides concise code snippets for each operation that reinforce what you've seen in the video.

Conclusion

Congratulations! You have now mastered the fundamental database operations using SQLAlchemy's ORM. You've seen how a potentially complex series of SQL commands can be handled through intuitive and maintainable Python code.

Key Takeaways:

• The SQLAlchemy Session acts as a "unit of work" or staging area for all database operations.
• Create: Instantiate a model object, session.add() it, and session.commit().
• Read: Use select() to build a query, session.execute() to run it, and .scalars() to retrieve your model objects.
• Update: Fetch an object, modify its Python attributes directly, and session.commit().
• Delete: Fetch an object, pass it to session.delete(), and session.commit().

In the last lesson, you created your database schema with Base.metadata.create_all(). This is great for initial setup, but what happens when you need to change your schema in a live application, like adding a hashed_password column to your User model? Simply changing the Python class won't update the database. Doing it manually is risky and error-prone.

In our next lesson, we will introduce Alembic, the official database migration tool for SQLAlchemy, which allows you to manage and apply changes to your database schema in a safe, version-controlled way.