2023/08/31(목) MySQL 데이터 저장과정 중 오류 해결

우리 팀은 주식 데이터를 API를 사용해 가져와서 DB에 저장하고 ML을 이용하여 앞으로의 주식 가격을 예측하는 프로젝트를 진행하기로 하였다.

사용할 데이터는 공공데이터 포털에서 제공하는 금융위원회 주식 시세정보 데이터이다.

https://www.data.go.kr/tcs/dss/selectApiDataDetailView.do?publicDataPk=15094808#tab_layer_detail_function

데이터 저장에 앞서 MySQL Workbench를 사용하여 다음과 같은 ER다이어그램을 그렸다.

그 이후 다음과 같은 코드를 사용해 API로 데이터를 불러오고, 100개를 저장하는데 문제 없이 성공하였다.

import requests
import pandas as pd

headers = {'Content-Type': 'application/json', 'Accept': '*/*'}
key = 개인 이 발급받은 key값
url = "https://apis.data.go.kr/1160100/service/GetStockSecuritiesInfoService/getStockPriceInfo"
body = {
    "serviceKey":key,
    "numOfRows": 100,
    "pageNo": 1,
    "beginBasDt" : 20120101,
    "ikeSrtnCd" : "000020",
    "resultType" : 'json'
    }

response = requests.get(url=url,params=body)
data = response.json()
table = pd.DataFrame(data['response']['body']['items']['item'])

Items = table[['srtnCd', 'isinCd', 'itmsNm']]
Items_Info = table.drop(['srtnCd', 'isinCd'], axis = 1)
Items_Info['basDt'] =  pd.to_datetime(Items_Info['basDt'], format = '%Y%m%d')

Items.to_sql('Items', con=engine, if_exists='append', index=False)
Items_Info.to_sql('Items_Info', con=engine, if_exists='append', index=False)

100개의 데이터가 잘 들어가는것을 확인 후 10000개의 데이터를 넣어보려고 시도해보았으나 duplicated Primary Key 라고 뜨면서 데이터가 들어가지지 않았다.( 안타깝게도 이 에러가 난 부분은 캡처를 하지 못했다.)

아무래도 items테이블에 설정해놓은 Primary Key 설정때문에 중복값이 들어가지 않아서 에러가 발생한것으로 생각되었다.

그래서 중간에 다음과 같은 코드를 넣어 중복값을 제거해주었다.

Items.drop_duplicates(inplace = True, subset="isinCd")

그 결과 데이터가 잘 들어가는 것을 확인 할 수 있었다.

하지만...

Items_Info 테이블에 데이터를 넣을 때 문제가 발생하였다.

혹시나 MySQL Workbench에서 ER다이어그램을 그리고 이것을 forward engineering하는 과정에서 변수가 생겼을까 싶어서 테이블을 모두 제거하고 SQL쿼리를 이용하여 다시 만들어주었다.

USE stock_db;

DROP TABLE Items_Info;
DROP TABLE Items;


CREATE TABLE Items(
	itmsNm VARCHAR(50) NOT NULL PRIMARY KEY,
    isinCd VARCHAR(50),
    srtnCd VARCHAR(50)
);

CREATE TABLE Items_Info(
	seq int auto_increment NOT NULL PRIMARY KEY,
	basDt DATETIME,
	itmsNm VARCHAR(50) NOT NULL,
	mrktCtg VARCHAR(50),
	clpr INT,
	vs INT,
	fltRt FLOAT,
	mkp INT,
	hipr INT,
	lopr INT,
	trqu INT,
	trPrc DOUBLE,
	lstgStCnt DOUBLE,
	mrktTotAmt DOUBLE,
	FOREIGN KEY (itmsNm)
	REFERENCES Items(itmsNm)
);

그 다음 다시 데이터를 저장하려 시도해보았으나 같은 에러가 발생.. 대체 뭐가 문제인걸까?

chatGPT에게 물어보았더니 다음과 같은 답변을 내놓았다.

뭐가 문제일지 고민하다가 PRIMARY KEY를 다른값으로 바꾸어보았다.

종목명보다 더 unique한 isin코드를 PRIMARY KEY로 설정해주고, 데이터를 넣어보았더니 결과는!

데이터가 들어가는데 성공했다!

왜 이러한 오류가 발생했는지 추측해보았다.

isinCd는 기업별 고유 식별 코드로, 기업마다 다 다른 코드를 가지고있다.
종목명(회사명)은 중복된 이름이 있을 수도 있다.
Items.drop_duplicates(inplace = True, subset="isinCd")에서 subset을 ‘isinCd’로 해주었기 때문에 같은 이름의 회사 정보가 있다.
따라서 종목명에 따라 세부항목들을 적재하려 하였을때 오류가 발생하였다.
(New!) 회사명이 변경되는 경우!

하루종일 오류 해결하느라 머리를 쥐어싸매면서 스트레스받았지만 결과적으로는 해결되었으니 만족!

이러한 방황도 내 성장의 밑거름이 될것이라 믿으며 글을 마친다.

민정e의 기술 블로그

2023/08/31(목) MySQL 데이터 저장과정 중 오류 해결

티스토리툴바