I am trying to use the SQLAlchemy-ImageAttach with Pydantic and FastAPI, but not having much success.

I have never used SQLAlchemy-ImageAttach before, and I am sure I am using it wrong. I cannot seem to save any images, but I can get all non-image aspects working. (I have successfully integrated SQLAlchemy + Pydantic + FastAPI, and there are also great examples to help.)

I am trying to make a DB-backed website where users can create quiz questions and embed images along with the questions, too. The code below highlights the portions related to the quiz questions + images.

My Pydantic models/schemas:

from typing import List, Optional

from pydantic import BaseModel


class QuestionPictureBase(BaseModel):
    pass


class QuestionPicture(QuestionPictureBase):
    id: int
    question_id: int

    class Config:
        orm_mode = True


class QuestionBase(BaseModel):
    header: str
    details: str


class QuestionCreate(QuestionBase):
    pass


class Question(QuestionBase):
    id: int
    creator_id: int
    pictures: List[QuestionPicture] = []

    class Config:
        orm_mode = True

The SQLAlchemy models:

from sqlalchemy import create_engine, Boolean, Column, ForeignKey, Integer, String, Text
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_imageattach.entity import Image, image_attachment

SQLALCHEMY_DATABASE_URL = "sqlite:///sqlalch.db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()


class Question(Base):
    __tablename__ = "questions"

    id = Column(Integer, primary_key=True, index=True)
    header = Column(String, index=True)
    details = Column(String, index=True)
    creator_id = Column(Integer, ForeignKey("users.id"))
*** pictures = image_attachment("QuestionPicture", uselist=True) ***
    creator = relationship("User", back_populates="questions")


class QuestionPicture(Base, Image):
    """Model for pictures associated with the question."""

    __tablename__ = "question_pictures"

    id = Column(Integer, primary_key=True)
    # Not sure if the question ID should also be a unique identifier
    # question_id = Column(Integer, ForeignKey("questions.id"), primary_key=True)
    question_id = Column(Integer, ForeignKey("questions.id"))
    question = relationship("Question", back_populates="pictures")

The highlighted line

*** pictures = image_attachment("QuestionPicture", uselist=True) ***

is where I think I am doing things wrong. But I am not quite sure what I should be doing.

I just don't really get how the image_attachment function call should be used. It just seems to work "automagically" in the SQLAlchemy-ImageAttach docs, and I do not understand it.

Does anyone have any working examples using SQLAlchemy-ImageAttach + Pydantic (+ FastAPI). It's the interaction with Pydantic that mostly has me confused.

Update

There was a request for more detail, and there is simply too much to put on a question. So instead, I created a git branch containing all of the backend, but only the backend, called temp, and pushed it to a public GitHub repo.

Solution

@r-m-n's solution below got me a good part of the way there, but my main problem was that it was unclear to me what my pictures database needed to contain.

After looking at some error output and reading the source code, I found the needed structure here and adjusted my alembic (db versioning) code as follows:

def upgrade():
    op.create_table(
        "question_pictures",
        sa.Column("id", sa.Integer, primary_key=True, index=True),
        sa.Column(
            "question_id", sa.Integer, sa.ForeignKey("questions.id"), nullable=False
        ),
        sa.Column("width", sa.Integer, nullable=False),
        sa.Column("height", sa.Integer, nullable=False),
        #: (:class:`str`) The mimetype of the image
        #: e.g. ``'image/jpeg'``, ``'image/png'``.
        sa.Column("mimetype", sa.String(255), nullable=False),
        sa.Column("original", sa.Boolean, nullable=False, default=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
    )

Of the list above, it is width, height, original, mimetype, and created_at that were missing and which I was unaware I needed to add.

(sa above is short for sqlalchemy and op is short for alembic.op.)