Is it possible to query using raw sql and get object back?

Question

In Hibernate it's possible to query using raw sql and get entities (objects) back. Something like: createSQLQuery(sql).addEntity(User.class).list().

Is it possible to do the same in sqlalchemy?

Answer 1

As @Ilja notes via link in a comment to the question, it is possible to do what you describe using .from_statement() as described in the documentation:

from sqlalchemy import Column, create_engine, Integer, select, String, text
from sqlalchemy.orm import declarative_base, Session

engine = create_engine("sqlite://")

Base = declarative_base()


class Person(Base):
    __tablename__ = "person"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    def __repr__(self):
        return f"<Person(id={self.id}, name='{self.name}')>"


Base.metadata.create_all(engine)

# sample data
with Session(engine) as session, session.begin():
    session.add_all(
        [Person(name="Adam"), Person(name="Alicia"), Person(name="Brandon")]
    )

# test
with Session(engine) as session, session.begin():
    sql = "SELECT id FROM person WHERE name LIKE 'A%'"
    results = session.scalars(select(Person).from_statement(text(sql))).all()
    print(results)
    # [<Person(id=1, name='Adam')>, <Person(id=2, name='Alicia')>]

Answer 2

When using the entityManager you can try:

entityManager.createNativeQuery("select some native query", User.class)

According to the API:

public Query createNativeQuery(String sqlString, Class resultClass);