def create_tables(self):
"""
Creates tables for database
:return: None
"""
sql_portfolio = """create table if not exists portfolio (
id integer primary key,
stock text not null);"""
sql_transaction = """create table if not exists transactions (
id integer primary key,
stock_id integer not null,
date text not null,
number_of_shares real not null,
price_per_share real not null,
transaction_type text not null,
FOREIGN KEY (stock_id) REFERENCES portfolio (id));
"""
try:
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_portfolio)
dataset.execute(sql_transaction)
con.close()
except Error:
print(Error.with_traceback())
finally:
con.close()
def delete_stock(self, stock):
"""
Deletes a stock and all transactions
:param stock: Stock object
:return: None
"""
if not isinstance(stock, Stock):
raise ValueError("Argument is not type Stock")
if len(stock.transactions) != 0:
for x in stock.transactions:
self.delete_transactions(x)
sql_statement = """delete from portfolio
where id = ?"""
argument_list = []
argument_list.append(stock.stock_id)
try:
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_statement, argument_list)
con.commit()
except Error:
print(Error.with_traceback())
finally:
con.close()
def get_stock(self, symbol):
"""
Returns a stock object containing the information from the database.
:param symbol: String of stock ticker symbol
:return: Stock object
"""
try:
if not isinstance(symbol, str):
raise ValueError("Parameter is not type string")
stock_to_return = Stock(symbol)
try:
tran_list = self.get_transactions(symbol)
except NoResultsException.NoResultsException:
tran_list = []
stock_id = self.get_stock_id(symbol)
stock_to_return.transactions = tran_list
stock_to_return.stock_id = stock_id
return stock_to_return
except Error:
print(Error.with_traceback())
def delete_individual_transaction_using_primary_key(self, primary_key):
"""
Deletes individual transaction given an transaction primary key
:param primary_key: integer primary key of transaction
:return: None
"""
if not isinstance(primary_key, int):
raise ValueError("Wrong argument. Argument must be an integer")
sql_statement = """delete from transactions where id = ?"""
argument_list = [primary_key]
try:
conn = self.__init_connection()
dataset = conn.cursor()
dataset.execute(sql_statement, argument_list)
conn.commit()
except Error:
print(Error.with_traceback())
finally:
conn.close()
def delete_transactions(self, tran):
"""
Deletes all transaction of a given stock
:param tran: Transaction from a given stock
:return: None
"""
if not isinstance(tran, Transaction):
raise ValueError("Argument is not type Transaction")
try:
list_of_arguments = [tran.symbol]
sql_command = """delete from transactions
where stock_id = (select id from portfolio where stock = ?)"""
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_command, list_of_arguments)
con.commit()
except Error:
print(Error.with_traceback())
finally:
con.close()
def get_all_stocks(self):
"""
Gets All stocks from the database with no transaction data
:return: List of stock objects
"""
sql_statement = """select id, stock from portfolio"""
list_to_return = []
try:
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_statement)
con.commit()
for x in dataset:
item = Stock(x[1])
item.stock_id = x[0]
list_to_return.append(item)
return list_to_return
except Error:
print(Error.with_traceback())
finally:
con.close()