굉장히 엄청나!

아래 글에 이어서 진행

https://mzero.tistory.com/98

6. 영화별 평점 평균 시각화 (3) - 원형 그래프 그리기

🌳긍정, 부정, 기타에 대한 원형 그래프 시각화하기

fig, axs = plt.subplots(5, 2, figsize=(15, 25))

axs = axs.flatten()

### 빈도 비율별 색상 정의
colors = ["pink", "gold", "whitesmoke"]

### 라벨 정의(파이 쪼개기)
labels_dict = {0 : "부정(1~4점)", 1 : "긍정(8~10점)", 2 : "기타(5~7점)"}

### 긍정/부정에 대한 원형 그래프 그리기
for title, ax in zip(avg_score.keys(), axs) :
    ### 영화별 건수 필터링하기
    num_reviews = len(df_new[df_new["title"] == title])
    # print(f"num_reviews = {num_reviews}")

    ### label 컬럼의 범주별로 갯수 필터링 하기
    # df_new["title"]==title 이 값을 만족하는 데이터만 추출
    values = df_new[df_new["title"]==title]["label"].value_counts()
    #print(f"values = {values}")

    ### 원형 그래프에 표시할 라벨값 정의하기
    # - 영화별로 긍정 또는 부정 또는 기타 중에 하나라도 없으면 처리가 필요함
    label_list = df_new[df_new["title"]==title]["label"].unique()
    labels=[]
    for k in label_list : 
        ### 영화별 실제 존재하는 긍정/부정/기타 라벨 정의하기
        labels.append(labels_dict[k])
    
    ### 각 그래프 제목 넣기
    ax.set_title(f"{title} ({num_reviews}명)", fontsize=15)
    
        ### 원형(pie) 그래프 그리기
    ax.pie(values, 
          # 원형그래프에 표시할 라벨 지정
          labels = labels,
          # 원형 그래프에 표시할 값의 소숫점 자리수 지정
          autopct = "%1.1f%%",
          # 원형 그래프 각 영역의 색상 지정
          colors=colors,
          # 그림자 효과 지정하기
          shadow=True,
          # 그래프의 시작위치를 12시 방향으로
          startangle=90)

### 이미지 저장하기
plt.savefig("./img/긍정_부정_원형그래프_시각화.png")    

plt.show()

🌳 최종 전처리된 데이터는 파일로 관리하기

• 파일명 df_new.csv
• 인덱스 미포함
• 저장 위치 : data 폴더

df_new.to_csv("./data/df_new.csv", index=False)

영화 데이터수집 웹크롤링

크롤링 Crawling
소프트웨어 따위가 웹을 돌아다니며 유용한 정보를 찾아 특정 데이터베이스로 수집해 오는 작업. 또는 그러한 기술

다음 영화 사이트 웹크롤링

• URL : http://movie.daum.net
• 다음영화 > 랭킹 > 박스오피스 > 월간 위치의 데이터 수집
• 수집데이터 : 영화제목, 평점, 댓글
• 생성할 데이터 : 긍정/부정 ( 별점 점수 별 긍정인지 부정인지 판단하는 데이터 생성 )
• 프로그램에서 https://movie.daum.net/ranking/boxoffice/monthly 주소를 열어서 크롬에서 제어
  

웹크롤링 라이브러리

• 정적인 웹크롤링을 할 경우
  • BeautifulSoup : 하나의 페이지에 보이는 부분만 수집할 때 사용
• 동적인 웹크롤링을 할 경우
  • selenium : 클릭과 같은 이벤트 등 페이지 전환을 하면서 수집할 때 사용
• 동적 웹페이지 처리를 위한 라이브러리
  • Prompt에서 설치 필요 : pip install selenium
  • from selenium import webdriver
  • webdriver : 브라우저 자체를 컨트롤 함
• 웹페이지 내에 데이터 추출을 위한 라이브러리
  • from selenium.webdriver.common.by import By
    
  • By : 페이지 안에 있는 html을 컨트롤 함
• 시간 라이브러리
  • import time
  • 웹브라우저에 접근 할 때 페이지에 클릭이라는 동적 이벤트를 발생시킨다. 이때  네트워크 사양에 따라 느리게 실행 될 수 있고  다른 pc보다 빠르게 실행 될 수 있다. 즉, 사양에 따라 페이지 로딩 시간이 다르게 나타난다다. 그 로딩시간에 다른 행동을 하지 못하도록 즉, 외부에서 읽어들일 수 있는 시간을 주기 위해 time 사용

1. 크롬 브라우저 띄우기

• 브라우저 컨트롤

driver = webdriver.Chrome()

• url을 이용하여 페이지 접근
  • get() : 페이지에 접근 후 해당 html 코드 읽어 들이기
  • driver 객체가 모든 정보를 가지고 있음

driver.get("https://movie.daum.net/ranking/boxoffice/monthly")

2. 제목이 있는 부분의 html 태그 경로(패스) 추출하기

• 크롬브라우저 → F12(개발자도구) → 영화제목 마우스 우클릭 → [검사] 클릭 → a 태그에 마우스 위치 후 우클릭 → copy → copy selector 클릭 → 해당 제목의 위치 저장

• 영화 제목이 있는 a 태그 위치 경로 확인
  • li:nth-child(1)에서 :nth-child(1) 이걸 빼면 ol아래 모든 li가 해당 된다.

movie_path = "#mainContent > div > div.box_boxoffice > ol > li > div > div.thumb_cont > strong > a"

• 현재 크롬브라우저에 보이는 영화제목 모두 추출하기
  
  • find_element() : 한건 조회
  • find_elements() : 여러건 조회(리스트 타입으로 반환)
  • By.CSS_SELECTOR : CSS 스타일 경로를 인식할 수 있도록 지정

   movie_elements = driver.find_elements(By.CSS_SELECTOR, movie_path)
    print(f"movie_elements length = {len(movie_elements)}")
    # 리스트 타입 0번째 제목 , 태그와 태그 사이에 text가 들어있다.( .text로 접근 )
    print(f"title[0] =>> {movie_elements[0].text}")
    print(f"movie_elements(제목) = {movie_elements}")

• 웹크롤링 처리가 모두 완료되면, driver 종료해야 한다

driver.quit()

•  try - except로 처리하기

try:
    driver = webdriver.Chrome()

    driver.get("https://movie.daum.net/ranking/boxoffice/monthly")

    movie_path = "#mainContent > div > div.box_boxoffice > ol > li > div > div.thumb_cont > strong > a"
    
    movie_elements = driver.find_elements(By.CSS_SELECTOR, movie_path)
    print(f"movie_elements length = {len(movie_elements)}")
    print(f"title[0] =>> {movie_elements[0].text}")
    print(f"movie_elements(제목) = {movie_elements}")

except Exception as e:
    print(e)
    driver.quit()

finally:
    driver.quit()

3. Click ( ) 이벤트 발생시키기 (1) - 상세 페이지

아래 코드는 모두 for i 문 안에 작성된 코드

• 제목을 클릭 시켜서 상세 페이지로 이동하기
  • 마우스로 제목을 클릭하는 행위와 동일한 코드
  • click() 이벤트 발생

# 제목 10개만 추출하기
    for i in range(10) : 
        title = movie_elements[i].text.strip()
        print(f"No[{i}] / title[{title}] Start----------------")

        ### 제목을 클릭 시켜서 상세 페이지로 이동하기
        movie_elements[i].click()

• 상세페이지로 접근했다라는 정보를 받아오기
  • 실제 상세페이지에 접근
  • window_handles : 페이지가 열릴때 마다 리스트타입으로 윈도우 정보를 순서대로 가지고 있는 객체이다. -1 은 마지막에 접근한 페이지를 의미한다.

      movie_handle = driver.window_handles[-1]
        # - 새로 열린 페이지로 전환하기
        driver.switch_to.window(movie_handle)

• 페이지 로딩 및 코드 읽어들이는 시간을 벌어주기

time.sleep(1)

→ 여기까지 실행하면 웹 페이지 열리고 영화 '서울의 봄' 상세페이지까지 들어갔다가 종료됨

4. Click ( ) 이벤트 발생시키기 (2) - [평점] 

• [평점] 탭 클릭 이벤트 발생 시키기

   tap_score_path = "#mainContent > div > div.box_detailinfo > div.tabmenu_wrap > ul > li:nth-child(4) > a"

• a태그 정보 가지고 오기

 tap_score_element = driver.find_element(By.CSS_SELECTOR, tap_score_path)

• [평점] 탭, 즉 a태그 클릭 이벤트 발생시키기[평점] 페이지로 접근했다라는 정보를 받아오기

	tap_score_handle = driver.window_handles[-1]
        # - 새로 열린 페이지로 전환하기
        driver.switch_to.window(tap_score_handle)

• 페이지 로딩 및 코드 읽어들이는 시간을 벌어주기

        time.sleep(1)

5. 모든 평점 / 리뷰 데이터 추출하기

• 모든 평점 데이터 추출하기

        score_path = "ul.list_comment div.ratings"
        score_lists = driver.find_elements(By.CSS_SELECTOR, score_path)
        print(f"평점 갯수 : {len(score_lists)}")

• 모든 리뷰 데이터 추출하기

        comment_path = "ul.list_comment p.desc_txt"
        comment_lists = driver.find_elements(By.CSS_SELECTOR, comment_path)
        print(f"리뷰 갯수 : {len(comment_lists)}")

6. 평점을 이용하여 긍정 / 부정 값 생성하기

• 평점 또는 리뷰 데이터가 없을 수 있기에 두개 리스트의 갯수 중 작은 값을 사용
• 평점 또는 리뷰가 없으면, 수집에서 제외
• 기존의  for j in range(len(score_lists))으로 for문을 돌릴 때, 리뷰가 없을 경우 len(comment_lists)는  len(score_lists)와 길이가 다르기 때문에 오류가 난다.

for_cnt = 0
        if len(score_lists) < len(comment_lists) :
            for_cnt = len(score_lists)
        elif len(score_lists) > len(comment_lists) :
            for_cnt = len(comment_lists)
        else :
            for_cnt = len(score_lists)

아래 코드는 모두 for j 문 안에 작성된 코드

• 평점, 리뷰 추출하기

 for j in range(for_cnt) : 
            ### 평점 추출하기
            score = score_lists[j].text.strip()
            
            ### 리뷰 추출하기
            comment = comment_lists[j].text.strip().replace("\n", "")
            
            print(f"{title}  \t{score}  \t{comment} \n")

• 평점을 이용해서 긍정/ 부정 데이터 생성
  • 긍정 : 평점이 8이상인 경우로, 긍정값은 1 사용
  • 부정 : 평점이 4이하인 경우로, 부정값은 0 사용
  • 기타 : 나머지, 기타값은 2 사용

   label = 0
            if  int(score) >= 8 :
                label = 1
            elif int(score) <= 4 :
                label = 0
            else :
                label = 2
                
    print(f"{title}  \t{score}  \t{comment} \t{label} \n")

7. 다시 메인으로 이동

아래 코드는 for j 문 밖에 작성된 코드

• 영화 한편에 대한 정보수집이 끝나면 다시 메인으로 이동
• execute_script() : 자바스크립트 문법 처리 함수
  

	driver.execute_script("window.history.go(-2)")
        time.sleep(1)

→ 여기까지 실행하면 영화 10개의 제목, 평점, 리뷰, 긍정/부정 데이터가 출력된다

8. 수집 데이터 저장하기

• 수집데이터 txt 파일로 저장시키기 ( 아래 코드는 for i 문 위에 작성된 코드)

 f = open("./data/movie_reviews.txt", "w", encoding="UTF-8")

• 파일에 쓰기  ( 아래 코드는 for j문 에 작성된 코드)

  f.write(f"{title}\t{score}\t{comment}\t{label}\n")

• 파일 자원 닫기 (아래 코드는 except 문에 작성된 코드)

f.close()

9. 모두 펼치기(더보기) 수행한 뒤 데이터 추출

아래 코드는 for i 문 안에서 모든 평점 데이터 추출하기 전에 작성된 코드

• [평점] 더보기 버튼 클릭하여 모든 평점 보이게 펼치기
  
• 처음에 실행했다가 오류난 이유는 영화의 평점 중에 리뷰가 적히지 않은게 있었음. 6번으로 가서  if 문 추가.

	### - 펼친 갯수 확인 변수
        more_view_cnt = 0

        ### 모두 펼치기(더보기) 수행
         while True :
            try: 
                more_view_path = "#alex-area > div > div > div > div.cmt_box > div.alex_more > button"
                more_view_element = driver.find_element(By.CSS_SELECTOR, more_view_path)

                more_view_element.click()
                
                ### 상세페이지로 접근했다라는 정보를 받아오기
                movie_handle = driver.window_handles[-1]
                # - 새로 열린 페이지로 전환하기
                driver.switch_to.window(movie_handle)
                ### 페이지 로딩 및 코드 읽어들이는 시간을 벌어주기
                time.sleep(1)

                ### 임시로 2번만 반복 처리 후 break 처리
                # if more_view_cnt == 2 :
                #   break
                
                ### 더보기 클릭 횟수 확인을 위해 1씩 증
                more_view_cnt += 1
                
            except Exception as e :
                ### 더이상 더보기 버튼이 보이지 않으면 오류 발생
                # - 오류 발생 시점이 더보기 버튼이 끝나는 시점
                break

        ### 더보기 클릭획수 확인하기
        print(f"더보기 클릭 횟수 : [{more_view_cnt}]")

→ 여기까지 실행하면 영화 10개의 제목과  더보기를 눌러 모두 펼쳤을 때의 평점, 리뷰, 긍정/부정 데이터가 출력된다

데이터 처리 시각화 (2)

https://mzero.tistory.com/89

[Data Wrangling] 데이터 처리 시각화 (1) 글에 이어서 진행

전체 파일 통합하기

• 1건 샘플링 프로세스를 이용하여 전체 파일 통합하기
• 최종 통합 데이더프레임 이름 :  df_bus_card_tot

from datetime import datetime

## 통합 시작 시간
start_date = datetime.today().strftime("%Y-%m-%d %H:%M:%S")

### 최종 통합 데이터프레임 이름 : df_bus_card_tot
df_bus_card_tot = pd.DataFrame()

### 0~79까지 폴더에 접근하기 위한 반복 수행
for i in range(0, 80, 1) :
    file_path = f"./01_data/org/trfcard({i})/trfcard.csv"
    df_bus_card_org = pd.read_csv(file_path)
    # 갯수 잘 반복했는지 확인용
    # print(i, " / ", len(df_bus_card_org))
    
    ### 메타정의서의 영문명, 한글명 컬럼 읽어들이기
    # - 데이터프레임 이름 : df_bus_cart_col_org
    file_path = f'./01_data/org/trfcard({i})/trfcard_columns.xlsx'
    df_bus_card_col_org = pd.read_excel(file_path,
                                        header=2,
                                        usecols="B:C")
    # print(i, " / ", len(df_bus_card_col_org))

    #방법 3
    df_bus_card_col_new_dict = {}
    
    for k, v in zip(df_bus_card_col_org.iloc[:, 0], df_bus_card_col_org.iloc[:, 1]):
        df_bus_card_col_new_dict[k] = v

    ### 컬럼명 변경하기
    # - inplace=True : 변경사항을 메모리에 반영하기
    df_bus_card_org.rename(columns=df_bus_card_col_new_dict, inplace = True)


    ### 데이터프레임 복제하기
    df_bus_card_kor = df_bus_card_org.copy()
    
    ### 1. 승차시각과 하차지각 데이터 타입을 문자열로 변환하기
    # - astype() : 데이터 형변환 함수
    df_bus_card_kor = df_bus_card_kor.astype({'승차시각':'str', '하차시각':'str'})

        df_bus_card = df_bus_card_kor[["승차시각","하차시각","승객연령","환승여부",
                                   "추가운임여부","승차정류장","하차정류장"]].copy()

    ### 3. 승차시각과 하차시각의 데이터타입을 날짜타입으로 변경하기(문자 타입만 datetime으로 변환가능하다.)
    df_bus_card["승차시각"] = pd.to_datetime(df_bus_card_kor.loc[:,"승차시각"])
    df_bus_card["하차시각"] = pd.to_datetime(df_bus_card_kor.loc[:,"하차시각"])

    ### 체류시간(분) 계산 및 컬럼 생성
    df_bus_card["버스내체류시간(분)"] = round((df_bus_card["하차시각"] - \
                                   df_bus_card["승차시각"]).dt.total_seconds()/60, 2)
    ### 5. 기준년도, 기준월, 기준일, 기준시간, 기준시간(분), 컬럼 생성하기
    # - 기준년도
    df_bus_card["기준년도"] = df_bus_card["승차시각"].dt.year
    # - 기준월
    df_bus_card["기준월"] = df_bus_card["승차시각"].dt.month
    # - 기준일
    df_bus_card["기준일"] = df_bus_card["승차시각"].dt.day
    # - 기준시간
    df_bus_card["기준시간"] = df_bus_card["승차시각"].dt.hour
    # - 기준시간(분)
    df_bus_card["기준시간(분)"] = df_bus_card["승차시각"].dt.minute

    #print(f"{i} / {len(df_bus_card)}")

    df_bus_card_tot = pd.concat([df_bus_card_tot, df_bus_card], axis=0, ignore_index=True)

## 통합 종료 시간
end_date = datetime.today().strftime("%Y-%m-%d %H:%M:%S")

print(f"전체 실행 시간 ==> {start_date} ~ {end_date}")
print(f" df_bus_card_tot ==> {len(df_bus_card_tot)}")
df_bus_card_tot

통합 데이터프레임 저장시키기

• 저장 파일 위치 : all 폴더
• 저장 파일 명 : 데이터프레임 변수명과 동일
• 확장자 : csv

save_path = "./01_data/all/df_bus_card_tot.csv"
df_bus_card_tot.to_csv(save_path, index = False)

데이터 설계하기

🫐 테이블 설계하기

• 테이블명, 컬럼명, 컬럼타입, null 여부에 대한 정의가 필요함
• 산출물
  - 테이블 정의서
  - ERD
  - 스크립트 명세서

🫐 테이블 정의서

• 자료형 길이 설정 시 한글은 *2
• double
  소숫점을 가지는 많은 양의 데이터
  정밀도를 요구하는 데이터
  소숫점 아래 값이 많은 데이터

🫐 스크립트 명세서

• SQL에 그대로 작성하여 테이블 생성

Create Table time_power_demand(
	ymd varchar(10) NOT NULL,
   time varchar(4) NOT NULL,
	power DOUBLE NOT null
);

DB 연결하기

🫐 데이터 저장시에 유용한 라이브러리

• sqlalchemy
• 설치 : pip install sqlalchemy

🫐  데이터 저장시에 유용한 라이브러리

• pymysql
• 설치 : conda install -c conda-forge pymysql
• 설치 : pip install pymysql

🫐 Database 연결하기

from sqlalchemy import create_engine

### 연결정보 작성
db_connection_info = "mysql+pymysql://gjuser:dbdb@localhost:3306/gjdb"

### Database 연결하기
db_connection = create_engine(db_connection_info)
db_connection

데이터 프레임을 Table에 저장시키기

🫐 컬럼명 수정

• 데이터프레임의 컬럼명을 테이블의 컬럼명과 같게 수정
• 특정 컬럼명만 수정하고자 할 때
  → df.columns = [{"년도" : "ymd"}]

# - 데이터프레임의 컬럼명 전체수정하기
df.columns = ["ymd", "time", "power"]
df

🫐 데이터 저장하기

df.to_sql(name="time_power_demand",
         con=db_connection,
         index=False,
         if_exists="append")

8760 rows 저장 완료

SQL 조회시 값이 들어가 있는 것을 확인할 수 있다.

🫐 데이터베이스 자원 반환(접속 끊기)

• 연결 종료하기

db_connection.dispose()

데이터 조회하기

🫐 데이터 조회

• 조회시에는 pymysql 라이브러리 사용

🫐데이터베이스 연결

import pymysql
### 접속 정보
# - 접속 허가를 위한 정보
# - 접속 id or 도메인
host = "localhost"
# - 사용자계정
user = "gjuser"
# - 패스워드
password = "dbdb"
# - 데이터베이스명
db = "gjdb"
# - 한글처리
charset = "utf8"
# - 조회시 컬럼명을 동시에 보여줄지 여부 설정
cursorclass = pymysql.cursors.DictCursor

🫐 DB 접속하기

• connect로 서버에 연결 허가를 요청하여 conn에 허가를 받음

try :
    conn = pymysql.connect(host = host,
                           user = user,
                           password = password,
                           db = db,
                           charset = charset,
                           # autocommit = autocommit,
                           cursorclass = cursorclass)
    print("DB접속 성공 >>> ", conn)
except :
    print("DB Server Checking...")

🫐 커서 받아오기

cur = conn.cursor()
cur

🫐 Select 문 생성 후 DB에게 요청 및 응답 받기

• execute가 "Select * from time_power_demand"라는 요청문을 서버에게 가지고 가서 그에 대한 답을 가져 

sql = "Select * from time_power_demand"
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.")

🫐 커서 및 커넥션 정보 반납(종료)하기

try:
    cur.close()
    conn.close()
except :
    print("이미 모든 커서와 접속 정보가 반납되었습니다.")

🎀 Union / Union All

Union

• 조회결과와 조회결과를 행단위로 합치는 기능
• 조회결과들 간의 칼럼의 개수는 동일해야 함
• 조회결과들 간의 데이터 타입을 동일해야 함
• 예시
  - 'member'와 'cart'는 구분자로 쓰임

SELECT 'member', mem_id, mem_name
FROM member
UNION
SELECT 'cart', cart_member, cart_prod
FROM cart;

찾은 행  28개

UNION ALL

• 회원 28명, 주문정보 135건 일 때 아래 코드 실행 시 결과
  UNION : 155건
  UNION ALL : 163건

SELECT 'member', mem_id, mem_name
FROM member
UNION all
SELECT 'cart', cart_member, cart_prod
FROM cart;

• UNION : 중복제거
• UNION ALL : 중복포함
• MINUS : 차집합
• INTERSECT : 교집합

Union 문제

문제 1 - 1번

SELECT mem_id, mem_name, SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY mem_id
UNION
SELECT '', '', SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY '', '';

찾은 행 29개

- 그룹화 하지 않은 SUM은 전체 행의 합이 구해진다.

- Group by ' ', ' ' 는 아무 의미가 없지만 빈 문자열로 컬럼을 지정하면 오류가 나기 때문에 문법상 적어준 것이다.

문제 1 - 2번

• Union 문제 1 - 1번 에서 총 구매금액 순을 내림차순으로 정렬
• 단, 총 구매금액의 총합은 마지막 행에 위치

SELECT A.mem_id, A.mem_name, A.total
FROM 
(SELECT mem_id, mem_name, SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY mem_id
ORDER BY total DESC) A
UNION
SELECT '', '', SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM member 
			left join cart
				ON (mem_id = cart_member)
			left JOIN prod
				ON (cart_prod = prod_id)
GROUP BY '', '';

🎀 Exists / Not Exists

Exists

• 조회결과가 1건이라도 있으면 true, 0건이면 false
• 서브쿼리를 적용(다중컬럼의 다중행 모두 가능)
• 예시
  - 구매내역이 있는 회원만 조회
  - 아래 쿼리 실행시 회원 28명 중 19명이 조회 된다.
  

  SELECT mem_id, mem_name
  FROM member
  WHERE EXISTS(SELECT *
  FROM cart
  WHERE cart_member = mem_id);​

Not Exists

• 예시
  - 구매 (주문) 내역이 없는 회원만 조회
  - 아래 쿼리 실행시 회원 28명 중 9명이 조회 된다.

SELECT mem_id, mem_name
FROM member
WHERE NOT EXISTS (SELECT * FROM cart WHERE mem_id = cart_member);

Exists / Not Exists 문제

문제 1번

• 2005년도 구매내역이 있는 회원 조회하기
• 단, 구매내역이 있는 회원의 총구매금액이 3천만원 이상인 데이터에 대해서만 조회
• 조회컬럼 : 회원아이디, 회원이름, 마일리지
• 방법 1 - Exists 사용

SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS (
		SELECT SUM(cart_qty*prod_sale)
			FROM cart, prod
			WHERE cart_member = mem_id
				AND cart_prod = prod_id
				AND left(cart_no,4) = '2005'
		HAVING SUM(cart_qty*prod_sale) >= 30000000);

• 방법 2 - Inline View 사용

SELECT A.mem_id, A.mem_name, A.mem_mileage
FROM(
	SELECT mem_id, mem_name, mem_mileage, SUM(nvl(cart_qty*prod_sale,0)) AS total
		FROM member 
        	LEFT JOIN cart
				ON ( mem_id = cart_member AND left(cart_no,4) = '2005')
			LEFT JOIN prod
				ON(cart_prod = prod_id)
		GROUP BY mem_id) A
WHERE A.total >= 30000000;

결과는 동일하다.

🎀 UPDATE

• 데이터 수정하기
• UPDATE 할 땐 수정하려는 값이 문제에서 제시한 수정 대상이 맞는지  SELECT ~ WHERE 절로 확인하기
• 수정 및 삭제 시 기준 컬럼은 PK값을 기준으로 행을 조건 처리 해야 함
  
• 예시 1
  - 회원아이디 a001, b001인 회원의 마일리지 값을 2로 수정

UPDATE member set mem_mileage = 2
WHERE mem_id IN ('a001','b001') ;

• 예시 2
  - 구매내역이 있는 회원의 마일리지 값을 3으로 일괄 수정처리하기

UPDATE member SET mem_mileage =3
WHERE EXISTS(SELECT *
FROM cart
WHERE cart_member = mem_id);

UPDATE 문제

문제 1

• Exists 문제 1번 조건에 만족하는 회원에 대해서 마일리지 값을 1000점 부여하기
• 방법 1 - Exists 사용

UPDATE member SET mem_mileage = 1000
WHERE EXISTS (
			SELECT SUM(cart_qty*prod_sale)
				FROM cart, prod
					WHERE cart_member = mem_id
						AND cart_prod = prod_id
						AND left(cart_no,4) = '2005'
					HAVING SUM(cart_qty*prod_sale) >= 30000000);

• 방법 2 - Inline View 사용

UPDATE member SET mem_mileage = (mem_mileage + 1000)
	WHERE mem_id IN(
		SELECT A.mem_id
		FROM (
			SELECT mem_id, mem_name, mem_mileage, SUM(nvl(cart_qty*prod_sale,0)) AS total
			FROM member 
				LEFT JOIN cart
					ON ( mem_id = cart_member AND left(cart_no,4) = '2005')
				LEFT JOIN prod
					ON(cart_prod = prod_id)
			GROUP BY mem_id) A
	WHERE A.total >= 30000000);

OPEN WEATHER를 이용해 날씨 API 불러오기에 이어서 이번엔 유튜브 API 불러오기 도전!

저번에 진행한 프로젝트에서 전기차 관련 영상을 볼 수 있는 페이지를 만들기 위하여 유튜브 API를 사용해 보았다.

내가 구현한 기능은 API를 사용하여 영상을 불러오고 지정된 키워드를 선택하면 해당 키워드와 관련된 영상이 나오는 것이였다.

솔직히 API를 받아와서 영상받아오는건 쉬웠는데 영상 보는 페이지 꾸미는게 더 어려웠다...?

어쨌든 API 불러오는 방법 포스팅 시작!

유튜브 API 받아오기

1. Google Cloud 접속하기

https://console.cloud.google.com/welcome?project=youtube2-402602

2. API 및 서비스 > 프로젝트 선택 > 새 프로젝트 생성

3. 프로젝트 생성 후 검색 창에 YouTube Data API v3 검색 > API 사용 선택

4. API 키 생성하기

사용자 인증 정보 만들기 > API 키를 선택하면 키가 발급된다.

나중에 영상을 불러올 주소를 받으면 해당 주소에 발급받은 키를 넣으면 된다.

5. YouTube API 통해 동영상 리스트 가져오기

사용 설정된  API 및 서비스 > YouTube Data API v3 > API 탐색기에서 사용해 보기

6. 카테고리에서 동영상 > list 선택 후 우측에 사용해 보기 

7. API 통신 테스트

요청 파라미터에 값을 입력 후 EXECUTE 선택

• part : snippet으로 설정하는 경우 영상의 제목, 설명 등을 식별하는 다른 속성을 포함하여 불러온다.
• chart : mostPopular로 설정할 경우 가장 인기있는 동영상을 불러온다.
• maxResults : 불러올 영상의 최대 갯수를 설정한다.
• regionCode : 지정된 지역에서 동영상을 선택한다.

아래와 같이 데이터가 잘 불러와 지는 걸 볼 수 있다.

나중에 자신이 원하는 곳에 영상을 불러오고 싶다면 아래의 주소에서 [YOUR_API_KEY]에 자신이 발급받은 API키를 넣어 입력하면 된다. 위에서 본 데이터와 동일한 정보의 데이터가 JSON 형태로 나온다.

※ 아래의 주소는 이전에 변수를 입력한 창에서 HTTP를 선택하면 GET 방식의 동일한 주소가 나온다.

https://youtube.googleapis.com/youtube/v3/videos?part=snippet%20&chart=mostPopular&maxResults=10&regionCode=kr&key=[YOUR_API_KEY]

Iframe 사용하여 영상 불러오기

1. IFrame Player API 접속 > 플레이어 매개변수 표시

https://developers.google.com/youtube/youtube_player_demo?hl=ko

2. IFrame 삽입 코드 html에 입력

[영상 ID]로 된 곳에 JSON으로 받아본 데이터에서 볼 수 있었던 영상의 ID를 입력하면 해당 영상이 화면에 뜨게 된다.

<iframe id="ytplayer" type="text/html" width="720" height="540" src="https://www.youtube.com/embed/[영상 ID]"
frameborder="0" allowfullscreen>

※ 아래 영상은 위와 같은 방법을 사용해 해당 글 HTML에 적용하여 영상을 불러오고 있는 것이다.

Youtube API 할당량 초과 에러

 영상을 계속 새로 불러오다보면 갑자기 영상이 안불러와 지는 상황이 생길 수 있는데 이는 유튜브 API 일일 할당량 한도를 초과한 것이다. 검색해보니 이럴 때에는 할당량 한도 연장 신청을 해야 하는데 시간이 좀 걸린다고 한다. 나같은 경우는 프로젝트를 진행하면서 바로 영상이 불러와 지는지 계속 확인해야했기 때문에 프로젝트를 아예 새로 생성해서 새로운 API를 받아오는 방법을 선택했다.

애드센스도 지금 몇주 째 승인안해주는데 연장 승인은 또 언제 해주겠어...?

📍Open Weather에서 날씨 API 받아오는 방법

https://mzero.tistory.com/18