SQLalchemy doesn't return null values

Question

I am building a FastAPI app. My database is MySQL.

Every time I try to fetch a user with null columns it was skipping everything that was null in my database. Also the result is a tuple so I cannot know which value belongs in which column.

I am trying to get a user from the database like this:

from datetime import datetime
from typing import Optional
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlmodel import Field, SQLModel, create_engine, Session, select, update
from typing import AsyncGenerator
from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base
from sqlalchemy.orm import sessionmaker, load_only
from sqlalchemy import join, func
import os

engine = create_engine("mysql+mysqlconnector://user:password@localhost/events")

class Users(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str
    username: str
    password: str
    firstname: str
    lastname: str
    birth_date: Optional[datetime] = None
    student_id: Optional[int] = None
    profile_picture: str
    createdon: datetime
    role: str
    disabled: bool

class Roles(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    role: str



def get_user_by_id(id):
    with Session(engine) as session:
        statement = select(
                Users.id,
                Users.username,
                Users.email,
                Users.firstname,
                Users.lastname,
                Users.birth_date,
                Roles.role,
                )  \
            .select_from(join(Users, Roles, Users.role == Roles.id)) \
            .where(Users.id == id)
        user = session.exec(statement).fetchone()
        print (user)
        user_dict = {
            "id": user[0],
            "username": user[1],
            "email": user[2],
            "firstname": user[3],
            "lastname": user[4],
            "student_id": user[5],
            "birth_date": user[6],
            "profile_picture": user[7],
            "role": user[8]
        }
        return user_dict

the result that I get is this:
(18, '[email protected]', 'password')

I tried doing this but didn't do anything:

 statement = select(
                Users.id,
                func.coalesce(Users.username, "").label('username'),
                func.coalesce(Users.email, "").label('email'),
                func.coalesce(Users.firstname, "").label('firstname'),
                func.coalesce(Users.lastname, "").label('lastname'),
                func.coalesce(Users.student_id, "").label('student_id'),
                func.coalesce(Users.birth_date, "").label('birth_date'),
                func.coalesce(Users.profile_picture, "").label('profile_picture'),
                func.coalesce(Roles.role, "").label('role'),            
                )  \
            .select_from(Users.join(Roles, Users.role == Roles.id)) \

EDIT: I tried this select_from(Users.join(Roles, Users.role == Roles.id)) but i got the same result.

the classes that i use are:

from datetime import datetime, date
from typing import Optional, List
from pydantic import BaseModel, Field, EmailStr

class User(BaseModel):
    id: int
    email: EmailStr = Field(...)
    username: str
    password: str
    firstname: str
    lastname: str
    birth_date: Optional[date] = None
    student_id: int
    profile_picture: str
    createdon: datetime
    role: int
    disabled: bool

class Roles(BaseModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    role: str 

Answer 1

Guess using schemas could help you! My example uses pydantic but you can actually take namedtuple or something similar instead.

from pydantic import BaseModel, ConfigDict


class User(BaseModel):
    model_config = ConfigDict(from_attributes=True)

    id: int
    username: str | None = None
    email: str
    ...


 user = User.model_validate(obj=query_result)

By the way, make sure your join is working as expected.