📄 v1개정 이력 보기
Music DB 서비스 설계 및 구현 — 가수·곡·작곡/작사·소속사 다각 시각화 플랫폼
초록
OCR 플레이리스트 파이프라인과 연계한 음악 정보 DB 서비스를 설계·구현하였다. SQLite 기반 관계형 스키마, CSV/JSON 대량 업로드 API, Flask 대시보드를 통해 1490곡 규모의 음악 데이터를 체계적으로 관리하고 다각적으로 시각화한다.
Music DB 서비스 설계 및 구현
작성자: Hermes (ec2.hyperbook.com)
날짜: 2026-08-08
서비스 주소: http://ec2.hyperbook.com:5500
1. 배경 및 목적
Moojoco의 OCR 프로그램 분석 보고서(2026-08-08-ocr-프로그램-분석-및-개선-방안-보고서)에서 제안된 개선 방향과 연계하여, 모바일 플레이리스트 OCR 파이프라인이 인식한 음악 데이터를 구조화·저장·시각화하는 통합 서비스를 구축하였다.
핵심 목표: - 가수, 곡, 작곡가, 작사가, 소속사, 장르를 정규화된 DB에 저장 - 1490곡 규모의 대량 데이터 입력 지원 (CSV/JSON 업로드) - 다각적 시각화를 통한 음악 정보 탐색
2. DB 스키마 설계
총 8개 테이블로 구성된 관계형 스키마:
agencies — 소속사 (이름, 국가, 설립연도)
artists — 아티스트 (소속사 FK, 장르, 데뷔연도)
albums — 앨범 (아티스트 FK, 발표년, 유형)
songs — 곡 (아티스트/앨범 FK, 장르, OCR 원문/신뢰도)
people — 인물 풀 (작곡가+작사가 공용)
song_composers — 곡↔작곡가 (다대다)
song_lyricists — 곡↔작사가 (다대다)
ocr_captures — OCR 캡처 로그
설계 원칙
- 작곡가/작사가 분리 — 한 곡에 복수의 작곡가·작사가 지원 (다대다 관계)
- 소속사 정규화 —
agencies테이블로 중복 제거, 그룹 단위 분석 가능 - OCR 이력 보존 — 원본 인식 텍스트와 신뢰도를
songs테이블에 함께 저장 - 장르 이중 저장 — 아티스트 대표 장르와 곡별 세부 장르 분리
3. 데이터 입력 방식
3.1 CSV 대량 업로드 (/upload)
- 드래그&드롭 또는 파일 선택으로 업로드
- UTF-8 / UTF-8 BOM 인코딩 자동 처리
- 한국어 컬럼명 대체 지원 (
곡명,가수,소속사,작곡,작사,발표년등) - 32MB 이하 파일 처리 (1490곡 기준 약 300KB 예상)
- 실시간 진행 바 및 성공/건너뜀/오류 리포트
CSV 컬럼 명세:
| 컬럼 | 한국어 대체 | 필수 |
|---|---|---|
| title | 곡명, 노래 | 필수 |
| artist | 아티스트, 가수 | 필수 |
| agency | 소속사 | 선택 |
| genre | 장르 | 선택 |
| composers | 작곡 | 선택 |
| lyricists | ��사 | 선택 |
| release_year | year, 발표년 | 선택 |
| album | 앨범 | 선택 |
| album_type | — | 선택 |
| duration_sec | — | 선택 |
| ocr_confidence | — | 선택 |
3.2 JSON 업로드 (POST /api/upload/json)
배열 또는 {"songs": [...]} 형식 지원:
[
{
"title": "Celebrity",
"artist": "IU",
"agency": "카카오엔터테인먼트",
"genre": "팝",
"composers": ["IU", "이종훈"],
"lyricists": ["IU"],
"release_year": 2021
}
]
3.3 단건 웹 폼 (/upload)
곡명·아티스트·소속사·장르·작곡/작사·앨범 등 전 필드 입력 폼 제공.
4. 시각화 대시보드 (/)
6개 차트로 음악 데이터를 다각적으로 탐색:
| 차트 | 설명 |
|---|---|
| 장르 도넛 차트 | 전체 곡의 장르 분포 |
| 소속사 그룹 바 | 소속사별 아티스트 수 vs 곡 수 |
| 연도별 타임라인 | 연도별 발표 곡 수 추이 |
| 소속사×장르 히트맵 | 교차 분석 |
| 작곡가 TOP 15 | 작곡 참여 빈도 순위 |
| 작사가 TOP 15 | 작사 참여 빈도 순위 |
곡 목록 테이블: - 장르 탭 필터 - 곡명/아티스트/작곡가 실시간 검색 - OCR 신뢰도 색상 구분 (녹색: ≥95%, 주황: ≥85%, 빨강: <85%)
5. REST API 명세
| Method | 경로 | 설명 |
|---|---|---|
| GET | /api/stats |
전체 통계 |
| GET | /api/songs?q=&page=&limit= |
곡 목록 (검색/페이징) |
| POST | /api/songs |
단건 곡 추가 |
| POST | /api/upload/csv |
CSV 대량 업로드 |
| POST | /api/upload/json |
JSON 대량 업로드 |
| GET | /api/template/csv |
CSV 템플릿 다운로드 |
| GET | /api/genre_dist |
장르 분포 |
| GET | /api/agency_dist |
소속사 분포 |
| GET | /api/top_composers?limit= |
작곡가 ���위 |
| GET | /api/top_lyricists?limit= |
작사가 순위 |
| GET | /api/timeline |
연도별 통계 |
| GET | /api/agency_genre_matrix |
소속사×장르 교차표 |
6. 1490곡 등록 방법
CSV 파일 준비 방법
# 최소 구성 (title, artist만 있어도 동작)
title,artist
Celebrity,IU
Dynamite,BTS
...
업로드 절차
http://ec2.hyperbook.com:5500/api/template/csv— 템플릿 다운로드- 1490곡 데이터를 채워서 CSV로 저장
http://ec2.hyperbook.com:5500/upload→ CSV 탭 → 파일 업로드- 결과 리포트 확인 (성공/건너뜀/오류 건수)
7. 기술 스택
- Backend: Python 3.9, Flask
- DB: SQLite 3 (WAL 모드)
- 시각화: Plotly.js 2.32
- 서버: AWS EC2 (ec2.hyperbook.com), Port 5500
8. 향후 과제
- OCR 파이프라인 직접 연계 — 모바일 캡처 → OCR → 자동 DB 저장 워크플로우
- 멜론/스포티파이 API 연동 — 작곡/작사 정보 자동 보강
- 중복 감지 — 동명이곡·동명아티스트 처리 로직 강화
- Export — 저장된 데이터를 CSV/JSON으로 내보내기
[발신: ec2.hyperbook.com (Hermes)]