개발팀_RPA_및_자동화

개요¶

기 수집된 권리확인 엑셀 5개 파일(경찰청 계열 보도자료 게시판)에서 G열 URL이 게시물 제목과 전부 불일치하는 문제가 확인되어, 5개 사이트 게시판을 전건 재크롤링하고 제목·날짜 매칭으로 실제 게시물 URL을 찾아 재작성한 작업. 기존 링크(수식)는 삭제하고 새 URL을 텍스트 + 활성 하이퍼링크로 교체했다.

• 작업일: 2026-08-04
• 대상 폴더: D:\아이티앤\사이트수집\● 사이트 장애 및 재크롤링 요청_260804
• 원본 백업: 동일 폴더 백업_원본\, 미매칭 목록: URL재작성_결과보고.txt

---

기존 URL이 잘못된 원인¶

G열이 실제 게시물 키가 아니라 엑셀의 다른 셀을 갖다 붙인 수식으로 만들어져 있었다.

파일	기존 G열 수식	문제
대구	="…view.do?bbsId=d142f452&num="&A3	num 자리에 엑셀 순번(A열) 을 참조 → 실제 게시물번호와 무관
울산	="…board.jsp?…&ridx="&H3&"…"	H열이 빈 셀 → ridx 없는 깨진 URL
전남	="…&bbsBid="&J3	J열이 빈 셀 → bbsBid 없는 깨진 URL
경남	전체 URL 문자열 (IPDS_IDX 내림차순)	순번 역순으로 기계 생성 → 제목과 뒤틀림
제주	…/rhksf2413/board/articles?act=view&seq=6002…	관리자 경로(rhksf2413) → 로그인 리다이렉트, seq도 순번 기반 추정치

---

처리 결과¶

파일	행수	URL 교체	미매칭(삭제 표기)	비고
대구광역시경찰청	1,213	1,213	0	전건 완료
울산경찰청	1,537	1,537	0	3건은 ridx 보간으로 복구
전남경찰청	1,494	1,494	0	전건 완료
경상남도경찰청	356	355	1	사이트에서 삭제된 게시물
제주경찰청	4,122	4,095	27	삭제 게시물(test/테스트 포함)
합계	8,722	8,694	28

미매칭 28건은 목록·사이트 검색까지 확인했으나 현재 사이트에 게시물이 없는(삭제된) 건 → G열 기존 링크 삭제 후 "삭제" 로 표기.

---

사이트별 크롤링 방법¶

1) 대구광역시경찰청 — dgpolice.go.kr¶

• 목록: GET /dgpo/bbs/List.do?bbsId=d142f452&pageNum={1..122} (10건/页, 총 1,216건)
• 상세 키: view.do?bbsId=d142f452&num={num} — 목록 <a href>에서 정규식 추출
• 함정: pageNum 파라미터를 붙이면 목록 링크에 &pageNum=1&num=…이 끼어들어 정규식이 어긋남 → (?:&|&)num=(\d+)로 대응
• WAF가 curl 차단(0바이트 응답) → 브라우저 UA 헤더 필수

2) 울산경찰청 — uspolice.go.kr¶

• 목록: GET /board2017.jsp?tab=bo20141217142950&menu=3&wmode=list&start={0,10,…} (게시물 1,556)
• 상세: board2017.jsp?…&wmode=read&ridx={ridx} — 목록의 goArticle('ridx','start') 파싱
• 함정 1: 목록 제목이 말줄임(…) 처리 → 상세 페이지 <th colspan="2">에서 전체 제목 확보 가능
• 함정 2: start=1000 등 특정 오프셋 창이 서버에서 빈 페이지로 반환(특정 게시물 포함 창 전체가 죽음) → 인접 오프셋(±1~9)으로 창을 밀어 회수, 그래도 안 잡히는 3건은 인접 게시물의 ridx 사이값 보간(1893↔1896 → 1894·1895, 2087↔2089 → 2088) 후 상세 페이지를 열어 제목 대조로 확정
• GET 검색(field/keyword)은 항상 0건 반환(POST 전용) → 검색 우회 불가, 위 보간 방식 사용

3) 전남경찰청 — jnpolice.go.kr¶

• 목록: GET /index.jsp?pid=AP0302&mode=list&cur_page={1..150} (총 1,497건)
• 상세 키: bbsBid — jsessionid·검색 파라미터 제거한 최소 URL로 정규화 https://www.jnpolice.go.kr/index.jsp?pid=AP0302&mode=view&bbsId=sub0302&bbsBid={bid}
• 페이지당 응답이 ~12초로 느려 전건 수집에 약 30분 소요

4) 경상남도경찰청 — gnpolice.go.kr¶

• 목록이 JS 렌더링(<tbody id="template-container">) → HTML 파싱 불가
• jquery.gnpBoard.js 분석으로 JSON API 발견: POST /gnpmng/sec/getBbsList.do 파라미터 BBS_ID=GNPMNG_D407, CURRENT_PAGE, PAGE_UNIT=100 → 전체 358건을 4회 호출로 수집 (제목·IPDS_IDX·등록일 JSON 제공)
• 상세: page.do?MENU_ID=NF02&Mode=view&IPDS_IDX={idx}&page=1

5) 제주경찰청 — jjpolice.go.kr¶

• 기존 수집 URL은 관리자 경로(/rhksf2413/…, 로그인 필요) → 공개 게시판 /jjpolice/notice/press.htm 사용
• 목록: GET press.htm?page={1..283} (15건/页, 총 4,308건) — 목록 제목은 말줄임되지만 <button title="…"> 속성에 전체 제목이 있어 상세 조회 불필요
• 상세: press.htm?act=view&seq={seq}
• 함정: 서버가 간헐적으로 요청과 다른 페이지를 반환 → "신규 0건이면 재시도" + 수집 후 목록번호(no) 연속성 검사로 누락 5개 페이지(75건) 탐지·재수집

---

매칭 로직 (제목 → URL)¶

엑셀 제목과 크롤링 제목이 그대로 일치하지 않는 경우가 많아 다단계 매칭 적용:

1. 정규화 후 제목+날짜 완전일치 — HTML 엔티티 이중 언이스케이프((→(), NFKC, 공백 제거
2. 말줄임 프리픽스 매칭(같은 날짜 내) — 사이트 제목이 ...로 잘린 경우 잘린 부분을 접두사로 비교. 엑셀 쪽이 잘린 경우(…검거()도 대응
3. 구두점 제거(alnum) 매칭 — 엑셀 인코딩 깨짐(·→?, ' 소실 등) 대응: 한글·영숫자만 남기고 비교 (제주에서 121건 추가 매칭)
4. 잔여건은 사이트 검색 기능 / ridx 보간으로 개별 확인, 그래도 없으면 "사이트에서 삭제됨" 판정

검증: 파일별 무작위 30셀 값·하이퍼링크 확인 + 사이트별 무작위 표본 URL을 실제 재요청해 응답 HTML에 엑셀 제목 포함 여부 자동 대조(대구 6/6, 전남 5/5, 경남 3/3, 제주 4/4, 울산 3/3 전부 일치).

---

엑셀 반영 방식¶

• Excel COM(win32com) 사용 — AutomationSecurity=3으로 매크로 실행 차단 상태에서 열어 VBA·수식·변호사 열 무손상 보존
• G열 처리: cell.Hyperlinks.Delete() (기존 링크 삭제) → cell.Value = url (수식 제거·텍스트 입력) → ws.Hyperlinks.Add(...) (새 링크 활성화)
• 미매칭 28건: 기존 링크 삭제 후 "삭제" 텍스트 표기
• 작업 전 원본 5개 파일 백업_원본\에 백업

---

코드¶

매칭 공통 모듈 matchlib.py¶

import re, html, unicodedata

def norm(s):
    s = html.unescape(html.unescape(str(s)))   # ( 등 이중 인코딩 대응
    s = unicodedata.normalize('NFKC', s)
    return re.sub(r'\s+', '', s)

def strip_trunc(s):
    """말줄임(...) 제거"""
    return re.sub(r'(\.\.\.|…)\s*$', '', s)

def match(excel_rows, posts, post_id, post_title_key='title', post_date_key='date', **kw):
    by_td, by_t, by_date = {}, {}, {}
    for p in posts:
        nt = norm(p[post_title_key])
        by_td.setdefault((nt, p[post_date_key][:10]), []).append(p)
        by_t.setdefault(nt, []).append(p)
        by_date.setdefault(p[post_date_key][:10], []).append(p)
    used, result, unmatched = set(), {}, []
    stats = {'exact': 0, 'prefix': 0, 'title_only': 0}

    def take(row, p, how):
        used.add(p[post_id]); result[row['excel_row']] = p; stats[how] += 1

    # 1차: 제목+날짜 완전일치
    pending = []
    for row in excel_rows:
        cands = [p for p in by_td.get((norm(row['title']), row['date'][:10]), []) if p[post_id] not in used]
        take(row, cands[0], 'exact') if cands else pending.append(row)

    # 2차: 같은 날짜 내 말줄임 프리픽스 매칭
    still = []
    for row in pending:
        nt, d, cands = norm(row['title']), row['date'][:10], []
        for p in by_date.get(d, []):
            if p[post_id] in used: continue
            st = norm(p[post_title_key]); ss = strip_trunc(st)
            if len(ss) < len(st):                      # 사이트 제목이 잘림
                if nt.startswith(ss): cands.append(p)
            elif st.startswith(nt[:max(1,len(nt)-2)]) or nt.startswith(st[:max(1,len(st)-2)]):
                cands.append(p)                        # 엑셀 쪽이 잘림
        take(row, cands[0], 'prefix') if cands else still.append(row)
    # (3차 title_only 생략) ...
    return result, still, stats

3차 구두점 제거 매칭 (엑셀 ·→? 깨짐 대응)¶

def alnum(s):
    s = html.unescape(html.unescape(str(s)))
    s = unicodedata.normalize('NFKC', s)
    return re.sub(r'[^0-9A-Za-z가-힣]', '', s)   # 한글·영숫자만 남김

for row in unmatched:
    nt, d = alnum(row['title']), row['date'][:10]
    for p in by_date.get(d, []):
        if p['seq'] in used: continue
        st_full = alnum(p['title']); st_strip = alnum(strip_trunc(p['title']))
        if nt == st_full or (len(st_strip) >= 8 and nt.startswith(st_strip)) \
           or (len(nt) >= 8 and st_full.startswith(nt)):
            used.add(p['seq']); res[row['excel_row']] = p; break

대구 크롤러 crawl_daegu.py (대표 예시)¶

import requests, re, json, time

H = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/126.0'}
BASE = 'https://www.dgpolice.go.kr/dgpo/bbs/List.do?bbsId=d142f452&pageNum={}'
ROW = re.compile(
    r'<td class="listnum">(\d+)</td>.*?'
    r'<a href="(/dgpo/bbs/view\.do\?[^"]*?(?:&amp;|&)num=(\d+))"[^>]*><span[^>]*>(.*?)</span></a>.*?'
    r'<td class="hidden-xs hidden-sm">[^<]*</td>\s*'
    r'<td class="hidden-xs hidden-sm">([\d-]+)</td>', re.S)

posts, sess, page = [], requests.Session(), 1
sess.headers.update(H)
while True:
    for attempt in range(4):                       # 재시도(백오프)
        try:
            r = sess.get(BASE.format(page), timeout=30); r.raise_for_status(); break
        except Exception:
            time.sleep(3 * (attempt + 1))
    rows = ROW.findall(r.text)
    if not rows: break                             # 마지막 페이지 판정
    for listnum, href, num, title, date in rows:
        posts.append({'listnum': int(listnum), 'num': int(num),
                      'title': re.sub(r'<[^>]+>', '', title).strip(), 'date': date,
                      'url': f'https://www.dgpolice.go.kr/dgpo/bbs/view.do?bbsId=d142f452&num={num}'})
    page += 1; time.sleep(0.4)
json.dump(posts, open('daegu_posts.json', 'w', encoding='utf-8'), ensure_ascii=False)

경남 — JSON API 호출부¶

r = s.post('https://www.gnpolice.go.kr/gnpmng/sec/getBbsList.do',
    data={'BBS_ID': 'GNPMNG_D407', 'CURRENT_PAGE': str(page), 'PAGE_UNIT': '100',
          'FROM_DATE': '', 'TO_DATE': '', 'SEARCH_CONTITION': '', 'SEARCH_KEYWORD': ''},
    headers={'X-Requested-With': 'XMLHttpRequest',
             'Referer': 'https://www.gnpolice.go.kr/gnpolice/page.do?MENU_ID=NF02'}, timeout=25)
for it in r.json()['list']:
    posts.append({'idx': it['IPDS_IDX'], 'title': it['CPDS_SUBJECT'].strip(),
                  'date': it['CPDS_WDATE'],
                  'url': f"…page.do?MENU_ID=NF02&Mode=view&IPDS_IDX={it['IPDS_IDX']}&page=1"})

제주 — 목록 파싱(전체 제목은 title 속성) + 오염 페이지 재시도¶

ROW = re.compile(
    r'<td class="no">(\d+)</td>.*?name="seq" value="(\d+)"/>.*?'
    r'<button type="submit" title="([^"]*)".*?<td class="wdate">([\d.\s]+)</td>', re.S)

first = get(1)
last_page = max(int(x) for x in re.findall(r'page=(\d+)', first))   # 283
for page in range(2, last_page + 1):
    new = add_rows(get(page))
    if new == 0:                       # 서버가 엉뚱한 페이지를 준 경우
        for _ in range(4):
            time.sleep(2)
            if add_rows(get(page)): break
# 수집 후 no(목록번호) 연속성 검사 → 빠진 페이지만 재수집
missing = sorted(set(range(1, TOP + 1)) - {p['no'] for p in posts})
pages = sorted({(TOP - n) // 15 + 1 for n in missing})

울산 — 서버 결함 창 회수 + ridx 보간¶

# start=1000 창이 비면 인접 오프셋으로 창을 밀어 회수
cur, _ = fetch(start)
if not cur:
    for delta in (1, 2, 5, -1):
        cur, _ = fetch(start + delta)
        if cur: break

# 그래도 안 잡힌 3건: 인접 ridx 사이값을 직접 열어 제목 검증
# listnum 363→ridx 1893, 366→1896  ⇒  364=1894, 365=1895 (검증 후 확정)
url = f'…board2017.jsp?tab=bo20141217142950&menu=3&wmode=read&ridx={ridx}&start=0'
title = re.search(r'<div class="bo_view">.*?<th colspan="2">(.*?)</th>', html_text, re.S)

엑셀 기록 write_excel.py¶

import win32com.client as win32

excel = win32.DispatchEx('Excel.Application')
excel.Visible = False
excel.DisplayAlerts = False
excel.AutomationSecurity = 3            # 매크로 실행 강제 차단 (VBA 보존)
wb = excel.Workbooks.Open(path, UpdateLinks=0)
ws = wb.Worksheets('권리확인')
excel.Calculation = -4135               # 수동 계산 (속도)
for row_str, url in mapping.items():
    cell = ws.Cells(int(row_str), 7)    # G열: URL주소
    try: cell.Hyperlinks.Delete()       # 1) 기존 링크 삭제
    except Exception: pass
    cell.Value = url                    # 2) 수식 → URL 텍스트
    ws.Hyperlinks.Add(Anchor=cell, Address=url, TextToDisplay=url)  # 3) 링크 활성화
wb.Save(); wb.Close(SaveChanges=False); excel.Quit()

---

산출물¶

경로	내용
● 사이트 장애 및 재크롤링 요청_260804\*.xlsm (5개)	G열 URL 교체 + 하이퍼링크 활성화 완료본
백업_원본\	수정 전 원본 백업
URL재작성_결과보고.txt	파일별 결과 + 미매칭 28건 상세 목록
scratchpad crawl\*_posts.json / map_*.json	사이트별 크롤링 원본 데이터 / 행→URL 매핑