개요
PostgreSQL 설치도 끝났고, 상장종목 데이터도 가져왔으니 가져온 데이터를 DB 화 하여 보관 후 이후 종목 공시 알림 시 사용하고자 한다.
테이블 만들기
PostgreSQL 은 이미 설치가 완료 되었기 때문에 pgAdmin4를 통해서 테이블을 만들고 python 프로그램으로 데이터를 넣어보기로 한다. 물론 python에서 DDL 명령어를 통해 할 수도 있겠지만 일단 귀찮다. 훌륭하게 제공하는 UI를 통해서 테이블을 만들어본다.
1. pgAdmin4 프로그램을 실행하면 기본브라우저에서 프로그램이 구동된다. 최초 구동 시 패스워드를 물어보는데 설치 시 설정한 슈퍼계정(postgres)에 대한 패스워드를 입력하면 좌측 탐색기의 하위 메뉴들을 Open 할 수 있다.
2. 본격적 테이블 생성을 위해 Schemas - public - Tables 메뉴로 이동한다. Tables 에서 우클릭하여 Create > Table... 선택!
3. General 탭: 테이블명, 소유자, 스키마 등을 설정할 수 있다. 나머지는 기본 설정으로 진행하고 테이블명만 data_corp 로 설정하였다.
4. Columns 탭: 테이블을 구성하는 각 컬럼들을 정의할 수 있다. Open Dart 에서 제공하는 Column 명, 길이에 맞추어 테이블을 생성하였다. 다만 회사를 정의하는 고유한 값인 corp_code 에 대해선 Not Null, Primary Key 옵션을 ON으로 설정하였다.
5. Constraints 탭: 테이블에 대한 제약조건을 생성할 수 있다. 4의 단계에서 Not NULL, Primary Key 옵션을 ON으로 설정했다면 자동으로 pk 에 관한 제약조건이 생성된다. 다른 키들을 설정할만큼 복잡한 테이블이 아니므로 Save 를 눌러서 테이블 생성 완료!
Python 필요 모듈 세팅
PostgreSQL 연동 방법은 다음의 블로그를 참조하였다. 연동방법이 간략하게 잘 나와있다.
# PostgreSQL 모듈 import psycopg2 from psycopg2._psycopg import cursor # XML 파싱 모듈 import xml.etree.ElementTree as ET import pandas as pd
모듈이 없어서 import 가 안될 경우 모듈 설치가 필요하다.
모듈이 import 된 후에는 XML 의 내용을 파싱하여 DB에 넣는 작업을 수행한다.
이전 포스팅에서 정리한 대로 Open Dart 에서 제공하는 CORPCODE.XML 파일은 다음과 같은 형태로 되어있다. 이를 참고하여 파싱을 진행한다.
[CORPCODE.XML]
<?xml version="1.0" encoding="UTF-8"?> <result> <list> <corp_code>00126380</corp_code> <corp_name>삼성전자</corp_name> <stock_code>005930</stock_code> <modify_date>20191209</modify_date> </list> ... </result>
Python 파싱 & INSERT 프로그램 작성
1. 데이터베이스 연결한다. password 부분엔 PostgreSQL 설치 시 설정한 패스워드 입력.
# 데이터 베이스에 연결 conn_string = "host='localhost' dbname='postgres' user='postgres' password='your_password'" # Connection 생성 conn = psycopg2.connect(conn_string) # Connection 에서 Cursor 가져오기 cursor = conn.cursor()
2. XML 문서를 파싱 후 출력해본다. 아래 프로그램을 실행하면 모든 기업의 회사코드, 회사명이 출력된다.
# XML 문서 파싱 doc = ET.parse("CORPCODE.xml") root = doc.getroot() # 최상단 TAG 아래 <list> 태그를 모두 찾아서 LOOP 실행 for country in root.iter("list"): # 회사고유번호 corpCode = country.findtext("corp_code") # 회사명 corpName = country.findtext("corp_name").replace("'", "") # 증권코드 stockCode = country.findtext("stock_code").replace(" ", "") # 마지막 수정일 modifyDate = country.findtext("modify_date") print("[" + str(corpCode) + "]" + str(corpName))
3. 파싱된 데이터를 가지고 실제 DB에 넣는 부분을 구현한다. 다만 8만여개가 넘는 모든 데이터가 아닌 증권코드를 가지고 있는 데이터만 넣고, 다음번에 XML 이 업데이트 될 때를 고려하여 기존에 있는 데이터라면 최종수정일을 비교하여 업데이트 하도록 하였다. (사실이게결론)
# PostgreSQL 모듈 import psycopg2 from psycopg2._psycopg import cursor # XML 파싱 모듈 import xml.etree.ElementTree as ET import pandas as pd def insert(): # 데이터 베이스에 연결 conn_string = "host='localhost' dbname='postgres' user='postgres' password='your_password'" # Connection 생성 conn = psycopg2.connect(conn_string) # Connection 에서 Cursor 가져오기 cursor = conn.cursor() # XML 문서 파싱 doc = ET.parse("CORPCODE.xml") root = doc.getroot() # 최상단 TAG 아래 <list> 태그를 모두 찾아서 LOOP 실행 for country in root.iter("list"): # 회사고유번호 corpCode = country.findtext("corp_code") # 회사명 corpName = country.findtext("corp_name").replace("'", "") # 증권코드 stockCode = country.findtext("stock_code").replace(" ", "") # 마지막 수정일 modifyDate = country.findtext("modify_date") # 증권코드가 비어있는 경우는 패스함 if len(stockCode) == 0: continue # corpCode 로 기존에 DB에 데이터가 있는지 확인한다. cursor.execute(f"select * from data_corp where corp_code='{corpCode}'") # select Query 를 실행한다. result = pd.DataFrame(cursor.fetchall()) # 이 아래 줄을 추가하면 pandas 가 데이터를 가져올때 column 명까지 가져온다. # result.loc[i, 'modify_date'] 이런식의 활용 가능 result.columns = [desc[0] for desc in cursor.description] # 기존에 데이터가 없다면 if len(result) < 1: # 신규 Row 를 DB에 넣는다. strInsertQuery = "insert into data_corp" strInsertQuery += "(corp_code, corp_name, stock_code, modify_date)" strInsertQuery += "values" strInsertQuery += f"('{corpCode}', '{corpName}', '{stockCode}', '{modifyDate}')" cursor.execute(strInsertQuery) conn.commit() else: # 기존에 데이터가 있다면 for i in result.index: strLastUpdateDate = result.loc[i, 'modify_date'] # 마지막 업데이트 일자를 비교하여 if strLastUpdateDate < modifyDate: # XML 내용이 최신일 경우 기존데이터를 업데이트 한다. strUpdateQuery = "update data_corp" strUpdateQuery += f" set corp_name='{corpName}'" strUpdateQuery += f" ,stock_code='{stockCode}'" strUpdateQuery += f" ,modify_date='{modifyDate}'" strUpdateQuery += f" where corp_code='{corpCode}'" cursor.execute(strUpdateQuery) conn.commit() else: break def main(): insert() if __name__ == '__main__': main()
4. 위의 프로그램을 실행하면 빠른속도로 DB에 데이터가 쌓인다. 2020년 7월 기준으로 3200여 개의 데이터가 쌓이는 것을 확인 할 수 있다.
마치며
조금씩 파이썬에 익숙해져 가는 것 같다. 아직 못써본 모듈이 많을 것 같은데 그래프도 써보고 싶고.. 데이터베이스는 이제 조금더 다듬어서 모듈화 해서 쓰면 향후에도 유용하게 쓸 수 있지 않을까 생각해본다.
댓글 없음:
댓글 쓰기