from typing import Optional

from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy import text
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import Session


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    fullname: Mapped[Optional[str]]


engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)
session = Session(engine)

textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")
textual_sql = textual_sql.columns(User.id, User.name, User.fullname)
orm_sql = select(User).from_statement(textual_sql)
for user_obj in session.execute(orm_sql).scalars():
    print(user_obj)