파이썬 SQLAlchemy Core PostgreSQL JSON ARRAY Enum MySQL ON DUPLICATE KEY 완벽 가이드

🗄️ PostgreSQL JSON 다루기

PostgreSQL은 JSON과 JSONB라는 강력한 데이터 타입을 지원합니다.
JSON 타입은 원본 문자열 그대로 저장하는 반면, JSONB는 바이너리 형식으로 저장되어 인덱싱과 검색 성능이 뛰어납니다.
SQLAlchemy Core에서는 sqlalchemy.dialects.postgresql.JSON 또는 JSONB를 사용하여 컬럼을 정의할 수 있습니다.
이를 통해 반정형 데이터를 테이블에 효율적으로 저장하고, 동적 스키마를 가진 데이터를 쉽게 처리할 수 있습니다.

🔎 JSON 컬럼 정의와 활용

예를 들어 사용자 프로필에 취향이나 환경설정 같은 다양한 데이터를 JSON 컬럼으로 저장할 수 있습니다.
SQLAlchemy Core에서는 컬럼을 정의할 때 PostgreSQL 전용 Dialect를 불러와 사용하면 됩니다.

from sqlalchemy import Table, Column, Integer, MetaData
from sqlalchemy.dialects.postgresql import JSONB

metadata = MetaData()

users = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("profile", JSONB)  # JSONB 타입 정의
)

이렇게 정의된 컬럼에는 딕셔너리 형태의 데이터를 직접 삽입할 수 있으며, PostgreSQL의 JSON 함수와 연계해 원하는 키 값만 빠르게 조회할 수 있습니다.

⚡ JSON 함수와 연산자 활용

PostgreSQL은 JSON 데이터를 다룰 때 다양한 연산자를 제공합니다.
예를 들어 -> 연산자는 JSON 객체에서 키를 조회하고, ->> 연산자는 텍스트 값으로 추출합니다.
SQLAlchemy Core에서는 이 연산자들을 직접 쓸 수 있도록 매핑을 지원합니다.

from sqlalchemy import select

# profile 컬럼의 "nickname" 키 값만 조회
stmt = select(users.c.profile["nickname"])

📂 PostgreSQL ARRAY 활용법

PostgreSQL은 배열(Array) 타입을 지원하여 하나의 컬럼에 여러 값을 저장할 수 있습니다.
예를 들어 사용자 취미, 태그, 여러 개의 이메일 주소와 같이 리스트 형태의 데이터를 테이블 컬럼에 직접 저장할 수 있죠.
SQLAlchemy Core에서는 sqlalchemy.dialects.postgresql.ARRAY를 통해 배열 컬럼을 정의하고 활용할 수 있습니다.

📝 ARRAY 컬럼 정의

예를 들어 사용자가 가진 태그를 배열로 저장한다고 할 때, 다음과 같이 정의할 수 있습니다.

from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy.dialects.postgresql import ARRAY

metadata = MetaData()

users = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("tags", ARRAY(String))  # 문자열 배열 컬럼
)

이렇게 정의하면 파이썬의 리스트 데이터를 그대로 삽입할 수 있으며, PostgreSQL은 이를 배열 타입으로 저장합니다.

🔍 배열 함수와 연산자

PostgreSQL은 배열을 다루기 위한 다양한 함수와 연산자를 제공합니다.
예를 들어 ANY와 ALL 연산자를 사용해 배열 내부에 특정 값이 포함되어 있는지 쉽게 확인할 수 있습니다.
SQLAlchemy Core에서는 이 연산자들도 매핑하여 사용할 수 있습니다.

from sqlalchemy import select

# "python"이라는 태그를 가진 사용자만 조회
stmt = select(users).where("python" == any_(users.c.tags))

🔢 PostgreSQL Enum 타입 매핑

PostgreSQL은 Enum 타입을 기본적으로 지원하여 값의 범위를 미리 정의하고 컬럼에 강제할 수 있습니다.
SQLAlchemy Core는 이러한 PostgreSQL 전용 Enum을 sqlalchemy.dialects.postgresql.ENUM 클래스로 매핑할 수 있습니다.
이 방식은 데이터 무결성을 유지하고, 특정 값만 허용해야 하는 경우 유용합니다.

📌 Enum 타입 정의

예를 들어 사용자의 역할(role)을 “admin”, “editor”, “viewer”로 제한한다고 하면 다음과 같이 정의할 수 있습니다.

from sqlalchemy import Table, Column, Integer, MetaData
from sqlalchemy.dialects.postgresql import ENUM

metadata = MetaData()

roles = ("admin", "editor", "viewer")

users = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("role", ENUM(*roles, name="role_enum"))
)

위 예제에서는 “role_enum”이라는 이름으로 Enum 타입을 생성하고, 컬럼에 적용했습니다.
이후 해당 컬럼에는 지정한 값들만 입력이 가능해집니다.

⚠️ Enum 관리 시 유의할 점

Enum은 데이터 무결성을 보장하지만, 나중에 값의 추가나 변경이 필요할 때 마이그레이션이 까다로울 수 있습니다.
따라서 자주 변하는 데이터라면 별도 테이블을 두고 참조하는 방식이 더 적합할 수 있습니다.

🔁 MySQL ON DUPLICATE KEY 구문

MySQL에서는 ON DUPLICATE KEY UPDATE 구문을 활용하여 중복 키가 발생했을 때 기존 데이터를 업데이트할 수 있습니다.
이는 데이터 삽입과 동시에 충돌을 방지하는 매우 실용적인 기능으로, “업서트(Upsert)” 전략이라고도 불립니다.
SQLAlchemy Core는 MySQL Dialect에서 이 구문을 매핑할 수 있도록 지원합니다.

📝 기본 사용법

예를 들어 사용자 이메일이 기본 키이거나 고유 제약 조건을 가진 경우, 같은 이메일로 데이터를 삽입하려 하면 오류가 발생합니다.
하지만 ON DUPLICATE KEY UPDATE 구문을 사용하면 기존 데이터를 업데이트하여 충돌을 방지할 수 있습니다.

from sqlalchemy.dialects.mysql import insert

stmt = insert(users).values(
    id=1, email="test@example.com", name="홍길동"
)

on_duplicate_stmt = stmt.on_duplicate_key_update(
    name=stmt.inserted.name
)

위 예제에서 새로운 사용자가 추가될 때 동일한 email 값이 이미 존재하면, 기존 레코드의 name 컬럼만 업데이트됩니다.

⚡ 활용 사례

• 📌로그 수집 시 중복 데이터를 허용하지 않고 최신 데이터로 갱신
• 📌상품 정보 업데이트 시 기존 데이터 보존 + 새로운 가격만 반영
• 📌사용자 접속 기록을 최신 상태로 유지

⚡ SQLAlchemy Core로 구현하기

지금까지 PostgreSQL의 JSON, ARRAY, Enum과 MySQL의 ON DUPLICATE KEY와 같은 DB 특화 기능을 살펴봤습니다.
이제 SQLAlchemy Core를 활용해 이러한 기능들을 파이썬 코드에서 유연하게 구현하는 방법을 정리해 보겠습니다.
Core 모드는 ORM보다 더 SQL 친화적이며, 데이터베이스 고유 기능을 직접 제어할 수 있다는 점이 가장 큰 장점입니다.

🛠️ PostgreSQL 특화 기능 예제

PostgreSQL의 다양한 자료형(JSON, ARRAY, Enum)은 다음과 같이 SQLAlchemy Core에서 정의할 수 있습니다.

from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy.dialects.postgresql import JSONB, ARRAY, ENUM

metadata = MetaData()

roles = ("admin", "editor", "viewer")

users = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("profile", JSONB),         # JSONB 컬럼
    Column("tags", ARRAY(String)),    # ARRAY 컬럼
    Column("role", ENUM(*roles, name="role_enum"))  # Enum 컬럼
)

이렇게 정의하면 PostgreSQL 고유 기능을 파이썬 코드에서 직접 활용할 수 있고, ORM보다 더 세밀한 쿼리 최적화가 가능합니다.

🔄 MySQL UPSERT 구현

MySQL에서는 ON DUPLICATE KEY UPDATE 구문을 SQLAlchemy Core에서 다음과 같이 구현할 수 있습니다.

from sqlalchemy.dialects.mysql import insert

stmt = insert(users).values(
    id=1, email="test@example.com", name="홍길동"
)

stmt = stmt.on_duplicate_key_update(
    name=stmt.inserted.name
)

이렇게 하면 데이터 삽입 시 충돌이 발생할 경우 자동으로 업데이트가 이루어져, 데이터 무결성을 유지하면서 코드 복잡도를 줄일 수 있습니다.

💡 실무 적용 팁

• 🗂️JSONB 컬럼에는 GIN 인덱스를 적용해 빠른 검색 성능 확보
• 📑ARRAY는 데이터가 자주 변할 경우 별도 테이블로 정규화 고려
• 🔒Enum 값은 초기 설계 시 신중히 정의하여 마이그레이션 비용 최소화
• ⚡MySQL 업서트는 대량 데이터 처리 시 성능 영향이 있으므로 필요한 곳에만 적용

❓ 자주 묻는 질문 (FAQ)