CRUD Operations

All database operations are performed using a Session.

Create a Session

File: `database.py`

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(bind=engine)

File: `main.py`

from database import SessionLocal

session = SessionLocal()

Create (Insert)

File: `main.py`

student = Student(name="John", age=20)

session.add(student)

session.commit()

Explanation

student = Student(name="John", age=20)

Creates a new Student object.

session.add(student)

Adds the object to the Session.

session.commit()

Saves the object to the database.

Read (Select)

File: `main.py`

from sqlalchemy import select

stmt = select(Student)

result = session.execute(stmt)

students = result.scalars().all()

Explanation

stmt = select(Student)

Creates a query to retrieve all students.

result = session.execute(stmt)

Executes the query and returns a Result object.

students = result.scalars().all()

Extracts the Student objects and returns them as a Python list.

Update

File: `main.py`

student = session.get(Student, 1)

student.name = "John Doe"
student.age = 21

session.commit()

Explanation

student = session.get(Student, 1)

Retrieves the student whose primary key is 1.

student.name = "John Doe"
student.age = 21

Updates the object’s attributes.

session.commit()

Saves the changes to the database.

Alternative (Direct Query)

from sqlalchemy import update

stmt = (
    update(Student)
    .where(Student.id == 1)
    .values(name="John Doe", age=21)
)

session.execute(stmt)

session.commit()

Delete

File: `main.py`

student = session.get(Student, 1)

session.delete(student)

session.commit()

Explanation

student = session.get(Student, 1)

Retrieves the student to be deleted.

session.delete(student)

Marks the object for deletion.

session.commit()

Removes the record from the database.

Alternative (Direct Query)

from sqlalchemy import delete

stmt = delete(Student).where(Student.id == 1)

session.execute(stmt)

session.commit()

Rollback Changes

File: `main.py`

try:
    session.commit()
except Exception:
    session.rollback()

Explanation

session.rollback()

Discards all uncommitted changes if an error occurs.

Close the Session

File: `main.py`

session.close()

Explanation

session.close()

Closes the Session and releases the database connection.

Quick Revision

Operation	ORM Approach	Direct Query
Create	`add()`	—
Read	`select()` → `execute()` → `scalars().all()`	—
Update	`get()` → Modify → `commit()`	`update()` → `execute()` → `commit()`
Delete	`get()` → `delete()` → `commit()`	`delete()` → `execute()` → `commit()`
Rollback	`rollback()`	—
Close	`close()`	—

Note: The object-based approach is the recommended way to work with SQLAlchemy ORM. Direct update() and delete() statements are useful for bulk operations and will be covered in detail in the Advanced Queries chapter.

​CRUD Operations

​Create a Session

​File: database.py

​File: main.py

​Create (Insert)

​File: main.py

​Explanation

​Read (Select)

​File: main.py

​Explanation

​Update

​File: main.py

​Explanation

​Alternative (Direct Query)

​Delete

​File: main.py

​Explanation

​Alternative (Direct Query)

​Rollback Changes

​File: main.py

​Explanation

​Close the Session

​File: main.py

​Explanation

​Quick Revision

CRUD Operations

Create a Session

File: `database.py`

File: `main.py`

Create (Insert)

File: `main.py`

Explanation

Read (Select)

File: `main.py`

Explanation

Update

File: `main.py`

Explanation

Alternative (Direct Query)

Delete

File: `main.py`

Explanation

Alternative (Direct Query)

Rollback Changes

File: `main.py`

Explanation

Close the Session

File: `main.py`

Explanation

Quick Revision