목표: 웹사이트 크롤링으로 저장한 csv 데이터를 MySql에 insert하기

csv 파일 불러오기

import pandas as pd

df = pd.read_csv("songs.csv", header=None, nrows=1000) # csv 저장할때 header 없이 저장했으므로 읽어들일 때에도 명시 필요
df = df.where(pd.notnull(df), None)

• 엑셀 파일 불러오는 경우 pd.read_excel() 사용
• 파일 내에 column에 대한 헤더가 존재한다면 header=None 삭제
  💡 해당 코드 없으면 csv 파일의 첫번째 row를 자동으로 제거해 불러옴

---

MySql 연동

import pymysql

# DB 정보
host = "localhost"
user = "root"
password = "0000"
database = "songmmelier"

# DB 연결
conn = pymysql.connect(host=host, user=user, password=password, db=database)
curs = conn.cursor(pymysql.cursors.DictCursor)

---

sql문 작성

# DB delete: 'rank' 컬럼에 값이 있는 모든 데이터 삭제
sql_delete = """DELETE FROM song WHERE `rank` IS NOT NULL;"""
curs.execute(sql_delete)

# DB insert
sql = """insert into song (`rank`, tj_number, title, artist) 
VALUES(%s, %s, %s, %s);""" # 주의: int type이어도 파이썬에서 쿼리 짤 때에는 문자열 자료형(%s)으로 지정

for idx in range(len(df)):
	curs.execute(sql, tuple(df.values[idx]))
     
conn.commit()

curs.close()
conn.close()

• 코드를 실행할 때마다 song 테이블의 탑100 노래 모두 삭제, 다시 데이터 삽입
• mysql에 'rank' 함수가 존재하므로 rank column 이름 작성 시 따옴표로 묶음 필요
• curs.execute(sql문): sql문 실행
• conn.commit(): 커밋
• curs.close(), conn.close(): 커서 닫기 및 연결 종료

---

최종 코드

import pymysql.cursors
import requests
from bs4 import BeautifulSoup as bs
import pandas as pd
from concurrent.futures import ThreadPoolExecutor
import csv
import pymysql

# 데이터 크롤링
def fetch_data(url):
    response = requests.get(url)
    # 한글 데이터 안깨지게 가져오려면 인코딩 필요
    response.encoding = 'utf-8'
    html = response.text
    soup = bs(html, 'html.parser')

    # .board_type1>tbody>tr:nth-child(n+2) 선택자를 사용하여 첫 번째 tr을 제외
    items = soup.select(".board_type1>tbody>tr:nth-child(n+2)")
    data = []
    for item in items:
        rank = item.select_one("td:nth-child(1)").text
        number = item.select_one("td:nth-child(2)").text
        title = item.select_one("td:nth-child(3)").text
        artist = item.select_one("td:nth-child(4)").text
        data.append((rank, number, title, artist))
    return data

# 수집할 URL 리스트 생성
urls = ["https://www.tjmedia.com/tjsong/song_monthPopular.asp" for _ in range(10)]  # URL을 10번만 호출하도록 수정

# 동시 요청 수행
data = []
with ThreadPoolExecutor(max_workers=10) as executor:
    results = executor.map(fetch_data, urls)

    for result in results:
        data.extend(result)

# 동시 요청으로 인한 데이터 중복 제거
data = list(set(data))

# 데이터를 DataFrame으로 변환
df = pd.DataFrame(data, columns=["Rank", "Number", "Title", "Artist"])

# DataFrame을 CSV 파일로 저장
df.to_csv("songs.csv", header=False, index=False, quoting=csv.QUOTE_ALL)

print("Data saved successfully to songs.csv")

# DB 정보
host = "localhost"
user = "root"
password = "0308"
database = "songmmelier"

# DB 연결
conn = pymysql.connect(host=host, user=user, password=password, db=database)
curs = conn.cursor(pymysql.cursors.DictCursor)

# DB delete: 'rank' 컬럼에 값이 있는 모든 데이터 삭제
sql_delete = """DELETE FROM song WHERE `rank` IS NOT NULL;"""
curs.execute(sql_delete)

# DB insert
sql = """insert into song (`rank`, tj_number, title, artist) 
VALUES(%s, %s, %s, %s);""" # 주의: int type이어도 파이썬에서 쿼리 짤 때에는 문자열 자료형(%s)으로 지정

for idx in range(len(df)):
	curs.execute(sql, tuple(df.values[idx]))
     
conn.commit()

curs.close()
conn.close()

print("Data saved successfully to mysql")

• 데이터를 수집하면서 이미 DataFrame이 생성되었으므로 따로 csv 파일을 읽어들이는 부분은 생략

---

느낀점

• 이전 포스팅에서 각 속성값이 쌍따옴표로 묶여 csv 파일로 저장되었기에 db 저장시 처리 방법을 고민했으나 실제로 db에 들어가는 값은 쌍따옴표 제거되어 들어감 👍

• 겁먹었었는데 나름 간단하게 코드를 완성했다.

• 비상비상...🚨 song 테이블에 기존 노래 데이터 + 노래방 탑100 데이터를 합치려고 했는데 지금 코드로는
  원래 순위권에 없던 노래를 song 테이블에 추가 ➡️ 이후 노래방 탑100 진입 ➡️ 새 레코드로 노래 데이터가 추가됨
  이런 문제가 있다. 탑100 노래 저장하는 테이블을 분리시켜야 할까?

---

참조: https://lifesteps.tistory.com/119