녹호두

스파르타코딩클럽 엑셀보다쉬운 SQL (4주) 33기

4주차 - 수강일자 211003

With

subquery 를 각각 써주는 게 아니라 table로 명명시켜서 쿼리문 작성 편의 및 가시성을 높인다.

구조 예시

( subquery )를 하나의 column처럼 사용

with table1 as ( subquery ) t1,
       table2 as ( subquery ) t2
select * from table

주의 점🔥

( subquery ) 마다 약어를 다 지정해줘야 한다.

적용 예시 - 4주 차 1번째 기록에 있는 예시

* 적용 전

select * from ( subquery ) s1
inner join ( subquery ) s2
on s1.column = s2.column

* 적용 후

with table1 as ( subquery ) s1,
	table2 as ( subquery ) s2
select * from s1
inner join s2
on s1.column = s2.column

서브 쿼리가 많아지고 복잡해질수록 with 구문을 쓰는 것이 훨씬 가독성이 좋아진다.

Substringindex

특정 기준으로 문자를 잘라서 원하는 부분만 출력하기

- python의 split과 동일

- SQL은 python과 달리 0,1,2,3으로 세는 게 아니라 1,2,3,4로 센다. 다만 -1이 마지막을 뜻하는 것은 동일하다.

구조 예시

SUBSTRING_INDEX( 자를 칼럼명, '기준 문자', 자른 후 출력할 위치)

적용 예시

email 칼럼에 들어있는 spartacodingclub@naver.com와 같은 이메일들을 @단위로 쪼개서 naver.com 만 보고 싶을 때

방법 1

SUBSTRING_INDEX(email, '@', 2) 

해석 : email을 @을 기준으로 잘라서 2번째에 있는 걸 출력해라

출력 값 : naver.com

방법 2

SUBSTRING_INDEX(email, '@', -1)

해석 : email을 @을 기준으로 잘라서 끝에 있는 걸 출력해라

출력 값 : naver.com

Substring

문자열을 특정 시점부터 특정 지점까지만 출력해라

구조 예시

SUBSTRING( 자를 칼럼명, 시점 위치 숫자로, 마지막 위치 숫자로)

적용 예시

created_at 칼럼에 들어있는 2020-07-07 15:00:22와 같은 일자-시간에서 날짜만 보고 싶을 때

SUBSTRING(created_at,1,10)

해석 : created_at에서 1번째 인 '2'부터 10번째에 위치한 '7'까지 출력해라

출력 값 : 2020-07-07

CASE

특정 조건에 따라 데이터를 구분할 때

- python의 if문과 비슷하다고 보면 편하다.

구조 예시 - case를 두 가지로 나눌 때

select column1, column2,
	case when column3 > ( 값 ) then '출력문구'
    	else '출력문구2' end
from table

구조 예시 - case를 세 가지 이상으로 나눌 때

select column1, column2,
	case when column3 > ( 값 ) then '출력문구'
    	when column3 > ( 값2 ) then '출력문구2'
    	else '출력문구3' end
from table

구조 예시 - 깔끔하게 정리할 때 / 실제 사용할 때

select column1, column2,
	(case when column3 > ( 값 ) then '출력문구'
    		when column3 > ( 값2 ) then '출력문구2'
    		else '출력문구3' end) as Partition
from table

case 구문을 하나로 () 묶어주고 as로 alias 별칭을 만들어준다.

주의 점🔥

조건을 걸 때마다 when을 써준다. when 절이 끝난다고 해서 콤마를 붙이거나 하지 않는다. (파이썬과 다름)

강의 기록

스파르타코딩클럽 엑셀보다쉬운 SQL (4주) 33기

4주차 - 수강일자 211003

Subquery

"쿼리안의 쿼리" 쿼리 여러 개가 포함관계로 구성되어 있다고 보면 편하다.

subquery는 select, from, where 모두에 들어갈 수 있다.

Select subquery

구조 예시

( subquery )를 하나의 column 처럼 사용

select column1, column2, ( subquery ) 
from table

주의 점🔥

( subquery ) 내의 where 절을 통해서 바깥 query와 ( subquery ) 간의 연결지점을 꼭 명시해야 한다.

From subquery

구조 예시

( subquery )를 하나의 table처럼 사용

select * from table t1
inner join ( subquery ) s1
on t1.column = s1.column

select * from ( subquery ) s1
inner join ( subquery ) s2
on s1.column = s2.column

주의 점🔥

( subquery ) 사용할 때마다, table 약어를 지정해줘야 한다!

Where subquery

구조 예시

where 칼럼명 in (subquery)

select * from table
where column in ( subquery )

주의 점🔥

( subquery )의 select 된 column과, 바깥 query의 column명을 동일하게 잡아줘야 연결이 된다.

강의 기록

파이썬 혼자놀기 패키지 (무료 강의)

2일 차 - 수강 일자 210926-29

(1) 기사 크롤링

목표 : 네이버에서 '추석' 뉴스 검색결과의 기사 제목, URL, 언론사 명을 크롤링한다.

전체코드

from bs4 import BeautifulSoup
from selenium import webdriver

driver = webdriver.Chrome('./chromedriver')

url = "https://search.naver.com/search.naver?where=news&sm=tab_jum&query=추석"

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

articles = soup.select('#main_pack > section.sc_new.sp_nnews._prs_nws > div > div.group_news > ul > li')

for article in articles:
    title = article.select_one('div.news_wrap.api_ani_send > div >  a').text 
    url = article.select_one('div.news_wrap.api_ani_send > div >  a')['href'] 
    press = article.select_one('a.info.press').text.split(' ')[0].replace('언론사','') 
    print(title, url, press)

driver.quit()

코드 설명

from bs4 import BeautifulSoup
from selenium import webdriver

library 로 beautifulsoup4, selnium을 사용한다.

사용에 앞서 chrome webdriver를 다운로드 해야한다. (다운로드 방법은 아래의 글 참조)

url = "https://search.naver.com/search.naver?where=news&sm=tab_jum&query=추석"
네이버에서 "추석"을 검색한 뉴스 결과를 url 로 삽입한다.

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

driver에 url을 입력하고, 그안에 소스를 가져와 req에 넣고 beautifulsoup에 적용시킨다.

articles = soup.select('#main_pack > section.sc_new.sp_nnews._prs_nws > div > div.group_news > ul > li')

네이버 검색결과는 구조가
sp_news 1 > ....
sp_news 5 > ...
이렇게되어있음.
그래서 앞에서 이미지 크롤링 한 것처럼 {i}반복구문을 사용할 수 없음
다른 방법은, 해당 기사 위에 위에 있는 ul 의 selector를 copy 해서 그 안의 li를 출력해라 라고 구문을 써주면됨 위 처럼

🔥 검색 포털, 사이트마다의 url 구조가 모두 달라서, 각 페이지마다의 상황을 고려해가면서 코드를 작성해주어야한다. 위 코드로 구글, 다음 등에서 진행하면 error 가 발생한다.

for article in articles:
title = article.select_one('div.news_wrap.api_ani_send > div > a').text 
url = article.select_one('div.news_wrap.api_ani_send > div > a')['href'] 
press = article.select_one('a.info.press').text.split(' ')[0].replace('언론사','') 
print(title, url, press)

- title 내용 : 나온 내용중에서 text만 필요해서 .text를 붙였다 .text는 여기 안붙이고 아래에 title.text로 해도 된다.

- url 내용 : url을 추가했음. url은 dictionary형태로 되어있어서(?) key값인 href를 넣어주니 value값인 url을 뱉어냄. copy selector 해서 붙여넣기

- press 내용 : 서, 가장 낮은단위로 a.info.press를 해보고 안되면 다른거 하려고 했는데 이걸로 돼서 진행함. 안되면 여러가지 시도해야됨. 그리고 .text를 붙여서 텍스트만 가져오고, 띄어쓰기를 기준으로 '선정'이라는 내용이 뒤에 붙어있어서 그부분 split으로 제거하고 앞에만 가져온다음에, replace로 '언론사'를 삭제

driver.quit()

driver 종료함! end.

출력 결과

추석 이후 서울 외곽지역 집값 상승 주도…강북·구로 높은 상승률 http://www.newsis.com/view/?id=NISX20211001_0001600454&cID=10401&pID=10400 뉴시스
반도체의 힘…수출, 추석 휴무에도 65년 만 최대치 https://www.joongang.co.kr/article/25011561 중앙일보
반도체·추석연휴에 현대차 판매 22.3%↓..생산일정 조정 http://news.mt.co.kr/mtview.php?no=2021100115384196008 머니투데이
나주시, 추석 연휴 이후 24명 신규확진...코로나 방역관리 촉각 http://www.sportsseoul.com/news/read/1065993?ref=naver 스포츠서울
추석 후폭풍에 거리두기 유지 ... 결혼식, 돌잔치 등만 인원 추가 허용 https://hankookilbo.com/News/Read/A2021100111130003341?did=NA 한국일보
추석 후 감염 확산세…충북 거리두기 3단계 2주 연장 https://www.news1.kr/articles/?4449640 뉴스1
전국 아파트값 상승폭 주춤 “추석 및 금리 인상 영향” http://www.fnnews.com/news/202109301159398552 파이낸셜뉴스
추석 연휴·대출규제, 아파트 매매·전셋값 상승세 꺾여 http://www.inews24.com/view/1408105 아이뉴스24
서울 일주일째 '요일 최다'…추석 감염확산 현실로(종합) http://www.edaily.co.kr/news/newspath.asp?newsid=02279606629185368 이데일리
'추석후 1주일 만에' 학생 1828명 확진…하루 261명꼴 http://www.newsis.com/view/?id=NISX20210930_0001598924&cID=10201&pID=10200 뉴시스

(2) 크롤링 결과 excel에 저장하기

목표 : 네이버에서 '추석' 뉴스 검색결과의 기사 제목, URL, 언론사 명을 크롤링한 결과물을 엑셀에 저장한다.

전체 코드

from bs4 import BeautifulSoup
from selenium import webdriver
from openpyxl import Workbook

driver = webdriver.Chrome('./chromedriver')

url = "https://search.naver.com/search.naver?where=news&sm=tab_jum&query=추석"

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

articles = soup.select('#main_pack > section.sc_new.sp_nnews._prs_nws > div > div.group_news > ul > li') #copy selector

wb = Workbook()
ws1 = wb.active
ws1.title = "articles"
ws1.append(["제목", "링크", "신문사"])

for article in articles:
    title = article.select_one('div.news_wrap.api_ani_send > div >  a').text 
    url = article.select_one('div.news_wrap.api_ani_send > div >  a')['href']
    press = article.select_one('a.info.press').text.split(' ')[0].replace('언론사','')

    ws1.append([title, url, press])

driver.quit()
wb.save(filename='articles.xlsx')

코드 설명

(1)의 내용을 기반으로 한다.

from openpyxl import Workbook

openpyxl 이라는 library를 사용한다. (설치방법은 아래의 링크 참조)

wb = Workbook()
ws1 = wb.active
ws1.title = "articles"
ws1.append(["제목", "링크", "신문사"])

wb라는 workbook을 만든다.

ws1의 title은 articles이고, 첫행에 제목, 링크, 신문사 열을 추가한다.

ws1.append([title, url, press])

ws1에 title, url, press에 값을 넣어준다. 

wb.save(filename='articles.xlsx')

file을 저장한다. 파일명은 'articles.xlsx'

출력 결과

(3) 기사의 썸네일까지 가져와서 엑셀에 저장하기

목표 : 네이버에서 '추석' 뉴스 검색결과의 기사 제목, URL, 언론사 명, 썸네일주소를 크롤링한다.

전체코드

from bs4 import BeautifulSoup
from selenium import webdriver
from openpyxl import Workbook

driver = webdriver.Chrome('./chromedriver')

url = "https://search.naver.com/search.naver?&where=news&query=추석"

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

articles = soup.select('#main_pack > section.sc_new.sp_nnews._prs_nws > div > div.group_news > ul > li')

wb = Workbook()
ws1 = wb.active
ws1.title = "articles"
ws1.append(["제목", "링크", "신문사", "썸네일"])

for article in articles:
    title = article.select_one('div.news_wrap.api_ani_send > div >  a').text
    url = article.select_one('div.news_wrap.api_ani_send > div >  a')['href']
    press = article.select_one('a.info.press').text.split(' ')[0].replace('언론사','')
    #thumb = article.select_one('div.news_wrap.api_ani_send > div >  a> img')['src']
    thumb = article.select_one('div.news_wrap.api_ani_send > a >img')['src']

    ws1.append([title, url, press, thumb])

driver.quit()
wb.save(filename='articles.xlsx')

다른 내용은 다맞는데 이상하게 thumb내용이 오류가 난다. (해결법 찾아서 수정 추가예정)

#thumb = articles.select_one ~ 에서 div 를 제거하니 정상 실행된다.

출력 결과

스파르타 코딩클럽 5만 원 할인 쿠폰 링크 💵

아래 링크를 통해 가입하시면, 즉시 5만원 할인 쿠폰이 발급됩니다!

파이썬 패키지 import 하는 법 (m1 맥북 ver)

1. pycharm 파이참을 실행 후, 상단의 Pycharm 클릭

2. Pycharm 클릭시 나오는 버튼 중 Preferences 클릭 

단축키 : command + , 

3. 왼쪽 탭에서, 작업중인 Project* 하단에 Python Interpreter 클릭 > 우측에 + 플러스 버튼** 클릭

* 저는 sparta_python 이라는 폴더에 작업 중이라 project 명이 sparta_python으로 나와있습니다.

** pycharm 버전에 따라 + 버튼 위치가 다를 수 있습니다.

4. import 해야하는 package 검색 > install Package 클릭 > 기다림 > 완료

sparta python 웹 크롤링 강의를 위해서는 아래의 3가지 package를 다운로드해야 합니다.

- selenium

- bs4
- dload 

스파르타 코딩클럽 5만 원 할인 쿠폰 링크 💵

아래 링크를 통해 가입하시면, 즉시 5만 원 할인 쿠폰이 발급됩니다!