SQL 학습 과제 2 - 주유소

서울시 주유소 데이터를 database에 저장하고 쿼리하여 가져오기

사용한 모듈

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

gmaps_key = "key"
gmaps = googlemaps.Client(key=gmaps_key)

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

1. 테이블 준비

cur = remote.cursor(buffered=True)

cur.execute("USE oneday")

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

# GAS_STATION
sql = "CREATE TABLE GAS_STATION (id INT NOT NULL AUTO_INCREMENT, \
    brand int, name VARCHAR(64) NOT NULL, city char(2), gu VARCHAR(10), \
    address VARCHAR(128), gasoline int, diesel int, self boolean, \
    car_wash boolean, charging_station boolean, car_maintence_store boolean, \
    convenience_store boolean, 24_hours boolean, lat DECIMAL(16, 14), lng DECIMAL(17, 14), \
    primary key (id), \
    CONSTRAINT fk_gas_brand FOREIGN KEY (brand) references GAS_BRAND(id))"
cur.execute(sql)

sql = "INSERT INTO GAS_BRAND VALUES (1, 'SK에너지')"
cur.execute(sql)

sql = "INSERT INTO GAS_BRAND VALUES (2, '현대오일뱅크')"
cur.execute(sql)

sql = "INSERT INTO GAS_BRAND VALUES (3, 'GS칼텍스')"
cur.execute(sql)

sql = "INSERT INTO GAS_BRAND VALUES (4, 'S-OIL')"
cur.execute(sql)

sql = "INSERT INTO GAS_BRAND VALUES (5, '알뜰주유소')"
cur.execute(sql)

sql = "INSERT INTO GAS_BRAND VALUES (6, '자가상표')"
cur.execute(sql)

remote.commit()

2. 함수 만들기

# 화폐 단위 문자형을 입력 받아 숫자형으로 반환하는 함수
def to_int(str):
    if str == "":
        return 0
    return int(str.replace(",", ""))

# 주유소 브랜드를 입력하면 GAS_BRAND 데이터를 참고하여 ID를 반환하는 함수
def get_brand_id(name):
    cur = remote.cursor(buffered=True)
    
    if name == "알뜰(ex)":
        name = "알뜰주유소"
        
    if name == "HD현대오일뱅크":
        name = "현대오일뱅크"
        
    sql = "SELECT id FROM GAS_BRAND WHERE name = '{}'".format(name)
    
    try:
        cur.execute(sql)
        result = cur.fetchall()
        return result[0][0]
    except Exception as e:
        print(name, e)

# 주소를 입력받아 구 이름을 반환하는 함수
def get_gu(address):
    return address.split(" ")[1]

# 주소를 입력받아 위도, 경도를 반환하는 함수
def get_lat_lng(address):
    try:
        tmp = gmaps.geocode(address, language="ko")[0]
        lat = tmp.get("geometry")["location"]["lat"]
        lng = tmp.get("geometry")["location"]["lng"]
        return [lat, lng]
    except Exception as e:
        print(address, e)
        return [0, 0]

3. 홈페이지에서 데이터 가져오기

앞에서 만든 함수를 활용해야 한다.
코드에 이미 구 이름에 대한 변수가 있어서, get_gu() 함수는 사용하지 않았다.

코드는 EDA 학습과제를 할 때 작성한 것을 가져와서 일부분만 수정했다.
이전에는 self 여부를 확인하는 코드를 따로 뺐는데, 다시 보니 주유소 정보 상세창에도 셀프 아이콘이 있었다!😂 코드 작성하는 것도 글쓰기랑 비슷한 것 같다. 다시 보면 이전에 보이지 않던 게 보이니 말이다.

url = "https://www.opinet.co.kr/searRgSelect.do"
driver = webdriver.Chrome(executable_path="경로")
driver.get(url)
driver.maximize_window()

# 시/도
sido_list_raw = driver.find_element(By.ID, "SIDO_NM0")
sido_list = sido_list_raw.find_elements(By.TAG_NAME, "option")

# 서울 선택
seoul_select = sido_list[1].get_attribute("value")
sido_list_raw.send_keys(seoul_select)

# 구 리스트 만들기
gu_list_raw = driver.find_element(By.ID, "SIGUNGU_NM0")
gu_list = gu_list_raw.find_elements(By.TAG_NAME, "option")

gu_names = [option.get_attribute("value") for option in gu_list]
gu_names = gu_names[1:]

# 데이터 가져오기
n = 1
for gu in tqdm_notebook(gu_names):
    driver.find_element(By.ID, "SIGUNGU_NM0").send_keys(gu)
    time.sleep(3)
    
    # 주유소별
    station_list_raw = driver.find_element(By.ID, "body1")
    station_list = station_list_raw.find_elements(By.TAG_NAME, "tr")
    
    for station in station_list:
        
        # 주유소 클릭
        station.find_element(By.TAG_NAME, "a").click()
        req = driver.page_source
        soup = BeautifulSoup(req, "html.parser")

        # 주유소명
        name = soup.select_one("#os_nm").text
        
        # 주소
        address = soup.select_one("#rd_addr").text
        
        # 브랜드
        brand = get_brand_id(soup.select_one("#poll_div_nm").text)
        
        # 셀프 여부     
        if soup.select("#self_icon"):
            is_self = 1
        else:
            is_self = 0
            
        # 휘발유 가격
        gasoline = soup.select_one("#b027_p").text
        
        # 경유 가격
        diesel = soup.select_one("#d047_p").text
        
        # 세차장 여부
        if "off" in soup.select_one("#cwsh_yn").get("src"):
            car_wash = 0
        else:
            car_wash = 1
        
        # 충전소 여부
        if "off" in soup.select_one("#lpg_yn").get("src"):
            charging_station = 0
        else:
            charging_station = 1
        
        # 경정비 여부
        if "off" in soup.select_one("#maint_yn").get("src"):
            car_maintence_store = 0
        else:
            car_maintence_store = 1
        
        # 편의점 여부
        if "off" in soup.select_one("#cvs_yn").get("src"):
            convenience_store = 0
        else:
            convenience_store = 1
                    
        # 24시간 운영 여부
        if "off" in soup.select_one("#sel24_yn").get("src"):
            all_day = 0
        else:
            all_day = 1

        geo = get_lat_lng(address)
        lat = geo[0]
        lng = geo[1]
                
        sql = "INSERT INTO GAS_STATION VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        cur.execute(sql, (n, brand, name, '서울', gu, address, to_int(gasoline), to_int(diesel), is_self, car_wash, charging_station, car_maintence_store, convenience_store, all_day, lat, lng))
        n += 1

# 드라이버 종료
driver.quit()

# 커밋
remote.commit()

# 입력된 데이터의 총 개수 확인
cur.execute("SELECT COUNT(*) FROM GAS_STATION")
cur.fetchall()

# 입력된 데이터 상위 10개 확인
cur.execute("SELECT * FROM GAS_STATION LIMIT 10")

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

sql = "SELECT * FROM (SELECT s.id id, b.name brand, s.name name, s.city city, s.gu gu, s.address address, s.gasoline gasoline, \
    s.diesel diesel, s.self self, s.car_wash car_wash, s.charging_station charging_station, s.car_maintence_store car_maintence_store, \
    s.convenience_store convenience_store, s.24_hours 24_hours, s.lat lat, s.lng lng FROM GAS_BRAND b, GAS_STATION s WHERE b.id = s.brand) t\
    ORDER BY t.id"
cur.execute(sql)
result = cur.fetchall()

# 컬럼명 가져오기
columns = [i[0] for i in cur.description]

# DataFrame으로 변환 후 CSV 파일로 저장
df = pd.DataFrame(data=result, columns=columns)
df.to_csv("[DS]sql2_chasuhui.csv", index=False, encoding="utf-8-sig")

4. 쿼리

4-1. OO빌딩에서 1km 이내에 위치한 주유소 조회

• POINT(경도, 위도)
• SET @location = POINT(경도, 위도) : 기준이 되는 위치 설정
• ST_DISTANCE_SPHERE(POINT, POINT) : 두 좌표 간 거리(단위: m)

# 기준 위치 설정(빌딩)
cur.execute("SET @location = POINT(127.029340, 37.495599)")

cur.execute("SELECT * FROM (SELECT s.id id, b.name brand, s.name name, address, \
    ST_DISTANCE_SPHERE(@location, POINT(lng, lat))/1000 distance \
    FROM GAS_BRAND b, GAS_STATION s WHERE b.id = s.brand) t \
    WHERE distance*1000 <= 1000")

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

4-2. OO빌딩에서 셀프주유가 가능하고 24시간이면서 편의점이 있는 가장 가까운 주유소 10개를, 휘발유 가격이 가장 저렴한 순으로 정렬하여 조회

제대로 이해한 게 맞다면...

cur.execute("SELECT * FROM (SELECT s.id id, b.name brand, s.name name, address, \
    gasoline, self, 24_hours, convenience_store, \
    ST_DISTANCE_SPHERE(@location, POINT(lng, lat))/1000 distance \
    FROM GAS_BRAND b, GAS_STATION s \
    WHERE b.id = s.brand and self = 1 and 24_hours = 1 and convenience_store = 1 \
    ORDER BY distance LIMIT 10) t \
    ORDER BY gasoline")

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

4-3. 구별로 주유소 브랜드별 휘발유 평균가격이 저렴한 순으로 정렬하여 조회

cur.execute("SELECT gu, b.name brand, avg(gasoline) avg_price \
    FROM GAS_BRAND b, GAS_STATION s \
    WHERE b.id = s.brand GROUP BY gu, brand ORDER BY avg_price")

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

remote.close()