def insert_stock(dataset):
cur = connection.cursor()
for d in dataset:
cur.execute('INSERT OR IGNORE INTO Stock (stock_id) VALUES (?)',
(d['stock_id'], ))
information = [d['stock_id'], d['price'], d['volume']]
cur.execute(
'SELECT Information.stock_id, Information.price, Information.volume '
'FROM Stock INNER JOIN Information on (Stock.stock_id = Information.stock_id)'
'WHERE Information.stock_id = ? AND Information.price = ? '
'ORDER BY Information.stock_id', (d['stock_id'], d['price']))
row = cur.fetchall()
if row:
total_volume = row[0][2] + d['volume']
update_stock([{
"stock_id": d['stock_id'],
"price": d['price'],
"volume": total_volume
}])
continue
cur.execute(
'INSERT INTO Information (stock_id, price, volume) VALUES (?, ?, ?)',
information)
connection.commit()
def create_table():
cur = connection.cursor()
cur.execute('DROP TABLE IF EXISTS TempSell')
cur.execute('CREATE TABLE IF NOT EXISTS TempBuy ('
'stock_id INTEGER PRIMARY KEY,'
'price FLOAT NOT NULL,'
'volume INTEGER NOT NULL);')
cur.execute('CREATE TABLE IF NOT EXISTS TempSell ('
'stock_id INTEGER PRIMARY KEY,'
'predictedPrice FLOAT NOT NULL,'
'price FLOAT NOT NULL,'
'volume INTEGER NOT NULL);')
cur.execute('CREATE TABLE IF NOT EXISTS Stock ('
'stock_id INTEGER PRIMARY KEY);')
cur.execute('CREATE TABLE IF NOT EXISTS Information ('
'stock_id INTEGER NOT NULL,'
'price FLOAT NOT NULL,'
'volume INTEGER NOT NULL,'
'FOREIGN KEY (stock_id) REFERENCES Stock(stock_id)'
'ON UPDATE CASCADE ON DELETE CASCADE);')
connection.commit()
def delete_temp(stock_id, mode):
assert mode in ["buy", "sell"]
cur = connection.cursor()
if mode == "buy":
cur.execute('DELETE FROM TempBuy WHERE stock_id = ?', [stock_id])
else:
cur.execute('DELETE FROM TempSell WHERE stock_id = ?', [stock_id])
connection.commit()
def update_stock(dataset):
cur = connection.cursor()
for d in dataset:
if d['volume'] == 0:
cur.execute(
'DELETE FROM Information WHERE stock_id = ? AND price = ?',
(d['stock_id'], d['price']))
else:
cur.execute(
'UPDATE Information SET volume = ? WHERE stock_id = ? AND price = ?',
(d['volume'], d['stock_id'], d['price']))
connection.commit()
def insert_temp(dataset, mode):
assert mode in ["buy", "sell"]
cur = connection.cursor()
for d in dataset:
if mode == "buy":
information = [d['stock_id'], d['price'], d['volume']]
cur.execute(
'INSERT INTO TempBuy (stock_id, price, volume) VALUES (?, ?, ?)',
information)
else:
information = [
d['stock_id'], d['predictedPrice'], d['price'], d['volume']
]
cur.execute(
'INSERT INTO TempSell (stock_id, predictedPrice, price, volume) VALUES (?, ?, ?, ?)',
information)
connection.commit()
def delete_stock(stock_id):
cur = connection.cursor()
cur.execute('PRAGMA foreign_keys = ON')
cur.execute('DELETE FROM Stock WHERE stock_id = ?', [stock_id])
connection.commit()