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):