> ## Documentation Index
> Fetch the complete documentation index at: https://genai.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# 10-Introduction to SQLAlchemy

> Understand why ORMs exist, how SQLAlchemy works, and the overall workflow before building your first ORM application.

# Learning Objectives

By the end of this chapter, you will be able to:

* Understand why ORMs are used.
* Explain what Object Relational Mapping (ORM) is.
* Understand the overall SQLAlchemy workflow.
* Identify the major SQLAlchemy components.
* Explain how Python objects are stored and retrieved from a database.

## Topics Covered

In this module, you'll learn:

1. [Introduction to ORM](#working-with-an-orm)
2. [Building Your First SQLAlchemy Application](#building-your-first-sqlalchemy-orm-application)
3. [CRUD Operations](#crud-operations-with-sqlalchemy-orm)
4. [Retrieving Results](#retrieving-results-in-sqlalchemy)
5. [ORM Relationships](#orm-relationships)

> **Try Yourself:** [💻 VS Code](vscode://file/Users/sivaprasad/Downloads/DSA%20With%20Python/public/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm-exercises.ipynb) | [🚀 Colab](https://colab.research.google.com/github/prasad230776/genai-course/blob/main/public/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm-exercises-colab.ipynb) | <a href="/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm-exercises.ipynb" download>📥 Download</a><br />
> **Verify Solutions:** [💻 VS Code](vscode://file/Users/sivaprasad/Downloads/DSA%20With%20Python/public/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm.ipynb) | [🚀 Colab](https://colab.research.google.com/github/prasad230776/genai-course/blob/main/public/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm-colab.ipynb) | <a href="/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm.ipynb" download>📥 Download</a>

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Working Without an ORM

A relational database understands only **SQL**.

Whenever an application needs to store, retrieve, update, or delete data, the application must send SQL statements to the database.

For example, to retrieve all students:

```sql theme={null}
SELECT * FROM students;
```

To insert a new student:

```sql theme={null}
INSERT INTO students (name, age)
VALUES ('Rahul', 22);
```

As applications grow, writing and maintaining SQL for every database operation becomes repetitive and difficult.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Working With an ORM

With an ORM, we work with Python classes and objects instead of writing SQL for most database operations.

To insert a new student:

```python theme={null}
student = Student(name="Rahul", age=22)

session.add(student)
session.commit()
```

To retrieve all students:

```python theme={null}
students = session.scalars(select(Student)).all()
```

Notice that we never wrote an SQL query.

SQLAlchemy automatically generates the required SQL, sends it to the database, retrieves the results, and converts them back into Python objects.

This allows us to focus on writing Python instead of manually writing SQL.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## What is an ORM?

**ORM (Object Relational Mapping)** is a technique that maps Python objects to database tables.

It allows us to work with:

| Python    | Database |
| --------- | -------- |
| Class     | Table    |
| Object    | Row      |
| Attribute | Column   |

Think of an ORM as a translator between Python and a relational database.

```
Python Objects
      ↓
     ORM
      ↓
SQL Queries
      ↓
Database
```

Throughout this workshop, we'll use **SQLAlchemy ORM**, one of the most popular ORM libraries in Python.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## The Big Picture

Before learning the individual components, let's understand the complete workflow.

### Writing Data (INSERT / UPDATE / DELETE)

```
Create Engine
      ↓
Create Session
      ↓
Create Python Object
      ↓
Session Tracks Changes
      ↓
SQLAlchemy Generates SQL
      ↓
Database Executes SQL
      ↓
Data Stored
```

### Reading Data (SELECT)

```
Create Engine
      ↓
Create Session
      ↓
Build Python Query
      ↓
SQLAlchemy Generates SQL
      ↓
Database Executes SQL
      ↓
Database Returns Rows
      ↓
SQLAlchemy Maps Rows to Python Objects
      ↓
Python Receives Objects
```

Notice the important role of SQLAlchemy.

* We write Python code.
* SQLAlchemy converts it into SQL.
* The database executes the SQL.
* SQLAlchemy converts the returned rows back into Python objects.

We'll learn each step of this workflow throughout this module.

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Understanding the Components

Now let's understand the responsibility of each component.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Engine

The **Engine** is the starting point of every SQLAlchemy application.

It knows:

* Which database to connect to.
* How to establish the connection.
* How to send SQL statements.

```
Application
      ↓
Engine
      ↓
Database
```

Think of the Engine as the **gateway** to the database.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Session

The **Session** is used to interact with the database.

It allows us to:

* Add new objects
* Retrieve objects
* Update objects
* Delete objects
* Commit or rollback transactions

```
Application
      ↓
Session
      ↓
Engine
      ↓
Database
```

Think of the Session as your **conversation with the database**.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## ORM Model

An ORM model is a Python class that represents a database table.

```python theme={null}
class Student(Base):
    ...
```

Each object created from this class represents one row in the table.

```
Python Class
      ↓
Python Object
      ↓
Database Row
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## SQLAlchemy

SQLAlchemy acts as the translator.

When we write Python code,

```python theme={null}
select(Student)
```

SQLAlchemy generates SQL.

```sql theme={null}
SELECT * FROM students;
```

When the database returns rows,

```
id | name | age
```

SQLAlchemy converts them into Python objects.

```
Student(id=1, name="Rahul", age=22)
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Database

The database stores the data and executes SQL statements.

It understands SQL, not Python objects.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Putting Everything Together

```
                Python Application
                       │
                       ▼
                 Create Engine
                       │
                       ▼
                 Create Session
                       │
            ┌──────────┴──────────┐
            │                     │
            ▼                     ▼
     Create Python Object    Build Python Query
            │                     │
            └──────────┬──────────┘
                       ▼
                 SQLAlchemy ORM
                       │
             Generates SQL
                       │
                       ▼
                   Database
                       │
          Executes SQL & Returns Rows
                       │
                       ▼
                 SQLAlchemy ORM
                       │
        Maps Rows to Python Objects
                       │
                       ▼
                Python Application
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Summary

In this chapter, you learned:

* Why ORMs are needed.
* What Object Relational Mapping (ORM) is.
* How SQLAlchemy translates Python code into SQL.
* The complete workflow for reading and writing data.
* The role of the Engine, Session, ORM Model, SQLAlchemy, and the Database.

In the next chapter, we'll start building this workflow by creating our first **Engine**, connecting to a database, defining our first ORM model, and creating our first table.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Building Your First SQLAlchemy ORM Application

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Learning Objectives

By the end of this chapter, you will be able to:

* Create a SQLAlchemy project.
* Connect to a SQLite database.
* Define an ORM model.
* Create database tables.
* Create a Session.
* Insert data into the database.

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Project Setup

Create a new project.

```bash theme={null}
mkdir sqlalchemy-demo
cd sqlalchemy-demo
```

Initialize the project.

```bash theme={null}
uv init
```

Install SQLAlchemy.

```bash theme={null}
uv add sqlalchemy
```

Project structure

```text theme={null}
sqlalchemy-demo/
│
├── .venv/
├── pyproject.toml
├── uv.lock
└── main.py
```

Throughout this chapter, we'll gradually build the same `main.py`.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Step 1 — Create the Engine

Every SQLAlchemy application starts by creating an **Engine**.

The Engine knows **which database** to connect to.

Import `create_engine`.

```python theme={null}
from sqlalchemy import create_engine
```

Create a Database URL.

```python theme={null}
DATABASE_URL = "sqlite:///students.db"
```

Create the Engine.

```python theme={null}
engine = create_engine(DATABASE_URL)
```

### Workflow

```text theme={null}
Application
      │
      ▼
Create Engine
      │
      ▼
Database
```

At this stage,

* Engine is created.
* No database operations have happened yet.

Current application

```python theme={null}
from sqlalchemy import create_engine

DATABASE_URL = "sqlite:///students.db"

engine = create_engine(DATABASE_URL)
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Step 2 — Create the Declarative Base

Every ORM model inherits from a common Base class.

Import `DeclarativeBase`.

```python theme={null}
from sqlalchemy.orm import DeclarativeBase
```

Create the Base class.

```python theme={null}
class Base(DeclarativeBase):
    pass
```

### Workflow

```text theme={null}
Base
 │
 ├── Student
 ├── Employee
 └── Department
```

SQLAlchemy uses the Base class to keep track of all ORM models.

Current application

```python theme={null}
from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase

DATABASE_URL = "sqlite:///students.db"

engine = create_engine(DATABASE_URL)


class Base(DeclarativeBase):
    pass
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Step 3 — Create an ORM Model

An ORM model represents a database table.

Import the required modules.

```python theme={null}
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column
```

Create the model.

```python theme={null}
class Student(Base):
    __tablename__ = "students"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    age: Mapped[int]
```

### Mapping

| Python    | Database |
| --------- | -------- |
| Class     | Table    |
| Object    | Row      |
| Attribute | Column   |

SQL Equivalent

```sql theme={null}
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);
```

Current application

```python theme={null}
from sqlalchemy import String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


DATABASE_URL = "sqlite:///students.db"

engine = create_engine(DATABASE_URL)


class Base(DeclarativeBase):
    pass


class Student(Base):
    __tablename__ = "students"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    age: Mapped[int]
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Step 4 — Create Database Tables

Create all tables.

```python theme={null}
Base.metadata.create_all(engine)
```

### What happens?

```text theme={null}
Engine
      │
      ▼
Connect to SQLite
      │
      ▼
students.db exists?
      │
 ┌────┴────┐
 │         │
No        Yes
 │         │
 ▼         ▼
Create     Open
Database   Database
      │
      ▼
Read ORM Models
      │
      ▼
Generate CREATE TABLE SQL
      │
      ▼
Create Tables
```

> **Note:** For SQLite, the database file is created automatically if it doesn't already exist. SQLAlchemy then creates the tables. For databases like PostgreSQL or MySQL, the database must already exist before connecting.

Current application

```python theme={null}
Base.metadata.create_all(engine)
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Step 5 — Create a Session

A Session is used to interact with the database.

Import `Session`.

```python theme={null}
from sqlalchemy.orm import Session
```

Create the Session.

```python theme={null}
with Session(engine) as session:
    pass
```

### Workflow

```text theme={null}
Application
      │
      ▼
Session
      │
      ▼
Engine
      │
      ▼
Database
```

Think of the Session as your **conversation with the database**.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Step 6 — Insert a Record

Create a Python object.

```python theme={null}
student = Student(
    name="Rahul",
    age=22,
)
```

Add the object to the Session.

```python theme={null}
session.add(student)
```

Save the changes.

```python theme={null}
session.commit()
```

Refresh the object.

```python theme={null}
session.refresh(student)
```

Display the generated ID.

```python theme={null}
print(student.id)
```

### Workflow

```text theme={null}
Create Python Object
      │
      ▼
Session.add()
      │
      ▼
Session.commit()
      │
      ▼
SQLAlchemy Generates INSERT
      │
      ▼
Database Stores Row
      │
      ▼
Session.refresh()
      │
      ▼
Updated Python Object
```

Generated SQL

```sql theme={null}
INSERT INTO students (name, age)
VALUES ('Rahul', 22);
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Complete Application

```python theme={null}
from sqlalchemy import String, create_engine
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    Session,
    mapped_column,
)


DATABASE_URL = "sqlite:///students.db"

engine = create_engine(DATABASE_URL)


class Base(DeclarativeBase):
    pass


class Student(Base):
    __tablename__ = "students"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    age: Mapped[int]


Base.metadata.create_all(engine)


with Session(engine) as session:

    student = Student(
        name="Rahul",
        age=22,
    )

    session.add(student)
    session.commit()
    session.refresh(student)

    print(student.id)
```

Run the application.

```bash theme={null}
uv run main.py
```

Output ?

<Accordion title="Show Output">
  ```text theme={null}
  1
  ```
</Accordion>

Open `students.db` using the SQLite extension.

You should see:

| id | name  | age |
| -- | ----- | --- |
| 1  | Rahul | 22  |

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Summary

Congratulations! 🎉

You have built your first SQLAlchemy ORM application.

You learned how to:

* Create an Engine.
* Create a Declarative Base.
* Define an ORM Model.
* Create database tables.
* Create a Session.
* Insert data into the database.

<div align="right">[Back to Top ↑](#topics-covered)</div>

# CRUD Operations with SQLAlchemy ORM

CRUD stands for:

* **C** – Create (INSERT)
* **R** – Read (SELECT)
* **U** – Update (UPDATE)
* **D** – Delete (DELETE)

These are the four fundamental database operations performed using SQLAlchemy ORM.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Create (INSERT)

### SQL

```sql theme={null}
INSERT INTO students (name, age)
VALUES ('Rahul', 22);
```

### SQLAlchemy ORM

```python theme={null}
session.add(Student(name="Rahul", age=22))
session.commit()
```

If you need the auto-generated values (such as the primary key), refresh the object.

```python theme={null}
student = Student(name="Rahul", age=22)

session.add(student)
session.commit()
session.refresh(student)

print(student.id)
```

### Workflow

```text theme={null}
Create Object
      ↓
session.add()
      ↓
session.commit()
      ↓
Database Row Created
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Read (SELECT)

### Retrieve All Records

#### SQL

```sql theme={null}
SELECT * FROM students;
```

#### SQLAlchemy ORM

```python theme={null}
students = session.scalars(select(Student)).all()
```

### Retrieve by Primary Key

#### SQL

```sql theme={null}
SELECT *
FROM students
WHERE id = 1;
```

#### SQLAlchemy ORM

```python theme={null}
student = session.get(Student, 1)
```

### Retrieve the First Record

#### SQL

```sql theme={null}
SELECT *
FROM students
LIMIT 1;
```

#### SQLAlchemy ORM

```python theme={null}
student = session.scalars(select(Student)).first()
```

### Workflow

```text theme={null}
Build Query
      ↓
Execute Query
      ↓
Retrieve Objects
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Update (UPDATE)

### Step 1 – Retrieve the Object

```python theme={null}
student = session.get(Student, 1)
```

### Step 2 – Modify the Object

```python theme={null}
student.age = 25
```

### Step 3 – Save the Changes

```python theme={null}
session.commit()
```

### Complete Example

#### SQL

```sql theme={null}
UPDATE students
SET age = 25
WHERE id = 1;
```

#### SQLAlchemy ORM

```python theme={null}
student = session.get(Student, 1)
student.age = 25
session.commit()
```

### Workflow

```text theme={null}
Retrieve Object
      ↓
Modify Object
      ↓
session.commit()
      ↓
Database Updated
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Delete (DELETE)

### Step 1 – Retrieve the Object

```python theme={null}
student = session.get(Student, 1)
```

### Step 2 – Delete the Object

```python theme={null}
session.delete(student)
```

### Step 3 – Save the Changes

```python theme={null}
session.commit()
```

### Complete Example

#### SQL

```sql theme={null}
DELETE FROM students
WHERE id = 1;
```

#### SQLAlchemy ORM

```python theme={null}
student = session.get(Student, 1)
session.delete(student)
session.commit()
```

### Workflow

```text theme={null}
Retrieve Object
      ↓
session.delete()
      ↓
session.commit()
      ↓
Row Deleted
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## CRUD Summary

| Operation  | SQL                       | SQLAlchemy ORM                           |
| ---------- | ------------------------- | ---------------------------------------- |
| Create     | `INSERT`                  | `session.add()` + `session.commit()`     |
| Read       | `SELECT`                  | `session.scalars(select(Student)).all()` |
| Read by ID | `SELECT ... WHERE id = ?` | `session.get(Student, id)`               |
| Update     | `UPDATE`                  | Modify object + `session.commit()`       |
| Delete     | `DELETE`                  | `session.delete()` + `session.commit()`  |

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Complete CRUD Example

```python theme={null}
### Create
session.add(Student(name="Rahul", age=22))
session.commit()

###Read
students = session.scalars(select(Student)).all()

### Update
student = session.get(Student, 1)
student.age = 25
session.commit()

### Delete
student = session.get(Student, 1)
session.delete(student)
session.commit()
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

## SQL SELECT vs SQLAlchemy ORM

| SQL                                                                                        | SQLAlchemy ORM                                                                       |
| ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------ |
| `SELECT * FROM students;`                                                                  | `select(Student)`                                                                    |
| `SELECT * FROM students;` *(Execute & Fetch All)*                                          | `session.scalars(select(Student)).all()`                                             |
| `SELECT * FROM students LIMIT 1;`                                                          | `session.scalars(select(Student)).first()`                                           |
| `SELECT * FROM students WHERE id = 1;`                                                     | `session.get(Student, 1)`                                                            |
| `SELECT * FROM students WHERE age > 20;`                                                   | `select(Student).where(Student.age > 20)`                                            |
| `SELECT * FROM students WHERE age > 20;` *(Execute & Fetch All)*                           | `session.scalars(select(Student).where(Student.age > 20)).all()`                     |
| `SELECT * FROM students WHERE age > 20 AND city = 'Hyderabad';`                            | `select(Student).where(Student.age > 20, Student.city == "Hyderabad")`               |
| `SELECT * FROM students WHERE age > 20 OR city = 'Hyderabad';`                             | `select(Student).where(or_(Student.age > 20, Student.city == "Hyderabad"))`          |
| `SELECT * FROM students ORDER BY name;`                                                    | `select(Student).order_by(Student.name)`                                             |
| `SELECT * FROM students ORDER BY age DESC;`                                                | `select(Student).order_by(Student.age.desc())`                                       |
| `SELECT * FROM students LIMIT 5;`                                                          | `select(Student).limit(5)`                                                           |
| `SELECT * FROM students LIMIT 5 OFFSET 10;`                                                | `select(Student).limit(5).offset(10)`                                                |
| `SELECT DISTINCT city FROM students;`                                                      | `select(Student.city).distinct()`                                                    |
| `SELECT COUNT(*) FROM students;`                                                           | `select(func.count(Student.id))`                                                     |
| `SELECT SUM(marks) FROM students;`                                                         | `select(func.sum(Student.marks))`                                                    |
| `SELECT AVG(marks) FROM students;`                                                         | `select(func.avg(Student.marks))`                                                    |
| `SELECT MIN(age) FROM students;`                                                           | `select(func.min(Student.age))`                                                      |
| `SELECT MAX(age) FROM students;`                                                           | `select(func.max(Student.age))`                                                      |
| `SELECT city, COUNT(*) FROM students GROUP BY city;`                                       | `select(Student.city, func.count()).group_by(Student.city)`                          |
| `SELECT city, COUNT(*) FROM students GROUP BY city HAVING COUNT(*) > 5;`                   | `select(Student.city, func.count()).group_by(Student.city).having(func.count() > 5)` |
| `SELECT * FROM students WHERE name LIKE 'R%';`                                             | `select(Student).where(Student.name.like("R%"))`                                     |
| `SELECT * FROM students WHERE age BETWEEN 20 AND 25;`                                      | `select(Student).where(Student.age.between(20, 25))`                                 |
| `SELECT * FROM students WHERE city IN ('Hyderabad', 'Bangalore');`                         | `select(Student).where(Student.city.in_(["Hyderabad", "Bangalore"]))`                |
| `SELECT * FROM students WHERE phone IS NULL;`                                              | `select(Student).where(Student.phone.is_(None))`                                     |
| `SELECT * FROM students WHERE phone IS NOT NULL;`                                          | `select(Student).where(Student.phone.is_not(None))`                                  |
| `SELECT * FROM students JOIN departments ON students.department_id = departments.id;`      | `select(Student).join(Department)`                                                   |
| `SELECT * FROM students LEFT JOIN departments ON students.department_id = departments.id;` | `select(Student).outerjoin(Department)`                                              |
| `SELECT * FROM students ORDER BY age DESC LIMIT 5;`                                        | `select(Student).order_by(Student.age.desc()).limit(5)`                              |

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Retrieving Results in SQLAlchemy

After building a query using `select()`, you need to decide **how you want to retrieve the results**.

SQLAlchemy provides different methods depending on whether you expect **one object**, **multiple objects**, or **a single value**.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Result Retrieval Methods

| Method          | Returns                | Use When                                                                                                              |
| --------------- | ---------------------- | --------------------------------------------------------------------------------------------------------------------- |
| `all()`         | List of objects        | Retrieve all matching records.                                                                                        |
| `first()`       | First object or `None` | Retrieve only the first matching record.                                                                              |
| `one()`         | Exactly one object     | Expect exactly one record. Raises an exception if none or multiple records are found.                                 |
| `one_or_none()` | One object or `None`   | Expect at most one record. Raises an exception if multiple records are found.                                         |
| `get()`         | Object or `None`       | Retrieve a record by its primary key.                                                                                 |
| `scalar()`      | Single scalar value    | Retrieve a single value such as `COUNT`, `SUM`, `AVG`, etc.                                                           |
| `scalars()`     | ScalarResult           | Retrieve ORM objects (or the first selected column) from a query. Usually followed by `all()`, `first()`, or `one()`. |

<div align="right">[Back to Top ↑](#topics-covered)</div>

## `all()`

Returns all matching records.

```python theme={null}
students = session.scalars(select(Student)).all()
```

Result

```python theme={null}
[
    Student(...),
    Student(...),
    Student(...)
]
```

Use when you want **every matching record**.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## `first()`

Returns only the first matching record.

```python theme={null}
student = session.scalars(select(Student)).first()
```

Result

```python theme={null}
Student(...)
```

or

```python theme={null}
None
```

Use when only the **first matching record** is required.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## `one()`

Returns exactly one record.

```python theme={null}
student = session.scalars(
    select(Student).where(Student.id == 1)
).one()
```

Raises an exception if:

* No record is found.
* More than one record is found.

Use when you are **certain** exactly one record exists.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## `one_or_none()`

Returns one record or `None`.

```python theme={null}
student = session.scalars(
    select(Student).where(Student.id == 1)
).one_or_none()
```

Returns

* One object
* `None`

Raises an exception if multiple records are found.

Use when the record may or may not exist.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## `get()`

Retrieves a record using its **primary key**.

```python theme={null}
student = session.get(Student, 1)
```

Returns

```python theme={null}
Student(...)
```

or

```python theme={null}
None
```

Use `get()` **only** when searching by the primary key.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## `scalar()`

Returns a single value.

Example:

```python theme={null}
count = session.scalar(
    select(func.count(Student.id))
)
```

Result

```python theme={null}
25
```

Commonly used with:

* `COUNT()`
* `SUM()`
* `AVG()`
* `MIN()`
* `MAX()`

<div align="right">[Back to Top ↑](#topics-covered)</div>

## `scalars()`

Returns ORM objects (or the first selected column) from a query.

Example

```python theme={null}
students = session.scalars(select(Student)).all()
```

Equivalent to

```python theme={null}
result = session.execute(select(Student))
students = result.scalars().all()
```

Use `scalars()` when retrieving ORM objects.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Quick Reference

| Requirement                       | Method              |
| --------------------------------- | ------------------- |
| All matching records              | `.all()`            |
| First matching record             | `.first()`          |
| Exactly one record                | `.one()`            |
| Zero or one record                | `.one_or_none()`    |
| Find by primary key               | `session.get()`     |
| Single value (COUNT, SUM, AVG...) | `session.scalar()`  |
| ORM objects                       | `session.scalars()` |

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Choosing the Right Method

```
Need all matching records?
        │
       Yes
        │
        ▼
      all()

Need only the first record?
        │
       Yes
        │
        ▼
     first()

Searching by primary key?
        │
       Yes
        │
        ▼
   session.get()

Expect exactly one record?
        │
       Yes
        │
        ▼
      one()

Expect zero or one record?
        │
       Yes
        │
        ▼
   one_or_none()

Need a single value (COUNT, SUM...)?
        │
       Yes
        │
        ▼
     scalar()

Need ORM objects from a SELECT query?
        │
       Yes
        │
        ▼
    scalars()
```

<div align="right">[Back to Top ↑](#topics-covered)</div>

# ORM Relationships

In real-world applications, a single table is rarely enough to represent all the required data. Instead, multiple tables work together by establishing **relationships** between them.

For example, in a Blog application:

* A user can write multiple blog posts.
* Every blog post belongs to one author.
* A blog post can have multiple tags.
* Every comment belongs to a blog post.

Instead of duplicating data across tables, relational databases connect tables using **Primary Keys** and **Foreign Keys**.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Why Relationships?

Suppose we store author information inside every blog post.

```text theme={null}
BlogPosts

+----+------------------+-----------+----------------+
| id | title            | author    | email          |
+----+------------------+-----------+----------------+
| 1  | FastAPI Basics   | John      | john@mail.com  |
| 2  | SQLAlchemy ORM   | John      | john@mail.com  |
+----+------------------+-----------+----------------+
```

This causes several problems.

* Duplicate data
* Wasted storage
* Difficult updates
* Risk of inconsistent data

Instead, store author information only once.

```text theme={null}
Users

+----+---------+----------------+--------+
| id | username| email          | role   |
+----+---------+----------------+--------+
| 1  | john    | john@mail.com  | AUTHOR |
+----+---------+----------------+--------+

BlogPosts

+----+------------------+-----------+
| id | title            | author_id |
+----+------------------+-----------+
| 1  | FastAPI Basics   |     1     |
| 2  | SQLAlchemy ORM   |     1     |
+----+------------------+-----------+
```

Now the author details exist only once, making the database normalized and easier to maintain.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Primary Key vs Foreign Key

Every table has a **Primary Key**, which uniquely identifies each row.

A **Foreign Key** stores the primary key value of another table.

```text theme={null}
Users
+----+----------+
| id | username |
+----+----------+
| 1  | john     |
| 2  | alice    |
+----+----------+

        ▲
        │
        │ Foreign Key
        │

BlogPosts
+----+----------------+-----------+
| id | title          | author_id |
+----+----------------+-----------+
| 1  | FastAPI Intro  |     1     |
| 2  | SQLAlchemy ORM |     1     |
| 3  | JWT Auth       |     2     |
+----+----------------+-----------+
```

Here,

* `Users.id` is the Primary Key.
* `BlogPosts.author_id` is the Foreign Key.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Types of Relationships

<div align="right">[Back to Top ↑](#topics-covered)</div>

## One-to-One (1:1)

One record is associated with exactly one record in another table.

Examples

* User → Profile
* Employee → Passport
* Student → Identity Card

```text theme={null}
User -------- Profile
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

## One-to-Many (1:N)

One record can have multiple related records.

This is the most common relationship.

Example

One author can write many blog posts.

```text theme={null}
John

├── FastAPI Basics
├── SQLAlchemy ORM
├── JWT Authentication
└── Docker Deployment
```

Only the child table stores the foreign key.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Many-to-One (N:1)

This is simply the reverse direction of One-to-Many.

Many blog posts belong to one author.

```text theme={null}
Blog Post
      │
      ▼
    Author
```

Although conceptually different, SQLAlchemy implements One-to-Many and Many-to-One together.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Many-to-Many (M:N)

Many records on one side are related to many records on the other side.

Example

```text theme={null}
Blog Post
      ▲
      │
 Association Table
      │
      ▼
      Tag
```

Examples

* Students ↔ Courses
* Users ↔ Roles
* Blog Posts ↔ Tags
* Movies ↔ Actors

Many-to-Many relationships always require an **association table**.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Relationship in Our Blog Application

Our application contains two tables.

```text theme={null}
User

id
username
email
role

        │
        │ One User
        ▼

BlogPost

id
title
content
author_id
```

One author can create multiple blog posts.

```text theme={null}
John

├── Blog 1
├── Blog 2
├── Blog 3
└── Blog 4
```

The relationship is therefore

> **One User → Many Blog Posts**

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# User Roles

Our application stores both **Authors** and **Admins** inside the same `users` table.

```text theme={null}
Users

+----+----------+--------+
| id | username | role   |
+----+----------+--------+
| 1  | john     | AUTHOR |
| 2  | admin    | ADMIN  |
+----+----------+--------+
```

The `role` determines permissions.

**Author**

* Create posts
* Update own posts
* Delete own posts

**Admin**

* View all posts
* Update any post
* Delete any post
* Manage users

Notice that **roles do not affect the database relationship**.

Both Admin and Author are simply users.

The relationship remains

```text theme={null}
User 1 ------< BlogPosts
```

Authorization is handled by the application logic, not by the database relationship.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Relationship in SQLAlchemy

SQLAlchemy models relationships in two different ways.

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Database Relationship

The database only understands foreign keys.

```python theme={null}
author_id = mapped_column(
    ForeignKey("users.id")
)
```

This creates the actual database constraint.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

## ORM Relationship

Python objects need an object reference.

For that, SQLAlchemy provides `relationship()`.

```python theme={null}
posts = relationship(
    "BlogPost",
    back_populates="author"
)
```

and

```python theme={null}
author = relationship(
    "User",
    back_populates="posts"
)
```

Now SQLAlchemy understands that these two models are connected.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Complete User Model

```python theme={null}
class User(Base):
    __tablename__ = "users"

    id = mapped_column(primary_key=True)
    username = mapped_column(unique=True)
    email = mapped_column(unique=True)
    role = mapped_column()

    posts = relationship(
        "BlogPost",
        back_populates="author"
    )
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Complete BlogPost Model

```python theme={null}
class BlogPost(Base):
    __tablename__ = "blog_posts"

    id = mapped_column(primary_key=True)
    title = mapped_column()
    content = mapped_column()

    author_id = mapped_column(
        ForeignKey("users.id")
    )

    author = relationship(
        "User",
        back_populates="posts"
    )
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# How SQLAlchemy Uses Relationships

Unlike SQL, SQLAlchemy lets us navigate related objects directly.

Instead of writing joins, we simply access attributes.

### Get all posts written by a user

```python theme={null}
user.posts
```

Returns

```python theme={null}
[
    BlogPost(...),
    BlogPost(...),
    BlogPost(...)
]
```

***

### Get the author of a blog post

```python theme={null}
post.author
```

Returns

```python theme={null}
User(...)
```

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Creating Relationships

Instead of assigning the foreign key manually,

```python theme={null}
post.author_id = user.id
```

you can assign the object itself.

```python theme={null}
post.author = user
```

SQLAlchemy automatically updates the foreign key during `session.commit()`.

Similarly,

```python theme={null}
user.posts.append(post)
```

automatically sets

```python theme={null}
post.author_id
```

This synchronization is handled by the ORM.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Understanding `back_populates`

`back_populates` connects both relationship properties.

```text theme={null}
User.posts
      ▲
      │
back_populates
      │
      ▼
BlogPost.author
```

Without `back_populates`, each relationship behaves independently.

With `back_populates`:

* Updating one side updates the other.
* Both objects remain synchronized.
* Navigation works in both directions.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Database Relationship vs ORM Relationship

| Database             | SQLAlchemy ORM           |
| -------------------- | ------------------------ |
| Foreign Key          | relationship()           |
| Enforced by Database | Managed by SQLAlchemy    |
| Stores IDs           | Stores Object References |
| Used by SQL          | Used by Python           |

Both work together.

The database guarantees referential integrity, while SQLAlchemy provides convenient object navigation.

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

## Practice

To reinforce what you've learned in this section, practice with the interactive follow-along notebook:

<CardGroup cols={1}>
  <Card title="Follow-Along Practice" icon="laptop-code">
    Practice creating SQLAlchemy engines, declaring ORM Base models, running CRUD operations, and building relationships between tables.

    [💻 VS Code](vscode://file/Users/sivaprasad/Downloads/DSA%20With%20Python/public/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm-exercises.ipynb) | [🚀 Colab](https://colab.research.google.com/github/prasad230776/genai-course/blob/main/public/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm-exercises-colab.ipynb) | <a href="/notebooks/workshop-notebooks/10-sql-orm/10-sql-orm-exercises.ipynb" download>📥 Download</a>
  </Card>
</CardGroup>

***

<div align="right">[Back to Top ↑](#topics-covered)</div>

# Summary

* Relationships connect tables using foreign keys.
* Primary keys uniquely identify rows.
* Foreign keys reference another table's primary key.
* SQLAlchemy models relationships using `relationship()`.
* One-to-Many is the most common relationship in REST APIs.
* `back_populates` connects both sides of the relationship.
* SQLAlchemy lets you navigate relationships using Python objects instead of writing SQL joins.
* User roles (Author/Admin) determine permissions but do **not** change the database relationship.
* The database manages data integrity, while SQLAlchemy provides an object-oriented interface to work with related data.