def select_and_insert(mode):
assert mode in ["buy", "sell"]
from database.stock import insert_stock, update_stock
cur = connection.cursor()
if mode == "buy":
cur.execute('SELECT * FROM TempBuy')
else:
cur.execute('SELECT * FROM TempSell')
rows = cur.fetchall()
for row in rows:
if mode == "buy":
if check(row[0], row[1]):
insert_stock([{
'stock_id': row[0],
'price': row[1],
'volume': row[2]
}])
delete_temp(row[0], "buy")
else:
if check(row[0], row[1]):
# row[1] -> predicted price, row[2]-> price we spent when we brought it
update_stock([{
'stock_id': row[0],
'price': row[2],
'volume': row[3]
}])
delete_temp(row[0], "sell")
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 get_stock():
from database.temp import select_and_insert
select_and_insert("sell")
select_and_insert("buy")
cur = connection.cursor()
cur.execute(
'SELECT Information.stock_id, Information.price, Information.volume '
'FROM Stock INNER JOIN Information on (Stock.stock_id = Information.stock_id)'
'ORDER BY Information.stock_id')
rows = cur.fetchall()
info = []
for row in rows:
info_dic = {'stock_id': row[0], 'price': row[1], 'volume': row[2]}
info.append(info_dic)
return info
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()