Sqlalchemy select only one column (syntax > 1.4!)

Question

I have seen this and this questions but they both for old Sqlalchemy version. I'm using the next syntax in my queries:

get_user_st = users.select().where(users.c.login == user.phone_number)
connection.execute(statement=get_user_st).fetchone()

Here are I selecting by phone_number. How I can select a whole column?

Wrong syntax which I already tried:

str(users.select(users.c.login))
'SELECT users.id, users.phone_number, users.email, users.login, users.full_name, users.position, users.hashed_password, users.role, users.created_datetime, users.last_update, users.owner_id \nFROM users \nWHERE users.login'

str(users.c.login.select())
Traceback (most recent call last):
  File "/snap/pycharm-community/238/plugins/python-ce/helpers/pydev/_pydevd_bundle/pydevd_exec2.py", line 3, in Exec
    exec(exp, global_vars, local_vars)
  File "<input>", line 1, in <module>
  File "/home/david/PycharmProjects/na_svyazi/venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 818, in __getattr__
    util.raise_(
  File "/home/david/PycharmProjects/na_svyazi/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'select'

I tried to find this case in Sqlalchemy docs but failed.

Getting db:

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError


engine_async = create_async_engine(url=config.SQLALCHEMY_ASYNCPG_URL, echo=False, future=True)  # Future -use style 2.0
session_async = sessionmaker(bind=engine_async, autoflush=True, class_=AsyncSession)

async def get_db():
    session = session_async()
    try:
        yield session
        await session.commit()
    except SQLAlchemyError as ex:
        await session.rollback()
        raise ex
    finally:
        await session.close()

Type of my session is:

type(postgres_session)
<class 'sqlalchemy.orm.session.AsyncSession'>

P.S. it preferable to not import select from sqlalchemy but use Table object (users.c or just users) if it's possible

Answer 1

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