2020년 7월 11일 토요일

[Python] PostgreSQL 12.3 연동하기 (feat. 증권시장 기업 DB화)

개요

 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 연동 방법은 다음의 블로그를 참조하였다. 연동방법이 간략하게 잘 나와있다.

 XML 을 파싱해서 원하는 데이터만 DB에 넣는 작업을 위해서 필요한 모듈은 다음과 같다.

# 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여 개의 데이터가 쌓이는 것을 확인 할 수 있다.



마치며

 조금씩 파이썬에 익숙해져 가는 것 같다. 아직 못써본 모듈이 많을 것 같은데 그래프도 써보고 싶고.. 데이터베이스는 이제 조금더 다듬어서 모듈화 해서 쓰면 향후에도 유용하게 쓸 수 있지 않을까 생각해본다.

댓글 없음:

댓글 쓰기

Recent Posts