def get_all(cls) -> list: """ Get all stocks from database """ stocks = [] with pool_manager() as conn: query = f"SELECT id, name, company_name, country, industry, sector, in_use FROM {cls._table};" try: conn.cursor.execute(query) stocks = conn.cursor.fetchall() except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError): message = "Could not get stocks" logger.warning(message) stocks = [ Stock(pk=pk, name=name, company_name=company_name, country=country, industry=industry, sector=sector, in_use=in_use) for pk, name, company_name, country, industry, sector, in_use in stocks ] return stocks
def get_data_for_last_day(cls, pk: int) -> list: stock_data_for_last_day = [] datetime_now = datetime.now().strftime(DATETIME_PATTERN) datetime_yesterday = (datetime.now() - timedelta(days=1)).strftime(DATETIME_PATTERN) with pool_manager() as conn: query = """SELECT * FROM stocks_data WHERE stock_id = %(stock_id)s AND %(yesterday)s <= created_at AND created_at < %(today)s ORDER BY created_at;""" try: conn.cursor.execute( query, { 'stock_id': pk, 'yesterday': datetime_yesterday, 'today': datetime_now }) stock_data_for_last_day = conn.cursor.fetchall() except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError) as err: logger.info(f"Error! {err}") stock_data_for_last_day = [ StockData(pk=pk, stock_id=stock_id, price=price, created_at=created_at) for pk, stock_id, price, created_at in stock_data_for_last_day ] return stock_data_for_last_day
def create(cls, stocks: list) -> object: """Creates a new record in Dashboard table :param stocks: list of stock ids for specific dashboard :return: instance of Dashboard """ stock_names = [stock.name for stock in stocks] stock_names.sort() stock_names = " ".join(stock_names) dashboard_hash = generate_dashboard_hash(stock_names=stock_names) # Check if dashboard already exists returning it existing_dashboard = Dashboard.get_by_hash(dashboard_hash) if existing_dashboard: return existing_dashboard with pool_manager() as conn: dashboard_has_stocks_table = 'public.dashboard_has_stocks' try: insert_dashboard_query = f"""INSERT INTO {cls._table} (dashboard_hash) VALUES (%(dashboard_hash)s) RETURNING dashboard_hash;""" conn.cursor.execute(insert_dashboard_query, {'dashboard_hash': dashboard_hash}) dashboard_hash = conn.cursor.fetchone() dashboard_has_stocks_insert_data = [(stock.pk, dashboard_hash) for stock in stocks] insert_dashboard_has_stocks_query = (f"INSERT INTO {dashboard_has_stocks_table} " "(stock_id, dashboard_hash)" " VALUES (%s, %s);") conn.cursor.executemany(insert_dashboard_has_stocks_query, dashboard_has_stocks_insert_data) return Dashboard(dashboard_hash=dashboard_hash) except (psycopg2.ProgrammingError, psycopg2.DatabaseError) as error: logger.info(f"Error Dashboard model create {error}")
def update(self, price=None, created_at=None): """ Changes values of the stock data to the given. :param price: StockData price :param created_at: StockData creation date, time :return: updated StockData instance """ data_to_update = [] if price is not None: data_to_update.append("price = %(price)s") if created_at is not None: data_to_update.append("created_at = %(created_at)s") created_at = created_at.strftime("%Y-%m-%d %H:%M:%S") query = f"""UPDATE {self._table} SET {', '.join(data_to_update)} WHERE id = %(id)s RETURNING id, stock_id, price, created_at;""" with pool_manager() as conn: try: conn.cursor.execute(query, { 'price': price, 'created_at': created_at, 'id': self.pk }) contents = conn.cursor.fetchone() price = contents[2] created_at = contents[3] self.price = price self.created_at = created_at return True except (DataError, ProgrammingError): return False
def create(cls, stock_id: int, price: float, created_at: datetime): """ Create new stock data :param stock_id: Stock id :param price: StockData price :param created_at: StockData creation date, time :return: StockData instance """ with pool_manager() as conn: query = f"""INSERT INTO {cls._table} (stock_id, price, created_at) VALUES (%(stock_id)s, %(price)s, %(created_at)s) RETURNING id, stock_id, price, created_at;""" try: conn.cursor.execute( query, { 'stock_id': stock_id, 'price': price, 'created_at': created_at.strftime("%Y-%m-%d %H:%M:%S") }) pk, stock_id, price, created_at = conn.cursor.fetchone() return StockData(pk=pk, stock_id=stock_id, price=price, created_at=created_at) except (DataError, ProgrammingError): return None
def update(self, name: str = None, company_name: str = None, country: str = None, industry: str = None, sector: str = None, in_use: bool = False) -> bool: """ Update an existing instance in database :param name: :param company_name: :param country: :param industry: :param sector: :param in_use: :return: True if update was successful and False if not """ data_to_update = [] if name is not None: data_to_update.append("name = %(name)s") if company_name is not None: data_to_update.append("company_name = %(company_name)s") if country is not None: data_to_update.append("country = %(country)s") if industry is not None: data_to_update.append("industry = %(industry)s") if sector is not None: data_to_update.append("sector = %(sector)s") if in_use is not None: data_to_update.append("in_use = %(in_use)s") query = f"""UPDATE {self._table} SET {', '.join(data_to_update)} WHERE id = %(pk)s RETURNING id, name, company_name, country, industry, sector, in_use; """ with pool_manager() as conn: try: conn.cursor.execute( query, { 'name': name, 'company_name': company_name, 'country': country, 'industry': industry, 'sector': sector, 'pk': self.pk, 'in_use': in_use }) pk, name, company_name, country, industry, sector, in_use = conn.cursor.fetchone( ) # pylint: disable=C0103, W0612 self.name = name self.company_name = company_name self.country = country self.industry = industry self.sector = sector self.in_use = in_use return True except (psycopg2.DataError, psycopg2.ProgrammingError): return False
def delete_by_hash(cls, dashboard_hash: str) -> bool: """ Deletes a Dashboard instance by its dashboard_hash. """ with pool_manager() as conn: query = f"DELETE FROM {cls._table} WHERE dashboard_hash = %(dashboard_hash)s;" try: conn.cursor.execute(query, {'dashboard_hash': dashboard_hash}) return True except(psycopg2.DataError, psycopg2.ProgrammingError) as error: logger.info(f"Error Dashboard model delete_by_hash {error}")
def delete_by_id(cls, pk: int): """ Delete stock data with given pk :param pk: StockData id :return: True if instance was deleted, else False """ if not StockData.get_by_id(pk): return False with pool_manager() as conn: query = f"DELETE FROM {cls._table} WHERE id = %(id)s " try: conn.cursor.execute(query, {'table': cls._table, 'id': pk}) return True except (DataError, ProgrammingError): return False
def get_by_hash(cls, dashboard_hash: str): """Getting a dashboard by its hash from Dashboard table :return: instance of DashboardConfig model """ with pool_manager() as conn: get_dashboard_id_query = f"""SELECT dashboard_hash FROM {cls._table} WHERE dashboard_hash = %(dashboard_hash)s;""" try: conn.cursor.execute(get_dashboard_id_query, {'dashboard_hash': dashboard_hash}) dashboard_hash = conn.cursor.fetchone()[0] if dashboard_hash: return Dashboard(dashboard_hash=dashboard_hash) except (psycopg2.ProgrammingError, psycopg2.DatabaseError, TypeError) as error: logger.info(f"Error Dashboard model get_by_hash {error}")
def delete_by_id(cls, pk: int) -> bool: # pylint: disable=C0103 """ Delete instance by id in database :param pk: instance id in database :return: True if delete was successful and False if not """ if not Stock.get_by_id(pk): return False with pool_manager() as conn: query = f"DELETE FROM {cls._table} WHERE id = %(id)s;" try: conn.cursor.execute(query, {'id': pk}) return True except (psycopg2.DataError, psycopg2.ProgrammingError): return False
def update(self, dashboard_hash: str) -> bool: """ Updates an existing dashboard. """ if not dashboard_hash: return False with pool_manager() as conn: query = f"""UPDATE {self._table} SET dashboard_hash = %(dashboard_hash)s WHERE dashboard_hash = %(dashboard_hash)s RETURNING dashboard_hash;""" try: conn.cursor.execute(query, {'dashboard': dashboard_hash}) dashboard_hash = conn.cursor.fetchone() self.dashboard_hash = dashboard_hash return True except (psycopg2.ProgrammingError, psycopg2.DatabaseError) as error: logger.info(f"Error Dashboard model update {error}")
def get_by_id(cls, pk: int): """ Get stock data with given pk. :param pk: StockData id :return: StockData instance with given pk """ with pool_manager() as conn: query = f"SELECT * FROM {cls._table} WHERE id = %(id)s" try: conn.cursor.execute(query, {'id': pk}) pk, stock_id, price, created_at = conn.cursor.fetchone() return StockData(pk=pk, stock_id=stock_id, price=price, created_at=created_at) except (DataError, ProgrammingError, TypeError): return None
def get_data_for_time_period(self, datetime_from: datetime, datetime_to: datetime) -> list: """ Return list of stock datas for current stock in specified period of time :param datetime_from: start time of period to get stock data :param datetime_to: end time of period to get stock data :return: list of StockData """ stock_data_for_time_period = [] datetime_from_str, datetime_to_str = datetime_from.strftime( DATETIME_PATTERN), datetime_to.strftime(DATETIME_PATTERN) with pool_manager() as conn: query = "SELECT id, stock_id, price, created_at FROM stocks_data " \ "WHERE stock_id = %(stock_id)s " \ "AND %(datetime_from)s <= created_at AND created_at < %(datetime_to)s " \ "ORDER BY created_at;" try: conn.cursor.execute( query, { 'stock_id': self.pk, 'datetime_from': datetime_from_str, 'datetime_to': datetime_to_str }) stock_data_for_time_period = conn.cursor.fetchall() except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError): message = f"Could not get stock data for pk={self.pk}, datetime_from={datetime_from_str}, " \ f"datetime_to={datetime_to_str}" logger.warning(message) stock_data_for_time_period = [ StockData(pk=pk, stock_id=stock_id, price=price, created_at=created_at) for pk, stock_id, price, created_at in stock_data_for_time_period ] if Stock._are_gaps_in_data(datetime_from, datetime_to, stock_data_for_time_period): self._fill_gaps_in_data(datetime_from, datetime_to, stock_data_for_time_period) return stock_data_for_time_period
def create(cls, name: str, company_name: str, country: str, industry: str, sector: str, in_use: bool = None) -> Stock: """ Create a new instance in database :param name: short name of company stocks :param company_name: name of company :param country: :param industry: :param sector: :return: instance """ with pool_manager() as conn: query = f"""INSERT INTO {cls._table} (name, company_name, country, industry, sector) VALUES (%(name)s, %(company_name)s) RETURNING id, name, company_name, country, industry, sector, in_use;""" try: conn.cursor.execute( query, { 'name': name, 'company_name': company_name, 'country': country, 'industry': industry, 'sector': sector }) pk, name, company_name, country, industry, sector, in_use = conn.cursor.fetchone( ) # pylint: disable=C0103 return Stock(pk=pk, name=name, company_name=company_name, country=country, industry=industry, sector=sector, in_use=in_use) except (psycopg2.DataError, psycopg2.ProgrammingError): message = f"Could not create Stock with name={name}, company_name={company_name}" logger.warning(message)
def get_stock_by_ids(cls, stock_ids): stock_ids = tuple(stock_ids) select_stocks_query = f"""SELECT id, name, company_name, country, industry, sector, in_use FROM {cls._table} WHERE id IN %(stock_ids)s""" with pool_manager() as conn: try: conn.cursor.execute(select_stocks_query, {'stock_ids': stock_ids}) stocks = conn.cursor.fetchall() stocks = [ Stock(pk=stock[0], name=stock[1], company_name=stock[2], country=stock[3], industry=stock[4], sector=stock[5], in_use=stock[6]) for stock in stocks ] return stocks except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError): message = "Could not get stocks" logger.warning(message)
def get_stocks(self): dashboard_has_stocks_table = 'public.dashboard_has_stocks' stocks_table = 'public.stocks' with pool_manager() as conn: get_stocks_id_query = f"""SELECT {dashboard_has_stocks_table}.stock_id,{stocks_table}.name, {stocks_table}.company_name, {stocks_table}.country, {stocks_table}.industry, {stocks_table}.sector, {stocks_table}.in_use FROM {dashboard_has_stocks_table} INNER JOIN {stocks_table} ON {dashboard_has_stocks_table}.stock_id = {stocks_table}.id WHERE {dashboard_has_stocks_table}.dashboard_hash = %(dashboard_hash)s;""" try: conn.cursor.execute(get_stocks_id_query, {'dashboard_hash': self.dashboard_hash}) list_of_stocks = conn.cursor.fetchall() list_of_stocks = [Stock(pk=stock[0], name=stock[1], company_name=stock[2], country=stock[3], industry=stock[4], sector=stock[5], in_use=stock[6]) for stock in list_of_stocks] return list_of_stocks except (psycopg2.ProgrammingError, psycopg2.DatabaseError, TypeError) as error: logger.info(f"Error Dashboard model get_stocks {error}")
def get_by_id(cls, pk: int) -> Stock: # pylint: disable=C0103 """ Get instance from database by id :param pk: instance id in database :return: instance from database """ with pool_manager() as conn: query = f"SELECT id, name, company_name, country, industry, sector, in_use FROM {cls._table} WHERE id = %(id)s" try: conn.cursor.execute(query, {'id': pk}) pk, name, company_name, country, industry, sector, in_use = conn.cursor.fetchone( ) return Stock(pk=pk, name=name, company_name=company_name, country=country, industry=industry, sector=sector, in_use=in_use) except (psycopg2.DataError, psycopg2.ProgrammingError, TypeError): message = f"Could not get stock by pk={pk}" logger.warning(message)