How to select specific columns from database table?

[christianholland]

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

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        session.exec(select(Hero)).all()


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

Description

In the docu it is mentioned that it is possible to select only specific columns from the datatable. However, the example is only given for SQL and not for SQLModel

So how can I implement this query in SQLModel?

SELECT id, name
FROM hero

Finally I also dont want to hardcode the column of interest but the column name(s) will be stored in a variable.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.8.1

Additional Context

No response

[byrman]

Perhaps there are other / better ways, but examining the echoed SQL, this seems to work:

from sqlalchemy.orm import load_only

def select_heroes():
    with Session(engine) as session:
        session.exec(select(Hero).options(load_only("id", "name"))).all()

Or (addressing your 2nd question) via: load_only(*fields)

[christianholland]

Thanks, the load_only() approach indeed works, eventhough the column id is returned by default (I guess because its the primary key?).

However, I am facing some problems when I want to implement this approach in my FastAPI app. I would like to use the same endpoint (get /heros/) to retrieve all hero columns or just a subset of them with a query parameter. As a respone model I am using HeroRead. Independent of the load_only() option the endpoint always returns all columns.

[byrman]

If you make the fields of your HeroRead model optional, you could do something along these lines:

@app.get("/heroes/", response_model=List[HeroRead], response_model_exclude_unset=True)
def get_heroes():
    return [hero.dict(exclude_none=True) for hero in select_heroes()]

[StefanBrand]

For reference, it is possible to select columns like this:

with Session(engine) as session:
    heroes = session.exec(
        select(
            Hero.id,
            Hero.name
        )
    ).all()

However, the return type is <class 'sqlalchemy.engine.row.Row'>

[christianholland]

Thanks both

[tiangolo]

Thanks for the help here @byrman and @StefanBrand ! 👏 🙇

Thanks for reporting back and closing the issue @christianholland 👍

[kashyapanup]

For reference, it is possible to select columns like this:

with Session(engine) as session:
    heroes = session.exec(
        select(
            Hero.id,
            Hero.name
        )
    ).all()

However, the return type is <class 'sqlalchemy.engine.row.Row'>

Is this still the best way to select only specific columns?
What is the best way to map this Row object to my model type? I'm not able to access the __dict__ attribute.

[bastienjalbert]

For reference, it is possible to select columns like this:

with Session(engine) as session:
    heroes = session.exec(
        select(
            Hero.id,
            Hero.name
        )
    ).all()

However, the return type is <class 'sqlalchemy.engine.row.Row'>

It seems to (still) be working great.
But it does not work on computed field / property. If you select a computed field (as described here), you'll face an error from SQLAlchemy :

sqlalchemy.exc.ArgumentError: Column expression, FROM clause, or other columns clause element expected, got <property object at 0x317ea5c10>.