[Python] Dart 재무재표 분석 _ [3] 불러온 Dart 재무제표 DB에 저장하기

[Python] Dart 재무재표 분석 _ [3] 불러온 Dart 재무제표 DB에 저장하기

단일회사 및 다중회사 주요계정, 단일회사 전체 재무제표를 가져와서 DB에 저장하기 위한

DB table을 생성하여보자 

Django model을 생성하기 위해 필요한 모델 구조를 파악해보자

rcept_no(접수번호) : CharField

bsns_year(사업연도) : CharField

stock_code(종목코드) : CharField

reprt_code(보고서코드) : CharField

account_nm(계정명) : CharField

fs_div(개별연결구분) : CharField

fs_nm(개별연결명) : CharField

sj_div(재무제표구분) : CharField

sj_nm(재무제표명) :  CharField

thstrm_nm(당기명) : CharField

thstrm_dt(당기일자) : DateField

thstrm_amount(당기금액) : BigIntegerField

thstrm_add_amount(당기누적금액) : BigIntegerField

frmtrm_nm(전기명) : CharField

frmtrm_dt(전기날자) : DateField

frmtrm_amount(전기금액) : BigIntegerField

frmtrm_add_amount(전기누적금액) : BigIntegerField

bfefrmtrm_nm(전전기명) : CharField

bfefrmtrm_dt(전전기날자) : DateField

bfefrmtrm_amount(전전기 금액) : BigIntegerField

ord(계정과목정렬순서) : CharField

 

다음은 단일계정 자세한 재무재표를 읽어와 db에 입력하는 과정이다.

 

deepacount db 모델링

stock/models.py

class Deepacount(models.Model):
    id = models.BigAutoField(primary_key=True)
    rcept_no = models.CharField(max_length=20)
    reprt_code = models.CharField(max_length=6)
    bsns_year = models.CharField(max_length=4)
    corp_code = models.CharField(max_length=8)
    sj_div = models.CharField(max_length=12)
    sj_nm = models.CharField(max_length=12)
    account_id = models.CharField(max_length=190)
    account_nm = models.CharField(max_length=60)
    account_detail = models.CharField(max_length=140)
    fs_div = models.CharField(max_length=40)
    thstrm_nm = models.CharField(max_length=12)
    thstrm_amount = models.CharField(max_length=50)
    thstrm_add_amount = models.CharField(max_length=50)
    frmtrm_nm = models.CharField(max_length=12)
    frmtrm_amount = models.CharField(max_length=32)
    frmtrm_q_nm = models.CharField(max_length=12)
    frmtrm_q_amount = models.CharField(max_length=32)
    frmtrm_add_amount = models.CharField(max_length=32)
    bfefrmtrm_nm = models.CharField(max_length=12)
    bfefrmtrm_amount = models.CharField(max_length=40)
    ord = models.CharField(max_length=52)
    corp_name = models.CharField(max_length=20)

 

실제 데이터 db에 입력하는과정 

create_model_stock_single_deepacnt.py

import os
import django
from django.db import connection as con
from datetime import datetime
os.environ.setdefault("DJANGO_SETTINGS_MODULE", 'testDjango.settings')
django.setup()

from stock.packages import create_dart_info as dart

from stock.models import DeepAcount as sda
from stock.models import Stock as si

crtfc_key = '1aeb3259d8e0aeba54201cf7a049848316269bcd'
reprt_code = ['11013', '11012', '11014', '11011']
this_year = int(format(datetime.today().year))
corp_name, bsns_year = input('상장기업이름,사업연도:').split( )

def get_corp_code(corp_nm):
    corp_name = corp_nm

    try:
        corp_code = si.objects.get(corp_name=corp_name).corp_code

        return(corp_code)
    except:
        con.rollback()
        print("Failed reset increment")
corp_code = get_corp_code(corp_name)


## Reset Increment
sda.objects.all().delete()

def MakeDefaultIncrement():
    try:
        cursor = con.cursor()

        strSql = 'alter table stock_deepacount auto_increment = 1;'
        result = cursor.execute(strSql)
        con.commit()
        con.close()
    except:
        con.rollback()
        print("Failed reset increment")

MakeDefaultIncrement()


## 단일회사 주요계정
def get_fnlttSinglAcntAll(crtfc_key, corp_code, bsns_year, reprt_code, fs_div = "CFS"):
    items = ["rcept_no","reprt_code","bsns_year","corp_code","sj_div","sj_nm", "account_id","account_nm","account_detail","thstrm_nm", "thstrm_amount","thstrm_add_amount","frmtrm_nm","frmtrm_amount", "frmtrm_q_nm","frmtrm_q_amount","frmtrm_add_amount","bfefrmtrm_nm", "bfefrmtrm_amount","ord"] 
    item_names = ["접수번호","보고서코드","사업연도","고유번호","재무제표구분", "재무제표명","계정ID","계정명","계정상세","당기명","당기금액", "당기누적금액","전기명","전기금액","전기명(분/반기)", "전기금액(분/반기)","전기누적금액","전전기명","전전기금액", "계정과목정렬순서"] 
    params = {'crtfc_key':crtfc_key, 'corp_code':corp_code, 'bsns_year':bsns_year, 'reprt_code':reprt_code, 'fs_div':fs_div} 
    url = "https://opendart.fss.or.kr/api/fnlttSinglAcntAll.json?" 
    return dart.get_convertFnltt(url,items,item_names,params)


for year in range(int(bsns_year), this_year + 1):
    for code in reprt_code:
        df = get_fnlttSinglAcntAll(crtfc_key, corp_code, year, code)
        ##print(df) 
        print(len(df.columns))
        for i in range(0, len(df)):
            sda.objects.create(
            corp_name  = corp_name,
            rcept_no=df.loc[i][0],
            reprt_code=df.loc[i][1],
            bsns_year=df.loc[i][2],
            corp_code=df.loc[i][3],
            sj_div=df.loc[i][4],
            sj_nm=df.loc[i][5],
            account_id=df.loc[i][6],
            account_nm=df.loc[i][7],
            account_detail=df.loc[i][8],
            thstrm_nm=df.loc[i][9],
            thstrm_amount=df.loc[i][10],
            thstrm_add_amount=df.loc[i][11],
            frmtrm_nm=df.loc[i][12],
            frmtrm_amount=df.loc[i][13],
            frmtrm_q_nm=df.loc[i][14],
            frmtrm_q_amount=df.loc[i][15],
            frmtrm_add_amount=df.loc[i][16],
            bfefrmtrm_nm=df.loc[i][17],
            bfefrmtrm_amount=df.loc[i][18],
            ord=df.loc[i][19],
                    )


data_stock = sda.objects.values()
print(data_stock)