update saved data into database

from datetime import date
import time
import pandas as pd
from io import StringIO
import sqlite3

def update_twse_db(orddate):

    dd=date.fromordinal(orddate)
    datestr=dd.strftime("%Y%m%d")

    f=open("d:/archive/stock/twse/"+datestr, "rt", encoding="utf8")
    print("fetching ", datestr)
    content=f.read()
    f.close()

    # if content is empty, the stock market is not open
    if content=='':
      return None

    # clear all '=' in some lines
    content=content.replace('=','')

    # filter all lines without 10 fields of data
    lines=content.split('\n')
    lines=list(filter(lambda l:len(l.split('",')) > 10, lines))

    # join all lines with carriage return
    content = "\n".join(lines) 

    # use pd to read content as csv file
    df=pd.read_csv(StringIO(content))

 

    # remove all element str and remove ','
    df = df.astype(str)
    df = df.apply(lambda s: s.str.replace(',', ''))

    # add 'date' field to the sheet
    df['orddate']=pd.to_numeric(orddate) 

    df = df.rename(columns={'證券代號':'stock_id'})
    df = df.rename(columns={'證券名稱':'name'})
    df = df.rename(columns={'成交股數':'shares'})
    df = df.rename(columns={'成交筆數':'transactions'})
    df = df.rename(columns={'成交金額':'amount'})
    df = df.rename(columns={'開盤價':'open'})
    df = df.rename(columns={'最高價':'high'})
    df = df.rename(columns={'最低價':'low'})
    df = df.rename(columns={'收盤價':'close'})
    df = df.rename(columns={'漲跌(+/-)':'sign'})
    df = df.rename(columns={'漲跌價差':'diff'})
    df = df.rename(columns={'最後揭示買價':'lastbuy'})
    df = df.rename(columns={'最後揭示買量':'lastbuys'})
    df = df.rename(columns={'最後揭示賣價':'lastsell'})
    df = df.rename(columns={'最後揭示賣量':'lastsells'})
    df = df.rename(columns={'本益比':'eps'})


    # drop stock id not equal to 4 chars
    df = df[df.stock_id.str.len()==4]
   
    # set index as stock_id and date
    df = df.set_index(['stock_id', 'orddate']) 
    df = df.apply(lambda ss:pd.to_numeric(ss, errors='coerce') if ((ss.name!='name') and (ss.name!='sign')) else ss)

    # remove the last empty field

    # df = df[df.columns[df.isnull().all() == False]]
    df = df.drop(columns=['Unnamed: 16','sign'])

    return df



def update_tpex_db(orddate):

    dd=date.fromordinal(orddate)
    datestr=dd.strftime("%Y%m%d")

    f=open("d:/archive/stock/tpex/"+datestr, "rt", encoding="utf8")

    print("fetching ", datestr)

    content=f.read()
    f.close()

    # clear all '=' in some lines
    content=content.replace('=','')

    # filter all lines without 10 fields of data
    lines=content.split('\n')
    lines=list(filter(lambda l:len(l.split(',')) > 10, lines))

    # join all lines with carriage return
    content = "\n".join(lines)

    # if content is less than 100 lines, the stock is not open
    if content.count('\n')<100:
      return None

    # use pd to read content as csv file
    df=pd.read_csv(StringIO(content))

    # remove all element str and remove ','
    df = df.astype(str)
    df = df.apply(lambda s: s.str.replace(',', ''))

    # add 'date' field to the sheet
    df['orddate']=pd.to_numeric(orddate)

    # remove all space char in column name
    for i in df.columns:
        df = df.rename(columns={i : i.replace(' ', '')})

    df = df.rename(columns={'代號':'stock_id'})
    df = df.rename(columns={'名稱':'name'})
    df = df.rename(columns={'收盤':'close'})
    df = df.rename(columns={'漲跌':'skip_1'})
    df = df.rename(columns={'開盤':'open'})
    df = df.rename(columns={'最高':'high'})
    df = df.rename(columns={'最低':'low'})
    df = df.rename(columns={'均價':'skip_2'})
    df = df.rename(columns={'成交股數':'shares'})
    df = df.rename(columns={'成交金額(元)':'amount'})
    df = df.rename(columns={'成交筆數':'transactions'})
    df = df.rename(columns={'最後買價':'lastbuy'})
    df = df.rename(columns={'最後賣價':'lastsell'})
    df = df.rename(columns={'發行股數':'allshares'})
    df = df.rename(columns={'次日參考價':'skip_3'})
    df = df.rename(columns={'次日漲停價':'skip_4'})
    df = df.rename(columns={'次日跌停價':'skip_5'})

    # drop stock id not equal to 4 chars (including '代號')
    df = df[df.stock_id.str.len()==4]

    # set index as stock_id and date
    df = df.set_index(['stock_id', 'orddate'])

    # convert all field to numbers except name
    df = df.apply(lambda s:pd.to_numeric(s, errors='coerce') if (s.name!='name') else s)

    # drop all columns to be skipped
    df = df.drop(columns=['skip_1','skip_2','skip_3','skip_4','skip_5'])

    return df

argv1='20190703'
argv2='20190731'

d1=date.fromisoformat(argv1[:4]+'-'+argv1[4:6]+'-'+argv1[6:8])
d2=date.fromisoformat(argv2[:4]+'-'+argv2[4:6]+'-'+argv2[6:8])

d1ord=d1.toordinal()
d2ord=d2.toordinal()

conn=sqlite3.connect('d:/archive/stock/twstock.db')

for i in range(d1ord, d2ord+1):

    # test if datalog contains orddate data
    cc = conn.execute("select twse_update from datalog where orddate="+str(i))
    res = cc.fetchone()

    if res!=None :
        continue

    # this orddate data is not updated yet, go on update the database
    df=update_twse_db(i)

    # if the content is empty, the day is off, skip
    if (type(df)!=pd.core.frame.DataFrame):
        continue

    df.to_sql('stock', conn, if_exists='append')
   
    # updata datalog for orddate
    cc = conn.execute("insert into datalog (orddate, twse_update) values ("+str(i)+",1)")   
    conn.commit()
   
    df=update_tpex_db(i)

    if (type(df)!=pd.core.frame.DataFrame):
        continue

    df.to_sql('stock', conn, if_exists='append')
   
    # update datalog for orddate
    cc = conn.execute("update datalog set tpex_update = 1 where orddate="+str(i))
    conn.commit()

conn.close()