Querying and Selecting Specific Column in SQLalchemy

SQLAlchemy ORM: SELECT specific columns with multiple JOIN and WHERE clause

I managed to make this work by not using select, and notice the use of in_ for value evaluation with a list.

 orm_query = (
    session.query(
        payment_plans.payment_plan_id,
        payment_plans.plan_id,
        payment_plans.amount,
        invoices.promo_code,
        users.sso_guid,
        users.user_id,
   )
   .join(users, payment_plans.user_id == users.user_id)
   .join(invoices, payment_plans.invoice_id == invoices.invoice_id)
   .where(payment_plans.due_date < '01/01/2022')
   .where(payment_plans.status.in_(["pending", "failed"]))
)

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.

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

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: selecting which columns of an object in a query

you can query for individual columns, which returns named tuples that do in fact act pretty much like your mapped object if you're just passing off to a template or something:

http://www.sqlalchemy.org/docs/orm/tutorial.html#querying

or you can establish various columns on the mapped class as "deferred", either configurationally or using options:

http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#deferred-column-loading

there's an old ticket in trac for something called "defer_everything_but()", if someone felt like providing tests and such there's no reason that couldn't be a feature add, here's a quick version:

from sqlalchemy.orm import class_mapper, defer
def defer_everything_but(entity, cols):
    m = class_mapper(entity)
    return [defer(k) for k in 
            set(p.key for p 
                in m.iterate_properties 
                if hasattr(p, 'columns')).difference(cols)]

s = Session()
print s.query(A).options(*defer_everything_but(A, ["q", "p"]))

defer() should really accept multiples, added ticket #2250 for that (edit: as noted in the comment this is in 0.9 as load_only())

---

Related Topics