def update_future(self): # print(self.stockcode, 'UPDATE_FUTURE') df_no_na = self.df_future.dropna() from_date = df_no_na.DATE.min() to_date = df_no_na.DATE.max() query = "DELETE FROM jazzdb.T_STOCK_FUTURE_PRICE WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df_no_na, 'jazzdb.T_STOCK_FUTURE_PRICE')
def update_smar(self): # print(self.stockcode, 'UPDATE_SMAR') df_no_na = self.df_smar.dropna() from_date = df_no_na.DATE.min() to_date = df_no_na.DATE.max() query = "DELETE FROM jazzdb.T_STOCK_DAY_SMAR WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df_no_na, 'jazzdb.T_STOCK_DAY_SMAR')
def update_ohlc(self): df = self.df_ohlc_day.copy() from_date = df.DATE.min() to_date = df.DATE.max() columns = [ 'STOCKCODE', 'DATE', 'OPEN', 'HIGH', 'LOW', 'CLOSE', 'VALUE', 'ADJCLASS', 'ADJRATIO', 'VOLUME' ] query = "DELETE FROM jazzdb.T_STOCK_OHLC_DAY WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df[columns], 'jazzdb.T_STOCK_OHLC_DAY')
def update_bb(self): df = self.df_bb.copy() table = 'jazzdb.T_STOCK_BB' columns = self.get_columns_from_table(table) if self.check_all_columns_in_dataframe(df, columns): # print("ALL COLUMNS EXISTS") df_no_na = df.dropna() from_date = df_no_na.DATE.min() to_date = df_no_na.DATE.max() query = "DELETE FROM jazzdb.T_STOCK_BB WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df_no_na, 'jazzdb.T_STOCK_BB')
def crawl_itooza(stockcode): url = "https://search.itooza.com/search.htm?seName=%s&cpv=#indexTable3" % ( stockcode) response = requests.get(url, headers=headers) response.encoding = 'EUC-KR' html = response.text try: df_list = pd.read_html(html) ## 연환산 df = pd.DataFrame(columns=[ 'STOCKCODE', 'TYPE', 'DATE', 'EPSC', 'EPSI', 'PER', 'BPS', 'PBR', 'DV', 'DVR', 'ROE', 'NPR', 'OPR' ]) _c = df_list[2].transpose() _c.reset_index(inplace=True) for row in _c.values[1:]: df.loc[len(df)] = [stockcode, 'c'] + row[:-1].tolist() ## 분기 _q = df_list[4].transpose() _q.reset_index(inplace=True) for row in _q.values[1:]: df.loc[len(df)] = [stockcode, 'q'] + row[:-1].tolist() ## 연간 _y = df_list[3].transpose() _y.reset_index(inplace=True) for row in _y.values[1:]: df.loc[len(df)] = [stockcode, 'y'] + row[:-1].tolist() df = df.fillna(-1) df.DATE = df.DATE.str.replace('월', '', regex=False) df.DATE = df.DATE.str.replace('.', '', regex=False) df = df[[ 'STOCKCODE', 'DATE', 'TYPE', 'EPSC', 'EPSI', 'PER', 'BPS', 'PBR', 'DV', 'DVR', 'ROE', 'NPR', 'OPR' ]] # df_origin = db.selectpd("SELECT * FROM jazzdb.T_STOCK_FINAN WHERE STOCKCODE = '079940' ORDER BY TYPE ASC, DATE DESC") # # print(df_origin) # current_quarter = '2103' # prev_quarter = '2106' # print(df[df['DATE'].isin([current_quarter, prev_quarter])]) current_quarter = '2106' df_current = df[df['DATE'].isin([current_quarter])] if len(df_current) > 0: query_delete = 'DELETE FROM jazzdb.T_STOCK_FINAN WHERE STOCKCODE="%s" AND DATE = "%s"' % ( stockcode, current_quarter) db.delete(query_delete) db.insertdf(df[df['DATE'].isin([current_quarter])], 'jazzdb.T_STOCK_FINAN') return True else: return False ## IF CHANGED OR NOT EXISTS DO UPDATE 를 구현하도록 ! except Exception as e: print(e)
from jazzstock_bot.common import connector_db as db from datetime import datetime as dt dq = ''' DELETE FROM jazzdb.T_STOCK_FUTURE_PRICE WHERE DATE IN ( SELECT DATE FROM jazzdb.T_DATE_INDEXED WHERE CNT BETWEEN 0 AND 61 ) ''' db.delete(dq) def db_readAll(): # DB에서 [종목명,종목코드] 로 구성된 데이터셋을 받아옴. # dbUpdateDate = db.selectSingleValue('SELECT max(date) FROM test.t_stock_shares_info') query = """ SELECT A.STOCKCODE, A.STOCKNAME FROM jazzdb.T_STOCK_CODE_MGMT A WHERE 1=1 AND A.LISTED = 1 """ for eachRow in db.select(query):