30 데이터 수집&전처리&데이터베이스에 삽입 사이클 | ✅ 저자: 이유정(박사)

수집 → staging 테이블에 넣고 → 분산 처리하는 방식을 위한 테이블 추가

테이블 생성하기: 우선 레퍼런스 대상 테이블 먼저 생성한다.

CREATE TABLE restaurants_kakao (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    area_name VARCHAR(100),
    kakao_place_id VARCHAR(100),
    address TEXT,
    phone VARCHAR(20),
    cuisine_type VARCHAR(20),
    category VARCHAR(20),
    tags TEXT,
    menu TEXT,
    rating FLOAT,
    keyword TEXT,
    rating_count INT,
    business_hour VARCHAR(100),
    latitude VARCHAR(20),
    longitude VARCHAR(20),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

테이블을 실행하고 새로고침을 눌러서 테이블이 생성되는지 확인한다.

id : 각 행(row)을 고유하게 식별하기 위한 기본 키 BIGINT : 정수형 타입으로, INT보다 더 큰 숫자를 저장할 수 있어요. - INT는 약 ±21억까지 - BIGINT는 ±922경까지 저장 가능

• 많은 데이터를 다룰 경우 BIGINT를 사용하면 안전합니다. AUTO_INCREMENT : 값을 자동으로 증가시켜주는 기능입니다.
• 레코드(행)를 삽입할 때 id 값을 입력하지 않아도 1, 2, 3, 4... 이렇게 자동으로 늘어나요. 예:

INSERT INTO restaurants_kakao (name, address) VALUES ('식당A', '서울시...'); 
-- 자동으로 id=1이 들어감

PRIMARY KEY : 이 컬럼을 테이블의 기본 키(Primary Key)로 지정합니다.

• 즉, 중복될 수 없고, 항상 고유(unique)해야 합니다.

전체 의미 요약:

id BIGINT AUTO_INCREMENT PRIMARY KEY

이 컬럼은:

• BIGINT 타입의 큰 정수를 저장하고
• 값을 자동으로 증가시키며
• 고유한 기본키로 테이블에서 각 행을 식별하는 역할을 합니다.

---

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

==created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP==

• 이 컬럼은 레코드가 처음 생성될 때의 시간을 저장해요.
• NOT NULL: 비워둘 수 없다는 의미.
• DEFAULT CURRENT_TIMESTAMP: 값을 따로 지정하지 않아도 현재 시각이 자동 입력됨. 예를 들어:

INSERT INTO restaurants_kakao (name) VALUES ('식당A'); 
-- created_at에는 자동으로 현재 시간이 입력됨 (예: 2025-07-20 17:22:35)

==updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP==

• 이 컬럼은 레코드가 수정(update)될 때마다 자동으로 현재 시간으로 갱신돼요.
• DEFAULT CURRENT_TIMESTAMP: 최초 INSERT 시 기본값으로 현재 시각 입력
• ON UPDATE CURRENT_TIMESTAMP: 나중에 UPDATE 될 경우 자동으로 현재 시각으로 갱신

UPDATE restaurants_kakao SET name = '식당B' WHERE id = 1; 
-- updated_at 컬럼이 현재 시각으로 자동 갱신됨

---

생성된 테이블 확인하기 단축키 F5

종속 테이블 만들기

CREATE TABLE restaurant_blog_reviews_kakao (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    published_date DATE,
    blog_url VARCHAR(255),
    restaurant_id BIGINT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants_kakao(id)
);

마지막 테이블 생성

CREATE TABLE restaurant_reviews_kakao (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    author VARCHAR(100),
    content TEXT,
    restaurant_id BIGINT,
    image_urls TEXT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants_kakao(id)
);

---

레스토랑 데이터 크롤러 하기

import pandas as pd
from time import sleep
import googlemaps
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support import expected_conditions as ec
from selenium.webdriver.support.ui import WebDriverWait
from sqlalchemy import create_engine
from webdriver_manager.chrome import ChromeDriverManager

# ✅ 구글 지도 API 키 설정
gmaps = googlemaps.Client(key='본인의 구글API키 삽입')

# ✅ MySQL DB 엔진 설정
DB_URL = 'mysql+pymysql://ai_restaurant:airest1234@superguard.iptime.org:16606/ai_restaurant?charset=utf8mb4'
engine = create_engine(DB_URL, echo=False)

# ✅ 웹드라이버 설정
def get_webdriver():
    options = Options()
    options.add_argument('--start-maximized')
    options.add_argument('--disable-blink-features=AutomationControlled')
    options.add_argument('--disable-gpu')
    # options.add_argument('--headless')  # 디버깅 시 주석 처리
    service = Service(ChromeDriverManager().install())
    driver = webdriver.Chrome(service=service, options=options)
    return driver

# ✅ 주소 → 위도/경도 변환
def geocode_address(address):
    geocode_result = gmaps.geocode(address)
    if geocode_result:
        location = geocode_result[0]['geometry']['location']
        return location['lat'], location['lng']
    else:
        return None, None

# ✅ HTML → 가게 데이터 추출
def get_items(html, cuisine, category):
    soup = BeautifulSoup(html, "html.parser")
    items = soup.select("li.PlaceItem.clickArea")
    results = []

    for item in items:
        try:
            address = item.find('p', {'data-id': 'address'})
            if not address:
                continue

            name = item.find('span', {'data-id': 'screenOutName'}).text
            kakao_place_id = item.find("a", {"data-id": "moreview"})["href"].split("/")[-1]
            phone = item.find('span', {'data-id': 'phone'}).text
            rating = item.find('em', {'data-id': 'scoreNum'}).text
            business_hour = item.find('a', {'data-id': 'periodTxt'}).text
            lat, lng = geocode_address(address.text)

            results.append({
                'name': name,
                'area_name': None,  # 현재 미수집
                'kakao_place_id': kakao_place_id,
                'address': address.text,
                'phone': phone,
                'cuisine_type': cuisine,
                'category': category,
                'tags': None,
                'menu': None,
                'rating': float(rating),
                'keyword': None,
                'rating_count': None,
                'business_hour': business_hour,
                'latitude': str(lat),
                'longitude': str(lng),
                'created_at': None,
                'updated_at': None
            })

        except Exception as e:
            print("❗ Parsing error:", e)
            continue

    return results

# ✅ 메인 크롤링 + DB 저장 함수
def get_data_from_kakaomap(search_keyword: str, cuisine: str, category: str):
    driver = get_webdriver()
    conn = engine.connect()
    try:
        # 카카오맵 접속 및 검색
        driver.get("https://map.kakao.com/")
        WebDriverWait(driver, 10).until(ec.visibility_of_element_located((By.ID, "search.keyword.query")))
        input_box = driver.find_element(By.ID, "search.keyword.query")
        input_box.send_keys(search_keyword)
        input_box.send_keys(Keys.ENTER)

        WebDriverWait(driver, 10).until(ec.element_to_be_clickable((By.ID, "info.search.place.more")))
        driver.execute_script("""var el = document.getElementById('dimmedLayer'); if (el) el.className = 'DimmedLayer HIDDEN';""")
        driver.find_element(By.ID, "info.search.place.more").click()
        WebDriverWait(driver, 10).until(ec.visibility_of_element_located((By.ID, "info.search.page")))

        all_items = []
        for page in range(1, 6):
            try:
                driver.find_element(By.ID, f"info.search.page.no{page}").click()
                WebDriverWait(driver, 10).until(ec.visibility_of_element_located((By.ID, "info.search.place.list")))
                html = driver.find_element(By.ID, "info.search.place.list").get_attribute("innerHTML")
                all_items.extend(get_items(html, cuisine, category))
                sleep(1)
            except Exception as e:
                print(f"❗ 페이지 {page} 오류:", e)

        driver.quit()

        # DB에서 기존 name+address 조합 불러오기
        existing = pd.read_sql(f'''
            SELECT name, address FROM restaurants_kakao
            WHERE cuisine_type="{cuisine}" AND category="{category}"
        ''', conn)

        df = pd.DataFrame(all_items)
        if df.empty:
            print("❗ 저장할 데이터 없음.")
            return

        # 중복 제거
        df_filtered = df[~df[['name', 'address']].apply(tuple, axis=1).isin(existing[['name', 'address']].apply(tuple, axis=1))]

        print(f"✔ 저장할 row count: {len(df_filtered)}")
        print(df_filtered[['name', 'address', 'kakao_place_id']].head())

        if df_filtered.empty:
            print("⚠ 모든 데이터가 이미 존재함.")
            return

        # DB 저장 (컬럼 순서 명시)
        df_filtered = df_filtered[[
            'name', 'area_name', 'kakao_place_id', 'address', 'phone',
            'cuisine_type', 'category', 'tags', 'menu', 'rating',
            'keyword', 'rating_count', 'business_hour',
            'latitude', 'longitude', 'created_at', 'updated_at'
        ]]

        with engine.begin() as trans_conn:
            df_filtered.to_sql('restaurants_kakao', trans_conn, if_exists='append', index=False)
        print("✅ DB 저장 완료!")

    except Exception as e:
        print("❌ 전체 오류 발생:", e)
    finally:
        conn.close()
        driver.quit()

Jupyter book에서 실행하여 크롤링 하기:

import sys
import os
print(os.getcwd())
sys.path.append(os.getcwd())

from crawler.restuarant_crawler import get_data_from_kakaomap

# get_data_from_kakaomap("강남구 카페", "디저트", "카페")
# get_data_from_kakaomap("강남구 해장국", "한식", "국밥")

get_data_from_kakaomap("강남구 치킨", "한식", "치킨")

# search = [
#     ("강남구 파스타", "양식", "파스타"),
#     ("강남구 초밥", "일식", "초밥"),
#     ("강남구 라멘", "일식", "리멘"),
#     ("강남구 스테이크", "양식", "스테이크"),
#     ("강남구 중국집", "중식", "중식"),
#     ("강남구 해물찜", "한식", "찜"),
#     ("강남구 맥주집", "주점", "주점"),
#     ("강남구 족발", "한식", "족발"),
#     ("강남구 와플", "디저트", "디저트"),
#     ("강남구 떡볶이", "한식", "분식"),
    
# ]

# for s in search:
#     get_data_from_kakaomap(*s)

---