SQL 과제 2

Ⅰ. AWS RDS

1. 주유소 데이터를 저장할 테이블 생성

# aws 연결
import mysql.connector

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

cursor = conn.cursor(buffered=True)

mysql> CREATE TABLE GAS_BRAND
    -> (
    -> id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(16) NOT NULL
    -> );

mysql> CREATE TABLE GAS_STATION
    -> (
    -> id int NOT NULL 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)
    -> );

2. GAS_BRAND 데이터 입력

sql = "INSERT INTO GAS_BRAND (name) VALUES ('SK에너지'), ('현대오일뱅크'), ('GS칼텍스'), ('S-OIL'), ('알뜰주유소'), ('자가상표')"

cursor.execute(sql)
conn.commit()

Ⅱ. 데이터 수집

1. 데이터값 변환 위한 함수 생성

# 화폐단위 문자형 입력받아 숫자형으로 변환하는 함수 
def toInt(str):
    return int(str.replace(',', ''))
    
# 주유소 브랜드를 입력하면 GAS_BRAND 데이터를 참고하여 ID를 반환하는 함수
def getBrandID(brandName):
    if brandName == '알뜰(ex)':
        brandName = '알뜰주유소'

    for item in gas_brand:
        if item['name'] ==  brandName:
            return item['id']
    return -1
    
# 주소를 입력받아 구 이름을 반환하는 함수
def getGuName(address):
    return address.split()[1]
    
# 주소를 입력받아 위도, 경도를 반환하는 함수
import googlemaps

gmaps_key = "AIzaSyAfUt64G3GBz-q0WUk8s4udRolATNQQLD4"
gmaps = googlemaps.Client(key=gmaps_key)

def getLocation(address):
    tmp = gmaps.geocode(address, language='ko')
    lat = tmp[0].get("geometry")["location"]["lat"]
    lng = tmp[0].get("geometry")["location"]["lng"]
    
    return lat, lng

2. 주유소 데이터 수집

def getOption(str):
    return False if '_off' in driver.find_element_by_css_selector(str).get_attribute('src').split('/')[-1] else True

---

import pandas as pd
import time
import googlemaps

gmaps_key = "AIzaSyAfUt64G3GBz-q0WUk8s4udRolATNQQLD4"
gmaps = googlemaps.Client(key=gmaps_key)

# datas = []
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)"

for i in range(len(gu_names)):
    gu_selector = f'#SIGUNGU_NM0 > option:nth-child({i+2})'
    driver.find_element_by_css_selector(gu_selector).click()
    station_items = driver.find_elements_by_css_selector('#body1 > tr') # 주유소 목록

    for idx in range(len(station_items)):
        detail_selector = f'#body1 > tr:nth-child({idx+1}) > td.rlist > a'
        driver.find_element_by_css_selector(detail_selector).click()

        name = driver.find_element_by_css_selector('.header #os_nm').get_attribute('innerText')
        gasoline = toInt(driver.find_element_by_css_selector('#b027_p').get_attribute('innerText'))
        diesel = toInt(driver.find_element_by_css_selector('#d047_p').get_attribute('innerText'))
        address = driver.find_element_by_css_selector('#rd_addr').get_attribute('innerText')
        brand = getBrandID(driver.find_element_by_css_selector('#poll_div_nm').get_attribute('innerText'))
        cwsh_yn = getOption('.service #cwsh_yn')
        lpg_yn = getOption('.service #lpg_yn')
        maint_yn = getOption('.service #maint_yn')
        cvs_yn = getOption('.service #cvs_yn')
        sel24_yn = getOption('.service #sel24_yn')

        try:
            driver.find_element_by_css_selector('#self_icon').get_attribute('alt')
            is_self = True

        except:
            is_self = False

        # address
        address = driver.find_element_by_css_selector('#rd_addr').get_attribute('innerText')

        # gu
        gu = getGuName(address)

        # lat,lng
        lat, lng = getLocation(address)

        cursor.execute(sql, (brand, name, gu, address, gasoline, diesel, is_self, cwsh_yn, lpg_yn, maint_yn, cvs_yn, sel24_yn, lat, lng))
        conn.commit()

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

        # datas.append({
        #     'name': name,
        #     'address': address,
        #     'brand': brand,
        #     'is_self': is_self,
        #     'gasoline': gasoline,
        #     'diesel': diesel,
        #     'car_wash': cwsh_yn,
        #     'charging_station': lpg_yn,
        #     'car_maintenance': maint_yn,
        #     'convenience_store': cvs_yn,
        #     '24_hour': sel24_yn,
        #     'gu': gu,
        #     'lat': lat,
        #     'lng': lng
        # })

        time.sleep(0.2)
    time.sleep(0.5)

driver.quit()
# df = pd.DataFrame(datas)
# df.tail()

3. 입력된 데이터의 총 개수 확인

cursor.execute("SELECT COUNT(*) FROM GAS_STATION")
result = cursor.fetchall()
print(result[0])

4. 입력된 데이터 상위 10개 확인

cursor.execute("SELECT * FROM GAS_STATION LIMIT 10 ")
result = cursor.fetchall()
for row in result:
    print(row)

5. csv파일 저장

• 주유소 브랜드 아이디 대신 브랜드명 표시
• 정렬 : 주요소 매장 아이디 순
• 브랜드 이름, 칼럼명 주의, id: GAS_STORE.id

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()

---

num_fields = len(cursor.description)
field_names = [i[0] for i in cursor.description]

print(field_names)

---

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

df.to_csv('gas_output.csv', index=False, encoding="utf-8")
df.head()  

5. csv파일 저장 (당일 날짜)

# csv 파일 저장
import datetime

now = datetime.datetime.now()
nowDate = now.strftime('%Y%m%d')

df.to_csv(f'./oilstation_oneday_{nowDate}.csv', encoding='utf-8')

# csv 파일 읽기
# stations = pd.read_csv('./oilstation_oneday_20230111.csv', encoding='utf-8')
# stations = pd.read_csv('./oilstation_oneday_20230111.csv', encoding='utf-8', index_col=0)
stations = pd.read_csv('./oilstation_oneday_20230111.csv', encoding='utf-8', thousands=',', index_col=0)
stations

6. 데이터 확인

stations.info()

Ⅲ. 데이터 분석

• 위도, 경도 정보 이용해 미왕빌딩에서 1km 내 위치한 주유소 정보 검색
• 주유소 아이디, 주유소 브랜드명, 주유소 매장명, 주소, 미왕빌딩으로부터의 거리(km)

sql = "SELECT s.id, b.name, s.name, s.address, (6371*acos(cos(radians(37.4955366))*cos(radians(s.lat))*cos(radians(s.lng)-radians(127.0293521))+sin(radians(37.4955366))*sin(radians(s.lat)))) distance " + \
      "FROM GAS_STATION s, GAS_BRAND b WHERE s.brand = b.id HAVING distance <= 1 ORDER BY distance"

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

• 위도, 경도 정보를 이용해 셀프주유가 가능하고 24시간이면서 편의점이 있는 가장 가까운 주유소 10개를 휘발유 가격이 가장 저렴한 순으로 정렬해 조회
• 주유소 아이디, 주유소 브랜드명, 주유소 매장명, 주소, 휘발유 가격, 부가정보 (셀프, 24시간, 편의점 여부), 미왕빌딩으로부터의 거리 (km)

sql = "SELECT t.id, t.brand, t.name, t.address, t.gasoline, t.self, t.24_hours, t.convenience_store, t.distance " + \
      "FROM (SELECT s.id id, b.name brand, s.name name, s.address, s.gasoline, s.self, s.24_hours, s.convenience_store, (6371*acos(cos(radians(37.4955366))*cos(radians(s.lat))*cos(radians(s.lng)-radians(127.0293521))+sin(radians(37.4955366))*sin(radians(s.lat)))) distance " + \
      "      FROM GAS_BRAND b, GAS_STATION s " + \
      "      WHERE b.id = s.brand AND s.self=True AND s.24_hours=True AND s.convenience_store=True " + \
      "      ORDER BY distance LIMIT 10) t " + \
      "ORDER BY t.gasoline"

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

• 구별로 주유소 브랜드별 휘발류 평균가격을 조회하여 저렴한 순으로 출력
• 구 이름, 주유소 브랜드 이름, 휘발류 평균 가격

sql = "SELECT s.gu, b.name, avg(s.gasoline) FROM GAS_BRAND b, GAS_STATION s 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 row in result:
    print(row) 

Ⅳ. 데이터 시각화

import matplotlib.pyplot as plt
import seaborn as sns
import platform
from matplotlib import font_manager, rc

%matplotlib inline
# get_ipython().run_inline_magic(“matplotlib”, “inline”)

path = "c:/Windows/Fonts/malgun.ttf"

if platform.system() == "Darwin":
    rc("font", family = "Arial Unicode MS")
elif platform.system() == "Windows":
    font_name = font_manager.FontProperties(fname=path).get_name()
    rc("font", family=font_name)
else:
    print("Unknown system... sorry~")

1. 휘발유 가격 비교

• boxplot(feat. seaborn)

plt.figure(figsize=(12, 8))
sns.boxplot(x="is_self", y="gasoline", data=stations, palette="Set1")
plt.grid(True)
plt.show()

2. 휘발유 브랜드별 가격 비교

• boxplot(feat. seaborn)

plt.figure(figsize=(12, 8))
sns.boxplot(x="brand", y="gasoline", hue="is_self", data=stations, palette="Set3")
plt.grid(True)
plt.show()

3. 휘발유 구별 가격 비교

• boxplot(feat. seaborn)

plt.figure(figsize=(20, 8))
sns.boxplot(x="gu", y="gasoline", hue="is_self", data=stations, palette="Set3")
plt.grid(True)
plt.show()

4.경유 가격 비교

• boxplot(feat. seaborn)

plt.figure(figsize=(12, 8))
sns.boxplot(x="is_self", y="diesel", data=stations, palette="Set1")
plt.grid(True)
plt.show()

5. 경유 브랜드별 가격 비교

• boxplot(feat. seaborn)

plt.figure(figsize=(12, 8))
sns.boxplot(x="brand", y="diesel", hue="is_self", data=stations, palette="Set3")
plt.grid(True)
plt.show()

Ⅴ. 지도 시각화

import json 
import folium
import warnings
warnings.simplefilter(action="ignore", category=FutureWarning)

1. 지도 시각화용 데이터프레임

import numpy as np 

gu_data = pd.pivot_table(data=stations, index="gu", values="gasoline", aggfunc=np.mean)
gu_data.head()

2. 구별 휘발유 가격 시각화

geo_path = "../data/02. skorea_municipalities_geo_simple.json"
geo_str = json.load(open(geo_path, encoding="utf-8"))

my_map = folium.Map(location=[37.5502, 126.982], zoom_start=10.5)
my_map.choropleth(
    geo_data=geo_str,
    data=gu_data,
    columns=[gu_data.index, "gasoline"],
    key_on="feature.id",
    fill_color="PuRd"
)
my_map