# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python [conda env:root] *
#     language: python
#     name: conda-root-py
# ---

# !conda install pymysql -y

import pymysql
import pandas as pd

# create database qa_api;
conn = pymysql.connect(host='localhost', user='****', password='****', db='qa_api', charset='utf8')
curs = conn.cursor()
conn.commit()

sql_create_company = """create table company(
stock_code char(6) not null, 
company_name varchar(50) not null, 
primary key (stock_code)
);
"""
sql_create_report = """create table report(
report_id int not null, stock_code char(6) not null, title varchar(512),
price decimal(10,2), opinion varchar(20), writer varchar(20),
source varchar(20), url varchar(512), contents longtext, report_date date,
primary key (report_id), 
foreign key (stock_code) references company (stock_code)
);
"""
curs.execute(sql_create_company)
curs.execute(sql_create_report)
conn.commit()

df_company = pd.read_csv('company.csv', encoding='cp949')

df_company['종목코드'] = df_company['종목코드'].astype(str)
df_company['종목코드'] = df_company['종목코드'].apply(lambda x: x.zfill(6))
df_company

df_report = pd.read_csv('report.csv', encoding='utf-8')

# +
df_report['기업코드'] = df_report['기업코드'].astype(str)
df_report['기업코드'] = df_report['기업코드'].apply(lambda x: x.zfill(6))

df_report['적정가'] = df_report['적정가'].apply(lambda x: x.replace(",",""))
df_report = df_report.where((pd.notnull(df_report)), None)

# +
sql = "insert into company (stock_code, company_name) values (%s, %s)"

for stock_code, company_name in zip(df_company['종목코드'], df_company['회사명']):
    curs.execute(sql, (stock_code, company_name))

conn.commit()

# +
sql = """insert into report
(report_id, stock_code, title, price, opinion, writer, source, url, contents, report_date)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

for i in df_report.index:
    try:
        report_date = df_report.loc[i,'날짜']
        stock_code = df_report.loc[i,'기업코드']
        title = df_report.loc[i,'제목']
        price = df_report.loc[i,'적정가']
        opinion = df_report.loc[i,'투자의견']
        writer = df_report.loc[i,'작성자']
        source = df_report.loc[i,'출처']
        url = df_report.loc[i,'PDF_URL']
        report_id = df_report.loc[i,'파일명']
        contents = df_report.loc[i,'html']
        curs.execute(sql, (report_id, stock_code, title, price, opinion, writer, source, url, contents, report_date))

    except:
        print(i)
        continue

conn.commit()
conn.close()
# -



StarcoderdataJupyterScriptsDedupFiltered