조회 시 빈 컬럼 GPT 자동 보완 가이드

0배경

테이블에 컬럼이 여러 차례 추가·수정되면서, 과거 데이터의 일부 컬럼이 빈 값('')으로 남는 경우가 생긴다 (예: 발음기호 ipa가 빈 definitive 등 8건). 일회성 배치 대신 조회될 때 자동으로 메꾸는 방식이 운영상 안전하고 누락이 없다. 이 문서는 그 패턴과, 다른 컬럼으로 확장하는 방법을 코드와 함께 정리한다.

1설계 원칙

• 조회 마지막 단계에 삽입 — 표를 내려주는 main_word_table. 로딩·조회버튼 모두 이 함수를 탄다.
• 빈 값이 있을 때만 호출 — 대상 컬럼이 빈 행이 있을 때만 GPT 1회(배치). 평소 호출 0.
• 영구 저장 + 재호출 없음 — 받은 값은 tb_word_entry에 UPDATE → 다음부터 호출 안 함.
• 기존 값 보존 — WHERE <col> IS NULL OR <col>='' 로 빈 값만 갱신(덮어쓰기 금지).
• 실패 안전 — GPT/DB 오류 시 조용히 건너뜀 → 화면 기존대로, 다음 조회 때 재시도.
• 컬럼명 화이트리스트 — 동적 SET {col} 에는 코드 설정(ENRICH_COLS 키)만 → 인젝션 불가.

2현재 배포 코드 (발음기호 ipa) LIVE

헬퍼 _fill_missing_ipa

def _fill_missing_ipa(request, words):
    """발음기호(ipa)가 빈 단어들을 GPT로 받아 tb_word_entry 에 저장하고 {word: ipa} 반환."""
    words = [w for w in dict.fromkeys(words) if w]   # 중복 제거 + 공백 제외
    if not words:
        return {}
    import openai
    from django.conf import settings as dj_settings
    result = {}
    try:
        prompt = (
            "다음 영어 단어들의 IPA 발음기호만 JSON으로 반환하세요.\n"
            '형식: {"words":[{"word":"...","ipa":"..."}]}\n'
            "슬래시(/)·대괄호([]) 없이 발음기호만. 모르면 빈 문자열.\n\n"
            f"단어: {', '.join(words)}"
        )
        client = openai.OpenAI(api_key=dj_settings.OPENAI_API_KEY)
        resp = client.chat.completions.create(
            model='gpt-4o-mini',
            messages=[{'role': 'user', 'content': prompt}],
            temperature=0.2, timeout=20,
        )
        txt = resp.choices[0].message.content or ''
        s, e = txt.find('{'), txt.rfind('}') + 1
        data = json.loads(txt[s:e]) if s != -1 and e > 0 else {}
        for it in data.get('words', []):
            w   = (it.get('word') or '').strip()
            ipa = (it.get('ipa') or '').strip().strip('/').strip('[]').strip()
            if w and ipa:
                result[w] = ipa
    except Exception:
        return {}
    if result:
        try:
            conn, cursor, _u = create_connection(request)
            for w, ipa in result.items():
                cursor.execute(
                    "UPDATE tb_word_entry SET ipa=%s "
                    "WHERE word=%s AND (ipa IS NULL OR ipa='')", (ipa, w))
            conn.commit()
            close_connection(conn, cursor)
        except Exception:
            pass
    return result

조회 함수 삽입 (main_word_table)

    existing_words = sql_statement.sql_dao(request, "sqls_main_word_table", "")

    # 조회 마지막 단계: ipa 빈 단어가 있으면 GPT로 채워 저장(이후 재호출 없음)
    _ipa_map = {}
    if isinstance(existing_words, (list, tuple)):
        _missing = [rec[0] for rec in existing_words if not (rec[1] or '').strip()]
        if _missing:
            _ipa_map = _fill_missing_ipa(request, _missing)

    rows = []; rows_cnt = 0; cur_no = 0
    for record in existing_words:
        cur_word, cur_ipa, cur_mean_en, cur_tag_text, cur_create_date, \
            cur_group_code, level, toeic_level, cur_pos_tag, cur_src_title = record
        if not (cur_ipa or '').strip() and cur_word in _ipa_map:
            cur_ipa = _ipa_map[cur_word]        # 방금 채운 발음기호로 표시
        cur_no += 1
        rows.append([cur_no, cur_word, _pos_abbr(cur_pos_tag), cur_ipa, cur_mean_en,
                     cur_tag_text, cur_create_date, cur_group_code, level, toeic_level,
                     cur_src_title or ''])
        rows_cnt += 1

rec[0]=단어, rec[1]=ipa. ipa 출처는 사전 tb_word_entry we (LEFT JOIN).

3일반화 — 여러 컬럼 보완 확장안

다른 컬럼(뜻·동의어·반의어 등)도 같은 방식으로 채우려면 _fill_missing_ipa 를 아래 설정 기반 헬퍼로 바꾼다. 이후 새 컬럼 보완 = 설정에 1줄 추가.

① 설정 + GPT 호출 + 저장

# tb_word_entry 에서 '비면' GPT로 보완할 컬럼.
#   DB컬럼 : (GPT 응답 JSON 필드명, 값 정리 함수)   ← 새 컬럼은 여기 1줄 추가(키=실제 컬럼명=화이트리스트)
ENRICH_COLS = {
    'ipa':        ('ipa',        lambda v: v.strip().strip('/').strip('[]').strip()),
    'meaning_kr': ('meaning_kr', lambda v: v.strip()),
    'meaning_en': ('meaning_en', lambda v: v.strip()),
    'synonym':    ('synonym',    lambda v: v.strip()),
    'antonym':    ('antonym',    lambda v: v.strip()),
}

def _gpt_enrich_words(words, cols):
    """words 에 대해 cols(ENRICH_COLS 키) 값을 GPT로 받아 {word: {col: value}} 반환."""
    words = [w for w in dict.fromkeys(words) if w]
    cols  = [c for c in cols if c in ENRICH_COLS]
    if not words or not cols:
        return {}
    import openai
    from django.conf import settings as dj_settings
    field_spec = ", ".join(f'"{ENRICH_COLS[c][0]}":"..."' for c in cols)
    prompt = (
        "다음 영어 단어들의 정보를 JSON으로만 반환하세요.\n"
        f'형식: {{"words":[{{"word":"...", {field_spec}}}]}}\n'
        "ipa 는 슬래시(/)·대괄호([]) 없이 발음기호만. 모르는 값은 빈 문자열.\n\n"
        f"단어: {', '.join(words)}"
    )
    out = {}
    try:
        client = openai.OpenAI(api_key=dj_settings.OPENAI_API_KEY)
        resp = client.chat.completions.create(
            model='gpt-4o-mini',
            messages=[{'role': 'user', 'content': prompt}],
            temperature=0.2, timeout=30,
        )
        txt = resp.choices[0].message.content or ''
        s, e = txt.find('{'), txt.rfind('}') + 1
        data = json.loads(txt[s:e]) if s != -1 and e > 0 else {}
        for it in data.get('words', []):
            w = (it.get('word') or '').strip()
            if not w:
                continue
            vals = {}
            for c in cols:
                field, clean = ENRICH_COLS[c]
                v = clean(str(it.get(field) or ''))
                if v:
                    vals[c] = v
            if vals:
                out[w] = vals
    except Exception:
        return {}
    return out

def _fill_missing_entry_fields(request, word_rows, cols):
    """word_rows: [(word, {col: 현재값, ...}), ...]. cols 중 '빈' 컬럼을 GPT로 받아
    tb_word_entry 에 저장하고, 실제 채운 값만 {word: {col: value}} 반환.
    빈 컬럼만 + DB에서도 여전히 빈 경우에만 UPDATE (기존 값 보존)."""
    cols = [c for c in cols if c in ENRICH_COLS]
    need = {}                       # word -> set(비어있는 컬럼)
    for word, cur in word_rows:
        miss = {c for c in cols if not (str(cur.get(c) or '')).strip()}
        if miss:
            need.setdefault(word, set()).update(miss)
    if not need:
        return {}
    filled = _gpt_enrich_words(list(need.keys()), cols)
    applied = {}
    if filled:
        try:
            conn, cursor, _u = create_connection(request)
            for word, miss in need.items():
                vals = filled.get(word) or {}
                for c in miss:
                    v = vals.get(c)
                    if not v:
                        continue
                    # c 는 ENRICH_COLS 화이트리스트 키 → 컬럼명 안전
                    cursor.execute(
                        f"UPDATE tb_word_entry SET {c}=%s "
                        f"WHERE word=%s AND ({c} IS NULL OR {c}='')", (v, word))
                    applied.setdefault(word, {})[c] = v
            conn.commit()
            close_connection(conn, cursor)
        except Exception:
            return {}
    return applied

② 조회 함수에서 사용

VIEW_FILL_COLS  = ('ipa',)          # 화면에 즉시 반영할 컬럼(표시용)
EXTRA_FILL_COLS = ()                # 표시는 안 해도 '이 김에' 채울 컬럼(예: ('synonym','antonym'))

    existing_words = sql_statement.sql_dao(request, "sqls_main_word_table", "")
    _ipa_map = {}
    if isinstance(existing_words, (list, tuple)):
        word_rows = [(rec[0], {'ipa': rec[1]}) for rec in existing_words]
        applied = _fill_missing_entry_fields(
            request, word_rows, tuple(VIEW_FILL_COLS) + tuple(EXTRA_FILL_COLS))
        _ipa_map = {w: v['ipa'] for w, v in applied.items() if v.get('ipa')}
    # 루프에서 ipa 치환 (2 와 동일)

③ 새 컬럼 보완 추가 절차

1. ENRICH_COLS에 1줄: 'examples_en': ('examples', lambda v: v.strip()),
2. 화면 표시면 VIEW_FILL_COLS + SELECT/rows 반영 / DB만이면 EXTRA_FILL_COLS
3. 배포 → 검증

4(선택) 전체 테이블 일괄 보완 스크립트

조회를 안 기다리고 한 번에 메꾸기. 같은 헬퍼 재사용.

# fill_missing_entry_cols.py  (프로젝트 루트, .venv 로 실행)
import os, django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'mdmproj1.settings'); django.setup()
from proj_sql_mapping import fn_connector
from app_word_work.pkg_views.view_word_work import ENRICH_COLS, _gpt_enrich_words

TARGET_COLS = ['ipa']          # 채울 컬럼
CHUNK = 40
conn = fn_connector(None); cur = conn.cursor()
cond = " OR ".join(f"({c} IS NULL OR {c}='')" for c in TARGET_COLS)
cur.execute(f"SELECT word FROM tb_word_entry WHERE status='Y' AND ({cond})")
words = [r[0] for r in cur.fetchall()]
print("대상:", len(words))
for i in range(0, len(words), CHUNK):
    batch  = words[i:i+CHUNK]
    filled = _gpt_enrich_words(batch, TARGET_COLS)
    for w, vals in filled.items():
        for c, v in vals.items():
            if c in TARGET_COLS and v:
                cur.execute(f"UPDATE tb_word_entry SET {c}=%s WHERE word=%s AND ({c} IS NULL OR {c}='')", (v, w))
    conn.commit(); print(f"{i+len(batch)}/{len(words)} 처리")
conn.close()

실행: cd /home/modam/pyDjangoDEV/mdmproj1 && sudo -u modam .venv/bin/python fill_missing_entry_cols.py

5주의 / 한계

• 분류 컬럼은 별도 — pos_tag, toeic_level, grade 는 성격이 달라(분류) 전용 스크립트가 있다(classify_*, reclassify_toeic.py). 이 보완 패턴에 넣지 말 것.
• 활용형 주의 — GPT는 표제어 기준. tb_word_entry는 보통 표제어라 문제 적음.
• 비용/지연 — 빈 값 있을 때만, 한 페이지 1회로 묶어 호출. 채우면 저장돼 재호출 없음. 전체 일괄은 CHUNK 분할.
• 덮어쓰기 금지 — 항상 WHERE <col> IS NULL OR <col>=''.

6배포 / 검증 / 롤백

P=/home/modam/pyDjangoDEV/mdmproj1; F=app_word_work/pkg_views/view_word_work.py
TS=$(ssh hetz02-server "date +%Y%m%d_%H%M%S")
ssh hetz02-server "cd $P && cp $F $F.bak_$TS"
scp <로컬수정본> hetz02-server:$P/$F
ssh hetz02-server "chown modam:modam $P/$F && cd $P && sudo -u modam .venv/bin/python manage.py check && touch wsgi.py mdmproj1/wsgi.py && echo DONE"

• 검증(렌더): Client 로 /app_word_work/main-wordtable/?source_type=TodayTop10&sel_level=A&source_status=C 조회 → 빈 컬럼이 채워져 내려오는지 + tb_word_entry 저장 확인.
• 롤백: cp $F.bak_<TS> $F 후 chown·리로드.