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