sqlalchemy Object with OrderingList - Session.commit() method fails

Question

Model that has attribute in type of sqlalchemy OrderingList fails on Database Session Commit step (as Database I am using PostgreSQL 13):

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relationship
from database.base_class import Base
from sqlalchemy.ext.orderinglist import ordering_list


class ContentType(Enum):
    SCENE_HEADING = 'scene_heading'
    ACTION = 'action'
    CHARACTER = 'character'
    PARENTHETICAL = 'parenthetical'
    DIALOGUE = 'dialogue'
    SHOT = 'shot'
    TRANSITION = 'transition'
    TEXT = 'text'


class EditorElement(Base):
    """
    Class that represents the Table of Editor Elements.
    """
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    content = Column(String, nullable=True)
    content_type = Column(ContentType, nullable=True)

    screenplay_id = Column(Integer, ForeignKey('screenplays.id', ondelete="CASCADE"), nullable=False)

    screenplay = relationship("Screenplay", back_populates="elements")


class Screenplay(Base):
    """
    Class that represents the screenplay table in the database.
    """
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String, index=True, nullable=False)
    elements = relationship("EditorElement", back_populates="screenplay", order_by="EditorElement.content_type",
                            collection_class=ordering_list('content_type'))

I am using FastAPI framework and in the CRUD file where the Screenplay is created, I have the following class:

class CRUDScreenplay(CRUDBase[models.Screenplay, schemas.ScreenplayBase, schemas.ScreenplayBase]):

    @staticmethod
    def create(db: Session, *, obj_in: schemas.ScreenplayCreate) -> models.Screenplay:
        db_obj = models.Screenplay()
        db_obj.name = obj_in['name']

        for element in obj_in.get('elements'):
            e = models.EditorElement(
                content=element.content,
                content_type=element.content_type,
                screenplay_id=db_obj.id
            )
            db_obj.elements.append(e)

        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj

Using the PyCharm debugger I was able to check when the error occurs, it seems to appear after Session.commit(). Here is the object elements before the commit() method

And here is the elements object after the commit() method.

And the error in the console:

INFO:     127.0.0.1:52649 - "POST /api/v1/screenplays/ HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 1649, in _object_value_for_elem
    return self._object_lookup[elem]
KeyError: 'scene_heading'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\uvicorn\protocols\http\httptools_impl.py", line 372, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\fastapi\applications.py", line 261, in __call__
    await super().__call__(scope, receive, send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__
    raise exc
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\middleware\cors.py", line 84, in __call__
    await self.app(scope, receive, send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\exceptions.py", line 82, in __call__
    raise exc
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\fastapi\middleware\asyncexitstack.py", line 21, in __call__
    raise e
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\fastapi\middleware\asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\routing.py", line 656, in __call__
    await route.handle(scope, receive, send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\routing.py", line 259, in handle
    await self.app(scope, receive, send)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\routing.py", line 61, in app
    response = await func(request)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\fastapi\routing.py", line 235, in app
    response_data = await serialize_response(
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\fastapi\routing.py", line 130, in serialize_response
    value, errors_ = await run_in_threadpool(
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\starlette\concurrency.py", line 39, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\anyio\to_thread.py", line 28, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(func, *args, cancellable=cancellable,
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\anyio\_backends\_asyncio.py", line 818, in run_sync_in_worker_thread
    return await future
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\anyio\_backends\_asyncio.py", line 754, in run
    result = context.run(func, *args)
  File "pydantic\fields.py", line 854, in pydantic.fields.ModelField.validate
  File "pydantic\fields.py", line 1071, in pydantic.fields.ModelField._validate_singleton
  File "pydantic\fields.py", line 1118, in pydantic.fields.ModelField._apply_validators
  File "pydantic\class_validators.py", line 313, in pydantic.class_validators._generic_validator_basic.lambda12
  File "pydantic\main.py", line 678, in pydantic.main.BaseModel.validate
  File "pydantic\main.py", line 562, in pydantic.main.BaseModel.from_orm
  File "pydantic\main.py", line 1001, in pydantic.main.validate_model
  File "pydantic\utils.py", line 409, in pydantic.utils.GetterDict.get
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\orm\attributes.py", line 481, in __get__
    return self.impl.get(state, dict_)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\orm\attributes.py", line 941, in get
    value = self._fire_loader_callables(state, key, passive)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\orm\attributes.py", line 977, in _fire_loader_callables
    return self.callable_(state, passive)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\orm\strategies.py", line 911, in _load_for_state
    return self._emit_lazyload(
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\orm\strategies.py", line 1051, in _emit_lazyload
    result = result.unique().scalars().all()
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\engine\result.py", line 1371, in all
    return self._allrows()
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\engine\result.py", line 401, in _allrows
    rows = self._fetchall_impl()
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\engine\result.py", line 1284, in _fetchall_impl
    return self._real_result._fetchall_impl()
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\engine\result.py", line 1696, in _fetchall_impl
    return list(self.iterator)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\orm\loading.py", line 147, in chunks
    fetch = cursor._raw_all_rows()
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\engine\result.py", line 393, in _raw_all_rows
    return [make_row(row) for row in rows]
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\engine\result.py", line 393, in <listcomp>
    return [make_row(row) for row in rows]
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 1768, in process
    value = self._object_value_for_elem(value)
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 1651, in _object_value_for_elem
    util.raise_(
  File "C:\Users\userh\Playground\screenplay-writer\backend\.venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
LookupError: 'scene_heading' is not among the defined enum values. Enum name: None. Possible values: None

However, the data was successfully created in the database, but FastAPI does not return anything due to this error.

There are some related issues in GitHub but I am was not able to figure out what is going on there:

1. Issue 1
2. Issue 2
3. Issue 3
4. Issue 4

UPDATE

Alembic (which is often used with FastAPI) or SLQAlchemy created the PostgreSQL ENUM Type in Uppercase.

I changed the definition of ContentType(enum.Enum) as it was in the example of the official documentation LINK. Thanks to @fchancel for pointing on that:

class ContentType(str, enum.Enum):
    HEADING = 'HEADING'
    ACTION = 'ACTION'
    CHARACTER = 'CHARACTER'
    PARENTHETICAL = 'PARENTHETICAL'
    DIALOGUE = 'DIALOGUE'
    SHOT = 'SHOT'
    TRANSITION = 'TRANSITION'
    TEXT = 'TEXT'

So, now my code works

Answer 1

It seems that when creating your elements, the content_type section is a problem, since it can't find heading_scene. To understand this, we need to see how you call your function and what obj_in contains.

However, it's possible that the source of the error is just your class ContentType(Enum) which to work properly should be class ContentType(str, Enum)