How to Create a Financial Statement Database

@listens_for(FinancialStatementLine.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    # insert logic here

import pandas as pd
from sqlalchemy.event import listens_for
from sqlalchemy import Column, ForeignKey, String, \
                       Integer, Float, Date
from sqlalchemy.orm import relationship
from sqlalchemy import Enum, UniqueConstraint
import enum
from setup_psql_environment import Base, session, db
from models.security import Security


class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column('name', String(100), nullable=False)
    cik = Column('cik', String(10))
    description = Column('description', String(2000))
    company_url = Column('company_url', String(100))
    sic = Column('sic', String(4))
    employees = Column('employees', Integer)
    sector = Column('sector', String(200))
    industry_category = Column('industry_category', String(200))
    industry_group = Column('industry_group', String(200))
    security = relationship('Security', back_populates='company')


class FinancialStatementType(enum.Enum):
    balance_sheet = 'balance sheet'
    income_statement = 'income statement'
    cash_flow_statement = 'cash flow statement'


class FinancialStatementPeriod(enum.Enum):
    fy = 'fy'
    q1 = 'q1'
    q2 = 'q2'
    q3 = 'q3'
    q4 = 'q4'


class FinancialStatement(Base):
    __tablename__ = 'financial_statement'
    id = Column(Integer, primary_key=True)
    name = Column('name', String(100), nullable=False)


class FinancialStatementLine(Base):
    __tablename__ = 'financial_statement_line'
    id = Column(Integer, primary_key=True)
    tag = Column(String(50), nullable=False, unique=True)
    name = Column('name', String(100), nullable=False, unique=True)
    description = Column('description', String(1000))
    sequences = relationship('FinancialStatementLineSequence', backref='line')
    facts = relationship('FinancialStatementFact', backref='line')


class FinancialStatementLineSequence(Base):
    __tablename__ = 'financial_statement_line_sequence'
    id = Column(Integer, primary_key=True)
    sequence = Column('sequence', Integer, nullable=False)
    financial_statement_id = Column(Integer,
                                    ForeignKey('financial_statement.id',
                                               onupdate='CASCADE',
                                               ondelete='CASCADE'),
                                    nullable=False)
    financial_statement_line_id = Column(Integer,
                                         ForeignKey('financial_statement_line.id',
                                                    onupdate='CASCADE',
                                                    ondelete='CASCADE'),
                                         nullable=False)
    UniqueConstraint('financial_statement_id',
                     'financial_statement_line_id')
    financial_statement = relationship('FinancialStatement')
    financial_statement_line = relationship('FinancialStatementLine')


class FinancialStatementLineAlias(Base):
    __tablename__ = 'financial_statement_line_alias'
    id = Column(Integer, primary_key=True)
    alias = Column(String(200), nullable=False, unique=True)
    financial_statement_line_id = Column(Integer,
                                         ForeignKey('financial_statement_line.id',
                                                    onupdate='CASCADE',
                                                    ondelete='CASCADE'),
                                         nullable=False)
    financial_statement_line = relationship('FinancialStatementLine')


class FinancialStatementFact(Base):
    __tablename__ = 'financial_statement_fact'
    __table_args__ = tuple(
        [UniqueConstraint('company_id',
                          'financial_statement_line_id',
                          'fiscal_year',
                          'fiscal_period')])
    id = Column(Integer, primary_key=True)
    fiscal_year = Column('fiscal_year', Integer, nullable=False)
    fiscal_period = Column('fiscal_period',
                           Enum(FinancialStatementPeriod),
                           nullable=False)
    filing_date = Column('filing_date', Date, nullable=False)
    start_date = Column('start_date', Date)
    end_date = Column('end_date', Date, nullable=False)
    amount = Column('amount', Float, nullable=False)
    company_id = Column(Integer,
                        ForeignKey('company.id',
                                   onupdate='CASCADE',
                                   ondelete='CASCADE'),
                        nullable=False)
    financial_statement_line_id = Column(
                                    Integer,
                                    ForeignKey('financial_statement_line.id',
                                               onupdate='CASCADE',
                                               ondelete='CASCADE'),
                                    nullable=False)

    company = relationship('Company')

commercial_income_statement = FinancialStatement(name='Commercial Income Statement')
commercial_balance_sheet = FinancialStatement(name='Commercial Balance Sheet Statement')
commercial_cash_flow_statement = FinancialStatement(name='Commercial Cash Flow Statement')

financial_income_statement = FinancialStatement(name='Financial Income Statement')
financial_balance_sheet = FinancialStatement(name='Financial Balance Sheet Statement')
financial_cash_flow_statement = FinancialStatement(name='Financial Cash Flow Statement')


@listens_for(FinancialStatement.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    session.add(commercial_income_statement)
    session.add(commercial_balance_sheet)
    session.add(commercial_cash_flow_statement)
    session.add(financial_income_statement)
    session.add(financial_balance_sheet)
    session.add(financial_cash_flow_statement)
    session.commit()

@listens_for(FinancialStatementLine.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    financial_statement_lines = pd.read_csv('financial_statements_lines.csv')
    financial_statement_lines = financial_statement_lines[['tag', 'name']].drop_duplicates('tag')

    for index, line in financial_statement_lines.iterrows():
        session.add(FinancialStatementLine(tag=line['tag'], name=line['name']))
    session.commit()

@listens_for(FinancialStatementLineSequence.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    financial_statement_lines = pd.read_csv('financial_statements_lines.csv')
    statement_types = ['commercial', 'financial']
    statement_codes = ['income_statement', 'balance_sheet_statement', 'cash_flow_statement']

    for statement_type in statement_types:
        for statement_code in statement_codes:
            statement_name = (statement_type + ' ' + statement_code.replace('_',' ')).title()
            statement = session.query(FinancialStatement) \
                .filter(FinancialStatement.name == statement_name).one()
            financial_statement_sequence = financial_statement_lines[
                (financial_statement_lines['statement_type'] == statement_type) & \
                (financial_statement_lines['statement_code'] == statement_code)]

            for index, row in financial_statement_sequence.iterrows():
                line = session.query(FinancialStatementLine) \
                    .filter(FinancialStatementLine.tag == row['tag']).one()
                session.add(FinancialStatementLineSequence(sequence=row['sequence'],
                                                        financial_statement_id=statement.id,
                                                        financial_statement_line_id=line.id))
    session.commit()

SELECT financial_statement_line.name FROM financial_statement_line
JOIN financial_statement_line_sequence
ON financial_statement_line.id = financial_statement_line_sequence.financial_statement_line_id
JOIN financial_statement
ON financial_statement.id = financial_statement_line_sequence.financial_statement_id
WHERE financial_statement.name = 'Commercial Income Statement'
ORDER BY financial_statement_line_sequence.sequence;