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, 'test2@test.com', '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
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.