데이터 엔지니어링

[Postgres, Crawler] part 1. 로컬 DB 생성 및 크롤러 연동

TODO LIST

데이터 베이스 툴 선택
PostgreSQL 설치
데이터 베이스 설정 (데이터 베이스, 테이블, 유저 생성)
크롤러와 DB 연동

1. 데이터 베이스 툴 선택

ORACLE, MySQL, PostgreSQL 등 다양한 데이터 베이스가 존재하고, 저마다의 장단점 또한 존재함.

따라서, 가장 좋은 데이터베이스는 없음. 상황에 따라 다름.

필자는 PostgreSQL이 익숙하고 GUI도 편해서 선택함. (표준 SQL 쿼리를 따르기 때문도 있음)

특히 DB 점유율 1위임 (아닐수도)

2. 로컬 PostgreSQL 설치 (mac 기준)

설치 사이트 접속 후 자신의 운영체제에 맞는 버전을 설치 (필자는 macOS 사용중)

(https://www.postgresql.org/download/)

오픈 소스인 경우 최신 버전을 설치하기 보다는 한 단계 이전 버전을 설치하는게 좋음 (오류 등의 발생 위험 낮음)

—> 15.7 버전 설치 (16.3도 되긴함)

설치 파일 실행

중간에 stack builder를 추가로 설치할건지 선택하는 창이 있는데,

공간 연산 (GIS)가 필요하지 않은 경우 굳이 설치 안해도 됨.

(필자는 안함)

여기서 지정한 비밀번호는 이후에 DB 접속할 때 사용해야함 (필수 기억)

postgresql의 기본 포트는 5432임 (어떻게 됐건 통신으로 데이터를 적재하는거다보니 포트가 존재해야함)

1234 등 다른 포트 번호로 지정해도 되지만, 가상 머신에서 보안 그룹 IP (허용할 포트)를 지정할때 postgresql의 포트를 5432로 지정해놓을만큼 불문율이니까 안건드는게 좋음

* 이외에는 next 연타하면 됨

3. PostgreSQL 초기 세팅 (user 및 DB 인스턴스 생성)

Psql실행
(윈도우 탐색기, 맥 Spotlight로 찾아도 되고, 터미널에 입력해서 실행시키는 것도 가능함)

# 맥 os
brew services start postgresql@15 # (@ 뒤는 설치한 버전)

# 윈도우
psql -U postgres # 안되는 경우는 환경 변수 지정 필요 (구글링 or 파일 탐색기로 psql 접속)

처음 접속시에는 password for user postgres에 설치할 때 설정했던 비밀번호만 입력하면 됨

(기본적으로 설치 시 superuser로 postgre라는 사용자가 생성됨)

4. DB 및 테이블 생성

크롤링한 데이터를 저장할 스키마대로 테이블을 만들어야함

(example_db는 필자가 임의로 지정했던 db명일 뿐, 자신의 db이름을 지어도 무관함)

# DB 생성
CREATE DATABASE example_db;

# \l 누르면 DB 인스턴스 목록 나옴

# 해당 DB에 접속
\c example_db

# user 확인
\du

기본값이었던 postgres라는 user가 있고,

권한은 슈퍼유저, 롤 만들기, DB 생성, 복제, RLS 통과 (그냥 모든 권한이 있음)

해당 user를 계속 사용해도 무방하나, 여러 유저가 같이 사용하는 경우 user의 식별이 필요하기에, 개인용 user를 생성하는것이 좋다.

# gunu라는 유저 생성, 비밀번호는 1234
CREATE USER gunu WITH PASSWORD '1234';
# 비밀번호 바꿀떄는 똑같은 명령어에 새로운 비밀번호를 넣으면 됨
ALTER USER gunu WITH PASSWORD '5994';

# gunu 계정에 db 생성, 복제 권한 추가
ALTER ROLE gunu WITH CREATEDB REPLICATION;

이제 만들어진 DB에 재접속하면 된다. (방식은 3가지 정도)

1번 방식 : psql실행 할때부터 접속할 DB와 user 지정

2번 방식 : user만 지정하고 로그인 한 후, \c 명령어로 목표 DB 접속

\c [db 이름] [user 이름]

* 추가로 편의상 시간을 한국 기준으로 변경 (아마 UTC 시간대로 설정되어 있을 것)

# 현재 시간 설정 확인
SHOW timezone;
# 한국 기준으로 변경
ALTER DATABASE example_db SET timezone = 'Asia/Seoul';

5. 테이블 지정

필자는 예제 코드로 여기어때에서 서울에 있는 호텔의 가격, 별점, 주소, 리뷰 수를 크롤링하는 파이썬 파일을 구현했고, 이에 맞춰 테이블 column을 지정했다.

(insert_time은 postgresql이 행을 추가할 떄마다 서울 기준 현재 시각을 알아서 찍도록 지정)

밑은 네이버 항공권 기준 테이블 구조임 (자신의 크롤링 내용 및 구조에 맞춰서 설정 필요)
위에 psql에 그대로 복사 붙여넣기 하면 됨 (사용하려는 db에 접속되어있는지 확인 할 것!)
- 셀 시작에 example_db=# 이런식으로 되어있어야함
  - (example_db는 필자가 임의로 지정했던 db명일 뿐, 자신의 db이름을 지어도 무관함)

CREATE TABLE hotels (
    id SERIAL PRIMARY KEY,
    hotel_name VARCHAR(255) NOT NULL,
    url TEXT,
    star_rating VARCHAR(50),
    price VARCHAR(100),
    address VARCHAR(255),
    rating NUMERIC,
    review_count INTEGER,
    insert_time TIMESTAMP
);

6. 파이썬 파일 수정 (DB 연결)

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
from webdriver_manager.chrome import ChromeDriverManager
from selenium.webdriver.chrome.options import Options
import time
import json
import psycopg2
from psycopg2 import sql
from datetime import datetime

options = Options()
options.add_experimental_option("detach", True)
options.add_argument("--headless")  # 헤드리스 모드 (백그라운드로 웹브라우저 열기)

def get_total_page_num(region, driver):
    url = f"https://www.yeogi.com/domestic-accommodations?searchType=KEYWORD&keyword={region}&page=1&personal=2"
    
    driver.get(url)
    time.sleep(1)
    
    h1_tag = driver.find_element(By.CSS_SELECTOR, "header.css-1psit91 h1")
    total_result_text = h1_tag.text.strip()
    total_result_num = ""
    for char in total_result_text:
        if char.isdigit():
            total_result_num += char
    print("총 검색 호텔 개수:", total_result_num)
    return int(total_result_num) // 20 + 1

def Crawling(region, total_page_num, driver, conn, cur):
    for page_num in range(total_page_num):
        url = f"https://www.yeogi.com/domestic-accommodations?searchType=KEYWORD&keyword={region}&page={page_num}"
        driver.get(url)
        time.sleep(2)
        script_tag = driver.find_element(By.XPATH, '//script[@type="application/ld+json"]')
        json_data = script_tag.get_attribute('innerText')
        
        data = json.loads(json_data)
        
        accommodations = data['mainEntity']['itemListElement']
        for accommodation in accommodations:
            item = accommodation['item']
            name = item['name']
            url = item['url']
            star_rating = item.get('starRating', '')
            price_range = item.get('priceRange', '')
            address = item['address']['addressLocality']
            
            aggregate_rating = item.get('aggregateRating', None)
            if aggregate_rating:
                rating_value = aggregate_rating.get('ratingValue', None)
                review_count = aggregate_rating.get('reviewCount', None)
            else:
                rating_value = None
                review_count = None
            
            insert_time = datetime.now()
            
            print(name)
            
            # PostgreSQL에 데이터 삽입
            insert_query = sql.SQL("""
                INSERT INTO hotels (hotel_name, url, star_rating, price, address, rating, review_count, insert_time)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """)
            try:
                cur.execute(insert_query, (name, url, star_rating, price_range, address, rating_value, review_count, insert_time))
            except Exception as e:
                print(e)
    
    conn.commit()
    return 0

if __name__ == "__main__":
    driver = webdriver.Chrome(options=options)
    region = "서울"
    total_page_num = get_total_page_num(region, driver)
    
    # PostgreSQL 연결
    conn = psycopg2.connect(
        dbname="example_db",
        user="gunu",
        password="5994",
        host="localhost",
        port="5432"
    )
    # DB와 상호작용하는 커서 (INSERT, DELETE 등)
    cur = conn.cursor()
    
    # 크롤러 작동
    Crawling(region, total_page_num, driver, conn, cur)
    
    # 크롤러 종료 후 DB 연결 종료
    cur.close()
    conn.close()

위 코드에서 execute는 git에서의 commit 개념이고, commit은 git에서의 push개념임
- execute하면 insert할 레코드들이 변경 사항으로 지정되고
- commit하면 변경사항이 연결된(conn)데이터 베이스에 적용됨
insert_qeury랑 execute의 두번째 파라미터의 매칭(순서)이 중요함
- 데이터베이스 테이블의 hotel_name필드에 넣을 값이 코드 상 name 변수에 있는 것
- 데이터베이스 테이블의 url 필드에 넣을 값이 코드 상 url 변수에 있는 것
- … (모르겠으면 구글링!, 그래도 모르겠으면 물어보기~)

7. pgAdmin4 활용하기 (GUI)

사실 터미널 사용하지 않고, GUI만으로도 지금까지 한 모든 것을 할 수 있음.

윈도우 탐색기 or Spotlight에서 pgAdmin4 실행파일을 실행시키면 gui가 실행됨

맨 처음에 postgresql 설치할때 설정했던 비밀번호 입력

터미널에서 만들었던 데이터 베이스 확인

터미널에서 지정했던 테이블 스키마대로 hotels 테이블이 생성된 것을 확인할 수 있다.

위에 있던 파이썬 파일을 실행시키면?

데이터 적재 완료~~!!!

(만약 테이블을 postgre(기본 유저)로 선언한 뒤에 새로 만든 유저로 해당 테이블을 수정하려하면 권한 오류가 뜸
-> 그럴떈 밑의 코드로 권한 부여를 해줘야함!)

-- 데이터베이스의 모든 테이블에 대한 모든 권한 부여
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO gunu;

-- 앞으로 생성될 테이블에 대해서도 자동으로 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO gunu;

-- 시퀀스에 대한 권한도 부여 (테이블의 자동 증가 ID 등을 위해)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO gunu;