SQLModel: how to load specific columns?

Question

I tried to load specific columns from a table via the following method:

from sqlalchemy.orm import load_only
from sqlmodel import (Field, Session, SQLModel, create_engine, select)
from 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())

However returns Row class which then I can't run toDict() since normally I get back Tx class that has that method. Hope it is clear what I want here, I did not expect Row class.

So then I try using the sqlalchemy method, however, this does not seem to work well, as it returns ALL columns, regardless of what I filter for:

from sqlalchemy.orm import load_only
from sqlmodel import (Field, Session, SQLModel, create_engine, select)
from 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())

Does this answer your question? [In SQLAlchemy, why is my load\_only not filtering any columns that I have specified?](https://stackoverflow.com/questions/50317889/in-sqlalchemy-why-is-my-load-only-not-filtering-any-columns-that-i-have-specifi) — rfkortekaas, May 13 '22 at 06:13

score 0 · Answer 1 · answered Apr 19 '23 at 16:23

You just have to get results and pass it to jsonable_encoder method.

jsonable_encoder receives an object, like a Pydantic model, and returns a JSON compatible version. https://fastapi.tiangolo.com/tutorial/encoder/

So the input results is a list containing objects of type sqlalchemy.engine.row.Row and the output will be a list containing objects of type dict.

from fastapi.encoders import jsonable_encoder
from fastapi.responses import JSONResponse

with Session(engine) as session:
    results = session.exec(select(Tx.id,Tx.party,Tx.time)).order_by(Tx.time.desc()).all()
json_compatible = jsonable_encoder(results)
return JSONResponse(status_code=200, content=json_compatible)

SQLModel: how to load specific columns?

1 Answers1