251030 [ Day 77 ] - SQL (6)

시작하며

오늘도 어제에 이어 FastAPI를 사용해서 MySQL과 연동하는 법을 공부했다.

CRUD in FastAPI

Pydantic 모델

• 요청 데이터의 구저를 정의하고, 자동으로 유효성 검사를 해주는 도구
• 데이터 구조를 정의하는 클래스로, 주로 요청 데이터의 구조와 타입을 명확히 지정할 때 사용

from pydantic import BaseModel

class UserCreate(BaseModel):
    username: str
    password: str
    name: str
    email: str

class UserUpdate(BaseModel):
    name: str
    email: str

READ

• 튜플의 리스트 → 딕셔너리의 리스트(RESTful API 표준)로 변환
  • 키로 값에 접근 가능
  • datetime → string으로 변환하여 직렬화

from fastapi import APIRouter, HTTPException
from app.db import get_connection
from app.schemas.post import PostCreate, PostUpdate

# 라우터 객체 생성
router = APIRouter()

# 최신순으로 불러오기
@router.get("/posts")
def get_posts():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM posts ORDER BY created_at DESC")
    result = cursor.fetchall()
    conn.close()
    return result

# 특정 ID로 불러오기
@router.get("/posts/{post_id}")
def get_post(post_id: int):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM posts WHERE id = %s", (post_id,))
    result = cursor.fetchone()
    conn.close()
    return result

CREATE

• cursor.lastrowid : 가장 최근에 INSERT된 행의 AUTO INCREMENT 값을 자동으로 저장하는 속성

@router.post("/posts")
def create_post(post: PostCreate):
    try:
        conn = get_connection()
        cursor = conn.cursor()
        sql = "INSERT INTO posts (author, content) VALUES (%s, %s)"
        cursor.execute(sql, (post.author, post.content))
        new_post_id = cursor.lastrowid # 방금 생성된 ID 가져오기
        conn.commit()

        # 방금 생성된 데이터 조회해서 반환
        cursor.execute("SELECT * FROM posts WHERE id = %s", (new_post_id,))
        new_post = cursor.fetchone()
        return new_post
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

UPDATE

• cursor.rowcount : 방금 실행한 SQL 쿼리로 영행을 받은 행의 개수를 알려주는 속성

@router.put("/posts/{post_id}")
def update_post(post_id: int, post:PostUpdate):
    try:
        conn = get_connection()
        cursor = conn.cursor()
        sql = "UPDATE posts SET content = %s WHERE id = %s"
        cursor.execute(sql, (post.content, post_id))

        if cursor.rowcount == 0:
            raise HTTPException(status_code=400, detail="해당 글이 없습니다.")
        
        # 수정사항 확인
        cursor.execute("SELECT * FROM posts WHERE id = %s", (post_id,))
        updated_post = cursor.fetchone()
        return updated_post
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

DELETE

@router.delete("/posts/{post_id}")
def delete_post(post_id: int):
    try:
        conn = get_connection()
        cursor = conn.cursor()
        sql = "DELETE FROM posts WHERE id = %s"
        cursor.execute(sql, (post_id,))
        conn.commit()

        if cursor.rowcount == 0:
            raise HTTPException(status_code=400, detail="해당 글이 없습니다.")
        
        return {"message": "글 삭제 완료"}
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

ORM

ORM(Object-Relational Mapping)

• 클래스를 SQL 테이블처럼 다룰 수 있게 해주는 도구
  • 객체와 관계형 데이터베이스를 자동으로 연결
  • SQL문을 직접 쓰지 않고, 코드로 데이터를 추가/조회/수정/삭제할 수 있음

ORM 장점

• SQL을 몰라도 DB 다룰 수 있음
• 코드를 객체지향적으로 유지 가능
• 보안에 유리(SQL Injection 방지)
• 유지보수가 쉬움(테이블 구조 바뀌면 클래스만 수정)

SQLAlchemy

• Python에서 가장 널리 사용되는 ORM 라이브러리
  • 파이썬 객체로 데이터베이스 테이블을 다룰 수 있게 해주는 도구

설치

• pip
  • pip install sqlalchemy
• conda
  • conda install conda-forge::sqlalchemy
    
    

마치며

내일이면 SQL에 대한 공부도 끝이 나고 다음 주부터는 아두이노에 대해서 공부할 것 같다. SQL도 데이터 분석에 있어서 중요한 내용이니까 이후에도 조금씩 공부해봐야겠다.