[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 접속)

 

터미널에서 psql 실행시킬 때

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;

 

---

이렇게 크롤러 -> 로컬 DB 적재까지 완료됐다!

생각보다 긴 과정이지만, 한번 따라하면서 이해하고나면 이후에는 데이터 베이스를 바라보는 시선이 달라진다!

뭐랄까 전에는 악명높은 빌런이었다가, 이젠 내가 부려먹는 하수인이 된느낌이랄까

(근데 또 알면 알수록 이건 새발의 피였구나 하게됨. 뭐든지 조금 알때가 가장 재밌는 법)

 

아무튼 다음글에서는 Tableau 데스크탑을 설치하는 법부터 로컬 DB에 연동하는 방법을 설명할 예정이다!

화이팅 화이팅~

 

ㄱㅊㅁ_ㅇㅈ