서울시 주유소 데이터 가져오기(SQL)

TaeHwi Kang·2022년 11월 17일
EDAsql
0

1. AWS RDS에 데이터 저장을 위한 작업하기

1) Database oneday에 주유소 테이터를 저장할 테이블 생성

# 작업은 스타벅스와 이디야 데이터를 위해 생성된 oneday 사용

# AWS RDS 접속
import mysql.connector

conn = mysql.connector.connect(
    host = "<database-1>,
    port = 3306,
    user = 'oneday',
    password = '1234',
    database = 'oneday'
)
cursor = conn.cursor(buffered=True)

# GAS_BRAND Table 생성
sql = "CREATE TABLE CAS_BRAND
       (
       id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name varchar(16) NOT NULL
       )"
cursor.excute(sql)

# GAS_STATION Table 생성
sql = "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)
       );"
cursor.excute(sql)

2. 주유소 데이터 가져오기

1) GAS_BRAND에 데이터 넣기

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

cursor.execute(sql)
conn.commit()

2) 데이터값 변환을 위한 함수 만들기

# 문자형을 입력받는 화폐 단위를 숫자형으로 변환
def toInt(str):
    return int(str.replace(',',''))

# 주유소 브랜드를 입력하면 GAS_BRAND 데이터를 참고하여 ID를 반환
def getBrandID(brandName):
	dict_cursor = conn.cursor(dictionary=True)
	dict_cursor.execute("SELECT * FROM GAS_BRAND")
	gas_brand = dict_cursor.fetchall()

    if brandName == '알뜰(ex)':
        brandName = '알뜰주유소'
    
    for item in gas_brand:
        if item['name'] == brandName:
            return item['id']
    return -1

# 주소를 입력받아 위도, 경도를 반환
def getLocation(address):
    import googlemaps
    
    gmaps_key = "AIzaSyBV8WEJnWPgayEEPew8333sPy7LGVbjaAc"
    gmaps = googlemaps.Client(key=gmaps_key)
    
    tmp = gmaps.geocode(address, language='ko')
    lat = tmp[0].get("geometry")["location"]["lat"]
    lng = tmp[0].get("geometry")["location"]["lng"]

    return lat, lng

3) 셀레니움으로 주유소 데이터 가져온 후 GAS_STATION Table에 데이터 넣기

from selenium import webdriver
from bs4 import BeautifulSoup
import time

# 주유소 비교사이트(오피넷) 접근
driver = webdriver.Chrome("./driver/chromedriver.exe")
url = "https://www.opinet.co.kr/searRgSelect.do"
driver.get(url)
time.sleep(1)
driver.get(url)
time.sleep(1)

## 부사정보 선택
# 세차장 선택
xpath_wash ='//*[@id="CWSH_YN"]'
tag_wash = driver.find_element_by_xpath(xpath_wash)
tag_wash.click()

# 경정비 선택
xpath_repair ='//*[@id="MAINT_YN"]'
tag_repair = driver.find_element_by_xpath(xpath_repair)
tag_repair.click()

# 편의점 선택
xpath_store ='//*[@id="CVS_YN"]'
tag_store = driver.find_element_by_xpath(xpath_store)
tag_store.click()

# 24시간 선택
xpath_24h ='//*[@id="SEL24_YN"]'
tag_24h = driver.find_element_by_xpath(xpath_24h)
tag_24h.click()

# 구 데이터 가져오기
gu_list_xpath = driver.find_element_by_xpath('//*[@id="SIGUNGU_NM0"]')
gu_list = gu_list_xpath.find_elements_by_tag_name("option ")
gu_list[1].get_attribute("value")

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

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

for gu in gu_names:
    element = driver.find_element_by_id("SIGUNGU_NM0")
    element.send_keys(gu)
    time.sleep(2)
    
    for num in range(1, int(driver.find_element_by_xpath('//*[@id="totCnt"]').text)+1):
        # 주유소 구 정보
        oil_gu = gu
        
        driver.find_element_by_css_selector("#body1 > tr:nth-child(" + str(num) + ") > td.rlist > a").click()
        driver.find_element_by_xpath('//*[@id="map_div"]/div[4]/div[2]/div[2]/div/div[3]').click()
        time.sleep(1)
        
        oil_req2 = driver.page_source
        oil_soup2 = BeautifulSoup(oil_req2, "html.parser")
        
        # 주유소 매장명
        oil_name = oil_soup2.find("div",{"class":"ollehmap-info-defaultStyle"}).find("label",{"id":"os_nm"}).text
        
        # 주유소 주소
        oil_address = oil_soup2.find("div",{"class":"overflow_gis_detail"}).find("label",{"id":"rd_addr"}).text
        
        # 주유소 브랜드
        oil_brand = getBrandID(oil_soup2.find("div",{"class":"overflow_gis_detail"}).find("label",{"id":"poll_div_nm"}).text)
        
        # 주유소 셀프 여부
        if oil_soup2.find("tbody").find_all("tr")[num-1].find("span"):
            if oil_soup2.find("tbody").find_all("tr")[num-1].find("span").text == "셀프":
                oil_self = True
            else: 
                oil_self = False  
        else: 
            oil_self = False
        
        # 주유소 가솔린 금액
        oil_gasoline = toInt(oil_soup2.find("div",{"class":"gis_detail_info_bcon mgt_15"}).find("label",{"id": "b027_p"}).text)
        
        # 주유소 디젤 금액
        oil_diesel = toInt(oil_soup2.find("div",{"class":"gis_detail_info_bcon mgt_15"}).find("label",{"id": "d047_p"}).text)

		# 주유소 세차장 여부
        if oil_soup2.find("div",{"class","service"}).find("img", {"id":"cwsh_yn"})["src"] == "/images/user/gis/oil_station_service1_01.gif":
            oil_wash= True
        else: 
            oil_wash = False
		
        # 주유소 충전소 여부
        if oil_soup2.find("div",{"class","service"}).find("img", {"id":"lpg_yn"})["src"] == "/images/user/gis/oil_station_service1_02_01.gif":
            oil_charge= True   
        else: 
            oil_charge = False
		
        # 주유소 경정비 여부
        if oil_soup2.find("div",{"class","service"}).find("img", {"id":"maint_yn"})["src"] == "/images/user/gis/oil_station_service1_03.gif":
            oil_repair= True
        else: 
            oil_repair = False
		
        # 주유소 편의점 여부
        if oil_soup2.find("div",{"class","service"}).find("img", {"id":"cvs_yn"})["src"] == "/images/user/gis/oil_station_service1_04.gif":
            oil_store= True
        else: 
            oil_store = False
    	
        # 주유소 24시 운영 여부
        if oil_soup2.find("div",{"class","service"}).find("img", {"id":"sel24_yn"})["src"] == "/images/user/gis/oil_station_service1_05.gif":
            oil_24h= True 
        else: 
            oil_24h = False
        
        # 주유소 위도, 경도 정보
        lat, lng = getLocation(oil_address)
        
        # AWS RDS GAS_STATION Table에 데이터 넣기
        cursor.execute(sql, (oil_brand,oil_name,oil_gu,oil_address,oil_gasoline,oil_diesel,oil_self,oil_wash,oil_charge,oil_repair,oil_store,oil_24h,lat,lng ) )
        conn.commit()
        
        driver.find_element_by_xpath('//*[@id="map_div"]/div[4]/div[2]/div[2]/div/div[6]').click()
        time.sleep(1)

4) 시각화 프로젝트를 위하여 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()

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

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

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

3. SQL 데이터 쿼리 연습

1) 위도, 경도 정보를 이용하여 미왕빌딩(서울특별시 강남구 강남대로 364)에서 1킬로 이내에 위치한 주유소 정보를 검색

# 미왕빌딩 위도, 경도
lat, lng = getLocation("서울특별시 강남구 강남대로 364")

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

# 검색결과
(10, 'SK에너지', 'SK서광주유소', '서울 강남구 역삼로 142', 0.5211768015682327)
(207, '현대오일뱅크', '현대오일뱅크(주)직영 서초제일주유소', '서울 서초구 사임당로 116 (서초동)', 0.8178851523499308)
(12, 'S-OIL', '에쓰-오일㈜직영 개나리주유소', '서울 강남구 언주로 423 (역삼동)', 0.8598884198660339)

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

sql ="SELECT t.id, t.brand, t.name, t.address, t.gasoline, t.distance 
      FROM (SELECT s.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()
conn.close()

for i in result:
    print(i)

 
# 검색결과
# (id, 브랜드, 매장, 주서, 가솔린, 미왕빌딩과의 거리)
(304, 'SK에너지', '행촌주유소', '서울 영등포구 도신로 130 (신길동)', 1595, 10.967313961207195)
(197, '알뜰주유소', '만남의광장주유소', '서울 서초구 양재대로12길 73-71', 1596, 4.117553260842911)
(186, 'SK에너지', '행촌제2주유소', '서울 서대문구 성산로 490 (대신동)', 1633, 10.71904091417879)
(187, '현대오일뱅크', '(주)가재울뉴타운주유소', '서울 서대문구 모래내로 205', 1633, 12.598375682812788)
(168, '현대오일뱅크', '현대오일뱅크㈜직영 신대방셀프주유소', '서울 동작구 시흥대로 616 (신대방동)', 1649, 10.961374990401174)
(253, 'GS칼텍스', '지에스칼텍스(주)가든파이브주유소', '서울 송파구 탄천동로 740', 1673, 5.97334294991482)
(262, 'SK에너지', '백제고분로주유소', '서울 송파구 오금로 143 (방이동)', 1675, 7.937305030965344)
(271, '현대오일뱅크', '현대오일뱅크㈜직영 올림픽셀프주유소', '서울 송파구 위례성대로 188 (오금동)', 1688, 9.180618090099538)
(315, 'SK에너지', 'SK에너지(주) 기린주유소', '서울 영등포구 선유로 270', 1689, 12.360138277778125)
(213, 'SK에너지', '대신석유(주)대우주유소', '서울 서초구 양재대로 173 (양재동)', 1749, 3.705246429450517)
profile
TaeHwi Kang
스터디 노트
이전 포스트

스타벅스와 이디야 데이터 가져오기(SQL)

다음 포스트

Git 내용 정리

0개의 댓글