SQL 학습 과제 1 - 스타벅스와 이디야

ready

# 사용한 모듈
import mysql.connector
import pandas as pd
import googlemaps
import time
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.common.by import By
from tqdm import tqdm_notebook

remote = mysql.connector.connect(
    host = "host",
    port = 3306,
    user = "admin",
    password = "password"
)

cur = remote.cursor(buffered=True)

1. database, user 생성

# database 생성
cur.execute("CREATE DATABASE oneday")

# user 생성
cur.execute("CREATE USER 'oneday'@'%' identified by '1234'")

2. 테이블 생성

cur.execute("USE oneday")

sql = "CREATE TABLE COFFEE_BRAND (id INT NOT NULL AUTO_INCREMENT, \
    name VARCHAR(32) NOT NULL, primary key (id))"
    
cur.execute(sql)

sql = "CREATE TABLE COFFEE_STORE (id INT NOT NULL AUTO_INCREMENT, \
    brand int, name VARCHAR(32) NOT NULL, gu_name VARCHAR(5), \
    address VARCHAR(128), lat DECIMAL(16, 14), lng DECIMAL(17, 14), \
    primary key (id), \
    CONSTRAINT fk_brand FOREIGN KEY (brand) references COFFEE_BRAND(id))"
    
cur.execute(sql)

3. COFFEE_BRAND 테이블에 데이터 입력

sql = "INSERT INTO COFFEE_BRAND VALUES (1, 'STARBUCKS')"
cur.execute(sql)

sql = "INSERT INTO COFFEE_BRAND VALUES (2, 'EDIYA')"
cur.execute(sql)

remote.commit()

4. 스타벅스 홈페이지 접근 시 팝업창 닫는 코드 에러 처리

url = "https://www.starbucks.co.kr/store/store_map.do"
driver = webdriver.Chrome(executable_path="path")
driver.get(url)
driver.maximize_window()

try:
    driver.find_element(By.CSS_SELECTOR, "holiday_notice_close a").click()
except Exception as e:
    print(e)

>>>
Message: no such element: Unable to locate element

5. 스타벅스 홈페이지에서 가져온 데이터 COFFEE_STORE 테이블에 입력하기

EDA 테스트 때 작성했던 코드를 기본으로, 위도와 경도도 같이 가져오도록 수정했다.

# 지역 버튼 클릭
driver.find_element(By.CSS_SELECTOR, "#container > div > form > fieldset > div > section > article.find_store_cont > article > header.loca_search > h3 > a").click()
time.sleep(1)

# 서울 버튼 클릭
driver.find_element(By.CSS_SELECTOR, "#container > div > form > fieldset > div > section > article.find_store_cont > article > article:nth-child(4) > div.loca_step1 > div.loca_step1_cont > ul > li:nth-child(1) > a").click()
time.sleep(2)
    
# 구(전체) 버튼 클릭
driver.find_elements(By.CLASS_NAME, "set_gugun_cd_btn")[0].click()

# 데이터 가져오기
req = driver.page_source
soup = BeautifulSoup(req, "html.parser")

store_list_raw = soup.select_one(".quickSearchResultBoxSidoGugun")
store_list = store_list_raw.select(".quickResultLstCon")

n = 1
for store in store_list:
    name = store.get("data-name")
    gu = store.select_one(".result_details").text.split(" ")[1]
    address = store.select_one(".result_details").text[:-9]
    lat = store.get("data-lat")
    lng = store.get("data-long")
    
    sql = "INSERT INTO COFFEE_STORE VALUES (%s, %s, %s, %s, %s, %s, %s)"
    cur.execute(sql, (n, 1, name, gu, address, lat, lng))
    n += 1

remote.commit()
driver.quit()

6. 이디야 홈페이지에서 가져온 데이터 COFFEE_STORE 테이블에 입력하기

이디야 홈페이지에서도 위도와 경도 데이터를 바로 가져올 수 있으나, 누락된 데이터가 군데군데 보여서 구글맵 API를 사용했다.

# 서울시 전체 구 리스트
all_gu = []
cur.execute("SELECT DISTINCT gu_name FROM COFFEE_STORE")

result = cur.fetchall()
for row in result:
   all_gu.append(row[0])

url = "https://ediya.com/contents/find_store.html"
driver = webdriver.Chrome(executable_path="path")
driver.get(url)
driver.maximize_window()

# 주소 버튼 클릭
driver.find_element(By.CSS_SELECTOR, "#contentWrap > div.contents > div > div.store_search_pop > ul > li:nth-child(2) > a").click()

n = 613
for gu in tqdm_notebook(all_gu):
    # 입력창 선택
    input_space = driver.find_element(By.CSS_SELECTOR, "#keyword")
    input_space.clear()
    input_space.send_keys("서울 " + gu)

    # 검색 버튼 클릭
    driver.find_element(By.CSS_SELECTOR, "#keyword_div > form > button").click()

    # 데이터 가져오기
    req = driver.page_source
    soup = BeautifulSoup(req, "html.parser")
    
    store_list_raw = soup.select_one(".result_list")
    store_list = store_list_raw.select(".item")

    for store in store_list:
        name = store.text.split(" ", 1)[0]
        address = store.text.split(" ", 1)[1]
        
        try:
            tmp = gmaps.geocode(address, language="ko")[0]
            lat = tmp.get("geometry")["location"]["lat"]
            lng = tmp.get("geometry")["location"]["lng"]
        except Exception as e:
            print(f"{name}: {e}")
            lat = 0
            lng = 0
        
        sql = "INSERT INTO COFFEE_STORE VALUES (%s, %s, %s, %s, %s, %s, %s)"
        cur.execute(sql, (n, 2, name, gu, address, lat, lng))
        n += 1

>>>
신촌하나로마트점: list index out of range
가재울점: list index out of range
내방역점: list index out of range
가든파이브테크노관점: list index out of range
잠실역점: list index out of range
라이프점: list index out of range

예외처리되어 출력된 매장들에 대해 직접 위도와 경도 데이터를 찾아 넣어줬다.

# 누락된 위도 경도 데이터 채우기
sql = "UPDATE COFFEE_STORE SET lat = 37.556111 WHERE name='신촌하나로마트점' AND brand = 2;"
cur.execute(sql)
sql = "UPDATE COFFEE_STORE SET lng = 126.933118 WHERE name='신촌하나로마트점' AND brand = 2;"
cur.execute(sql)
...

remote.commit()
driver.quit()

7. 데이터 조회하기

# 스타벅스 매장 주요 분포 지역 - 매장수가 많은 상위 5개 구이름, 매장 개수
cur.execute("SELECT gu_name, count(*) FROM COFFEE_STORE \
    WHERE brand = 1 GROUP BY gu_name ORDER BY count(*) DESC LIMIT 5")

result = cur.fetchall()
for row in result:
    print(row)

# 이디야 매장 주요 분포 지역 - 매장수가 많은 상위 5개 구이름, 매장 개수
cur.execute("SELECT gu_name, count(*) FROM COFFEE_STORE \
    WHERE brand = 2 GROUP BY gu_name ORDER BY count(*) DESC LIMIT 5")

result = cur.fetchall()
for row in result:
    print(row)

# 구별 브랜드 각각의 매장 개수 조회 - 구이름, 브랜드이름, 매장 개수
cur.execute("SELECT s.gu_name, b.name, count(s.id) FROM COFFEE_BRAND b, COFFEE_STORE s \
    WHERE b.id = s.brand GROUP BY gu_name, brand ORDER BY gu_name")

result = cur.fetchall()
for row in result:
    print(row)

# 구별 브랜드 각각의 매장 개수 조회 - 구이름, 스타벅스 매장 개수, 이디야 매장 개수
cur.execute("SELECT s.gu_name, count(s.id), e.count_id FROM COFFEE_STORE s, \
    (SELECT gu_name, count(id) count_id FROM COFFEE_STORE WHERE brand = 2 GROUP BY gu_name) e \
    WHERE s.gu_name = e.gu_name and brand = 1 GROUP BY gu_name ORDER BY gu_name")

result = cur.fetchall()
for row in result:
    print(row)

8. CSV 파일로 저장하기

sql = "SELECT * FROM (SELECT s.id s_id, b.name s_brand, s.name s_name, s.gu_name s_gu, \
    s.address s_address, s.lat s_lat, s.lng s_lng \
    FROM COFFEE_BRAND b, COFFEE_STORE s \
    WHERE b.id = s.brand and s.brand = 1) starbucks, \
    (SELECT s.id e_id, b.name e_brand, s.name e_name, s.gu_name e_gu, \
    s.address e_address, s.lat e_lat, s.lng e_lng \
    FROM COFFEE_BRAND b, COFFEE_STORE s \
    WHERE b.id = s.brand and s.brand = 2) ediya \
    ORDER BY starbucks.s_id, ediya.e_id"
    
cur.execute(sql)
result = cur.fetchall()

컬럼명 가져오는 걸 한참 고민했다.
다양한 방법 중에서 description 속성을 사용해봤다.
✨mysqlcursor.description

# 테이블에서 컬럼명 가져오기
columns = [i[0] for i in cur.description]

# 데이터프레임 만들기
df = pd.DataFrame(data=result, columns=columns)

encoding 옵션을 "utf-8"로 주니까 한글이 깨져서 "utf-8-sig"로 변경했다.

# CSV 파일로 저장
df.to_csv("file_name", index=False, encoding="utf-8-sig")

remote.close()

쿼리가 길어지다 보니까 좀 헷갈린다.
잘못된 쿼리로 조회해서 강제 종료도 해보고...😂 좋은 경험이었다.