[MySQL] upsert에 대한 이해

upsert

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

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

create_database('mysql+pymysql://root:1234@127.0.0.1:3306/exam') # 마지막에 만들고 싶은 데이터베이스 이름 입력

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('price', con=engine, if_exists='append', index=False) # if_exist = 'append'는 테이블이 존재할 경우 기존 테이블에 데이터를 추가합니다.
engine.dispose()

# 하루가 지나서 시계열 데이터가 추가되었다고 가정
new = pd.DataFrame({
	"날짜": ['2021-01-04],
    "티커": ['000001'],
    "종가": [1320],
    "거래량": [1500]
})
price = pd.concat([price,new])

# 이렇게 할 경우 01-02, 01-03 데이터와 함께 총 3개가 추가가 되버림.
# if_exists=replace로 바꿔서하면 기존의 데이터가 삭제되버림.

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

# 기존의 데이터는 그대로 두고 새로 입력되는 데이터는 추가하는 것이 필요함. 그것이 upsert

• SQL에서 upsert 구현

insert into @table
(arg1, arg2, arg3)
valeus
(@arg1, @arg2, @arg3)
on duplicate key update(key를 제외한 update할 컬럼) # 키 값(args1)에 데이터(@arg1)가 이미 존재한다면
arg2 = @arg2, arg3 = @arg3 # @arg2와 @arg3로 데이터를 업데이트

use exam;

CREATE TABLE price_2(
	날짜 varchar(10),
    티커 varchar(6),
    종가 int,
    거래량 int,
    PRIMARY KEY(날짜, 티커)
);

insert into price_2 (날짜, 티커, 종가, 거래량)
values
('2021-01-02', '000001', 1340, 1000),
('2021-01-03', '000001', 1315, 2000),
('2021-01-02', '000002', 500, 200);

#upsert
insert into price_2 (날짜, 티커, 종가, 거래량)
values
('2021-01-02', '000001', 1340, 1000),
('2021-01-03', '000001', 1315, 2000),
('2021-01-02', '000002', 500, 200);
('2021-01-03', '000002', 1380, 3000); #새로 입력되는 데이터
as new # new라는 별명으로
on duplicate key update # 프라이머리 키에 데이터가 존재할 경우 업데이트하고 아니면 입력
종가 = new.종가, 거래량 = new.거래량; # primary 키를 제외한 나머지 열들 업데이트 설정

insert into price_2 (날짜, 티커, 종가, 거래량)
values
('2021-01-02', '000001', 1300, 1100),# 업데이트
('2021-01-04', '000001', 1300, 2000); # 새로 입력되는 데이터
as new
on duplicate key update
종가 = new.종가, 거래량 = new.거래량;

• python에서 upsert 구현

price = pd.DataFrame({
	"날짜": ['2021-01-04', '2021-01-04'],
    "티커": ['000001', '000002'],
    "종가": [1320, 1315],
    "거래량": [2100, 1500]
})

args = price.values.tolist() # 데이터프레임의 value 부분을 리스트 형태로

con = pymysql.connect(
	user = 'root',
    passwd = '1234',
    host = '127.0.0.1',
    db = 'exam'
    charset = 'utf8')
   
# values 부분에는 입력하는 데이터의 열 갯수만큼 %s를 입력
query = """
	insert into price_2 (날짜, 티커, 종가, 거래량)
    values (%s,%s,%s,%s) as new
    on duplicate key update
    종가 = new.종가, 거래량 = new.거래량;
"""

mycursor = con.cursor()
mycursor.executemany(query, args)
con.commit()
con.close()