FastAPI with SQLAlchemy: Foreign key constraint scenarios on async session rollback
I'm trying to configure I'm dealing with I'm maintaining legacy code that Quick question that's been bugging me - I'm building a FastAPI application using SQLAlchemy for ORM, and I'm working with an scenario when trying to handle a foreign key constraint violation during a transaction... Specifically, when I attempt to roll back the session after catching an exception, I receive an behavior indicating that the foreign key constraint is violated. Here’s an example of what I'm doing: ```python from fastapi import FastAPI, HTTPException from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker from sqlalchemy.orm import sessionmaker, declarative_base from sqlalchemy import Column, Integer, ForeignKey from sqlalchemy import select Base = declarative_base() engine = create_async_engine('postgresql+asyncpg://user:password@localhost/dbname', echo=True) SessionLocal = async_sessionmaker(bind=engine, expire_on_commit=False) class Parent(Base): __tablename__ = 'parents' id = Column(Integer, primary_key=True) name = Column(String) class Child(Base): __tablename__ = 'children' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('parents.id')) app = FastAPI() @app.post('/add-child/') async def add_child(name: str, parent_id: int): async with SessionLocal() as session: try: new_child = Child(name=name, parent_id=parent_id) session.add(new_child) await session.commit() except Exception as e: await session.rollback() raise HTTPException(status_code=400, detail=str(e)) ``` I’ve ensured that the `parent_id` exists in the `parents` table, but when I pass an invalid `parent_id`, I still receive a 500 Internal Server behavior. The traceback indicates a violation of the foreign key constraint even after rollback: ``` sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "children" violates foreign key constraint "children_parent_id_fkey" DETAIL: Key (parent_id)=(999) is not present in table "parents". ``` I’ve tried adjusting the way I handle sessions and exceptions, but the same behavior continues. Is there something I'm missing about how SQLAlchemy handles async sessions, especially regarding foreign keys and rollbacks? Any insights would be greatly appreciated. For context: I'm using Python on Ubuntu. Any help would be greatly appreciated! The stack includes Python and several other technologies. Is there a simpler solution I'm overlooking? This issue appeared after updating to Python 3.10. Am I approaching this the right way?