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)