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

SQLAlchemy

• 응용 프로그램 개발자에게 SQL의 모든 기능과 유연성을 제공하는 Python SQL 도구 키트 및 개체 관계형 매퍼
• python에서 사용하는 대표적인 ORM

ORM(Object Relational Mapping)이란?

• 객체와 DB의 테이블이 매핑을 이루는 것
• DB의 테이블 객체화 시켜서 데이터를 CRUD 한다.
• SQL을 직접 작성하지 않고 테이블 조작 가능
• 사용하는 DBMS가 변경된다면 엔진만 바꿔주면 된다는 장점!
• 쿼리 대신 메소드를 이용해서 CRUD 할 수 있다.

SQLAlchemy 사용해서 DB 연결 및 테이블 생성, 데이터 추가해보기

1. google colab에는 sqlalchemy가 설치되어 있기 때문에 pip install 하지 않아도 된다.

import sqlalchemy as db

2. DB 접속 정보 파일 불러와서 각 변수에 저장하기

DB_INFO = "접속정보파일경로"
with open(DB_INFO, "r", encoding="utf-8") as f:
    db_info = yaml.load(f, Loader=yaml.Loader)

# 각 변수에 저장
HOST = db_info["HOST"]
USER = db_info["USER"]
PASSWD = db_info["PASSWD"]
PORT = 3306
DB_NAME = db_info["USER"]

3. DB 연결 엔진 생성

conn_url = f"mysql+pymysql://{USER}:{PASSWD}@{HOST}/{DB_NAME}"
engine = db.create_engine(conn_url) # 연결 엔진이 반환

4. 테이블과 매핑할 클래스 작성 (Base)

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

5. Base 클래스를 상속받는 클래스 만들기

- 실제 DB에 생성될 테이블을 정의한다(= CREATE TABLE)

class MovieInfo(Base):
    #매핑할 테이블명
    __tablename__ = "테이블명"

    # 컬럼명 정의
    id = db.Column(db.Integer, primary_key = True, autoincrement = True)
    actor = db.Column(db.String(255))
    director = db.Column(db.String(100))
    pubDate = db.Column(db.Integer)
    subtitle = db.Column(db.String(255))
    title = db.Column(db.String(100), nullable = False)
    userRating = db.Column(db.FLOAT)

• db.Integer : 정수타입
• db.FLOAT : 실수타입
• db.String(size) :  문자열
• primary_key = True : 기본키로 설정
• autoincrement = True : 자동증가 설정
• nullable = False : null값 허용 안함

6. 테이블 생성

Base.metadata.create_all(engine)

7. DB의 테이블과 상호작용 하기 위한 세션 객체 생성 및 반환

Session = db.orm.sessionmaker(engine)
sess = Session()

<데이터 수집>

1. API 접근을 위한 KEY_FILE 불러오기

KEY_FILE = "파일경로"
with open(KEY_FILE, "r", encoding="utf-8") as f:
    naver_keys = yaml.load(f, Loader=yaml.Loader)

CLIENT_ID = naver_keys["CLIENT_ID"]
CLIENT_SECRET = naver_keys["CLIENT_SECRET"]

2. API 접근 : 영화 정보 수집

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

params = {
    "query" : "어벤져스: 엔드게임"
}

result = naver_api.search_api(url, CLIENT_ID, CLIENT_SECRET, params)

<DB 테이블에 데이터 저장>

1. 필요 없는 데이터 삭제

"""
{'lastBuildDate': 'Wed, 24 Aug 2022 09:23:53 +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'}]}
"""

items = result["items"][0]
del items["image"], items["link"]

2. 필요한 데이터를 담은 items를 unpacking 해서 movie_info에 넣어주기

movie_info = MovieInfo(**items)

3. 테이블에 insert하고 저장하기

sess.add(movie_info)
sess.commit()

 

 

결과) 데이터가 잘 추가된걸 확인할 수 있다!

 

+) 결과 확인하는 코드

sess.query(MovieInfo).all() #select * from 테이블명;