Query for specific columns sqlalchemy

Question

I have the table soldiers:

class Soldier(Base):
    __tablename__ = "soldiers"

    id = Column(String(36), default=lambda: str(uuid4()), primary_key=True, unique=True)
    name = Column(String(50), nullable=False)
    service_number = Column(Integer, nullable=False)
    commander_id = Column(String(36), nullable=False, index=True)
    created_at = Column(Date, nullable=False, default=func.now())

and this async query:

result = await db.execute(select(Soldier))
soldiers = result.scalars().all()

db comes from here:

async def get_db():
    async with engine.begin() as connection:
        await connection.run_sync(Base.metadata.create_all)
    db = AsyncSession()
    try:
        print("connection opened")
        yield db
    finally:
        print("connection closed")
        await db.close()

For the problem itself. How do I query only for specific columns? I'm using a MySQL db hosted on planetscale (vitess)

The result when trying this is a list of the first column argument, say its id - so I get an array of id's in the response:

result = await db.execute(select(Soldier.id, Soldier.name, Soldier.service_number))
soldiers = result.scalars().all()

also tried using this but same result:

result = await db.execute(select(Soldier).with_only_columns(Soldier.id, Soldier.name, Soldier.service_number))
soldiers = result.scalars().all()

Answer 1

When you use Session.scalars(), it will deliver the first "column" of each row. If your select() is on a model, such as select(Soldier), it will return a list of tuples of the form: (Soldier(...),) (a one element tuple with the soldier object), so using .scalars() will return a list of Soldier objects. Session.scalars() is a convenience method for this case.

But when you selected specific columns (like select(Soldier.id, Soldier.name, Soldier.service_number)), you will want to use the result directly, since a list of tuples of the form (id, name, service_number) will be returned. If you used scalars on it, you will only get the first column, which is id here.