SQL_Oil Data 가공

• python을 통해 Mysql를 작업하고 데이터를 가공할 수 있도록 데이터 처리 과정을 학습

import mysql.connector.connect(
	host = ,
    port = ,
    user = ,
    password = ,

• 해당 문법을 통해 python에서 sql 연결
  
• 작업을 위해 두 개의 테이블을 만듦
• GAS_BRAND는 foreign key를 연결하기 위해 만들고,
• GAS_STATION은 데이터를 저장할 공간 Table로 생성
  
• 콘솔창에서 mysql 접속 후 확인 결과 정상적으로 데이터 생성 완료
  
• sql에는 sql 문법을 넣고, val에는 넣을 내용을 입력

sql = "insert into <TableName>(column1, column2...) values(1, 2, 3...)
val = []형태 안에 tuple형태로 저장
cursor.execute(sql, val)
conn.commit()

• 위에 문법을 통해 tuple안의 내용을 sql에 저장이 가능
• GAS_BRAND로 사용할 6개의 브랜드 내용을 입력
  
• python에서 해당 내용을 select문으로 확인 결과 정상적으로 입력 완료
  
• 사이트에서 데이터 추출 과정에서 사용할 함수 생성
• ','콤마를 삭제하는 함수, 브랜드 명을 브랜드 넘버로 바꾸는 함수, 행정구 이름을 뽑는 함수, 경위도를 생성하는 함수(googlemap 사용)
  
• 4개의 함수 정상적으로 작동
• 1,000 -> 1000
• SK에너지 -> 1
• '서울시 강남구 헌릉로 730' -> 강남구
• '서울시 강남구 헌릉로 730' -> 37...., 127.....

• 사이트에서 데이터 추출을 위한 서울시 25개 구이름 리스트 생성
  
• 각 데이터를 추출후 담을 변수 생성
• city의 경우 모든 25개 '구'가 서울시이기 때문에 서울로 통일
  
• 해당 내용은 TOY_Project의 Oil_DBA에서 사용한 코드를 그대로 사용
• 해당 사이트 휘발유, 경유, 주소, 부가서비스 정보 등 추출
  
• 해당 사이트에서 html 위치를 확인 후 BeautifulSoup을 통해 웹 데이터 추출

for gu_list in tqdm_notebook(gu_name):
    driver.find_element(By.XPATH, '//*[@id="SIGUNGU_NM0"]').send_keys(gu_list)
    req = driver.page_source
    soup = BeautifulSoup(req, "html.parser")
    time.sleep(1)

    
    for num in range(1, int(soup.select_one("#totCnt").get_text())+1):
        time.sleep(1)
        driver.find_element(By.XPATH, f'//*[@id="body1"]/tr[{num}]/td[1]/a').click()
        req = driver.page_source
        soup = BeautifulSoup(req, "html.parser")

        time.sleep(1)

        brand = make_brandId(soup.select("ul > li > label")[23].get_text())
        brand_raw_name.append(soup.select("ul > li > label")[23].get_text())
        name = soup.select_one("#os_nm").get_text()
        city = '서울'
        gu = gu_list
        address = soup.select_one("#rd_addr").get_text()
        gasoline = make_int(soup.select("td > label")[2].get_text()) 
        diesel = make_int(soup.select("td > label")[4].get_text())
        self = 0


        lanlng = make_LatLng(soup.select_one("#rd_addr").get_text())
        lat = lanlng[0]
        lng = lanlng[1]
        
        if str(soup.select_one("#cwsh_yn")).find("off") > 1:
                car_wash = 0
        else:
                car_wash = 1
                           
        if str(soup.select_one("#lpg_yn")).find("off") > 1:
                charging_station = 0
        else:
                charging_station = 1
                        
        if str(soup.select_one("#maint_yn")).find("off") > 1:
                car_maintenance = 0
        else:
                car_maintenance = 1
                    
        if str(soup.select_one("#cvs_yn")).find("off") > 1:
                convenience_store = 0
        else:
                convenience_store = 1
                           
        if str(soup.select_one("#sel24_yn")).find("off") > 1:
               hour = 0
        else:
               hour = 1
        
        sql = "insert into GAS_STATION (brand, name, city, gu, address, gasoline, diesel, self, car_wash, charging_station, car_maintenance, convenience_store, 24_hour, lat, lng) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        val = (brand, name, city, gu, address, gasoline, diesel, self, car_wash, charging_station, car_maintenance, convenience_store, hour, lat, lng)
        print(val)
        cursor.execute(sql, val)
        conn.commit()

• 처음 for문에 서울시 25개의 구 이름을 넣고,
• 두번째 for문에는 해당 구의 주유소를 하나씩 클릭하며 해당 주유소의 모든 정보를 추출해서 insert sql문법을 사용해 정보를 mysql에 담음
  
• python에서 데이터 확인 결과 정상적으로 추출 및 저장 완료
  
• 이제 Dataframe 생성을 위해 각 빈 리스트 목록을 생성
  
• sql에서 select column1 문법을 통해 column의 데이터를 뽑고 해당 데이터를 리스트에 저장
  
• 각 리스트에 저장한 데이터를 DataFrame으로 변환
• 저장한 Dataframe을 csv파일로 저장

cursor.execute("select id, brand, name, address,
(6371*acos(cos(radians(37.495584))*cos(radians(lat))*
cos(radians(lng)-radians(127.029347))+sin(radians(37.495584))*
sin(radians(lat)))) as distance from GAS_STATION HAVING distance < 1.0")

cursor.fetchall()

• 해당 경위도에서 반경 1km 이내에 주유소를 검색하는 코드를 입력
• 해당 코드는 구글링의 결과...진짜 저걸 어떻게 만들었을까 경이롭다...읽어보니 지구의 반지름 길이 어쩌구저쩌구하는데 매우 대단...
  
• 서울시 미왕빌딩으로부터 반경 1km이내에 3개의 주유소가 존재하며, 해당 주유소의 데이터

cursor.execute("select id, brand, name, address, gasoline, 
self,24_hour,convenience_store,(6371*acos(cos(radians(37.495584))*
cos(radians(lat))*cos(radians(lng)-radians(127.029347))+
sin(radians(37.495584))*sin(radians(lat)))) as distance 
from GAS_STATION where self = 1 and 24_hour = 
1 and convenience_store = 1 having 
distance <= 12.613106807312574 order by gasoline limit 10")

cursor.fetchall()

• 해당 경위도로부터 셀프, 24시간 주유소이면서 가격이 싼 순서대로 10개의 주유소 리스트를 작성
  
• having문을 사용함으로써 조건을 추가하여 10개의 주유소가 가격이 싸며, 셀프 세차 및 24시간이 가능한 주유소 리스트