UnmappedInstanceError exception with (wrongly) unrefreshed model object

[orenl]

First Check

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

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

import pytest

from pydantic import BaseModel
from typing import List, Optional
from sqlmodel import Session, SQLModel, Field, Relationship, create_engine
from sqlmodel.pool import StaticPool

# MODELS

class Ticket(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

class Mascot(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

class Groups(SQLModel):
    ticket: Ticket
    mascot: Mascot

# FIXTURES

@pytest.fixture(name='session')
def session_fixture():
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        yield session

@pytest.fixture(name='ticket')
def ticket_fixture(session: Session):
    t = Ticket()
    session.add(t)
    session.commit()
    session.refresh(t)
    yield t

@pytest.fixture(name='mascot')
def mascot_fixture(session: Session):
    m = Mascot()
    session.add(m)
    session.commit()
    session.refresh(m)
    yield m

# TEST

def test_combine(
    session: Session,
    ticket: Ticket,
    mascot: Mascot,
):
    # Problem 1: @ticket is empty, @mascot is set (reverse their order would
    # reverses the outcome). Access to any field in @ticket fixes the problem.
    print(f'ticket = {ticket.__dict__}')           # <-- ticket is empty, no ticket.id value
    print(f'mascot = {mascot.__dict__}')           # <-- mascot has proper mascot.id as expected

    # this works (and when un-commented, makes problem 2 below disappear):
    # id = ticket.id if ticket else 0

    # Problem 2: this fails (but making class Groups not inherit SQLModel or
    # BaseModel, makes the problem disappear):
    group = Groups(ticket=ticket, mascot=mascot)
    id = group.ticket.id if group.ticket else 0    # <-- sqlalchemy.orm.exc.UnmappedInstanceError

Description

Use pytest to run this sample code.

The code defines two simple models/tables (Ticket, Mascot) and a third model (Group) that groups them. It uses pytest to populate the database for testing. The are two problems that occur in the function test_combine():

1. The function takes three fixtures: session and the models ticket, mascot. The expected behavior is to have both properly initialized inside the function. However, in reality only the latter (mascot) holds data while the former (ticket) appears empty. If we reverse their order in the function arguments, the reverse happens. Also, any access to the content (e.g. to ticket.id) refreshes the object - so perhaps somehow the object became stale.

2. The function then instantiates a Group object containing the two models, and then tries to access the (stale, from before) ticket. This time, the object does not get auto-refreshed, but instead an exception is raised. Like before, access to the content (e.g. to ticket.id) any time earlier makes the problem go away. Also, changing class Group to not inherit from SQLModel (or pydantic.BaseModel), also resolves the problem. So perhaps this is related to nested models (even though Group is not intended to be user/input facing so does not strictly need SQLModel/BaseModel capabilities).

Operating System

Linux

Operating System Details

Ubuntu 21.10 sqlalchemy 1.4.35 pydantic 1.9.1 pytest 7.1.2

SQLModel Version

0.0.6

Python Version

3.9

Additional Context

To run the test, run the command:

pytest -s reproduce.py

[byrman]

Calling commit() will expire all objects in the session (see docs), so ticket gets expired as a result of committing mascot and vice versa. Try this instead:

with Session(engine, expire_on_commit=False) as session:
    yield session

[orenl]

Got it, thanks - that explains why the former is expired - and solved the problem.

I'm still puzzled why then, direct access to ticket.id works (it gets automatically refreshed) while the same access through the Groups instance as in group.ticket.id raises an exception?

[byrman]

Good question! I think it has to do with ticket and group.ticket being different objects. Apparently, a new ticket object is created by pydantic during the parsing / validation process. If you create group without validation, group = Groups.construct(ticket=ticket, mascot=mascot), no new ticket is created and things work as expected.