목표
- 크롤링한 데이터들을 mysql의 데이터베이스에 데이터를 넣는다.
준비물
- mysql이 설치 되어 있어야 한다.
mysql 계정은 root, 비밀번호는 1234 라고 가정한다.
방법
1. 명령 프롬프트(cmd)를 킵니다.
2. 아래 명령어로 데이터베이스를 생성합니다.
mysql -u root -p # root 계정으로 접속
<자신의 비밀번호 입력>
CREATE DATABASE dabang_data; # dabang_data DB 생성
show databases; # DB 조회
3. Python에서 mysql을 접속합니다. 그리고 테이블을 생성합니다.
- 터미널에서 mysqlclient 라이브러리 설치 (Python으로 mysql에 접속을 도와주는 라이브러리)
pip install mysqlclient
- Python 코드에서 mysql 접속하여 테이블 생성하는 코드 작성
# DB 연결
import MySQLdb
conn = MySQLdb.connect(
user="root", # user명
passwd="1234", #pw명
host="localhost",
db="dabang_data" #연결할 DB명
# charset="utf-8"
)
print(type(conn)) # 정상 연결 시 : <class 'MySQLdb.connections.Connection'>
cursor = conn.cursor()
print(type(cursor)) # 정상 연결 시 : <class 'MySQLdb.cursors.Cursor'>
# DB 구문 수행
cursor.execute("CREATE TABLE IF NOT EXISTS coliving (room_no INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(70))")
conn.commit()
- 테이블 생성 확인
명령 프롬프트 (cmd) 에서 진행
# mysql 접속
mysql -u root -p
<비밀번호 입력>
# dabang_data 데이터베이스 안에 있는 테이블 확인
use dabang_data;
show tables;
테이블의 각 필드 지정도 잘 되었는지 확인합니다.
describe coliving;
4. 테이블에 크롤링한 데이터를 INSERT 합니다.
// 크롤링 로직 내에 삽입
cursor.execute("INSERT INTO coliving (name) VALUES (%s)", (house["NAME"],)) # 자리 표시자 (%s) 활용
// 넣은 다음에는 항상 commit을 해줘야 반영이 안정적으로 된다. (auto commit이 아닐 수도 있으므로)
conn.commit()
주의 : SQL injection을 고려해야 한다.
cursor.execute(f"INSERT INTO coliving (name) VALUES ('{house_name}')")위 코드는 SQL injection 공격에 취약하다.
house_name 값에 사용자가 입력한 값이 SQL 쿼리의 일부로 해석되기 때문이다.
조금 더 쉽게 설명하기 위해 예를 들자면, house_name 값에 아래와 같은 값이 들어갔다고 가정하자.
house_name = "'; DROP TABLE coliving; --"
그러면 최종적으로 생성되는 SQL 쿼리 문은 다음과 같다.
INSERT INTO coliving (name) VALUES (''; DROP TABLE coliving; --')그러면 의도한 것과 다르게 coliving 테이블을 삭제하는 명령이 되어 버린다.
-> 이 방법을 방지하기 위해 자리 표시자를 활용한다.
자리 표시자의 경우 SQL 쿼리에서 값을 안전하게 삽입하도록 도와준다. (SQL Injection 공격 방지)
cursor.execute("INSERT INTO coliving (name) VALUES (%s)", (house["NAME"],))
이러면 house["NAME"] 값에
"'; DROP TABLE coliving; --"과 같이 들어가도, 자리 표시자가 따옴표를 떼고 문자열(%s)로 표현한다.
자동으로 작은 따옴표로 감싼 다음 필요한 경우 이스케이프 처리를 한다.
'; DROP TABLE coliving; -- # 작은 따옴표를 뗀다.
''; DROP TABLE coliving; --' # 작은 따옴표로 감싼다.
'\'; DROP TABLE coliving; --' # 이스케이프 처리를 한다.데이터베이스는 이 문자열을 단순한 데이터로 인식하기 때문에 '; DROP TABLE coliving; -- 문자열이 INSERT 되게 된다. (SQL 쿼리 실행이 아니다.)
어려우니 쉽게 요약하면, 항상 SQL에 데이터를 삽입할 때 직접 값을 삽입하는 것보다 자리 표시자(%s, %d 등) 을 이용하는 것이 좋다.
5. 데이터가 잘 추가 되었는지 확인합니다.
방법 1) mysql에서 직접 조회select * from coliving; 명령어를 통해 데이터를 전체 조회 합니다.
방법 2) python을 통해 조회한다.
cursor.execute("SELECT * FROM coliving") # SELECT * FROM coliving 쿼리 결과들이 cursor에 저장됨.
results = cursor.fetchall() # 한 개만 가져올 때는 fetchone() 메서드 사용 (결과들이 저장된 cursor 객체에서 결과를 가져와 result에 저장)
for result in results: # 결과를 하나씩 꺼내 출력
print(result)
참고 - 담기는 것은 리스트 안에 튜플들이 담기는 구조로 담기게 된다.
# 결과는 리스트 안에 튜플 형태
[
(1, '꼬모쉐 홍제역점')
(2, '단잠스테이 원룸텔')
(3, '명문게스트하우스')
(4, 'HJ HOUSE')
(5, '패시브스테이 50')
(6, '레지던스 라움')
(7, '레지던스 라움 노량진역점')
(8, '숲속휴식')
(9, 'JJ빌리지')
(10, '홍제스테이')
]
