1. AWS RDS (MySQL) 에 프로젝트 관련 Database 를 생성하고, 접근 가능한 사용자 계정을 생성하세요.
AWS RDS에 데이터베이스를 생성하고 이곳에 접근하여 oneday 데이터베이스와 oneday 유저를 생성하고 권한을 grant를 사용하여 외부에서 접근이 가능하도록 데이터베이스를 설정해줫습니다.
2. 스타벅스 이디야 데이터를 저장할 테이블을 다음의 구조로 생성하세요. (PDF 파일 참고)
create table COFFEE_STORE VALUES()
create table COFFEE_BRAND VALUES()
를 통해 PDF 파일에서 제시한 대로 테이블을 생성해줬습니다.
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
+---------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| brand | int | YES | MUL | NULL | |
| name | varchar(32) | YES | | NULL | |
| gu_name | varchar(128) | YES | | NULL | |
| lat | decimal(16,14) | YES | | NULL | |
| lng | decimal(17,14) | YES | | NULL | |
+---------+----------------+------+-----+---------+----------------+3. Python 코드로 COFFEE_BRAND 데이터를 다음과 같이 입력하고 확인하세요. (PDF 파일 참고)
insert into COFFEE_BRAND VALUES (1, 'STARBUCKS')
insert into COFFEE_BRAND VALUES (2, 'EDIYA')
+----+----------+
| id | name |
+----+----------+
| 1 | STARBUCK |
| 2 | EDIYA |
+----+----------+FOREGIN KEY를 만들어 주기 위해 다음과 같이 ID 값과 NAME값을 넣어 주었습니다.
4. 스타벅스 페이지에 접근하는 코드에서 팝업창이 없는 경우, 팝업창을 닫는 코드에서 에러가 발생합니다.
팝업창이 생기지 않아서 에러는 나오지않았으나 나왔다고 가정하고 try ~ except문을 사용하여
try:
# 팝업창을 닫는 코드
driver.find_element_by_css_selector('/holyday_notice_close a').click
except NoSuchElementException as e:
# NoSuchElementException이 발생한 경우에 대한 예외처리
print("팝업창이 없습니다. 에러 메시지:", e)에러가 나오지 않도록 하였습니다.
5. Python 코드로 스타벅스 페이지에서 데이터를 가져올때, COFFEE_STORE 테이블에 바로 입력하도록 수정하세요. 🍺🍺
- 데이터 세트: 매장 이름, 매장이 위치한 구 이름, 매장 주소, 위도, 경도
- 필요한 데이터를 한세트씩 가져와서 COFFEE_STORE 테이블에 각각INSERT 하도록 합니다.
- 입력된 데이터의 총 갯수를 쿼리하여 결과를 확인합니다.
- 입력된 데이터 상위 10개를 쿼리하여 결과를 확인합니다.
우선 스타벅스의 매장 이름, 매장이 위치한 구 이름, 매장 주소, 위도, 경도 데이터를 크롤링 하였습니다.
sql = 'insert into COFFEE_STORE values (%s, %s, %s, %s, %s, %s, %s)'
n = 1
# 전체 코드 불러오기
# 홈페이지를 크롤링함
content = driver.page_source
# bs4를 사용하여 파싱
soup = BeautifulSoup(content, 'html.parser')
# 데이터 프레임 만들기 위한 컬럼 만들기
datas = []
for row in tqdm_notebook(seoul_list):
datas = []
datas.append(n)
datas.append(1)
datas.append(row.get('data-name'))
adr = row.select_one('p').text[:-9]
tam = re.split(' ', adr)
datas.append(tam[1])
datas.append(adr)
datas.append(row.get('data-lat'))
datas.append(row.get('data-long'))
cursor.execute(sql, tuple(datas))
print(tuple(datas))
conn.commit()
n += 1다음과 같이 크롤링 후 조건대로 데이터베이스에 데이터를 넣어줍니다.
+----------+
| count(*) |
+----------+
| 612 |
+-------
+----+-------+-----------------+---------+--------------------------------------------+-------------------+--------------------+
| id | brand | name | gu_name | address | lat | lng |
+----+-------+-----------------+---------+--------------------------------------------+-------------------+--------------------+
| 1 | 1 | 역삼아레나빌딩 | 강남구 | 서울특별시 강남구 언주로 425 (역삼동) | 37.50108700000000 | 127.04306900000000 |
| 2 | 1 | 논현역사거리 | 강남구 | 서울특별시 강남구 강남대로 538 (논현동) | 37.51017800000000 | 127.02222300000000 |
| 3 | 1 | 신사역성일빌딩 | 강남구 | 서울특별시 강남구 강남대로 584 (논현동) | 37.51393090000000 | 127.02060570000000 |
| 4 | 1 | 국기원사거리 | 강남구 | 서울특별시 강남구 테헤란로 125 (역삼동) | 37.49951700000000 | 127.03149500000000 |
| 5 | 1 | 대치재경빌딩 | 강남구 | 서울특별시 강남구 남부순환로 2947 (대치동) | 37.49466800000000 | 127.06258300000000 |
| 6 | 1 | 봉은사역 | 강남구 | 서울특별시 강남구 봉은사로 619 (삼성동) | 37.51500000000000 | 127.06319600000000 |
| 7 | 1 | 압구정윤성빌딩 | 강남구 | 서울특별시 강남구 논현로 834 (신사동) | 37.52279340000000 | 127.02860090000000 |
| 8 | 1 | 코엑스별마당 | 강남구 | 서울특별시 강남구 영동대로 513 (삼성동) | 37.51015000000000 | 127.06027500000000 |
| 9 | 1 | 삼성역섬유센터R | 강남구 | 서울특별시 강남구 테헤란로 518 (대치동) | 37.50775000000000 | 127.06065100000000 |
| 10 | 1 | 압구정R | 강남구 | 서울특별시 강남구 언주로 861 (신사동) | 37.52736690000000 | 127.03306100000000 |
+----+-------+-----------------+---------+--------------------------------------------+-------------------+--------------------+---+
입력된 데이터
6. Python 코드로 이디야 페이지에서 데이터를 가져올때, COFFEE_STORE 테이블에 바로 입력하도록 수정하세요.🍺🍺
- 데이터 세트 : 매장 이름, 매장이 위치한 구 이름, 매장 주소, 위도, 경도
- 이디야 페이지에서 검색에 사용할 구 이름은 COFFEE_STORE 에서 중복을 제거하는 쿼리를 사용하여 가져와서 {‘서울 ‘ + 구이름} 형식으로 변환하여 사용하도록 합니다.
- 필요한 데이터를 한 세트씩 가져와서 COFFEE_STORE 테이블에 각각 INSERT 하도록 합니다.
- (주의) COFFEE_STORE 테이블에 입력할 구 이름은 {‘서울 ‘} 이 제거된 구 이름입니-다.
- 입력된 데이터의 총 갯수를 쿼리하여 결과를 확인합니다.
- 입력된 데이터 상위 10개를 쿼리하여 결과를 확인합니다.
스타벅스 데이터와 마찬가지로 이디야 커피의 매장 이름, 매장이 위치한 구 이름, 매장 주소, 위도, 경도 데이터를 크롤링해줍니다. 하지만 이디야 커피는 홈페이지에 위도, 경도 데이터가 회손 되어있어서 GOOGLE MAP을 이용하여 크롤링합니다.
for i in gu_list:
keyword = driver.find_element(By.CSS_SELECTOR, '#keyword')
keyword.clear()
keyword.send_keys(i)
driver.find_element(By.CSS_SELECTOR, '#keyword_div > form > button').click()
time.sleep(1)
html = driver.page_source
# bs4를 사용하여 파싱
soup = BeautifulSoup(html, 'html.parser')
contents = soup.select('#placesList li')
for row in contents:
# 지점명
if n == 919 or n == 942 or n == 1010 or n == 1042 or n == 1075:
datas = []
datas.append(n)
datas.append(2)
datas.append(row.select_one('dt').text)
# 구
idya = row.select_one('dd').text
gu2 = re.split(' ', idya)
datas.append(gu2[1])
# 주소
datas.append(row.select_one('dd').text)
# 위도 경도
adr = gu2[0] + gu2[1] + gu2[2] + gu2[3]
tmp = gmaps.geocode(adr, language='ko')
lat = tmp[0].get('geometry')['location']['lat']
lat = round(lat, 7)
lng = tmp[0].get('geometry')['location']['lng']
lng = round(lng, 7)
datas.append(lat)
datas.append(lng)
cursor.execute(sql, tuple(datas))
print(tuple(datas))
conn.commit()
else:
datas = []
datas.append(n)
datas.append(2)
datas.append(row.select_one('dt').text)
# 구
idya = row.select_one('dd').text
gu2 = re.split(' ', idya)
datas.append(gu2[1])
# 주소
datas.append(row.select_one('dd').text)
# 위도 경도
tmp = gmaps.geocode(idya, language='ko')
lat = tmp[0].get('geometry')['location']['lat']
lat = round(lat, 7)
lng = tmp[0].get('geometry')['location']['lng']
lng = round(lng, 7)
datas.append(lat)
datas.append(lng)
cursor.execute(sql, tuple(datas))
print(tuple(datas))
conn.commit()
n += 1또 주소를 크롤링 할때 오류가 나는 구간이 있어 다음과 같이 IF문으로 분기를 나눠주었습니다.
+----------+
| count(*) |
+----------+
| 1222 |
+----------+
+-----+-------+------------------------+---------+---------------------------------------------------+-------------------+--------------------+
| id | brand | name | gu_name | address | lat | lng |
+-----+-------+------------------------+---------+---------------------------------------------------+-------------------+--------------------+
| 613 | 2 | 강남YMCA점 | 강남구 | 서울 강남구 논현동 | 37.51367870000000 | 127.03171240000000 |
| 614 | 2 | 강남구청역아이티웨딩점 | 강남구 | 서울 강남구 학동로 338 (논현동, 강남파라곤) | 37.51655130000000 | 127.04013910000000 |
| 615 | 2 | 강남논현학동점 | 강남구 | 서울 강남구 논현로131길 28 (논현동) | 37.51518990000000 | 127.02755390000000 |
| 616 | 2 | 강남대치점 | 강남구 | 서울 강남구 역삼로 415 (대치동, 성진빌딩) | 37.50143400000000 | 127.05232800000000 |
| 617 | 2 | 강남도산점 | 강남구 | 서울 강남구 도산대로37길 20 (신사동) | 37.52228210000000 | 127.03147990000000 |
| 618 | 2 | 강남율현점 | 강남구 | 서울 강남구 밤고개로21길 8 (율현동, 세곡프라자) | 37.47358220000000 | 127.10783110000000 |
| 619 | 2 | 강남자곡점 | 강남구 | 서울 강남구 자곡로 180 (자곡동, 강남유탑유블레스) | 37.47348460000000 | 127.10309790000000 |
| 620 | 2 | 개포동역점 | 강남구 | 서울 강남구 개포로82길 11 (개포동, 삼우빌딩) | 37.48893480000000 | 127.06793520000000 |
| 621 | 2 | 건설회관점 | 강남구 | 서울 강남구 언주로 711 (논현동) | 37.51630030000000 | 127.02991230000000 |
| 622 | 2 | 대청역점 | 강남구 | 서울 강남구 개포로109길 34 (개포동, 대청프라자) | 37.49421560000000 | 127.07761700000000 |
+-----+-------+------------------------+---------+---------------------------------------------------+-------------------+--------------------+마지막으로 데이터베이스의 저장값을 조건대로 확인해 줍니다.
7. Python 코드에서 다음의 데이터를 쿼리를 사용하여 조회하세요.
- 스타벅스 매장 주요 분포 지역 (매장수가 많은 상위 5개 구이름, 매장 개수 출력)
- 이디야 매장 주요 분포 지역 (매장수가 많은 상위 5개 구이름, 매장 개수 출력)
- 구별 브랜드 각각의 매장 개수 조회 (구이름, 브랜드이름, 매장 개수 출력)
- 구별 브랜드 각각의 매장 개수 조회 (구이름, 스타벅스 매장 개수, 이디야 매장 개수 출력)
매장 주요 분포 지역 (매장수가 많은 상위 5개 구이름, 매장 개수 출력)
select brand, gu_name, COUNT(gu_name) AS cnt from COFFEE_STORE where brand = 2 GROUP BY gu_name order by cnt desc limit 5;
+-------+----------+-----+
| brand | gu_name | cnt |
+-------+----------+-----+
| 2 | 강남구 | 39 |
| 2 | 영등포구 | 39 |
| 2 | 송파구 | 35 |
| 2 | 강서구 | 31 |
| 2 | 마포구 | 31 |
+-------+----------+-----+-+-----+구별 브랜드 각각의 매장 개수 조회 (구이름, 브랜드이름, 매장 개수 출력)
SELECT
-> gu_name,
-> CASE
-> WHEN brand = 1 THEN 'STARBUCKS'
-> WHEN brand = 2 THEN 'EDIYA'
-> END AS brand_name,
-> COUNT(gu_name) AS gu_name_count
-> FROM COFFEE_STORE
-> GROUP BY brand_name, gu_name;
+----------+------------+---------------+
| gu_name | brand_name | gu_name_count |
+----------+------------+---------------+
| 강남구 | EDIYA | 39 |
| 강동구 | EDIYA | 23 |
| 강북구 | EDIYA | 12 |
| 강서구 | EDIYA | 31 |
| 관악구 | EDIYA | 26 |
| 광진구 | EDIYA | 19 |
| 구로구 | EDIYA | 24 |
| 금천구 | EDIYA | 19 |
| 노원구 | EDIYA | 18 |
| 도봉구 | EDIYA | 21 |
| 동대문구 | EDIYA | 23 |
| 동작구 | EDIYA | 20 |
| 마포구 | EDIYA | 31 |
| 서대문구 | EDIYA | 16 |
| 서초구 | EDIYA | 29 |
| 성동구 | EDIYA | 19 |
| 성북구 | EDIYA | 26 |
| 송파구 | EDIYA | 35 |
| 양천구 | EDIYA | 19 |
| 영등포구 | EDIYA | 39 |
| 용산구 | EDIYA | 13 |
| 은평구 | EDIYA | 23 |
| 종로구 | EDIYA | 28 |
| 중구 | EDIYA | 30 |
| 중랑구 | EDIYA | 27 |
| 강남구 | STARBUCKS | 90 |
| 강북구 | STARBUCKS | 6 |
| 강서구 | STARBUCKS | 27 |
| 관악구 | STARBUCKS | 12 |
| 광진구 | STARBUCKS | 18 |
| 금천구 | STARBUCKS | 13 |
| 노원구 | STARBUCKS | 14 |
| 도봉구 | STARBUCKS | 7 |
| 동작구 | STARBUCKS | 11 |
| 마포구 | STARBUCKS | 36 |
| 서대문구 | STARBUCKS | 22 |
| 서초구 | STARBUCKS | 48 |
| 성북구 | STARBUCKS | 15 |
| 송파구 | STARBUCKS | 36 |
| 양천구 | STARBUCKS | 17 |
| 영등포구 | STARBUCKS | 42 |
| 은평구 | STARBUCKS | 14 |
| 종로구 | STARBUCKS | 40 |
| 중구 | STARBUCKS | 54 |
| 강동구 | STARBUCKS | 17 |
| 구로구 | STARBUCKS | 14 |
| 동대문구 | STARBUCKS | 12 |
| 성동구 | STARBUCKS | 14 |
| 용산구 | STARBUCKS | 25 |
| 중랑구 | STARBUCKS |
``` 8 |
+----------+------------+---------------+구 | 2 | 27 |
+----------+-------+--------+구별 브랜드 각각의 매장 개수 조회 (구이름, 스타벅스 매장 개수, 이디야 매장 개수 출력)
SELECT
-> gu_name,
-> SUM(CASE WHEN brand = 1 THEN 1 ELSE 0 END) AS 스타벅스,
-> SUM(CASE WHEN brand = 2 THEN 1 ELSE 0 END) AS 이디야
-> FROM COFFEE_STORE
-> GROUP BY gu_name;
+----------+----------+--------+
| gu_name | 스타벅스 | 이디야 |
+----------+----------+--------+
| 강남구 | 90 | 39 |
| 강동구 | 17 | 23 |
| 강북구 | 6 | 12 |
| 강서구 | 27 | 31 |
| 관악구 | 12 | 26 |
| 광진구 | 18 | 19 |
| 구로구 | 14 | 24 |
| 금천구 | 13 | 19 |
| 노원구 | 14 | 18 |
| 도봉구 | 7 | 21 |
| 동대문구 | 12 | 23 |
| 동작구 | 11 | 20 |
| 마포구 | 36 | 31 |
| 서대문구 | 22 | 16 |
| 서초구 | 48 | 29 |
| 성동구 | 14 | 19 |
| 성북구 | 15 | 26 |
| 송파구 | 36 | 35 |
| 양천구 | 17 | 19 |
| 영등포구 | 42 | 39 |
| 용산구 | 25 | 13 |
| 은평구 | 14 | 23 |
| 종로구 | 40 | 28 |
| 중구 | 54 | 30 |
| 중랑구 | 8 | 27 |
+----------+----------+--------+
중요한 건 꺽였는데도 그냥 하는 마음