How to Select Only One Column Using Sqlalchemy

How can I select only one column using SQLAlchemy?

You would have to do something along these lines:

session.query(Table.col1).filter(User.name=='Name')

Sqlalchemy select only one column (syntax > 1.4!)

If you are using SQLAlchemy core. rather than using the select method of the table instance, use the select function (docs) or if it is necessary to use the table's method, use select.with_only_columns.

import sqlalchemy as sa


engine = sa.create_engine('postgresql:///test', echo=True, future=True)
Users = sa.Table('users', sa.MetaData(), autoload_with=engine)

with engine.begin() as conn:
    q = Users.select().with_only_columns(Users.c.id, Users.c.name)
    res = conn.execute(q)
    for row in res:
        print(row)

Note that this core behaviour is not new to SQLAlchemy 1.4, although the syntax for select has changed slightly.

If you want to query an ORM model class' attributes, the syntax is similar, but you access the columns directly

q = sa.select(User.name)
result = session.execute(q)

If you want to use an async driver, the code might look like this:

import asyncio

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession

async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg:///test", echo=True, future=True
    )

    async with engine.connect():

        Session = orm.sessionmaker(engine, class_=AsyncSession)
        session = Session()
        # Users is the table from the earlier example
        result = await session.execute(
            Users.select.with_only_columns(Users.c.name)
        )
        print(result.fetchall())

        await session.close()
    await engine.dispose()


asyncio.run(async_main())

SQLAlchemy select only one column

Try this one:

role_tbl.select([role_tbl.c.passwd]).where(username=='name').execute().fetchall()

Or probably there is no such column in this table.

You can check it by printing all columns

print role_tbl.columns

P.S.
And also you should use one instance of metadata: MetaData(engine) (it should store information about all tables)

Flask SQLAlchemy query, specify column names

You can use the with_entities() method to restrict which columns you'd like to return in the result. (documentation)

result = SomeModel.query.with_entities(SomeModel.col1, SomeModel.col2)

Depending on your requirements, you may also find deferreds useful. They allow you to return the full object but restrict the columns that come over the wire.

SQLAlchemy join only one column

db.query(models.Data).all() returns an array of Data objects. So you can define a custom property on the Data class to extract names from attributes relationship:

class Attribute(Base):
    __tablename__ = "attributes"

    id = Column(BigInteger, primary_key=True, index=True)
    data_id = Column(BigInteger, ForeignKey("data.art_no"))
    name = Column(VARCHAR(500), index=True)

    data = relationship("Data", back_populates="attributes_rel")


class Data(Base):
    __tablename__ = "data"

    art_no = Column(BigInteger, primary_key=True, index=True)
    multiplier = Column(Float)

    attributes_rel = relationship("Attribute", back_populates="data", cascade="all, delete, delete-orphan")
    
    @property
    def attributes(self):
        return [attribute.name for attribute in self.attributes_rel]

Note that by default sqlalchemy will fetch attributes_rel for each Data object separately upon access. This might result in N+1 selects problem. To avoid that you should specify relationship loading technique

Also take a look at with_entities and hybrid attributes

how to select only some columns in SQLAlchemy?

here is the code that works for me:

from sqlalchemy import select
from sqlalchemy.sql import and_

results = select([orm_obj.c.poi_id, orm_obj.c.poi_name])\
    .where(and_(orm_obj.c.id > 1, orm_obj.c.id < 100)).execute()
for id, name in results:
    print id, name

Select only certain fields from the model using SqlAlchemy Core

This version of the query worked

events_invites = await database.fetch_all(query=select([event_invites.c.from_user, event_invites.c.created_at, event_invites.c.to_event]).where(event_invites.c.to_user == pk).order_by(event_invites.c.created_at))

Maybe someone could use

How to query specific columns from an array with sqlalchemy?

load_only should do the job:

from sqlalchemy.orm import load_only
rows = ["username", "role", "last_seen"]
session.query(Users).filter(Users.id.in_(user_ids)).options(load_only(*rows)).all()

---

Related Topics