[Python] SQLAlchemy Error: InvalidRequestError - Can't operate on closed transaction inside...

I'm encountering an issue while working with SQLAlchemy in a FastAPI project. I've set up a route that's supposed to add items to a database using a context manager and nested transactions. If a single item is failed to be added (due to constraints or any reason) it should not be included in the commit. However the remaining items, added both before or after, should be included.

When using nested transactions, I would expect to be able to keep track of my failed and succesful additions. However, I keep running into the following error:

sqlalchemy.exc.InvalidRequestError: Can't operate on a closed transaction inside a context manager.

I've provided the relevant code below:

router = APIRouter()

@lru_cache()
def get_session_maker() -> sessionmaker:
# create a reusable factory for new AsyncSession instances
engine = create_engine(SQLALCHEMY_DATABASE_URI, echo=True)
return sessionmaker(engine)

def get_session() -> Generator[Session, None, None]:
cached_sessionmaker = get_session_maker()
with cached_sessionmaker.begin() as session:
yield session

@router.post("/items")
def add_items(
session: Session = Depends(get_database.get_session),
) -> Dict[str, Any]:

request_inputs = [
RequestInput(name="chair", used_for="sitting"),
RequestInput(name="table", used_for="dining"),
RequestInput(name="tv", used_for="watching"),
]

uploaded_items = []
failed_items = []
for request_input in request_inputs:
try:
with session.begin_nested():
item= Item(
**request_input.dict()
)
session.add(item)
session.refresh(item)

uploaded_items += 1

except IntegrityError as e:
# Handle any integrity constraint violations here
session.rollback()
failed_items += 1
except Exception as e:
# Handle other exceptions
session.rollback()
failed_items += 1

session.commit()

return {
"uploaded": uploaded_items,
"failed": failed_items,
}


It is obviously caused by my session to be closed prematurely, however I cannot figure out where I am closing the transaction to early, whilst trying to add all non failed items to my db. Can someone please help me understand why I'm encountering this error and how to fix it?

Thank you in advance for your assistance.

I tried to use session.begin_nested() to keep track of the status of my transaction, however it seems to close somewhere. if not used the begin_nested(), I only commit the items before the failed instance. All items afterwards are excluded.

Continue reading...