[Python] How to map query results to objects in SQLModel?

I'm using SQLModel to interact with a MariaDB database. Supposed that my mapping looks like this (note that QueryResult is not a table):

from sqlmodel import Field, SQLModel


class Person(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
first_name: str
last_name: str
age: int | None = None


class QueryResult(SQLModel):
full_name: str
person_age: int | None = None


A simple query is to select some people and concatenate their first and last name into name.

from sqlalchemy import func
from sqlmodel import Session, col, select

with Session(engine) as session:
query = (
select(
func.concat(Person.first_name, Person.last_name).label('full_name'),
col(Person.age).label('person_age')
)
.select_from(Person)
)
results = session.exec(query).all()
print(results)


The results is a list of tuples. However, I want it to be a list of QueryResult. I've made sure that the label and field names are matched. But what else do I need to do to map the result to the defined class?

I know that I can do it manually by looping through the results, but it does not feel right to me. SQLAlchemy, which SQLModel is based on, must have a solution for this.

Continue reading...