Skip to content

SQLAlchemy to Pydantic: from_attributes

In real applications, data usually comes from a database. With SQLAlchemy, each row comes back as an ORM object: an Employee object with attributes like first_name and salary. But your program or API should not hand these ORM objects to the outside world. It should return Pydantic models, because they:

  • contain only the fields you choose (no accidental salary or password leaks),
  • are validated,
  • turn into JSON with one call (model_dump_json()).

The problem: Pydantic normally reads input from a dictionary (data["first_name"]), while an ORM object has attributes (obj.first_name). One setting solves it:

class EmployeeRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)   # read obj.first_name, not only data["first_name"]
    ...

EmployeeRead.model_validate(orm_employee)

This lesson follows 05_orm_from_attributes.py. Sections 2 to 7 need SQL Server; section 1 runs anywhere.

The database used in lessons 05 and 06

The project's database.py file defines the SQLAlchemy tables and the connection. Its connection details come from settings.py and the .env file (explained in lesson 07). The important part is the Employee table class:

class Employee(Base):
    __tablename__ = "Employees"

    id: Mapped[int] = mapped_column(primary_key=True)
    first_name: Mapped[str] = mapped_column(String(50))
    last_name: Mapped[str] = mapped_column(String(50))
    email: Mapped[str] = mapped_column(String(100), unique=True)
    salary: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    hire_date: Mapped[date]
    is_active: Mapped[bool] = mapped_column(default=True)
    department_id: Mapped[int] = mapped_column(ForeignKey("Departments.id"))

    # Each employee belongs to one department
    department: Mapped[Department] = relationship(back_populates="employees")

database.py also has create_database_and_tables(), which creates the database PydanticTutorialDB and the Departments and Employees tables if they are missing, and add_sample_data(), which adds three departments (IT, HR, Finance) and five employees (Asha, Ravi, Neha, Kiran, Meena) the first time. SessionLocal() opens a session, the SQLAlchemy "unit of work" (similar to a DbContext in .NET).

Don't confuse the two "Employee" classes. database.Employee is the SQLAlchemy table class. The Pydantic classes in this lesson have different names (EmployeeRead, EmployeePublic, EmployeeCard): they describe the data you send out.

1. The idea, with a plain Python class (no database yet)

from_attributes works with any object, not only database rows. Let's see it with a normal class first:

from pydantic import BaseModel, ConfigDict, ValidationError

class Car:                                   # an ordinary class, not Pydantic
    def __init__(self, brand, model, year, secret_code):
        self.brand = brand
        self.model = model
        self.year = year
        self.secret_code = secret_code


class CarWithoutConfig(BaseModel):
    brand: str
    model: str
    year: int


class CarRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)   # <- the important line

    brand: str
    model: str
    year: int


car = Car("Tata", "Nexon", "2024", secret_code="X-99")

try:
    CarWithoutConfig.model_validate(car)
except ValidationError as error:
    print("Without from_attributes:", error.errors()[0]["msg"])

car_read = CarRead.model_validate(car)
print("With from_attributes   :", car_read)          # year "2024" became 2024, secret_code is NOT copied
print("As JSON                :", car_read.model_dump_json())
Output
Without from_attributes: Input should be a valid dictionary or instance of CarWithoutConfig
With from_attributes   : brand='Tata' model='Nexon' year=2024
As JSON                : {"brand":"Tata","model":"Nexon","year":2024}
  • Without the setting, Pydantic refuses the object: it expects a dictionary.
  • With from_attributes=True, Pydantic reads car.brand, car.model and car.year.
  • Normal validation still happens: the text "2024" became the int 2024.
  • secret_code is not copied, because CarRead doesn't declare it. Only the fields you declare are read. This is how you control what leaves your program.

2. Database setup

from database import Employee, SessionLocal, add_sample_data, create_database_and_tables

create_database_and_tables()
add_sample_data()
print("Database, tables and sample data are ready.")

You can run this lesson as many times as you like: existing tables and data are left alone.

3. One ORM object to a Pydantic model

from datetime import date
from decimal import Decimal
from sqlalchemy import select

class EmployeeRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)

    id: int
    first_name: str
    last_name: str
    email: str
    salary: Decimal
    hire_date: date
    is_active: bool
    department_id: int


with SessionLocal() as session:
    orm_employee = session.scalars(select(Employee).order_by(Employee.id)).first()
    print("ORM object     :", orm_employee, type(orm_employee))

    employee = EmployeeRead.model_validate(orm_employee)
    print("Pydantic model :", employee)
    print("JSON           :", employee.model_dump_json())
Sample output (with the sample data; ids can differ in your database)
ORM object     : <Employee id=1 name='Asha'> <class 'database.Employee'>
Pydantic model : id=1 first_name='Asha' last_name='Rao' email='asha.rao@example.com' salary=Decimal('95000.00') hire_date=datetime.date(2021, 4, 1) is_active=True department_id=1
JSON           : {"id":1,"first_name":"Asha","last_name":"Rao","email":"asha.rao@example.com","salary":"95000.00","hire_date":"2021-04-01","is_active":true,"department_id":1}
  • select(Employee).order_by(Employee.id) builds the query SELECT ... FROM Employees ORDER BY id; .first() takes the first row as an ORM object.
  • The ORM object prints with its own __repr__ (<Employee id=1 name='Asha'>), which is not very useful for an API.
  • EmployeeRead.model_validate(orm_employee) reads each attribute and builds a validated Pydantic model, which becomes clean JSON in one call.

4. Many rows to a list of models

with SessionLocal() as session:
    rows = session.scalars(select(Employee).where(Employee.is_active).order_by(Employee.salary.desc())).all()
    employees = [EmployeeRead.model_validate(row) for row in rows]

for e in employees:
    print(f"   {e.id:>3}  {e.first_name:<8} {e.last_name:<8} {e.salary:>12,.2f}")
Sample output
     4  Kiran    Desai      110,000.00
     1  Asha     Rao         95,000.00
     2  Ravi     Kumar       72,000.50
     3  Neha     Patil       65,000.00
  • The query keeps only active employees (where(Employee.is_active)), so Meena, who is inactive, is not in the list, and sorts them by salary, highest first.
  • A simple list comprehension converts every row: [EmployeeRead.model_validate(row) for row in rows].
  • The employees list is used after the with block, when the session is already closed. That's safe because the Pydantic models are plain data, not connected to the database.

5. A smaller model hides fields

Different screens need different data. A public staff directory should not show salaries. Make a small model for it:

class EmployeePublic(BaseModel):
    model_config = ConfigDict(from_attributes=True)

    id: int
    first_name: str
    last_name: str


with SessionLocal() as session:
    rows = session.scalars(select(Employee).order_by(Employee.id)).all()
    public = [EmployeePublic.model_validate(row).model_dump() for row in rows]

print(public)
Sample output (first two of five shown)
[{'id': 1, 'first_name': 'Asha', 'last_name': 'Rao'}, {'id': 2, 'first_name': 'Ravi', 'last_name': 'Kumar'}, ...]

The same database rows, but only three fields come out. No email, no salary. You don't need to remember to delete anything: what is not declared is never copied.

6. Different names and calculated values

The names in your output don't have to match the database column names. validation_alias says which attribute of the ORM object to read from, and @computed_field (from lesson 04) adds calculated values:

from pydantic import Field, computed_field

class EmployeeCard(BaseModel):
    model_config = ConfigDict(from_attributes=True)

    employee_no: int = Field(validation_alias="id")            # reads orm.id
    given_name: str = Field(validation_alias="first_name")     # reads orm.first_name
    family_name: str = Field(validation_alias="last_name")
    hire_date: date

    @computed_field
    @property
    def display_name(self) -> str:
        return f"{self.family_name.upper()}, {self.given_name}"

    @computed_field
    @property
    def years_in_company(self) -> int:
        return (date.today() - self.hire_date).days // 365


with SessionLocal() as session:
    for row in session.scalars(select(Employee).order_by(Employee.hire_date)):
        print("  ", EmployeeCard.model_validate(row).model_dump_json())
Sample output (first two rows; years depend on today's date)
   {"employee_no":4,"given_name":"Kiran","family_name":"Desai","hire_date":"2018-11-05","display_name":"DESAI, Kiran","years_in_company":7}
   {"employee_no":3,"given_name":"Neha","family_name":"Patil","hire_date":"2020-01-10","display_name":"PATIL, Neha","years_in_company":6}

The database still has id, first_name and last_name, but the output uses employee_no, given_name and family_name, plus two values that don't exist in the table at all.

7. The other direction: Pydantic model to ORM object

copy = employees[0].model_dump(exclude={"id"})        # dict without the id
new_orm_object = Employee(**copy)                     # unpack into the ORM class
print("New ORM object (not saved):", new_orm_object, new_orm_object.email)
Sample output
New ORM object (not saved): <Employee id=None name='Kiran'> kiran.desai@example.com

model_dump() gives a dictionary whose keys match the table columns, so Employee(**copy) creates a new ORM object. The id is excluded because SQL Server generates it when the row is inserted. (The object is not saved here; lesson 06 does real inserts.)

Remember

  • Convert ORM objects to Pydantic models inside the with SessionLocal() block. Related data (like employee.department) is loaded from the database the first time you touch it, which needs an open session.
  • from_attributes also works for nested models, for example an employee with its department. That's in lesson 06.

Exercises

From the end of 05_orm_from_attributes.py:

  1. Make DepartmentRead(id, name, location) with from_attributes and print every Department as JSON (from database import Department).
  2. Make EmployeeSalary(full_name, salary) where full_name is a computed_field.
  3. Load only employees hired after 2021-01-01 and convert them to EmployeePublic.
  4. Try EmployeeRead.model_validate(row) after removing from_attributes and read the error.

Summary

  • SQLAlchemy gives you ORM objects; your program should return Pydantic models.
  • ConfigDict(from_attributes=True) lets Pydantic read attributes (obj.name), not only dictionary keys.
  • Convert with Model.model_validate(orm_object), and use a list comprehension for many rows.
  • Only declared fields are copied, so small models (like EmployeePublic) hide sensitive columns.
  • validation_alias renames fields; @computed_field adds calculated values.
  • Employee(**model.model_dump()) goes the other way, from a Pydantic model to an ORM object.

Next: nested models and Create / Update / Read schemas with full CRUD.