Question 202532 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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()
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search