CODEWITHSUNDEEP
pythonmysqlsqlalchemyplanetscalejasync-sql
Lee Skies
Lee Skies

Reputation: 91

Query for specific columns sqlalchemy

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()

Upvotes: 0

Views: 419

Answers (2)

Ammar Poursadegh
Ammar Poursadegh

Reputation: 73

you can try this:

async def get_soldiers():
    async with AsyncSession(engine) as session:
        stmt = select(Soldier.id, Soldier.name, Soldier.service_number)
        result = await session.execute(stmt)
        soldiers = result.scalars().all()
        return soldiers

put your prefered columns on the satement

Upvotes: 1

Louis Huang
Louis Huang

Reputation: 984

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.

Upvotes: 2

Related Questions