def insert_yf_daily_price(securityID,
data,
*,
data_vendorID=0,
cursor=None) -> int:
"""
data: pd.DataFrame of only 1 symbol
data_vendor is default to be Yahoo Finance
"""
if isinstance(data, list):
if len(data) > 0:
if len(data[0]) == 6:
csr = cursor if cursor is Cursor else connect_as_user().cursor(
)
query = insert_daily_price_query(securityID, data_vendorID,
'%s', '%s', '%s', '%s', '%s',
'%s')
number_of_rows = csr.executemany(query, data)
return number_of_rows
else:
raise Exception('Number of columns is not correct.')
else:
raise Exception('Cannot insert an empty dataset.')
else:
raise Exception('Dataset is of unsupported type.')
def does_exist(query) -> bool:
conn = connect_as_user()
cursor = conn.cursor()
cursor.execute(query)
res = cursor.fetchall()
does_exist = len(res) > 0
return does_exist
def bulk_insert_yf_daily_price(lst_symbols,
dataset,
*,
data_vendorID,
cursor=None) -> int:
"""
data: pd.DataFrame of records of more than 1 symbol
lst_symbols: list of symbols contained in the DataFrame as strings
Returns number of rows affected
"""
if len(lst_symbols) == 0:
raise Exception(
'A list of symbols contained in the dataset must be specified')
elif not isinstance(dataset, DataFrame):
raise Exception('Dataset is of unsupported type')
else:
if isinstance(dataset.columns, MultiIndex):
csr = cursor if cursor is Cursor else connect_as_user().cursor()
lst_db_symbols = get_security_symbols(cursor=csr)
number_of_rows = 0
for symbol in lst_symbols:
if not (symbol in lst_db_symbols):
ticker = yf.Ticker(symbol)
insert_yf_ticker(ticker)
securityID = get_security_ids(f"WHERE abbrev = '{symbol}'")[0]
data = dataset[symbol]
prep_data = parse_price_df(data)
number_of_rows += insert_yf_daily_price(
securityID, prep_data, data_vendorID=data_vendorID)
return number_of_rows
else:
raise Exception('Unable to parse data from DataFrame')
def step3_test_create_tables(self):
connection = connect_as_user('algotrader1', 'algotrading')
if connection != None:
cursor = connection.cursor()
setup_schema(cursor)
res = cursor.execute(
"SELECT table_name FROM information_schema.tables WHERE table_schema='algotrading'"
)
self.assertEqual(res, len(setup_schema_queries))
else:
self.skipTest("No DB connection found")
def get_data_vendor_ids(cursor=None):
csr = cursor if cursor is Cursor else connect_as_user().cursor()
if csr:
csr.execute(f"SELECT ID FROM {data_vendor_table_name}")
rows = csr.fetchall()
lst = [row[0] for row in rows]
return lst
else:
raise Exception(
get_data_vendor_ids.__name__ +
' Cannot connect to the database to verify data vendor.')
def get_currency_ids(condition='', *, cursor=None):
csr = cursor if cursor is Cursor else connect_as_user().cursor()
if csr:
query = f'SELECT ID FROM {currency_table_name} ' + condition
csr.execute(query)
rows = csr.fetchall()
lst = [row[0] for row in rows]
return lst
else:
raise Exception(get_currency_ids.__name__ +
' Cannot connect to the database to verify currency.')
def get_country_ids(cursor=None):
csr = cursor if cursor is Cursor else connect_as_user().cursor()
if csr:
csr.execute(f'SELECT ID FROM {country_table_name}')
rows = csr.fetchall()
lst_countries = [row[0] for row in rows]
return lst_countries
else:
raise Exception(
get_country_ids.__name__ +
' Cannot connect to the database to verify country name.')
def setUp(self):
print('setUp method called.')
self._cursor = connect_as_user().cursor()
mute_log()
db_reset = DbInitializationTest()
db_reset.run_tests()
unmute_log()
self._ticker = yf.Ticker('AAPL')
if self._testMethodName == self.test_insert_daily_price.__name__:
insert_yf_ticker(self._ticker)
def get_security_symbols(condition='', *, cursor=None) -> list:
csr = cursor if cursor is Cursor else connect_as_user().cursor()
if csr:
query = f'SELECT abbrev FROM {securities_table_name} ' + condition
csr.execute(query)
rows = csr.fetchall()
lst_symbols = [row[0] for row in rows]
return lst_symbols
else:
raise Exception(
get_security_symbols.__name__ +
' Cannot connect to the database to get security data.')
def insert_yf_ticker(new_ticker, *, cursor=None):
"""
Parse a ticker from yfinance and insert if not exists
"""
if type(new_ticker) == yf.Ticker:
try:
symbol = new_ticker.info['symbol']
except ValueError as e:
raise e
if not security_exists(security_abbrev=symbol):
csr = cursor if cursor is Cursor else connect_as_user().cursor()
# insert if not exists
currency = new_ticker.info['currency']
exchange = new_ticker.info['exchange']
insert_currency(csr, currency)
insert_exchange(csr, exchange)
exchangeID = get_exchange_ids(f"WHERE abbrev = '{exchange}'")[0]
currencyID = get_currency_ids(f"WHERE abbrev = '{currency}'")[0]
insert_security(csr, exchangeID, symbol, currencyID=currencyID)
else:
raise Exception('Method requires object of class yfinance.Ticker.')
def step2_test_SU_connection(self):
setup_db(drop_old_info=True)
conn = connect_as_user('algotrader1')
self.assertIsInstance(conn, DB_Connection)
f"SELECT * FROM {daily_price_table_name} WHERE securityID=1")
if cursor.fetchone() != None: is_passed = False
self.assertTrue(is_passed)
def run_tests(self, cursor):
if not isinstance(cursor, Cursor):
raise Exception("DB cursor not found.")
for name, step in self._steps():
try:
step(cursor)
print('Test {} passed'.format(name))
except Exception as e:
self.fail("{} failed ({}: {})".format(step, type(e), e))
print('All tests passed')
if __name__ == "__main__":
# Reset DB for testing
print('Resetting database for testing')
mute_log()
db_resetter = DbInitializationTest()
db_resetter.run_tests()
unmute_log()
# insert & delete test
conn = connect_as_user()
cursor = conn.cursor()
tester = DBInsertTests()
tester.run_tests(cursor)