[FastAPI] SQLAlchemy 상세 - Delete (6-4)

 

1. SQL 방식

delete 함수 사용

from sqlalchemy import delete

stmt = delete(User).where(User.id == 1)
await db.execute(stmt)
await db.commit()

 

---

2. ORM 방식

ORM을 사용하는 경우, 먼저 객체를 조회한 후 삭제

stmt = select(User).where(User.id == 1)
result = await db.execute(stmt)
user = result.scalars().first()

if user:
    await db.delete(user)
    await db.commit()

 

---

3. ORM 관계 삭제

ORM 관계에서 하위 테이블의 데이터 삭제는 Cascading을 설정하여 자동으로 처리 가능

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    posts = relationship("Post", back_populates="user", cascade="all, delete")


class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    user_id = Column(Integer, ForeignKey("users.id"))
    
    user = relationship("User", back_populates="posts")

 

User 삭제 시, Post 자동 삭제

stmt = select(User).where(User.id == 1)
result = await db.execute(stmt)
user = result.scalars().first()

if user:
    await db.delete(user)  # 관련 Post도 삭제됨
    await db.commit()

---

4. FastAPI 예시

@app.detete 사용

@app.delete("/users/{user_id}")
async def delete_user(user_id: int, db: AsyncSession = Depends(get_db)):
    stmt = delete(User).where(User.id == user_id)
    result = await db.execute(stmt)
    await db.commit()

    if result.rowcount == 0:
        raise HTTPException(status_code=404, detail="User not found")

    return {"message": "User deleted successfully"}