def add_stock(data):
code = data[0].split()[0]
db.cursor.execute("SELECT * FROM stock WHERE code=?", (code, ))
result = db.cursor.fetchone()
if result is None:
db.cursor.execute("INSERT INTO stock(code, name, listed_day, listed_type, CFICode, listed) VALUES(?, ?, ?, ?, ?, ?)", \
(data[0].split()[0],
data[0].split()[1],
data[2].replace('/', '-'),
LISTED_TYPE.state_mapping_reverse[data[3]],
data[5],
1))
else:
db.cursor.execute("UPDATE stock SET listed = 1 WHERE code=?", (code, ))
db.commit()
def add_daily(date, info):
db.cursor.execute(
f"SELECT * FROM daily WHERE date='{date}' and code='{info.code}'")
result = db.cursor.fetchone()
if result != None:
logger.error(f"The data is duplicated: {info}")
return
db.cursor.execute(f"SELECT id FROM stock WHERE code='{info.code}'")
result = db.cursor.fetchone()
if result != None:
stock_id = result[0]
if info.volume != 0:
db.cursor.execute("INSERT INTO daily(date, code, name, open, high, low, close, volume, stock_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)", \
(date, info.code, info.name, info.open, info.high, info.low, info.close, info.volume, result[0]))
else:
db.cursor.execute("INSERT INTO daily(date, code, name, volume, stock_id) VALUES(?, ?, ?, ?, ?)", \
(date, info.code, info.name, info.volume, stock_id))
db.commit()
else:
logger.error(f"Cannot find stock for {info}")
def reset_listed():
db.cursor.execute("UPDATE stock SET listed = 0")
db.commit()
