AIoT 정규 28일차

Steam게임 추천 프로그램 만들기

1. 구상

1) 데이터 수집

: 웹 크롤링을 활용해서 Steam사이트에 올라와 있는 게임과 리뷰를 가져와 JSON 파일과 CSV파일로 저장.

 

2)  DB에 테이블 형식으로 저장

: 웹 크롤링으로 수집한 파일을 MySQL에 테이블 형식으로 저장. Game_Id를 Primary Key로 지정하고 "games" 테이블과 "reviews" 테이블사이에 외래키로 설정한다.

 

3) 쿼리문을 이용해서 추천 메커니즘 구상

: MySQL 쿼리문사용해 게임 타이틀을 검색하면 리뷰에 달린 좋아요 수를 기준으로 높은 순으로 정렬해서 display 한다. 리뷰에는 리뷰어 닉네임, 리뷰어가 플레이해 본 게임수(리뷰 신뢰성 증가), 리뷰어의 리뷰수, 추천 유무, 리뷰 작성일, 리뷰 내용을 display 한다.

 

4) GUI를 사용해 해당 기능 구현

 

2) DB에 테이블 형식으로 저장 - 06.02.2024

: 오늘 목표는 Web-Crawling으로 수집한 CSV 파일을 MySQL에 Load 하는 것이다.

 

1-1) games.csv(game list)에 있는 Data를 필요한 것만 추출한다.

: A열은 사실상 필요 없는 Data이기 때문에 제거해 줬다.

 

 

이후에 MySQL Workbench에서 데이터를 Load 한다.

CREATE TABLE games (
	game_id int not null,
	game_name varchar(100) not null
); -- games 테이블 생성

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\games.csv'
INTO TABLE games
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'; -- games.csv에 있는 데이터 블러오기

 

정상적으로 데이터가 Load 되었는지 확인해 보자.

select*from games;

 

결과

다행히 정상적으로 Load 된 걸 볼 수 있다.

 

문제는 reviews.csv파일인데...

 

1-2) reviews.csv 파일 load 하기

: 파일을 확인해 보면 이런 식으로 유니코드를 들어오는 과정에서 데이터가 깨져있어 파일을 load 할 때 오류가 예상된다.

 

일단 한 번 실행시켜 보자.

CREATE TABLE reviews(
	review_id int not NULL AUTO_INCREMENT,
	game_id int not null,
    useful int not null,
    funny int not null,
    username VARCHAR(100),
    own int NOT null,
    reviews int not null,
    recommend int not null,
    date_review varchar(50) not null,
    review_text VARCHAR(10000),
    PRIMARY key(review_id)
);

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\reviews.csv'
INTO TABLE reviews
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

하지만  역시...

 

reviews안에 있는 리뷰들이 텍스트가 VARCHAR(10000)가 담을 수 있는 양보다 커서 오류가 생겼다. 그래서 엑셀상에서 =LEFT(K1, "원하는 글자 수") 함수를 사용해서 캐릭터 크기를 줄였다. 그리고 review_id를 만들기 위해서 1부터 시작해서 총 리뷰 수만큼 입력해 줬다.

 

이런 식으로 정리를 하였다.

 

그러고 다시 코드를 실행을 시켜보니...

Error Code: 1300. Invalid utf8mb4 character string:

 

역시나 또 오류가 발생한다. review.csv파일에 있는 유니코드를 해석하지 못하는 error가 발생한 것이라고 보면 된다.

 

이를 해결하기 위해서 review.csv파일에 있는 CHARATER SET을 latin1으로 설정해서 data를 load 하여 유니코드를 해석할 수 있게 바꿔어 주었다.

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\reviews.csv'
INTO TABLE reviews
CHARACTER SET latin1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

 

이제 데이터를 모두 Load 했으니 테이블이 잘 만들어져 있는지 확인해 보자.

SELECT *
FROM games
INNER JOIN reviews ON games.game_id = reviews.game_id;

 

이렇게 잘 Load 된 걸 볼 수 있다.

 

전체적인 SQL문은 다음과 같다.

use sarang; --User 변경

DROP TABLE games; -- games 테이블 미리 만들어져 있지 않게 확인

CREATE TABLE games (
	game_id int not null,
	game_name varchar(100) not null
); -- games 테이블 생성

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\games.csv'
INTO TABLE games
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'; -- games.csv에 있는 데이터 블러오기  

drop table reviews; -- reviews테이블이 미리 만들어져 있지 않게 확인

CREATE TABLE reviews(
	review_id int not NULL AUTO_INCREMENT,
	game_id int not null,
    useful int not null,
    funny int not null,
    username VARCHAR(100),
    own int NOT null,
    reviews int not null,
    recommend int not null,
    date_review varchar(50) not null,
    review_text VARCHAR(10000),
    PRIMARY KEY(review_id)
); - review_id를 PRIMARY KEY로 설정하는 테이블 reviews를 생성

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\reviews.csv'
INTO TABLE reviews
CHARACTER SET latin1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'; -- review.csv파일에 있는 유니코드를 해석하지 못하는 error가 발생해서 CHARATER SET을 latin1으로 설정해서 data를 load함

SELECT *
FROM games
INNER JOIN reviews ON games.game_id = reviews.game_id; -- join조건을 두 테이블 간의 공통된 game_id 를 기반으로 결합해서 모든 열을 반환해 게임과 해당 리뷰를 함께 표시한다.