Adding more tables to my scripts makes other table data disappear.

[EtienneParmentier]

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 documentation, with the integrated search.
• I already searched in Google "How to X" and didn't find any information.
• I already read and followed all the tutorials in the docs and didn't find an answer.

Example Code

import os
import datetime
import sqlalchemy as sa
from decimal import Decimal
from sqlmodel import SQLModel, Session, Field, create_engine, Relationship, select


class Single(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**32)

    humanName: str = Field(max_length=10, unique=True)
    creationDateUTC: datetime.datetime = Field(default_factory=lambda: datetime.datetime.now(datetime.timezone.utc))

    caracs: list["Carac"] = Relationship(back_populates="single", cascade_delete=True)


class Carac(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**32)

    dateUTC: datetime.datetime = Field(default_factory=lambda: datetime.datetime.now(datetime.timezone.utc))

    singleId: int = Field(foreign_key="single.id", ondelete="CASCADE")
    single: Single = Relationship(back_populates="caracs")

    steps: list["Step"] = Relationship(back_populates="carac", cascade_delete=True)


class Step(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**32)

    caracId: int = Field(foreign_key="carac.id", ondelete="CASCADE")
    carac: Carac = Relationship(back_populates="steps")

    capas: list["CapaPoint"] = Relationship(back_populates="step", cascade_delete=True)
    # zernikes: list["ZernikePoint"] = Relationship(back_populates="step", cascade_delete=True)


class CapaPoint(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**64)

    capa_pF: Decimal = Field(max_digits=5, decimal_places=3)
    t_ms: int = Field(gt=-1, lt=2**32)

    stepId: int = Field(foreign_key="step.id", ondelete="CASCADE")
    step: Step = Relationship(back_populates="capas")


# class ZernikePoint(SQLModel, table=True):
#     id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**64)

#     t_ms: int = Field(gt=-1, lt=2**32)
#     rms: Decimal = Field(max_digits=5, decimal_places=3)

#     stepId: int = Field(foreign_key="step.id", ondelete="CASCADE")
#     step: Step = Relationship(back_populates="zernikes")

#     coeffs: list["ZernikeCoeff"] = Relationship(back_populates="point", cascade_delete=True)


# class ZernikeCoeff(SQLModel, table=True):
#     id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**64)

#     order: int = Field(gt=0, lt=2**8)
#     value: Decimal = Field(max_digits=5, decimal_places=3)

#     pointId: int = Field(foreign_key="zernikepoint.id", ondelete="CASCADE")
#     point: ZernikePoint = Relationship(back_populates="coeffs")


url = sa.URL.create(drivername="postgresql+psycopg2", username="banc", password=os.getenv("DB_PASSWORD"), host="127.0.0.0", port="4242")
engine = create_engine(url)

SQLModel.metadata.create_all(engine)


def create_data():
    with Session(engine) as session:
        singles = [Single(humanName="S666"), Single(humanName="S4242")]
        caracs = (Carac(single=single) for single in singles)
        steps = (Step(carac=carac) for carac in caracs for _ in range(200))
        capas = (CapaPoint(step=step, capa_pF=0.9, t_ms=12) for step in steps for _ in range(20))
        # zernikes = (ZernikePoint(step=step, t_ms=12, rms=0.12) for step in steps for _ in range(20))
        # coeffs = (ZernikeCoeff(point=point, order=o, value=0.23) for point in zernikes for o in range(21))

        # session.add_all(coeffs)
        session.add_all(capas)
        session.commit()


def display_data():
    with Session(engine) as session:
        stmt = select(Single).where(Single.humanName == "S666")
        results = session.exec(stmt)
        single = results.first()
        print(single.caracs[0].steps[0].capas)


create_data()
display_data()

Description

I am creating a database for measurements made using a test-bench. Singles (One half of a pair of glasses) are Characterized. A characterisation is made of a bunch of Steps during which the Capacitance is measured, along with Zernike Coefficients.
Now the weird part: without the commented code, the display_data() code does print a list of capacitance measurements as expected. When I uncomment the zernike coefficients classes and code, the list of capacitance is empty, and I don't understand why.

edit°1 : After more tests, it looks like it depends on the order in which I add the objects to the session: Whatever is added first gets to be saved in the database, the second one is not saved.
edit°2 : After reading the whole logs using echo=True at engine creation, I can confirm that the missing objects are not inserted inside the database.

I have edited the database IP adress and port, for obvious security reasons.

Operating System

Windows

Operating System Details

No response

Project Version

0.0.39

Python Version

Python 3.14.2

Additional Context

This is my first sqlmodel project, I have no experience in databases / SQL

[EtienneParmentier]

It turns out the problem was solved when using list comprehension instead of generator comprehension ?