[Python] DB 연결 / 테이블 생성 / 데이터 추가 (pymysql 사용)

앞서, DB를 연결하고 그 안에 있는 테이블을 불러와 정보를 확인하는 것에 대해 배웠다.

오늘은 테이블이 없는 DB를 연결해서 테이블을 생성하고 그 안에 데이터를 추가하는 것에 대해 배웠다.

모든 과정은 구글 colab으로 진행했다.

 

1️⃣ DB 연결 과정은 아래의 게시글에서 확인하자.

[Python] DB 연결 및 테이블 정보 불러오기 (pymysql 사용)

 

2️⃣ connect를 이용해서 연결 객체를 받아올 때 db를 선택하지 않고 객체 생성을 할 수 있다.

이 경우에는 따로 DB를 연결해주는 코드(=연결객체명.select_db("DB명"))를 작성해야한다.

# 연결 객체 안에서 db 선택
conn = pymysql.connect(
    user = USER,
    passwd = PASSWD,
    host = HOST,
    port = PORT,
    db = 'DB명'
)

# 연결 객체 안에서 db 선택하지 않고 나중에 선택
conn = pymysql.connect(
    user = USER,
    passwd = PASSWD,
    host = HOST,
    port = PORT,
)

conn.select_db("DB명")

MySQL 경우 값을 추가, 갱신, 삭제하는 과정이 자동 commit 되는데

Python으로 할 경우에는 autocommit_mode = False이므로 자동 commit이 되지 않아 값을 추가, 갱신, 삭제할 경우 수동으로 commit 해주어야한다.

conn.autocommit_mode
# False

 

3️⃣ NAVER Open API를 활용하여 데이터를 받아와 DB 안에 테이블을 생성하고 데이터를 추가해보자.

 

1) 먼저 NAVER Open API를 활용할 경우, NAVER에서 제공하는 CLIENT_ID와 CLIENT_SECRET를 이용해야한다.

=> NAVER Open API 실습 게시물에서 자세한 내용 확인

[Python] Open API 실습 - ③ NAVER Developers

 

2) 데이터를 수집하기 위해서는 요청 url과 요청 변수, CLIENT_ID, CLIENT_SECRET이 필요하다.

=> NAVER Open API 실습을 통해 검색 기능이 들어있는 패키지를 만든 적이 있어 그걸 이용해보고자 한다.

(https://github.com/kyeon06/api_test.git)

!pip install git+https://github.com/kyeon06/api_test.git

 

- 필요한 데이터를 미리 설정해준다.

import yaml
KEY_FILE = "파일주소"
with open(KEY_FILE, "r") as f:
    naver_keys = yaml.load(f, Loader= yaml.Loader)

client_id = naver_keys["CLIENT_ID"]
client_secret = naver_keys["CLIENT_SECRET"]

#요청 url
url = "https://openapi.naver.com/v1/search/movie.json"

#요청 변수
params = {"query" : "어벤져스: 엔드게임"}

 

3) 패키지를 이용해 영화정보 내용을 수집해보자.

from my_api import naver_api
result = naver_api.search_api(url, client_id, client_secret, params)

ex)
{'lastBuildDate': 'Tue, 23 Aug 2022 16:21:06 +0900',
 'total': 1,
 'start': 1,
 'display': 1,
 'items': [{'title': '<b>어벤져스: 엔드게임</b>',
   'link': 'https://movie.naver.com/movie/bi/mi/basic.nhn?code=136900',
   'image': 'https://ssl.pstatic.net/imgmovie/mdi/mit110/1369/136900_P57_104126.jpg',
   'subtitle': 'Avengers: Endgame',
   'pubDate': '2019',
   'director': '앤서니 루소|조 루소|',
   'actor': '로버트 다우니 주니어|크리스 에반스|크리스 헴스워스|마크 러팔로|스칼릿 조핸슨|제레미 레너|돈 치들|폴 러드|브리 라슨|카렌 길런|브래들리 쿠퍼|조슈 브롤린|',
   'userRating': '9.38'}]}

이런 식으로 정보가 추출 된다.

여기서 title, subtitle, pubDate, director, actor, userRating만 가져와 DB에 저장해보자.

 

4) 테이블 생성하기

sql = """
    create table if not exists tb_movie_info(
        id int unsigned not null auto_increment,
        actor varchar(255),
        director varchar(100),
        pubDate int,
        title varchar(100) not null,
        subtitle varchar(100),
        userRating float,
        primary key(id)
    )
"""

cur.execute(sql)

=> 테이블 SQL을 작성한 후 execute()로 DB에 전달한다.

 

5) 데이터 추가하기

- result["items"] 의 속성 값들을 하나씩 추가해준다!

- 마지막에 꼭 execute를 통해 DB에 전달하고 commit을 통해 저장해주기!

sql = f"""
        insert into tb_movie_info(actor, director, pubDate, title, subtitle, userRating)
        values(
            '{result["items"][0]["actor"]}',
            '{result["items"][0]["director"]}',
            {result["items"][0]["pubDate"]},
            '{result["items"][0]["title"]}',
            '{result["items"][0]["subtitle"]}',
            {result["items"][0]["userRating"]}
        );
    """
cur.execute(sql)
cur.commit()

 

6) 잘 저장되어 있는지 확인하기

sql = "select * from tb_movie_info;"
cur.execute(sql)
cur.fetchall()

#output
{'id': 1,
  'actor': '로버트 다우니 주니어|크리스 에반스|크리스 헴스워스|마크 러팔로|스칼릿 조핸슨|제레미 레너|돈 치들|폴 러드|브리 라슨|카렌 길런|브래들리 쿠퍼|조슈 브롤린|',
  'director': '앤서니 루소|조 루소|',
  'pubDate': '2019',
  'title': '<b>어벤져스: 엔드게임</b>',
  'subtitle': 'Avengers: Endgame',
  'userRating': 9.38}