CODEWITHSUNDEEP
pythonsqlalchemyfastapipydantic
Ben
Ben

Reputation: 21685

I have a SQLAlchemy model with extra calculated fields introduced by a query. How do I serialize this with pydantic?

I have a table of posts and a table of post_votes. A post vote can have a value of +1 or -1. For example,

posts
   id title
1:  1   foo
2:  2   bar
3:  3   baz

post_votes
   id post_id value
1:  1       1     1
2:  2       1    -1
3:  3       1     1
4:  4       2     1

I've set up SQLAlchemy models for Post and PostVote as follows. (In reality, post_votes also includes a user_id, but I've excluded it here.)

models.py

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String(50), nullable=False, unique=True)
    votes = relationship("PostVotes", back_populates="post")
    
    
class PostVote(Base):
    __tablename__ = 'post_votes'

    id = Column(Integer, primary_key=True)
    post_id = Column(Integer, ForeignKey('posts.id'), nullable=False)
    value = Column(Integer)
    post = relationship("Post", back_populates="votes")

I've also defined a pydantic model for serializing a Post instance to JSON.

schemas.py

from typing import Optional
from pydantic import BaseModel

class Post(BaseModel):
    id: int
    title: str

    class Config:
        orm_mode = True

This works great. I can fetch a post by id with something like db_post = db.query(models.Post).where(models.Post.id == id).one() and serialize it to JSON using the pydantic Post schema.

...but now I want to include the total number of likes (+1s) and dislikes (-1s) in the resulting JSON. I can update my pydantic schema simply by adding attributes

likes: int
dislikes: int

but I can't figure out how to write a SQLAlchemy query to "play nicely" with this schema. The closest thing I can come up with is

# sum the likes and dislikes by post_id
stmt = db.query(
  models.PostVote.post_id,
  func.count(1).filter(models.PostVote.value == 1).label('likes'),
  func.count(1).filter(models.PostVote.value == -1).label('dislikes')
).group_by(models.PostVote.post_id).subquery()


# left join to posts
db_post = db.query(models.Post, stmt.c.likes, stmt.c.dislikes). \
  join(stmt, models.Post.id == stmt.c.post_id, isouter=True)

# filter by post_id
db_post = db_post.where(models.Post.id == 1).one()

This returns a tuple like (<db.models.Post object at 0x10577b850>, 2, 1). When using FastAPI, when I set my response_model as schemas.Post, this throws an error, presumably because pydantic is expecting likes and dislikes to be attributes of the Post model - not values in a tuple alongside it.

Upvotes: 2

Views: 1917

Answers (1)

Ben
Ben

Reputation: 21685

I solved this by discovering and implementing SQLAlchemy Hybrid Attributes.

In essence, I add an attribute called likes and another attribute called dislikes to my Post model without actually inserting these columns into the posts SQL table.

Here's what the addition of likes looks like.

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String(50), nullable=False, unique=True)
    votes = relationship("PostVotes", back_populates="post")

    @hybrid_property
    def likes(self):
        return sum(v.value == 1 for v in self.votes)

    @likes.expression
    def likes(cls):
        return select(func.count(1).filter(PostVote.value == 1)). \
            where(PostVote.problem_id == cls.id). \
            label('likes')

Now when I fetch a single Post instance

db_post = db.query(models.Post).where(models.Post.id == 1).one()

I get back a single Post instance with likes as an attribute

print(db_post.likes)  # 2

This then flows nicely into my pydantic model.

Performance?

One thing I'm still unsure about is the strain this puts on performance. There are times when I want to fetch a post but don't need to know the associated likes / dislikes count. I'm not sure if this attribute loads lazily or how to force it to do so.

Upvotes: 1

Related Questions