select

select()

Возвращает объект Select

select_stmt = select([user_table])
conn.execute(select_stmt).fetchall()

Select()

class sqlalchemy.sql.expression.Select

stmt = select([user_table])
# select user.id from "user"

connection.execute(stmt)

order_by()

stmt.order_by(user_table.c.username)
'''
select
    user.id
from
    "user"
order by
    user.username
'''

select_from()

(
    select([user_table, address_table])
    .select_from(
        user_table.join(address_table)
    )
)

where()

stmt.where(user_table.c.username == 'ilnurgi')
'''
select
    user.id
from
    "user"
where
    user.username = 'ilnurgi'
'''

stmt.where(
    or_(
        user_table.c.username == 'ilnurgi',
        user_table.c.username == 'ilnurgi1',
    )
)
'''
select
    user.id
from
    "user"
where
    user.username = 'ilnurgi'
    or
    user.username = 'ilnurgi1'
'''

(
    stmt
    .where(user_table.c.username == 'ilnurgi')
    .where(user_table.c.fullname == 'ilnurgi1')
)
'''
select
    user.id
from
    "user"
where
    user.username = 'ilnurgi'
    and
    user.fullname = 'ilnurgi1'
'''