[sql 과제] 02. 유가 데이터 분석

문제 1.

주유소 데이터를 저장할 테이블을 다음의 구조로 생성하세요. (PDF 파일 참고)

import mysql.connector

conn = mysql.connector.connect(
    host = "host",
    port = 3306,
    user = "oneday",
    password = "1234",
    database = "oneday"
)

cursor = conn.cursor(buffered=True)

# gas_brand
sql_b = "CREATE TABLE GAS_BRAND(" + \
            "id int not null auto_increment primary key, " + \
            "name varchar(16) not null)"

cursor.execute(sql_b)

# gas_station
sql_s = "CREATE TABLE GAS_STATION(" + \
            "id int auto_increment primary key, " +\
            "brand int not null, " +\
            "name varchar(64) not null, " +\
            "city char(2) not null, " +\
            "gu varchar(10) not null, " +\
            "address varchar(128) not null, " +\
            "gasoline int not null, " +\
            "diesel int not null, " +\
            "self boolean not null, " +\
            "car_wash boolean not null, " +\
            "charging_station boolean not null, " +\
            "car_maintenance boolean not null, " +\
            "convenience_store boolean not null, " +\
            "24_hours boolean not null, " +\
            "lat decimal(16,14) not null, " +\
            "lng decimal(17,14) not null, " +\
            "foreign key (brand) references GAS_BRAND(id));"

cursor.execute(sql_s)

문제 2.

Python 코드로 GAS_BRAND 데이터를 다음과 같이 입력하고 확인하세요. (PDF 파일 참고)

• '현대오일뱅크'에서 'HD현대오일뱅크'로 명칭 변경됨 -> 데이터에 반영함

📍 오류 발생:

IntegrityError: 1452 (23000): 
Cannot add or update a child row: 
a foreign key constraint fails 
(oneday.GAS_STATION, CONSTRAINT GAS_STATION_ibfk_1 FOREIGN KEY (brand) REFERENCES GAS_BRAND (id)) 

📍 해결:
데이터 입력 확인: 삽입하려는 데이터가 GAS_BRAND 테이블에 존재하는지 확인하세요. 존재하지 않는 브랜드의 id를 GAS_STATION 테이블에 넣으면 이러한 에러가 발생할 수 있습니다.

# 어디에서 오류 발생하고 있는지 확인
print(f"{brand}")

--> 과제에서 제시된 GAS_BRAND의 '현대오일뱅크' 브랜드가 최근 이름을 'HD현대오일뱅크' 로 변경하여, 사이트에서 가져온 정보와 맞지 않아 발생하는 오류였다.

# GAS_BRAND 변경해주기
sqltmp = "UPDATE GAS_BRAND SET name = 'HD현대오일뱅크' WHERE id = 2;"
cursor.execute(sqltmp)
conn.commit()

queries1 = [
    (1, 'SK에너지'),
    (2, 'HD현대오일뱅크'),
    (3, 'GS칼텍스'),
    (4, 'S-OIL'),
    (5, '알뜰주유소'),
    (6, '자가상표')
]

query = "INSERT INTO GAS_BRAND VALUES (%s, %s)"
cursor.executemany(query, queries1)

conn.commit()

제출 1.

• Table 생성 결과 : Desc GAS_BRAND; Desc GAS_STATION;

sql_result = "DESC GAS_STATION"
cursor.execute(sql_result)

result = cursor.fetchall()
for i in result:
    print(i)

제출 2.

• GAS_BRAND 조회 결과 : SELECT * FROM GAS_BRAND;

sql_result = "SELECT * FROM GAS_BRAND"
cursor.execute(sql_result)

result = cursor.fetchall()
for i in result:
    print(i)

문제 3.

다음의 함수와 그 함수를 테스트하는 코드를 작성하세요. (PDF 파일 참고)

• 화폐단위 문자형을 입력받아 숫자형으로 반환하는 함수 (테스트 입력 : ‘1,000’)

# 화폐단위 문자형 -> 숫자형 
def stringToInt(s):
    if s != '':
        s = s.replace(',', '')
        return int(s)
    else: 
        return None
        
stringToInt('1,000')

• 주유소 브랜드를 입력하면 GAS_BRAND 데이터를 참고하여 ID 를 반환하는 함수 (테스트 입력 : ‘SK에너지’)

# 주유소 브랜드를 입력하면 GAS_BRAND 데이터를 참고하여 ID 반환
def getID(brand):
    sql_result = "SELECT * FROM GAS_BRAND"
    cursor.execute(sql_result)
    result = cursor.fetchall()
    for i in result:
        if i[1] == brand:
            return i[0]
        # 브랜드명이 '알뜰(ex)'인 경우 있음
        elif brand == '알뜰(ex)':
            return 5

getID('SK에너지')

• 주소를 입력받아 구 이름을 반환하는 함수 (테스트 입력 : ‘서울시 강남구 헌릉로 730’)

# 주소를 입력받아 구 이름 반환
def getGu(add):
    addList = add.split()
    return addList[1]
    
getGu('서울시 강남구 헌릉로 730')

• 주소를 입력받아 위도, 경도를 반환하는 함수 (테스트 입력 : ‘서울시 강남구 헌릉로 730’)

import googlemaps
gmaps_key = "AIzaSyBn4xqGnCRJRbB-y4uCvBjqNu97pCuXcnc"
gmaps = googlemaps.Client(key = gmaps_key)

# 주소를 입력받아 위도, 경도 반환
def getLL(add):
    tmp = gmaps.geocode(add, language='ko')
    lat = tmp[0].get("geometry")["location"]["lat"]
    lng = tmp[0].get("geometry")["location"]["lng"]

    return lat, lng

getLL('서울시 강남구 헌릉로 730')

문제 4.

Python 코드에서 주유소 페이지에서 데이터를 가져올때, GAS_STATION 테이블에 바로 입력하도록 수정하세요. (앞서 생 성한 함수활용)

• 주의. city 는 ‘서울’ 로 고정, 부가정보 데이터 타입
• 입력된 데이터의 총 갯수를 쿼리하여 결과를 확인합니다. - 입력된 데이터 상위 10개를 쿼리하여 결과를 확인합니다.

제출 4.

• 주유소 데이터 관련 코드 (ipynb)

import time 
from selenium import webdriver
from selenium.webdriver.common.by import By
from bs4 import BeautifulSoup
from tqdm import tqdm_notebook

# 오피넷 -> 구 정보 가져오기
url = 'https://www.opinet.co.kr/searRgSelect.do'
driver = webdriver.Chrome()
driver.get(url)

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:]

sql = "INSERT INTO GAS_STATION (brand, name, city, gu, address, gasoline, diesel, self, " +\
        "car_wash, charging_station, car_maintenance, convenience_store, 24_hours, lat, lng) " +\
        "VALUES (%s, %s, '서울', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

• diesel 값이 없는 주유소가 있어서, diesel column을 null값을 허용하게 변경.

def check(data, tag):
    return 'off' not in data.select_one(tag)['src']

sqltmp = "ALTER TABLE GAS_STATION MODIFY diesel int NULL;"
cursor.execute(sqltmp)
conn.commit()

for gu in tqdm_notebook(gu_names):
    element = driver.find_element(By.ID, 'SIGUNGU_NM0')
    element.send_keys(gu)
    time.sleep(0.5)

    html = driver.page_source
    soup = BeautifulSoup(html, 'html.parser')

    # 검색할 주유소 개수
    cnt = int(driver.find_element(By.ID, 'totCnt').text)

    for i in range(1, cnt+1):

        # 각 주유소 클릭
        station = driver.find_element(By.CSS_SELECTOR, f'#body1 > tr:nth-child({i}) > td.rlist > a')
        station.click()

        html = driver.page_source
        soup = BeautifulSoup(html, 'html.parser')

        data = soup.select('#os_dtail_info')[0]

        # brand
        brand = getID(data.select_one('#poll_div_nm').text)

        # name
        name = data.select_one('.header').text.strip()

        # address
        address = data.select_one('#rd_addr').text

        # gasoline
        gasoline = stringToInt(data.select_one('#b027_p').text)

        # diesel
        diesel = stringToInt(data.select_one('#d047_p').text)

        # self 
        slf = data.select_one('#SPAN_SELF_VLT_YN_ID')
        if type(slf.find('img')) == type(None):
            is_self = False
        else:
            is_self = True

        # car_wash
        car_wash = check(data, '#cwsh_yn')

        # charging_station
        charging_station = check(data, '#lpg_yn')

        # car_maintenance
        car_maintenance = check(data, '#maint_yn')

        # convenience_store
        convenience_store = check(data, '#cvs_yn')

        # 24_hours
        sel24 = check(data, '#sel24_yn')

        tmp = gmaps.geocode(address, language='ko')
        # lat
        lat = tmp[0].get('geometry')['location']['lat']

        # lng
        lng = tmp[0].get('geometry')['location']['lng']

        cursor.execute(sql, (brand, name, gu, address, gasoline, diesel, 
                            is_self, car_wash, charging_station, car_maintenance, convenience_store, sel24, lat, lng))
         
        conn.commit()
        
# 데이터 개수 확인
cursor.execute("select count(*) from GAS_STATION")
result = cursor.fetchall()
print(result[0])

# 데이터 상위 10개 출력
cursor.execute("select * from GAS_STATION limit 10")
result = cursor.fetchall()
for i in result:
    print(i)

문제 5.

시각화 프로젝트를 위하여 다음의 규칙으로 쿼리하여 CSV 파일로 저장합니다. (Python 코드로 작성)

• 전체 데이터를 가져오는데, 주유소 브랜드 아이디 대신 브랜드명이 표시되어야 합니다. (정렬 : 주유소 매장 아이디 순)
• 다음의 형식으로 저장되어야 함 (브랜드 이름, 칼럼 명 주의, id : GAS_STORE.id)

제출 5.

• 시각화 프로젝트 관련 코드 (ipynb), 결과 파일 (csv)

import pandas as pd

sql = "select s.id, b.name 'brand', s.name, s.city, s.gu, s.address, s.gasoline, s.diesel, s.self, " +\
        "s.car_wash, s.charging_station, s.car_maintenance, s.convenience_store, s.24_hours, " +\
        "s.lat, s.lng " +\
        "from GAS_BRAND b, GAS_STATION s " +\
        "where b.id = s.brand ORDER BY s.id"

cursor.execute(sql)
result = cursor.fetchall()

columns = [i[0] for i in cursor.description]

• disel 값이 없는 주유소(https://www.opinet.co.kr/searRgSelect.do)가 있기 때문에 disel column 이 float 로 변환됨
• 파이썬에서 NaN(Not a Number) 값을 float로 처리하기 때문

df = pd.DataFrame(result)
df.columns = columns
df.head()

df.to_csv('./sql2_oil_station_data.csv', index=False, encoding='utf-8')

# 확인
df = pd.read_csv('./sql2_oil_station_data.csv', index_col=0, thousands=',', encoding='utf-8')
df.head()

문제 6.

위도, 경도 정보를 이용하여 미왕빌딩에서 1킬로 이내에 위치한 주유소 정보를 검색하세요.

• 주유소 아이디, 주유소 브랜드명, 주유소 매장명, 주소, 미왕빌딩으로 부터의 거리 (km)

제출 6.

• 쿼리, 결과

# 미왕빌딩 주소: 서울 강남구 강남대로 364
lat, lng = getLL('서울 강남구 강남대로 364')
lat, lng

sql = "select s.id, b.name 'brand', s.name, s.address, " +\
        "(6371 * acos(cos(radians(37.4955525)) * cos(radians(s.lat)) * cos(radians(s.lng) - radians(127.0292924)) + sin(radians(37.4955525)) * sin(radians(s.lat)))) 'distance' " +\
        "from GAS_BRAND b, GAS_STATION s " +\
        "where s.brand = b.id " +\
        "having distance <= 1 " +\
        "order by distance"
cursor.execute(sql)
result = cursor.fetchall()

for i in result:
    print(i)

문제 7.

위도, 경도 정보를 이용하여 미왕빌딩에서 셀프주유가 가능하고 24시간이면서 편의점이 있는 가장 가까운 주유소 10개를 휘 발유 가격이 가장 저렴한 순으로 정렬하여 조회하세요.

• 주유소 아이디, 주유소 브랜드명, 주유소 매장명, 주소, 휘발유 가격, 부가정보 (셀프, 24시간, 편의점 여부), 미왕빌딩으로 부터의 거리 (km)

제출 7.

• 쿼리, 결과

sql = "select * " +\
        "from (select s.id, b.name 'brand', s.name, s.address, s.gasoline, s.self, s.24_hours, s.convenience_store, " +\
        "(6371 * acos(cos(radians(37.4955525)) * cos(radians(s.lat)) * cos(radians(s.lng) - radians(127.0292924)) + sin(radians(37.4955525)) * sin(radians(s.lat)))) 'distance' " +\
        "from GAS_BRAND b, GAS_STATION s " +\
        "where s.brand = b.id and s.self=1 and s.24_hours=1 and s.convenience_store=1 " +\
        "order by distance limit 10) as tmp " +\
        "order by tmp.gasoline;"
cursor.execute(sql)
result = cursor.fetchall()

for i in result:
    print(i)

문제 8.

구별로 주유소 브랜드 별 휘발유 평균가격을 조회하여 저렴한 순으로 출력하세요.

• 구 이름, 주유소 브랜드 이름, 휘발유 평균 가격

제출 8.

• 쿼리, 결과

sql = "select s.gu, b.name, avg(s.gasoline) " +\
        "from GAS_STATION as s, GAS_BRAND as b " +\
        "where b.id = s.brand " +\
        "group by s.gu, b.name " +\
        "order by s.gu, avg(s.gasoline)"

cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

conn.close()