Pandas Not a Time (NaT) value raises TypeError when being converted into a <datetime | None> column type using model_validate()

[Alzter]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlmodel import SQLModel, Field
from datetime import datetime
import pandas as pd
from fastapi import FastAPI

# HeroMeeting has a nullable datetime field called "time"
class HeroMeeting(SQLModel):
    id : int | None = Field(primary_key=True, default=None)
    name : str = Field(max_length=255, default=None)
    time : datetime | None = Field(default=None)

app = FastAPI()

# Create a list of HeroMeeting entries, one of which has datetime set to None
hero_meetings = [
    {"name": "Cape Repair Workshop", "time": datetime(day=7, month=2, year=2022)},
    {"name": "Night Owl Patrol", "time": datetime(day=10, month=2, year=2022)},
    {"name": "Budget Villain Confrontation", "time": datetime(day=12, month=2, year=2022)},
    {"name": "Secret Lair Lease Inspection", "time": datetime(day=15, month=2, year=2022)},
    {"name": "Moral Ambiguity Training", "time": datetime(day=18, month=2, year=2022)},
    {"name": "Bootleg Justice League AGM", "time": datetime(day=20, month=2, year=2022)},
    {"name": "Midnight Mask Exchange", "time": None},
]

# Convert list of HeroMeeting entries into a Pandas DataFrame and back
hero_meetings = pd.DataFrame(hero_meetings)
hero_meetings = hero_meetings.to_dict(orient="records")

@app.post("/get_first_hero_meeting", response_model = HeroMeeting)
def get_first_hero_meeting():
    # WILL NOT raise exception because the datetime is assigned
    return HeroMeeting.model_validate(hero_meetings[0])

@app.post("/get_last_hero_meeting", response_model = HeroMeeting)
def get_last_hero_meeting():
    # WILL raise exception because the datetime has been converted from None to Not a Time (NaT) by Pandas, which is not correctly converted to NONE by SQLAlchemy
    return HeroMeeting.model_validate(hero_meetings[-1])

@app.post("/get_hero_meetings", response_model = list[HeroMeeting])
def get_hero_meetings():
    meetings = []
    
    for meeting in hero_meetings:
        
        # WILL raise an exception once it reaches a meeting with an unset time
        meeting = HeroMeeting.model_validate(meeting)
        meetings.append(meeting)

    return meetings

Description

• Create a model called "HeroMeeting" which has a nullable datetime field "time".
• Create a list of dicts to represent HeroMeeting instances, some of which have "time" fields set to None.
• Convert the list of dicts into a Pandas DataFrame, and then convert it back into a list of dicts again.
• Create a FastAPI path that returns a list of HeroMeetings.
• In this path, parse each HeroMeeting dict into a HeroMeeting instance using HeroMeeting.model_validate(...).
• When SQLAlchemy attempts to parse a HeroMeeting dict with an unset "time" field into a HeroMeeting instance, it will raise a TypeError: 'float' object cannot be interpreted as an integer.

Cause

• Columns within a Pandas DataFrame using the datetime data type use Not a Time (NAT) to represent null values.
• When converting a JSON object (list of dicts) into a DataFrame and back, empty timestamps are changed from None to NaT, which has the type pandas._libs.tslibs.nattype.NaTType.
• SQLAlchemy does not accept NaTType as a valid data type for a datetype column and raises a TypeError when attempting to parse it.

Workaround

• Explicitly convert NaT back to None before calling model_validate.

@app.post("/get_hero_meetings", response_model = list[HeroMeeting])
def get_hero_meetings():
    meetings = []
    
    for meeting in hero_meetings:
    
        # FIX: Explicitly convert Not a Time (NaT) back to None.
        if pd.isna(meeting["time"]): meeting["time"] = None
        
        meeting = HeroMeeting.model_validate(meeting)
        meetings.append(meeting)

    return meetings

Operating System

Linux

Operating System Details

Linux nixos 6.12.59 #1-NixOS SMP PREEMPT_DYNAMIC Mon Nov 24 09:36:08 UTC 2025 x86_64 GNU/Linux

SQLModel Version

0.0.24

Python Version

3.12.12

Additional Context

Using pandas 2.3.1

[YuriiMotov]

Does it work with pure Pydantic model?

[YuriiMotov]

I checked it works the same way with Pydantic model. So, it's not an SQLModel's issue.

You can solve this by adding a field_validator to convert NaT to None:

...

+from pydantic import field_validator

class HeroMeeting(SQLModel):
    id: int | None = Field(primary_key=True, default=None)
    name: str = Field(max_length=255, default=None)
    time: datetime | None = Field(default=None)

+   @field_validator("time", mode="before")
+   @classmethod
+   def nat_to_none(cls, value):
+       if value is pd.NaT:
+           return None
+       return value

...