키움증권 Open API - 차트 데이터 관리 DB 제작 (1)

지난 게시글에서는 filtered_code() 함수를 통해 불러온 종목 코드들을 바탕으로 전체 종목의 일봉 차트 데이터를 조회하는 부분까지 알아봤다. 이번 게시글에서 알아볼 내용은 바로 차트 데이터를 DB에 저장하고 그를 어떻게 관리해야 하는지 등에 대한 방법론적인 부분에 대해 다루어볼 예정이다.

 

차트 데이터 DB에 저장하기

지난 시간에 제작했던 코드를 보면 for code in code_list:라고 제작한 부분이 있는데, 이는 code_list 안에 있는 ['000020', '000040', '000050' ···]와 같은 값들 중에서 code = '000020'이라는 가정 하에 코드를 실행하고, 그 다음 번 반복에서는 code = '000040'이라는 가정 하에 코드를 실행하는 방식으로 동작한다. 즉, 동작 그때 그때의 code라는 변수에는 종목 코드가 입력되어 있는 것이며, 이를 바탕으로 rq_chart_data 함수에 전달할 종목 코드 란에도 code라는 변수를 전달해주었었다. 

본인의 데이터베이스에 차트 데이터를 저장하는 방법은 이전 게시글에서 다루었었으니 여기서는 자세한 설명은 생략하도록 하고, 다시 본론으로 돌아와서 우리는 이 code라는 변수를 바탕으로 DB에 저장하는 것 역시 자동적으로 처리하도록 구축할 수 있다. 이전에 to_sql을 사용할 때 입력했던 name="      "부분이 기억날지 모르겠지만, 바로 이 부분이 테이블명에 해당하는 부분이었고 우리는 이를 종목코드로 설정했었다. 하지만 주의사항이 하나 있었는데, 테이블명은 숫자로만 이루어지면 안 되기 때문에 반드시 알파벳 하나는 앞에 있어야 한다고 설명했었다. 이 부분을 참고하여 코드를 작성해보면 아래와 같이 제작할 수 있을 것이다.

for code in code_list:
    print("종목 코드:", code)
    trade.rq_chart_data(code, "20210101", 1)
    df_day_data = pandas.DataFrame(trade.day_data, columns=['date', 'open', 'high', 'low', 'close', 'volume', 'trade_volume'])
    print(df_day_data)
    df_day_data.to_sql(name="s" + code, con=engine_all, index=False, if_exists='replace')
	print("종목코드:", code, "  데이터베이스에 저장되었습니다.")

즉, rq_chart_data 함수에 code, '20210101', 1이라는 세 개의 변수를 전달해주었기 때문에 해당 함수에서 자동적으로 해당 종목에 대한 20210101까지의 일봉 차트 데이터를 모두 불러온 후 self.day_data 변수에 입력해주었었다. 이를 해당 class 밖에서 사용하려고 하다 보니 trade.day_data와 같은 식으로 사용했던 것 뿐이다. 그리고 최종적인 결과물은 차트 데이터가 저장되어 있는 trade.day_data를 DataFrame 형식으로 만든 값인 df_day_data였다. 따라서 이 df_day_data에 이전 게시글에서 살펴봤던 .to_sql을 사용하여 데이터베이스에 정보를 전달하는 것이고, name="s" + code 부분은 해당 데이터베이스 내에서 저장할 테이블명을 "s종목코드"와 같은 형태로 만들도록 하는 것이다. 그 뒤에 있는 con=engine_all 역시 이전 게시글에서 설명했던 내용인데, engine_all은 현재 day_data라는 데이터베이스에 연결되어 있다.

따라서 위의 코드를 실행시켜보면, 아래와 같이 차트 데이터는 잘 조회되고 그 데이터는 각 종목코드 앞에 's'가 붙은 테이블명으로 데이터베이스에 저장되고 있음을 확인할 수 있다. 즉, 동작하는 데 있어 아무런 문제가 없는 코드를 제작했다는 것이다.

# 이상 생략 #
종목 코드: 000070
           date   open   high    low  close   volume trade_volume
0      20201230  19100  19800  18800  19650   623218        12110
1      20201229  18750  19400  18750  19150   471430         9017
2      20201228  20000  20050  18600  18650  1215378        23326
3      20201224  20300  20500  19500  20000  1222707        24381
4      20201223  23500  24300  20450  20450  2745447        61422
...         ...    ...    ...    ...    ...      ...          ...
47655  19850109   7256   7256   7256   7256      382            0
47656  19850108   7248   7248   7248   7248      167            0
47657  19850107   7243   7243   7239   7243      335            0
47658  19850105   7239   7239   7239   7239      143            0
47659  19850104   7239   7239   7239   7239       96            0

[47660 rows x 7 columns]
종목코드: 000070   데이터베이스에 저장되었습니다.

workbench

 

---

728x90

---

 

데이터 관리용 데이터베이스 생성하기

위처럼 현재 코드에는 아무런 문제가 없는데, 한 가지 문제가 있다. 그는 바로 차트 데이터를 모두 저장하고 싶은데, 코드를 실행하면 여김없이 '000020'부터 새롭게 조회한다는 것이다. 무려 s000020이라는 테이블이 이미 존재하고 있음에도 불구하고 말이다.

따라서 우리는 s000020이라는 테이블 명이 있는지를 확인하도록 하는 코드를 구축한 후에, 이미 해당 테이블명을 가진 테이블이 있다면 저장하는 절차를 건너뛰도록 구축해야 할까? 그렇지 않다. 왜냐하면 s000020 테이블이 있다고 하더라도 그 안에 있는 데이터가 최신 데이터인지 아닌지 알 수 있는 방법이 없기 때문이다. 따라서 우리는 각 종목코드 별로 일봉 데이터는 저장이 되어있는지, 그리고 그 일봉 데이터의 최신 일자는 언제인지 등을 확인할 필요가 있다. 그래야만 이전에 구축했던 latest_date()함수를 바탕으로 해당 데이터가 최신 데이터인지 확인할 수 있고 저장이 안 되어 있다면 전체 데이터를 저장하도록 구축할 수 있기 때문이다. 

그렇다면 데이터베이스를 관리하기 위해 우리가 확인해야 할 정보에는 무엇들이 있을지 생각해보자. 

• 해당 종목에 대한 일봉 데이터가 저장이 되어 있는지
• 저장된 일봉 데이터가 최신 데이터인지 

위의 두 가지 내용을 바탕으로 하나의 표를 만들어보면, 아래와 같이 만들 수 있을 것이다. 아래와 같은 데이터베이스를 하나 만든 이후에, 특정 종목코드의 일봉 저장 일자를 확인하고 일자에 입력되어 있는 데이터가 없거나 오늘 일자가 아니라면 데이터 조회를 하도록 하고, 오늘 일자라면 건너뛰도록 하는 코드를 구축함으로써 차트 데이터를 이어받을 수 있다. 물론 일봉 저장 일자 외에도 여러 가지 데이터들이 필요할텐데, 그는 필요에 따라 만들어서 데이터베이스를 제작해주면 된다. 혹시 모르니 데이터베이스를 생성할 때에는 분봉 차트 데이터에 대한 부분도 함께 구축하도록 하겠다.

종목코드	일봉 저장 일자
000020
000040
000050
000060
000070

 

일단 우리가 기존에 차트 데이터를 저장하는 day_data 데이터베이스 외에, 별도로 종목별 데이터를 관리하기 위한 데이터베이스가 필요하니 데이터베이스를 새롭게 만들어주도록 하자. 데이터베이스 이름은 자유이니 본인이 사용하고 싶은 이름으로 만들어주면 된다. 본인은 item_savepoint라는 이름으로 데이터베이스를 만들었다. (이전에 check_db()라는 함수를 통해 데이터베이스를 생성하는 코드를 만들었다.)

파이썬 + MySQL, 데이터베이스 자동 처리하기

이로서 우리는 이제 두 개의 데이터베이스를 사용하게 되었다. Open API 코드가 있는 부분의 하단에 보면, import 아래에 check_db()가 두 개가 있음을 확인할 수 있고, 이제 해당 코드를 실행하게 되면 두 개의 데이터베이스가 존재하는지의 여부를 확인한 후에 키움증권 Open API에 로그인을 하게 되는 것이다. 어떤가? 자동으로 처리하도록 만들었더니 만들고자 하는 데이터베이스의 이름만 넣어주니 편하지 않은가?

import ~~~~

posting_mysql.check_db('day_data')
posting_mysql.check_db('item_savepoint')

 

WorkBench에서도 잘 생성됐다

 

데이터베이스 관리용 테이블 제작하기

이 부분은 차트 데이터를 저장하기 위해 DataFrame으로 만들어서 저장하는 방법과는 달리, mysql에서 사용하는 CREATE TABLE과 INSERT 명령어를 사용할 것이다. 왜냐하면 매번 새롭게 추가되는 종목들이 있고 사라지는 종목들이 있으며 그때 그때마다 새롭게 상장된 종목 코드도 우리의 데이터베이스에 추가되어야 해당 종목에 대한 데이터를 관리할 수 있기 때문이다. 하지만 어떤 종목을 대상으로 할 것인지는 걱정할 필요가 없다. 왜냐하면 우리는 이미 대상 종목의 범위를 filtered_code()함수를 통해 한정지었기 때문이다. 

그렇다면 이제 item_savepoint 데이터베이스가 생성되었으니, 그 안에 테이블을 만들어주도록 해야 한다. 테이블 이름은 데이터베이스 이름과 마찬가지로 item_savepoint로 할 것인데, 우리는 앞에서 테이블을 만드는 데에 있어서 제작해두었던 함수가 우리를 엄청 편하게 해주는 경험을 겪었으니 테이블도 함수를 제작함으로써 자동 처리를 하도록 하자.

def check_table(db_name, table_name):

일단 테이블이 존재하는지를 확인하기 위해서는 해당 테이블이 데이터베이스 내에 얼마나 있는지를 확인해야 하기 때문에 해당 함수에서는 데이터베이스의 이름(db_name)과 테이블의 이름(table_name)을 함께 받아오도록 했다. 그 후에, check_db() 함수를 불러와서 해당 db_name을 전송한 후 데이터베이스가 있는지 확인한 후에, 해당 데이터베이스 내에 테이블이 있는지를 확인하는 절차를 거치도록 구축하면 된다.

def check_table(db_name, table_name):
	check_db(db_name)

이제 check_db 함수를 통해서 db_name이라는 이름을 가진 데이터베이스를 생성했으니(또는 존재한다는 것을 확인했으니) 이제는 테이블이 존재하는지 그 여부를 확인해야 한다. 이와 관련하여 check_db 함수를 제작할 때 가장 먼저 mysql.connector.connect()를 사용했던 것처럼 이번에도 마찬가지로 연결을 할 것인데, 이번에는 데이터베이스를 지정해서 연결해줄 것이다. 즉 check_db 함수에서는 mysql과 연결했다면, check_table 함수에서는 mysql 안에 있는 데이터베이스와 연결을 한다는 것이다.

def check_table(db_name, table_name):
	check_db(db_name)

	connect = mysql.connector.connect(user='root', password=PASSWORD, host='127.0.0.1', db=db_name, charset='utf8mb4')
	cur = connect.cursor()

위의 코드 중 PASSWORD 부분만 별도로 입력하면 된다. 뒤 부분에 있는 db=db_name에서의 db_name은 이미 함수 def check_table의 변수로 받아왔기 때문이다. 이제 db_name(임시 변수)이라는 데이터베이스에 연결했다면, 해당 데이터베이스 내에 특정 테이블이 있는지를 확인해야 할 것이다. 특정 테이블이 있는지를 확인하는 함수는 마찬가지로 SHOW TABLES LIKE를 사용하면 되며, 그 결과값의 개수( len(result) )가 0이라면 없는 것이고 1이라면 존재하는 것이다.

def check_table(db_name, table_name):
	check_db(db_name)

	connect = mysql.connector.connect(user='root', password=PASSWORD, host='127.0.0.1', db=db_name, charset='utf8mb4')
	cur = connect.cursor()
    
	cur.execute("SHOW TABLES LIKE '" + table_name + "'")
	result = cur.fetchall()
    
	if len(result) == 0:
		pass
	else:
		pass

이제 위와 같은 형태로 제작했다면, if문 아래에서 CREATE TABLE이라는 명령어를 통해 현재 연결되어 있는 데이터베이스(변수 db_name) 안에 테이블(변수 table_name)을 생성하라는 코드를 구축하면 된다. 하지만 여기서 주의해야 할 점은 바로 데이터베이스를 생성할 때에는 그냥 이름만 지정해주면 됐는데, 테이블을 생성할 때에는 그 안에 들어갈 칼럼의 이름을 지정해주어야 한다는 것이다. 칼럼은 단순하게 말해, 표가 있다고 했을 때 그 표의 세로축을 의미한다. 따라서 칼럼명은 세로축의 맨 위에 들어갈 대표적인 이름으로, 예를 들면 'code'등을 입력할 수 있을 것이다. 물론 우리는 일봉 차트 데이터를 저장했는지 그 여부를 확인하기 위해 해당 테이블을 만드는 것이기 때문에 'code'와 'sv_day'라는 두 개의 칼럼명을 지정해줄 것이다.

def check_table(db_name, table_name):
	check_db(db_name)

	connect = mysql.connector.connect(user='root', password=PASSWORD, host='127.0.0.1', db=db_name, charset='utf8mb4')
	cur = connect.cursor()

	cur.execute("SHOW TABLES LIKE '" + table_name + "'")
	result = cur.fetchall()

	if len(result) == 0:
		print("[DB 관리] ", table_name, "table을 생성합니다.")
		cur.execute("CREATE TABLE `" + db_name + "`.`" + table_name + "` (`code` VARCHAR (100), `sv_day` VARCHAR(100))")
		print("[DB 관리] ", table_name, "table이 생성되었습니다.")
	else:
		print("[DB 관리] ", table_name, "table이 이미 존재합니다.")

	connect.commit()
	cur.close()

이제 이 코드를 실행할 때에는 check_table('item_savepoint', 'item_savepoint')와 같은 형태로 db_name과 table_name을 각각 입력해주면 된다. 

[DB 관리] item_savepoint DB가 이미 존재합니다.
[DB 관리] item_savepoint table을 생성합니다.

그 후에 WorkBench를 들어가서 보면, 아래와 같이 item_savepoint 데이터베이스 안에는 item_savepoint라는 테이블이 있고, 그 안에 있는 Columns에는 code와 sv_day가 잘 생성되어 있는 모습을 확인할 수 있다.

 

이번 포스팅에서 추가적으로 다루게 된 내용들이 많아지는 바람에, 2편으로 나누어서 제작하게 되었습니다. 다음 게시글에서 차트 데이터를 관리하는 방법에 대해 마무리하겠습니다.

 

 

---