Python, MySQL - 시계열 데이터 처리를 위한 upsert 알아보기_1

to_sql() 함수를 이용해 시계열 데이터를 SQL에 저장할 경우 발생하는 문제와

upsert 기능을 사용하는 방법에 대해 설명해보겠습니다,

 

<시계열 데이터의 특성> 1. insert :시간이 지남에 따라 데이터가 추가됨 2. update :간혹 과거 데이터가 수정됨

 

 

입력하고자 하는 데이터가 기존 테이블에 값이 있는 경우 새로운 데이터로 업데이트하고

값이 없는 경우에는 새로운 데이터를 추가하는 기능을 mysql에서는 'upsert'라고 부릅니다.

 

 

이제 시계열 데이터를 데이터베이스에 추가하는 예제를 살펴보겠습니다.

#1. 필요한 라이브러리 불러오기
import pandas as pd
from sqlalchemy import create_engine 
from sqlalchemy_utils import create_database

#2. 데이터베이스 만들기
create_database('mysql+pymysql://root:1234@127.0.0.1:3306/exam') # exam이라는 db만들기

#3. 데이터 만들에서 exam db에 넣기
price = pd.DataFrame({
    "날짜": ['2021-01-02', '2021-01-03'],
    "티커": ['000001', '000001'],
    "종가": [1340, 1315],
    "거래량": [1000, 2000]
})

engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/exam')
price.to_sql(name = 'price', con = engine, index = False, if_exists = 'append') 
# if_exists = 'append'는 테이블이 존재할 경우 기존 테이블에 데이터를 추가
engine.dispose()

위의 코드를 작성하면 아래와 같이 의도된 결과가 나오는 것을 확인할 수 있습니다.

 

결과 이미지

 

 

여기까지는 문제가 없습니다. 

그러나 하루가 지나서 다음날의 데이터가 추가되는 상황을 보여드리겠습니다.

import pandas as pd
from sqlalchemy import create_engine 
from sqlalchemy_utils import create_database

#기존 데이터
price = pd.DataFrame({
    "날짜": ['2021-01-02', '2021-01-03'],
    "티커": ['000001', '000001'],
    "종가": [1340, 1315],
    "거래량": [1000, 2000]
})

#새로운 데이터
new = pd.DataFrame({
    "날짜": ['2021-01-04'],
    "티커": ['000001'],
    "종가": [1320],
    "거래량": [1500]
})

price = pd.concat([price,new]) #기존 데이터와 새로운 데이터 합침

engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/exam')
price.to_sql(name = 'price', con = engine, index = False, if_exists = 'append') 
engine.dispose()

위의 코드를 실행시키면 exam db에 새로운 데이터가 추가될 것입니다.

 

결과 이미지

 

이와 같이 기존 데이터와 누적되어서 같은 데이터가 반복되는 문제가 발생합니다.

이러한 문제를 해결하는 과정은 다음 블로그에서 소개해드리겠습니다.