select specific columns returning Row objects instead of ORM class, load_only() doesn't work

[johnatr]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

1)

from sqlalchemy.orm import load_only
from sqlmodel import (Field, Session, SQLModel, create_engine, select)
from chalicelib.models import ( Tx )

engine = create_engine(url, echo=True)

        with Session(engine) as session:
            results = session.exec(select(Tx.Id, Tx.party, Tx.time)
                                   .order_by(Tx
                                             .time
                                             .desc())
                                   ).all()

            myResults = []
            
            for result in results:
                myResults.append(result.toDict())

2)

from sqlalchemy.orm import load_only
from sqlmodel import (Field, Session, SQLModel, create_engine, select)
from chalicelib.models import ( Tx )

engine = create_engine(url, echo=True)

        with Session(engine) as session:
            results = session.exec(select(Tx)
                                   .options(load_only("id", "party", "time")))
                                   .order_by(Tx
                                             .time
                                             .desc())
                                   ).all()
            myResults = []
            
            for result in results:
                myResults.append(result.toDict())

Description

Basically I'm following the two methods mentioned here: #232

1. Selecting columns inside select() returns Row class object instead of the desired ORM class Tx like before

I expected Tx(id=2, party='new', time='0000:00:00') instead got Row object that I am having trouble now converting to a dict with .toDict() that normally converts that Tx object into a dict.

2. Using sqlalchemy's load_only filter gets ignored.

I expected Tx(id=2, party='new', time='0000:00:00') but instead got all the columns associated.

Want to know if I'm doing this the "right" way, seeing as there is no documentation and I could only find this information after coming across #232 via Google search.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.8

Additional Context

No response

[byrman]

2. Using sqlalchemy's load_only filter gets ignored.

The excluded columns will still be retrieved if you inspect them within the context of the session. Could it be that this lazy loading gives you the impression that load_only does not work?

[anupkashyap]

I had the same issue. Not sure which is the better approach. load_only does seem to work for me. Also I've tried passing the fields from the model instead of the hard-coded field strings and that seems to work too. Something like this

results = session.exec(select(Tx)
                 .options(load_only(Tx.id, Tx.party, Tx.time)))
                 .order_by(Tx
                           .time
                           .desc())
                 ).all()

Wanted to also know the best way to do this. Would be great to include it in the documentation too. I can help with adding it to the documentation if there is a "good" approach for it.

[YuriiMotov]

Works well in current version (0.0.24).

from sqlalchemy.orm import load_only
from sqlmodel import Field, Session, SQLModel, create_engine, select


class Tx(SQLModel, table=True):
    id: int = Field(primary_key=True)
    party: str
    time: str
    something: str


engine = create_engine("sqlite:///", echo=True)
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    session.add(
        Tx(party="Party", time="Time", something="Something")
    )
    session.commit()


with Session(engine) as session:
    results = session.exec(
        select(Tx)
            .options(load_only(Tx.id, Tx.party))
            .order_by(Tx.time.desc())
    ).all()
    myResults = []

for result in results:
    myResults.append(result.dict())

print(myResults)
# [{'party': 'Party', 'id': 1}]